Project

General

Profile

PostgreSQL upgrade

Scope

This page documents the setup and use of pg_upgrade to upgrade the chimera, enstore-db, and other PostgreSQL database clusters from version 9.2 to 9.3. The 9.2 database cluster cannot otherwise be used with PostgreSQL 9.3. An alternative, one which is outside the scope of this page, is to use pg_dump followed by pg_restore.

Substitutions

  • DBUSER refers to the user as which the PostgreSQL service and/or the postmaster process is run. For chimera, it is expected to be enstore. For enstore-db and others, it is expected to be products.
  • DATADIR refers to the data directory path for the database cluster. Note that it is intended for the directory name to remain the same after the upgrade. To clarify, this is the directory that contains the postgresql.conf file.

Requirements

To optionally cache the needed RPMs in advance, refer to PostgreSQL RPMs download only.

PostgreSQL 9.2

It is assumed that PostgreSQL 9.2 is already installed, configured, and functional. Nevertheless, the following are the steps to meet this requirement:

  • Install pgdg. Note that specifying the rpm --nodeps option is one way to work around the unsatisfied sl-release package dependency.
    • If using SLF6:
      [root@host ~]# rpm --nodeps -iv http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-sl92-9.2-8.noarch.rpm
    • If using SLF5:
      [root@host ~]# rpm --nodeps -iv http://yum.postgresql.org/9.2/redhat/rhel-5-x86_64/pgdg-sl92-9.2-8.noarch.rpm
  • Install postgresql92 and associated packages.
    [root@host ~]# yum -y install postgresql92 postgresql92-devel postgresql92-libs postgresql92-server postgresql92-contrib

    If the installation terminates with an error indicating a missing uuid dependency, first install or upgrade the missing package:

    [root@host ~]# yum -y --enablerepo=epel install uuid

  • For chimera, PostgreSQL will be run as a service under a non-default user. In this case, configure the service. This step can be skipped for enstore-db and others which are not run as a service.
    [root@host ~]# sed -i'-original' -e 's/postgres:postgres/enstore:enstore/g' -e 's/l postgres /l enstore /g' /etc/init.d/postgresql-9.2
  • For chimera, configure the data directory, as for example:
    [root@host ~]# echo "PGDATA=/srv1/pnfs/db/psql-data" > /etc/sysconfig/pgsql/postgresql-9.2

    PGOPTS="i" does not have to be entered in the target file, as "listen_addresses = '*'" can be configured in a .conf file instead.

  • For chimera, ensure the service is functional with the database cluster which is to be upgraded.

PostgreSQL 9.3

  • Install pgdg. Note that specifying the rpm --nodeps option is one way to work around the unsatisfied sl-release package dependency.
    • If using SLF6:
      [root@host ~]# rpm --nodeps -iv http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-sl93-9.3-1.noarch.rpm
      * If using SLF5:
      [root@host ~]# rpm --nodeps -iv http://yum.postgresql.org/9.3/redhat/rhel-5-x86_64/pgdg-sl93-9.3-1.noarch.rpm
  • Install postgresql93 and associated packages.
    [root@host ~]# yum -y install postgresql93 postgresql93-devel postgresql93-libs postgresql93-server postgresql93-contrib

    If the installation terminates with an error indicating a missing uuid dependency, first install or upgrade the missing package:

    [root@host ~]# yum -y --enablerepo=epel install uuid

  • Configure the hashed binaries and man pages for the necessary PostgreSQL programs for the correct version of PostgreSQL. Failing this, Enstore may upon reboot attempt to start the affected databases with the wrong version of PostgreSQL. Run the following idempotent procedure as root:
    VER="9.3" 
    
    for PROG in "initdb" "pg_ctl" "pg_config" "postmaster" "psql"; do
    
      # Configure program:
      rm -f /var/lib/alternatives/pgsql-${PROG};
      /usr/sbin/alternatives --install /usr/bin/${PROG} pgsql-${PROG} /etc/alternatives/pgsql-${PROG} 920 --slave /etc/alternatives/pgsql-${PROG} pgsql-${PROG} /usr/pgsql-${VER}/bin/${PROG};
    
      # Configure man page:
      rm -f /var/lib/alternatives/pgsql-${PROG}man;
      /usr/sbin/alternatives --install /usr/share/man/man1/${PROG}.1 pgsql-${PROG}man /etc/alternatives/pgsql-${PROG}man 920 --slave /etc/alternatives/pgsql-${PROG}man pgsql-${PROG}man /usr/pgsql-${VER}/share/man/man1/${PROG}.1;
    
    done
    
  • For chimera, PostgreSQL will be run as a service under a non-default user. In this case, configure the service. This step can be skipped for enstore-db and others which are not run as a service.
    [root@host ~]# sed -i'-original' -e 's/postgres:postgres/enstore:enstore/g' -e 's/l postgres /l enstore /g' /etc/init.d/postgresql-9.3
  • For chimera, configure the intended data directory, as for example:
    [root@host ~]# echo "PGDATA=/srv1/pnfs/db/psql-data" > /etc/sysconfig/pgsql/postgresql-9.3

    Alternatively, to use the pre-existing data directory:
    [root@host ~]# cp -a /etc/sysconfig/pgsql/postgresql-9.2 /etc/sysconfig/pgsql/postgresql-9.3

    PGOPTS="i" does not have to be entered in the target file, as "listen_addresses = '*'" can be configured in a .conf file instead.

  • For chimera, enable the service for the relevant runlevels. The service must however remain stopped.
    [root@host ~]# /sbin/chkconfig postgresql-9.3 on
    [enstore@host ~]# /sbin/chkconfig --list postgresql-9.3
    postgresql-9.3     0:off    1:off    2:on    3:on    4:on    5:on    6:off
    

Preparation

Stop 9.2 process

  • Ensure all connected replicas, if any, are first stopped. The replicas can be reinitialized with the matching version after the primary has been upgraded.
    [root@replica_host ~]$ /sbin/service postgresql-9.2 stop
    Stopping postgresql-9.2 service:                           [  OK  ]
    
  • Ensure the postgresql-9.2 service is both stopped and disabled.
    [root@host ~]$ /sbin/service postgresql-9.2 stop
    Stopping postgresql-9.2 service:                           [  OK  ]
    [enstore@host ~]$ /sbin/service postgresql-9.2 status
    postgresql-9.2 is stopped
    
    [root@host ~]$ /sbin/chkconfig postgresql-9.2 off
    [enstore@host ~]$ /sbin/chkconfig --list postgresql-9.2
    postgresql-9.2     0:off    1:off    2:off    3:off    4:off    5:off    6:off
    
  • For enstore-db and others that are not run as a service, identify and stop the associated postmaster processes. To assist in identification, also check the Enstore configuration to confirm the directories and ports for the PostgreSQL instances.
    [root@host ~]# su - products -s /bin/bash

    If the error "id: cannot find name for group ID 4525" is returned, first add the missing group.

    [root@host ~]# groupadd --gid 4525 products

    Proceed.
    -bash-3.2$ source ~enstore/.bashrc
    
    [products@host ~]$ ps aux | grep [p]ostmaster
    products 26669  0.0  0.3 261948 13432 pts/2    S    Feb19   0:32 postmaster -p 9999 -D /srv1/enstore/databases1/enstore-db -i
    products 26690  0.0  0.3 261944 13404 pts/2    S    Feb19   0:04 postmaster -p 9900 -D /srv1/enstore/databases1/accounting-db -i
    products 26704  0.0  0.3 261940 13396 pts/2    S    Feb19   0:00 postmaster -p 9902 -D /srv1/enstore/databases1/drivestat-db -i
    
    [products@host ~]$ /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv1/enstore/databases/enstore-db
    [products@host ~]$ /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv1/enstore/databases/accounting-db
    [products@host ~]$ /usr/pgsql-9.2/bin/pg_ctl stop --mode fast --pgdata /srv1/enstore/databases/drivestat-db
    

Initialize 9.3 cluster

For each database cluster which is to be upgraded:

  • Confirm that sufficient free space exists in the filesystem which will be used to store the upgraded database. The amount of disk space required is approximately equal to what is currently used by the data directory.
    [DBUSER@host ~]$ du -hs DATADIR
    118G    DATADIR
    
    [DBUSER@host ~]$ df -h DATADIR
    Filesystem            Size  Used Avail Use% Mounted on
    /dev/mapper/vg_raid1-en_raid1
                          1.4T  368G  938G  29% /srv1
    

    In the above example, about 118G is required and 938G is available.
  • Append a suffix, e.g. "-9.2", to its directory name. This allows the original directory name to be reused for the upgraded database cluster.
    [DBUSER@host ~]$ mv DATADIR DATADIR-9.2
  • Create the intended data directory:
    [root@host ~]$ mkdir -m 0700 DATADIR
    [root@host ~]$ chown DBUSER:DBUSER DATADIR
    

    Note that it may not be possible to create the directory directly by DBUSER due to insufficient permissions.
  • Initialize the database cluster in its designated directory:
    [DBUSER@host ~]$ /usr/pgsql-9.3/bin/initdb --pgdata=DATADIR

Make work directory

  • Create and change to a temporary directory from which the upgrade can be run. Some temporary files will be written in this directory. This step is not essential but it is recommended.
    [DBUSER@host ~]$ mkdir -p /tmp/pg_upgrade_run
    [DBUSER@host ~]$ cd /tmp/pg_upgrade_run/
    

Procedure

For each database cluster which is to be upgraded:

Upgrade

  • Run the following multiline command. Note that it contains "time" for analytical purposes only. Ensure the values of old-datadir and new-datadir are accurate.
    [DBUSER@host pg_upgrade_run]$ time /usr/pgsql-9.3/bin/pg_upgrade \
    --link \
    --old-datadir=DATADIR-9.2 \
    --new-datadir=DATADIR \
    --old-bindir=/usr/pgsql-9.2/bin/ \
    --new-bindir=/usr/pgsql-9.3/bin/
    

    pg_upgrade writes temporary log files to the current directory - these are automatically deleted. It also writes two scripts, namely analyze_new_cluster.sh and delete_old_cluster.sh - these are preserved.

    The "--jobs" option of pg_upgrade was tested and observed to not speed the upgrade. This is because the upgrade is I/O bound, not CPU bound.

Configure

  • The existing database configuration files in new-datadir must now be configured. These files include postgresql.conf, pg_hba.conf, and those matching recovery.*. This step is not automatic. The configuration must conform to PostgreSQL 9.3. At minimum, the value of log_directory in postgresql.conf must be valid and writable, failing which PostgreSQL will fail to start. Identify the differences between the configuration files and then manually copy over the relevant settings:
    [DBUSER@host ~]$ diff -y -W $COLUMNS --suppress-common-lines DATADIR-9.2/postgresql.conf DATADIR/postgresql.conf | less
    
    [DBUSER@host ~]$ diff -y -W $COLUMNS DATADIR-9.2/pg_hba.conf DATADIR/pg_hba.conf | less
    
    [DBUSER@host ~]$ cp -av DATADIR-9.2/recovery.* DATADIR/
    
  • For chimera, start the service:
    [root@host ~]# /sbin/service postgresql-9.3 start
  • For enstore-db and others that are not run as a service, identify and start the associated postmaster processes:
    [products@host ~]$ /usr/pgsql-9.3/bin/pg_ctl start --pgdata DATADIR
  • Check the startup log to confirm that PostgreSQL is running normally:
    [DBUSER@host ~]$ ls -tr /srv1/postgres-log/* | tail -1 | xargs tail -vn +1

Confirm

  • Confirm that the database cluster is running as expected:
    [DBUSER@host ~]$ /usr/pgsql-9.3/bin/psql -qt -p <PORT> -d postgres -c 'SHOW server_version;'
     9.3.8
    [DBUSER@host ~]$ /usr/pgsql-9.3/bin/psql -qt -p <PORT> -d postgres -c '\l'
    

Analyze

  • With the service running, run the post-upgrade script analyze_new_cluster.sh. It internally runs "/usr/pgsql-9.3/bin/vacuumdb --all --analyze-only". As of 2014, it was observed to require 5 minutes with the stken chimera database cluster.
    [DBUSER@host pg_upgrade_run]$ time ./analyze_new_cluster.sh
  • Disregard the delete_old_cluster.sh script. The pg_upgrade_run directory can now be deleted.

Finalization

Dependents

Identify and update any scripts that have the older version of PostgreSQL hardcoded. These include but are not limited to cron jobs.

Cleanup

Uninstall v9.2 of pgdg and PostgreSQL:

[root@host ~]# yum remove pgdg-sl92 postgresql92 postgresql92-libs

Reconfirm

After all database clusters on the host have been upgraded, reboot the host once. After the reboot, for each database cluster:

  • Confirm that it started automatically and correctly.
  • Reconfirm that it is running the correct server version.

Revert

If the --link option for pg_upgrade was used, it is not possible to revert the upgrade. If the option was not used and PostgreSQL 9.2 must be returned to use:

  • Ensure the PostgreSQL 9.3 service if stopped and disabled and that the postmaster process is stopped.
  • Move the directory DATADIR-9.2 back to its original location, i.e. DATADIR.
  • Remove the suffix ".old" from the "$PGDATA/global/pg_control" file if it exists. Although the suffix was not observed to exist in tests, this is a documented instruction.
    [DBUSER@host DATADIR] mv ./global/pg_control.old ./global/pg_control
  • Enable the PostgreSQL 9.2 service or start the corresponding postmaster process.

Resources

  1. http://www.postgresql.org/docs/9.3/static/upgrading.html
  2. http://www.postgresql.org/docs/9.3/static/pgupgrade.html
  3. http://blog.55minutes.com/2013/09/postgresql-93-brew-upgrade/
  4. http://www.uptimemadeeasy.com/databases/upgrade-postgresql/