PostgreSQL Migration – pg_upgrade and Tablespaces from 9.2 to 9.3

migrationpostgresql-9.2postgresql-9.3upgrade

I have a 9.2 installed with tablespaces sitting at:

/var/lib/postgresql/9.2/main

and

/opt/postgres/9.2/second

And unfortunately they are on different filesystems.

This is a Debian package installation of 9.2. The 9.3 will be compiled from source. Since every example I have seen does not show how to deal with a second and a third table space, does anyone have the correct command line for this? Does pg_upgrade take multiple -d and -D options?

The intent is for the data to stay on the same file systems and to use link-mode.

Best Answer

PostgreSQL creates subdirectories named for the PostgreSQL version within each tablespace.

So AFAIK pg_upgrade doesn't need to be told to take special action for the tablespaces. It will use the same tablespaces as the original database, but won't conflict because of the use of different subdirectories within the tablespace root.

The caveat is that if you have tablespaces inside your 9.2 data directory, this won't work. You shouldn't do that in the first place. Move the tablespaces to a location outside the 9.2 datadir before doing the upgrade.

Related Question