michael orlitzky

Upgrading PostgreSQL 9.x on Gentoo

posted 2012-02-13; updated 2015-06-04

Update 2015-06-04
Fuck this whole article, please just use pg_dump and pg_restore to dump/restore everything before/after the upgrade. It's easier, trust me.

Hello

This is here because most upgrade instructions don't work too well on Gentoo. This is not really Gentoo's fault at the moment; the pg_upgrade tool makes you jump through hoops, but it's still miles ahead of pg_dump and pg_restore.

In the following, OLD will refer to your old version. For example, 9.0. Likewise, NEW will refer to the new version, e.g. 9.1.

Configure the new Postgres

  1. Edit /etc/conf.d/postgresql-NEW and set PG_INITDB_OPTS="--locale=en_US.UTF-8".
  2. Run emerge --config dev-db/postgresql-server:NEW, and say yes to create data files for the new version.
  3. Add local all all trust to both /etc/postgresql-{OLD,NEW}/pg_hba.conf.
  4. Migrate postgresql.conf from /etc/postgresql-OLD/ to /etc/postgresql-NEW/.

Copy your Configuration to the Data Directory

No, this doesn't make sense, but pg_upgrade doesn't let you tell it where the config files are stored.

root # cp /etc/postgresql-OLD/*.conf /var/lib/postgresql/OLD/data/

root # cp /etc/postgresql-NEW/*.conf /var/lib/postgresql/NEW/data/

root # chown postgres:postgres /var/lib/postgresql/OLD/data/*.conf

root # chown postgres:postgres /var/lib/postgresql/NEW/data/*.conf

Stop the Old Postgres

Run /etc/init.d/postgres-OLD stop to stop the old instance. Neither of them can be running during the upgrade. If you'd like to check the new one before the upgrade, you can start/stop it here.

Switch User and Upgrade

We eselect the new version first so that we get the correct pg_upgrade command…

root # eselect postgresql set NEW

Now we can upgrade,

root # su - postgres

postgres $ cd /var/lib/postgresql

postgres $ pg_upgrade -d /var/lib/postgresql/OLD/data/ -D /var/lib/postgresql/NEW/data/ -b /usr/lib/postgresql-OLD/bin/ -B /usr/lib/postgresql-NEW/bin/

Switch System Versions

Assuming that worked, let's switch from the old version to the new.

root # rc-update del postgresql-OLD default

root # rc-update add postgresql-NEW default

Clean Up

Get rid of junk from the old version.

root # rm /etc/conf.d/postgresql-OLD

root # rm -rf /var/lib/postgresql/OLD

root # rm -rf /etc/postgresql-OLD

root # emerge -C =dev-db/postgresql-server-OLD

And the conf files we stuck in the data directory…

root # rm /var/lib/postgresql/NEW/data/*.conf

Secure the New Version

First, edit /etc/postgresql-NEW/pg_hba.conf and remove the local all all trust line that we added earlier.

Finally, make sure the permissions are set correctly on the configuration files:

root # chown postgres:postgres /etc/postgresql-NEW/*

root # chmod 600 /etc/postgresql-NEW/*

Start the New Version

You were going to forget, I know you were.