Instalar o actualizar PostgreSQL 8.4 con 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/

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

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.