An almost idiot's guide to Install and Upgrade to PostgreSQL 8.4 with Yum

In this exercise, we'll go thru our steps for upgrading a Redhat Enterprise Linux 64-bit PostgreSQL box from PostgreSQL 8.3 to PostgreSQL 8.4. If you don't have any kind of PostgreSQL installed on your box, you can skip the Upgrade step.

UPDATE - since Devrim's move from Command prompt - he has started a new yum repository. You may want to use this one instead since it seems more up to date than the other. http://yum.pgrpms.org/

UPDATE

We have instructions for installing PostgreSQL 9.0 via yum.

Updgrading from PostgreSQL 8.* to PostgreSQL 8.4

If you are starting from scratch -- just skip this section.

If you are upgrading from 8.4.0 to 8.4.1 you can get away with a simple
yum update postgresql
and skip the rest of this article.

If you are upgrading from PostgreSQL 8.3 to 8.4, in theory you can use PgMigrator, but in practice, particularly with a Yum install, you are bound to run into obstacles. If you are running an older version, you must dump and restore.

PgMigrator difficulties

For starters we did try upgrading our database using the PgMigrator but were unsuccessful. Keep in mind these comments are not flames -- just things we see that are obstacles to upgrade and perhaps can be made less painful for future migration work and we consider important to increase PostgreSQL adoption.

This process turns out to be a bit more difficult if you are using Yum for a couple of reasons.

Yum and I think most distros always install in the same location -- the bin folder and bin/pgsql so its hard to tell which files you need and pgMigrator requires both the new binaries and the old binaries to complete. To work around that problem -- we did compile from scratch a PostgreSQL 8.3 that matched our 8.3 version by downloading the source and compile and setting --prefix== another location just to make the migrator happy.
pg_migrator was not happy with the location of pg_migrator.so -- this is probably a 64-bit issue and one we run into a lot. No biggies just symlink
ln -s /usr/lib64/pg_migrator.so /usr/local/pgsql84/lib/pg_migrator.so
The third obstacle we ran into was an insurmountable one. Or rather one we didn't really care to tackle because we figured trying to work around it would bite us in the future and also prevent us from using Yum which we really like using by the way. This is that the default date time storage between 8.3 and 8.4 has changed. So it seems to be able to migrate or at least on our 64-bit Linux box, we would need to recompile the PostgreSQL 8.4 to ignore which sounded like a future management nightmare waiting to happen. The error you get is this Old and new pg_controldata date/time storage types do not match. You will need to rebuild the new server with configure --disable-integer-datetimes or get server binaries built with those options.

I suspect this will become a non-issue in PostgreSQL 8.4 to 8.5

Please see Greg's notes about suggestions for this issue

Using the tried and true dump/restore but slower more space needed

We were upgrading 800 gb database which reduces down to a 80gb backup file. Space was a concern, but this is a dev box we wanted to blow out and have clean anyway. We had a good last night backup from production so we were in good shape to uninstall and just remove our data folder.

Note location of your old data cluster:


psql –h localhost –U postgres –p 5432
SELECT setting FROM pg_settings WHERE name='data_directory';

Gives something like:

             setting
---------------------------------
/var/lib/pgsql/data/

Make sure to backup at least the .conf files to different location which are located in same folder as data and also make backup of users and other global settings.
```
mkdir /pgbak
cp /var/lib/pgsql/data/*.conf /pgbak
pg_dumpall -h localhost -p 5432 -U postgres --globals-only > /pgbak/globals.sql

		
```
Make backup of each database or whole server. We liked compressed backups of each db since we don't always have space for a non-compressed and sometimes want to only selectively restore part of a db. Repeat the below for each db.
```
			/usr/bin/pg_dump -i -h localhost -U postgres -F c -b -v -f "/pgbak/mydb_beforeupgrade.backup" mydb
		
		
```
Shut down the old service.
service postgresql stop

Uninstall it.

yum erase postgresql

You should get a screen that looks something like this and choose y to uninstall:

Dependencies Resolved

================================================================================
 Package                Arch       Version                  Repository     Size
================================================================================
Removing:
 postgresql             x86_64     8.3.7-1PGDG.rhel5        installed     4.7 M
Removing for dependencies:
 postgresql-contrib     x86_64     8.3.7-1PGDG.rhel5        installed     1.3 M
 postgresql-devel       x86_64     8.3.7-1PGDG.rhel5        installed     4.9 M
 postgresql-plperl      x86_64     8.3.7-1PGDG.rhel5        installed      68 k
 postgresql-server      x86_64     8.3.7-1PGDG.rhel5        installed      12 M

Transaction Summary
================================================================================
Install      0 Package(s)
Update       0 Package(s)
Remove       5 Package(s)

Is this ok [y/N]: y

If you have space you can move your old cluster.
mv /var/lib/pgsql/data to /var/lib/pgsql/dataold
but if not and you know your backup is sound or this is a dev box just destroy it. You are destroying data so proceed with caution.
rm -rf /var/lib/pgsql/data

Installing PostgreSQL 8.4.1 from Yum repository

Figure out which OS you are on. Note the pgdn repository only works with CentOS and Redhat 4 and above, or Fedora.
```
uname –a
		
```
If you see a el5 you are most likely running an enterprise linux. If you see x64, then you have a 64-bit installation.
```
vi /etc/redhat-release
```
May help narrow it down.
Download the right file - from http://yum.pgsqlrpms.org/reporpms/8.4/. In our case we were running a redhat EL distro so we downloaded.
```
cd /pgbak
wget  http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-redhat-8.4-1.noarch.rpm 
		
```
Install the rpm
rpm -ivh pgdg-redhat-8.4-1.noarch.rpm

To get a list of postgresql related stuff:

yum list | grep postgresql

If you see postgresql from other repositories besides pgdg84, then you need to exclude postgresql from coming from other repositories by following the below instructions excerpted from PostgreSQL How to Yum

* As root, cd /etc/yum.repos.d

    * Edit distro's .repo file:
          o On Fedora, edit fedora.repo and fedora-updates.repo, [fedora] sections
          o On CentOS, edit CentOS-Base.repo, [base] and [updates] sections.
          o On Red Hat, edit edit /etc/yum/pluginconf.d/rhnplugin.conf [main] section.
                + Add to the bottom of the section:

                  exclude=postgresql*

To get a listing of what is available in the PostgreSQL 8.4.1 yum
yum list | grep pgdg84
Install what you want: The developer package is needed to compile things like PostGIS if you are going to compile your own. the name would be different if you are on a 32-bit box.
```
yum install postgresql-devel.x86_64
yum install postgresql-server
yum install postgresql-contrib
yum install postgresql-plperl 
```
If you decide to use non-default location for data, you need to edit the postgresql ~~service~~ sysconfig file and change the PGDATA argument.
~~vi /etc/rc.d/init.d/postgresql~~
vi /etc/sysconfig/pgsql/postgresql
(For vi you do a I to insert a line -- ESC then :w to save and exit) NOTE: postgresql in sysconfig/pgsql may not exist so you may need to create it and put in a line denoting where you want the data -- something like below
PGDATA=/var/lib/pgsql/data

Create data cluster:


mkdir /var/lib/pgsql/data
chown postgres /var/lib/pgsql/data
su postgres
initdb -D /var/lib/pgsql/data

service postgresql initdb

Set postgresql service restart automatically on reboots.

su root
chkconfig --list (to see list of services)
chkconfig postgresql on

Start the service.
service postgresql start

Copy config, Restore accounts -- this is needed only if you were upgrading.

		
cp /pgbak/pg_hba.conf /yourdatacluster/
(NOTE: for postgresql.conf -- you are best manually editing that since its changed considerably from 8.3 to 8.4)
vi /var/lib/pgsql/data/postgresql.conf (-- use your old file as reference)
psql -U postgres -d postgres -f /pgbak/globals.sql

Install pgadmin pack if you want to be able to change config settings directly from pgAdmin III
psql -U postgres -d postgres -f /usr/share/pgsql/contrib/adminpack.sql
Restart for whatever config changes you made to take effect
service postgresql restart
If you have database backups, first create a blank db for each and then restore with pg_restore.