Upgrading PostgreSQL Databases

One day after I upgraded my Fedora Linux box, I found that I could not start the PostgreSQL database system service. Examining logs, i.e., /var/log/messages in my case, I found the following log messages,

Oct 23 21:33:56 localhost postgresql-check-db-dir: 
       An old version of the database format was found.
Oct 23 21:33:56 localhost postgresql-check-db-dir: 
       You need to dump and reload before using PostgreSQL 9.3.
Oct 23 21:33:56 localhost postgresql-check-db-dir: 
       See /usr/share/doc/postgresql/README.rpm-dist for more information.
Oct 23 21:33:56 localhost systemd: 
       postgresql.service: control process exited, code=exited status=1
Oct 23 21:33:56 localhost systemd: 
       Failed to start PostgreSQL database server.
Oct 23 21:33:56 localhost systemd: 
       Unit postgresql.service entered failed state.

The above message basically indicates that the database file is in an old format and the file format is no longer supported by the new version of the PostgreSQL database system, in the above, not supported by PostgreSQL 9.3. The solution is to upgrade the database file to the new format supported by the new version of PostgreSQL. According to /usr/share/doc/postgresql/README.rpm-dist, there are two options.

• Option 1.

  If you are upgrading across more than one major release of PostgreSQL
  (for example, from 8.3.x to 9.0.x), you will need to follow the "traditional"
  dump and reload process to bring your data into the new version.  That is:
  *before* upgrading, run pg_dumpall to extract all your data into a SQL file.
  Shut down the old postmaster, upgrade to the new version RPMs, initdb,
  and run the dump file through psql to restore your data.

• Options 2.

  In some major releases, the RPMs also support in-place upgrade from the immediately previous major release. Currently, you can upgrade in-place from 9.2.x to 9.3.x.

We now need to determine what version of the PostgreSQL whose format the database files are in, for which we go to the database file directory. In Fedora Linux, by default, it is at /var/lib/pgsql. We can then determine the version of PostgreSQL as follows.

$ sudo cat /var/lib/pgsql/data/PG_VERSION
9.1

Since PostgreSQL is version 9.1, we will have to use Option 1 described above. The Option 1 requires us to use pg_dumpall of the old version of PostgreSQL, in this case, version 9.1 to dump the content of the databases to a SQL file. If you observe the eror message mentioned at the beginning of this post, it is very likely that the old version of PostgreSQL has been upgraded to the new version and the old version does not exist on your Linux system.

To upgrade the database file format, I use a workaround in which I download and install the old version of PostgreSQL and use it to complete the database upgrade process as shown below.

1. Open a terminal window and prepare a directory in which we will install the old version of PostgreSQL.

   
     mkdir -p $HOME/pgold/source
     

2. Download the source code of the olde version of PostgreSQL. In my case, the old version of PostgreSQL is 9.1. Source code of old versions of PostgreSQL are available at the PostgreSQL's web site.

   
     cd $HOME/pgold/source
     wget -c \
       https://ftp.postgresql.org/pub/source/v9.1.14/postgresql-9.1.14.tar.bz2
     

3. We now extract the source code, compile, and install the old version of PostgreSQL to directory $HOME/pgold/postgresql.

   
     tar xjvf postgresql-9.1.14.tar.bz2
     cd postgresql-9.1.14
     ./configure --prefix=$HOME/pgold/postgresql
     make
     make install
     

4. We now run an instance of the old version of PostgreSQL in foreground. Typically, the PostgreSQL service is running under username postgres. We will first become postgres and then run the old version of PostgreSQL as follows.

   
     sudo -s       # to become root
     su - postgres # to become postgres
     $HOME/pgold/postgresql/bin/postmaster -D /var/lib/pgsql/data
     

5. Open a second terminal window and dump the content of the old databases to a SQL file.

   
     # in the second terminal window
     sudo -s       # to become root
     su - postgres # to become postgres
     pg_dumpall > /var/lib/pgsql/postgresql_olddb.sql
     

   If you somehow do not possess the PostgreSQL's password of user postgres or any other database administrator's credential and cannot run pg_dumpall without a successful authentication, you can edit the PostgreSQL Client Authentication Configuration File /var/lib/pgsql/data/pg_hba.conf to trust any Unix domain socket connections to have a line as follows,

   
     # in /var/lib/pgsql/data/pg_hba.conf
     local   all  all  trust
     

   You will have to restart the old PostgreSQL program by simply killing processing using CTRL-C and running the postmaster again as described above.
6. You can now shutdown the old PostgreSQL program. You will go to the terminal window where the postmaster program is running foreground and press CTRL-C to kill the process.
7. Now backup the old database files. In case of a failure to complete the process, we can restore the database to its original state using the backup. In either the first or the second terminal window, you should remain as user postgres.

   
     mv /var/lib/pgsql/data /var/lib/pgsql/data_olddb.backup
     

8. Now initialize database files using binaries of the new version of PostgreSQL in either terminal window in which you should remain as user postgres.

   
     /bin/initdb -D /var/lib/pgsql/data
     

9. In one of the two terminal window, exit to your shell and then start the new PostgreSQL service.

   
     exit # exit to your shell
     sudo systemctl start postgresql.service
     

   Now you should be able to start the new PostgreSQL service.
10. We now import the content of the old database to the new database. You will need to go to the terminal window in which you remain as user postgres.

    
      psql < /var/lib/pgsql/postgresql_olddb.sql
      

We are done and can now celebrate.