Fixing PostgreSQL database after automatic upgrade (Fedora)

Hi,
I’m posting this here so that you don’t have to work your way through it yourself.

I recently preformed a FC15->16 upgrade, thus upgrading my postgresql-server installation (9.0 -> 9.1). To my surprise, when I tried to start the server, I got the following error:

[root@PC10CIS pgsql]# service postgresql start
Redirecting to /bin/systemctl  start postgresql.service
Job failed. See system logs and 'systemctl status' for details.

So I did. In the logs, I found:

Dec 22 09:56:35 PC10CIS postgres[4536]: [1-1] FATAL:  database files are incompatible with server
Dec 22 09:56:35 PC10CIS postgres[4536]: [1-2] DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.2.
Dec 22 09:56:35 PC10CIS pg_ctl[4532]: FATAL:  database files are incompatible with server
Dec 22 09:56:35 PC10CIS pg_ctl[4532]: DETAIL:  The data directory was initialized by PostgreSQL version 9.0, which is not compatible with this version 9.1.2.
Dec 22 09:56:40 PC10CIS pg_ctl[4532]: pg_ctl: could not start server
Dec 22 09:56:40 PC10CIS pg_ctl[4532]: Examine the log output.
Dec 22 09:56:40 PC10CIS systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 22 09:56:40 PC10CIS systemd[1]: Unit postgresql.service entered failed state.

So i thought, OK let’s run pg_upgrade. But since the old bin dir of postgresql-server was gone, I could not do it. So here are the steps:

1. Move the old database files

   $ sudo mv /var/lib/pgsql/data{,.old}

2. Initialize a new database

   $ sudo su - postgres -c "initdb -D /usr/local/pgsql/data"

3. Go to http://pkgs.org and find the version you had previously installed (worry only about the first two numbers (as in 9.0.5 – the major version is 9.0)) and download it
4. Then, use rpm2cpioto extract the files, like that:

   $ mkdir -p /tmp/pgsql-install ; cd /tmp/pgsql-install ; rpm2cpio ~/Downloads/postgresql-server-9.0.4-1.fc15.x86_64.rpm | cpio -idmv

5. Run the upgrade
    $ su - postgres -c "pg_upgrade -b /tmp/pgsql-install/usr/bin/ -B /usr/bin/ -d /usr/local/pgsql/data.old -D /usr/local/pgsql/data"

6. Copy over old config files

   $ sudo cp /usr/local/pgsql/data{.old,}/pg_hba.conf  ; sudo cp /usr/local/pgsql/data{.old,}/postgresql.conf

And now you’re good to go!

Advertisement