Technology Insights

Upgrade PostgreSQL 8.3 To PostgreSQL 8.4 On A CentOS Server Using Yum

Insights4 Comments

I have been needing to upgrade the PostgreSQL servers at my work for awhile now and was finally forced to do so after needing some of the features available with PostgreSQL 8.4.X. Currently we are running PostgreSQL 8.3.10 which is super old but it is definitely not the latest stable release available. One thing I am looking forward to testing is the improvements to the vacuum process. Over the past two years I have become a lot more familiar with Postgres and one thing that has been disappointing is the downtime involved in reclaiming disk space via data removed from PSQL database tables. The full vacuum process, which is required to reclaim disk after deleting data from a table, in 8.3 requires a lock on tables which essentially causes downtime. When you are dealing with large tables over 10GB in size this can take days to complete. Follow the directions below to first backup all of your PostgreSQL data, remove the current PostgreSQL installation, and then install PostgreSQL 8.4 on a CentOS Linux server.

Technology Insights

No operator matches the given name and argument type(s). You might need to add explicit type casts.

Insights3 Comments

When working on writing some directions for someone at work I ran into a PostgreSQL command line query error. I have much more experience using MySQL so my initial Postgres commands always mimic what I would do in MySQL. Anyhow when attempting to run a SELECT statement matching contents of a specific column I received the error below. After a little research I came up with the answer fairly easily but wanted to note here to help others out and serve as a reference point if I forgot the proper PSQL query in the future. Below I describe the query that caused the error, the error returned by Postgres, and the proper query to use when attempting to match a variable in a Postgres SELECT statement.

Technology Insights

PostgreSQL 8.3: postgres wont start with stats_start_collector = on

InsightsLeave a comment

PostgreSQL 8.3 changed the way the Query and Index Statistics are configured from previous versions of postgreSQL including 8.2 and below. Things are technically easier now as the stats collecting is turned on by default and there are less commands to enter into the configuration to get all of the stats you will need. I had to look into this problem as I wanted to install some cacti postgres templates and the configuration stated to add the following lines to the postgresql.conf configuration file.