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

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.

1. 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.
2. 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

3. 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.

1. 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/
   

2. 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
   
   		

3. 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
   		
   		

4. Shut down the old service.

   service postgresql stop

5. 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
   		

6. 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

1. 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.

2. 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 
   		

3. Install the rpm

   rpm -ivh pgdg-redhat-8.4-1.noarch.rpm

4. 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*
   

5. To get a listing of what is available in the PostgreSQL 8.4.1 yum

   yum list | grep pgdg84

6. 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 

7. 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

8. 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

9. Set postgresql service restart automatically on reboots.

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

10. Start the service.

    service postgresql start

11. 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
    		

12. 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

13. Restart for whatever config changes you made to take effect

    service postgresql restart
14. If you have database backups, first create a blank db for each and then restore with pg_restore.