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.
-
Open a terminal window and prepare a directory in which we will install the old version of PostgreSQL.
mkdir -p $HOME/pgold/source
-
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
-
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
-
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
-
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.
-
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.
-
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
-
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
-
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.
-
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.
