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.
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.
/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_dumpallof 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
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
postgresand 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.
If you somehow do not possess the PostgreSQL's password of user
# in the second terminal window sudo -s # to become root su - postgres # to become postgres pg_dumpall > /var/lib/pgsql/postgresql_olddb.sql
postgresor any other database administrator's credential and cannot run
pg_dumpallwithout a successful authentication, you can edit the PostgreSQL Client Authentication Configuration File
/var/lib/pgsql/data/pg_hba.confto trust any Unix domain socket connections to have a line as follows,
You will have to restart the old PostgreSQL program by simply killing processing using
# in /var/lib/pgsql/data/pg_hba.conf local all all trust
CTRL-Cand running the
postmasteragain as described above.
You can now shutdown the old PostgreSQL program. You will go to the terminal window where the
postmasterprogram is running foreground and press
CTRL-Cto 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
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
/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.
Now you should be able to start the new PostgreSQL service.
exit # exit to your shell sudo systemctl start 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
psql < /var/lib/pgsql/postgresql_olddb.sql