[Solved] Implementing PostgreSQL 9.0

[Guest Geoff]

Just wondering when PostgreSQL 9.0 will be installed. Not really a priority, but some users might want it, and I was wanting to move my site to PostgreSQL, but found that the version was too old.

 

Thanks!

[Byron]

This support request is being escalated to our root admin.

[Ashoat]

It's looking pretty indefinite at this point. Currently, cPanel/WHM depends on OS vendors for PostgreSQL versions, and RHEL/CentOS currently has it at 8.1. No idea when they'll push 9.0... RedHat is usually pretty conservative with software versions.

 

There's a cPanel feature request here for the company to maintain its own version of PostgreSQL.

[Guest Geoff]

Well, I am not really an what you would call an expert on this subject, but I found a few blog posts that might help:

 

http://people.planetpostgresql.org/devrim/...CentOSRHEL.html

http://davidghedini.blogspot.com/2010/10/i...-on-centos.html

[Ashoat]

Took me a while, but I think that it worked out. Is it looking like upgraded from your end?

[Guest Geoff]

Well, CPanel claims that the PostgreSQL server is offline, but I can connect using a PHP script.

 

However, when connecting with a PHP script, the version is still at 8.1.

 

[Ashoat]

That's weird. PostgreSQL seems to be installed...

[Guest Geoff]

PHP Script Source:

 

<?php

 

 

pg_connect("host=localhost dbname=postgres port=5432 user=gfishing password=****");

 

$res = pg_query("SELECT version()");

 

echo var_dump(pg_fetch_array($res));

 

Result of the Script:

 

array(2) {

[0]=>

string(105) "PostgreSQL 8.1.22 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)"

["version"]=>

string(105) "PostgreSQL 8.1.22 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-48)"

}

 

(Notice that the version is 8.1, not 9.x)

CPanel Screenshot:

[Ashoat]

I'm not entirely sure what's happening. My guess is that I need to uninstall PostgreSQL 8.1 before I install 9.0, but I fear that running something like "yum remove pgsql" will result in all the databases being wiped out.

[Guest Geoff]

The procedure to upgrade PostgreSQL is below:

 

15.4. Upgrading

These instructions assume that your existing installation is under the /usr/local/pgsql directory, and that the data area is in /usr/local/pgsql/data. Substitute your paths appropriately.

 

The internal data storage format typically changes in every major release of PostgreSQL. Therefore, if you are upgrading an existing installation that does not have a version number of "9.0.x", you must back up and restore your data. If you are upgrading from PostgreSQL "9.0.x", the new version can use your current data files so you should skip the backup and restore steps below because they are unnecessary.

 

If making a backup, make sure that your database is not being updated. This does not affect the integrity of the backup, but the changed data would of course not be included. If necessary, edit the permissions in the file /usr/local/pgsql/data/pg_hba.conf (or equivalent) to disallow access from everyone except you.

 

To back up your database installation, type:

 

pg_dumpall > outputfile

If you need to preserve OIDs (such as when using them as foreign keys), then use the -o option when running pg_dumpall.

 

To make the backup, you can use the pg_dumpall command from the version you are currently running. For best results, however, try to use the pg_dumpall command from PostgreSQL 9.0.2, since this version contains bug fixes and improvements over older versions. While this advice might seem idiosyncratic since you haven't installed the new version yet, it is advisable to follow it if you plan to install the new version in parallel with the old version. In that case you can complete the installation normally and transfer the data later. This will also decrease the downtime.

 

Shut down the old server:

 

pg_ctl stop

On systems that have PostgreSQL started at boot time, there is probably a start-up file that will accomplish the same thing. For example, on a Red Hat Linux system one might find that this works:

 

/etc/rc.d/init.d/postgresql stop

If restoring from backup, rename or delete the old installation directory. It is a good idea to rename the directory, rather than delete it, in case you have trouble and need to revert to it. Keep in mind the directory might consume significant disk space. To rename the directory, use a command like this:

 

mv /usr/local/pgsql /usr/local/pgsql.old

Install the new version of PostgreSQL as outlined in Section 15.5.

 

Create a new database cluster if needed. Remember that you must execute these commands while logged in to the special database user account (which you already have if you are upgrading).

 

/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

Restore your previous pg_hba.conf and any postgresql.conf modifications.

 

Start the database server, again using the special database user account:

 

/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data

Finally, restore your data from backup with:

 

/usr/local/pgsql/bin/psql -d postgres -f outputfile

using the new psql.

 

Further discussion appears in Section 24.4, including instructions on how the previous installation can continue running while the new installation is installed.

 

These mention how to install PostgreSQL 9 without removing all of the databases.

[Ashoat]

Not quite. The only parts there relevant to our installation involve restarting the daemon. The issue for us is that we have /both/ PostgreSQL 8 and PostgreSQL 9 installed, and the binaries are linked up to use PostgreSQL 8 instead of 9. We need to remove PostgreSQL 8, but we have to make sure we don't delete the databases in the process.

[Guest Geoff]

Yes, but if you back up the data on the old 8.x server, and restore it on the 9.x server, you won't lose any data.

 

It tells you how to back up and restore in the instructions I posted.

[Ashoat]

The thing is, if this operation fails, PostgreSQL will be down for a while and it might take me a lot of effort to take it up again. Also, backup operations can be very disk intensive... and HelioHost's disk IO is always at a peak.

 

Is there a particular reason you need version 9?

[Guest Geoff]

Drupal 7 only supports PostgreSQL version 9. I was thinking that it wouldn't be too much trouble to upgrade versions, but I guess I was wrong. Maybe later in the future, after charlie is up.

[jje]

Err, isn't Drupal compatible with MySQL?