Postgresql – How to use pg_upgrade on arch linux

arch-linuxpostgresql

I was running postgres 8.4 on arch but now have 9 installed I want to use pg_upgrade to upgrade but I don't know what all the options I have to use are, and all the directories that I have to point it to. it asks for a previous pg binary directory… but that's gone. Will it still work? note: I've no important data, just play db's I just want to know how to do it.

Best Answer

I'm sure you've read the documentation but just in case you haven't - "F.32. pg_upgrade".

The step-by-step instructions are especially detailed.

As a minumum you need to provide values for --old-datadir, --new-datadir, --old-bindir and --new-bindir options.

If your old installation is missing you could always (I hope so, I've never used Arch Linux) re-install it without overwriting the old data directory. If your distribution doesn't facilitate that you could re-install (temporarily) the old version from source.

Then you could run pg_upgrade. Remember to configure the two co-existing installations (the new one and the old one) to use different ports. And also set (temporarily) the local access method for both to "trust".

Related Solutions

Postgresql – n easy way to partially backup Postgres

You can try the COPY command, but note that this sucks for incremental backups.

Another (possibly better depending) option may be a hot standby server that you shut down and take (filesystem-level) backups of periodically - This lets you use smarter tools which may take a binary diff of the DB table files and not have to suck down the whole thing every time you make a backup.
The other advantage is you have a well-knwon and well-supported recovery path this way :-)

Ubuntu – Multiple instances of Postgres after Ubuntu upgrade

Ubuntu being a Debian-based system has the ability to run multiple instances of Postgres as you are seeing. Generally unless configured otherwise the first instance started will have the default Postgres port and each subsequent instance will take the next higher port if it is available. These ports can be defined in the config files under /etc/postgresql/<version>/<cluster>/ so that they don't change should you need to but I'd actually need to. In fact a cursory look at the /etc/init.d/postgresql would also show you that you can set environment variables through /etc/postgresql/<version>/<cluster>/environment.

The package managers were even nice enough to include /usr/share/doc/postgresql-common/README.Debian.gz which has a section that quickly explains how the cluster management is handled including the pg_lsclusters command which will give you the Postgres version and port number along with other nice details about each configured cluster.

To answer question in comment regarding stopping an instance... You can edit the start.conf file under /etc/postgresql/<version>/<cluster>/ and change it from auto to disabled or manual. To quote the default start.conf file itself...

# Automatic startup configuration
# auto: automatically start/stop the cluster in the init script
# manual: do not start/stop in init scripts, but allow manual startup with
#         pg_ctlcluster
# disabled: do not allow manual startup with pg_ctlcluster (this can be easily
#           circumvented and is only meant to be a small protection for
#           accidents).

Best Answer

Related Solutions

Postgresql – n easy way to partially backup Postgres

Ubuntu – Multiple instances of Postgres after Ubuntu upgrade

Related Topic