Many are experimenting with Postgres 10 and testing their applications against it. Postgres binaries from BigSQL are inherently relocatable / portable . This makes it especially easy if you want try upgrading or just want to check the upgradability of your application.

Here are the steps for an easy upgrade from Postgres 9.6 to Postgres 10. Almost the same steps will work across operating systems and versions of Linux / Mac / Windows

Get relocatable binaries side by side

The design of package management of Postgres by BigSQL allows us to have side by side installation of different versions of postgres. Since binaries are unpacked into a different directory based on versions, no conflict with existing versions are expected.

This illustration assumes that Postgres 9.6 is already present in the system and we want to try upgrading to Postgres 10 on the same server.

Go to Installation directory

cd bigsql/

Get Postgres 10 Binaries

./pgc install pg10

This step will create a seperate directory pg10 and keep all the Postgres 10 binaries inside.

Initialize Postgres 10 data directory.

./pgc init pg10

This step prompts for super user password and initializes data directory.

Important point to note: This initialize will choose the next available port rather than the existing instance automatically. If your current postgres instance (Postgres 9.6) is running on 5432, Postgres 10 will be initialized to use 5433

At this stage we have 2 options to proceed.

Option 1. Take logical backup of Postgres 9.6 instance and import in Postgres 10

Option 2. Use pg_upgrade for creating a upgraded copy of data directory.

Option 1. Take logical backup of Postgres 9.6 instance and import in Postgres 10

We need to startup newly initialized Postgres 10 instance

./pgc start pg10

Now we can have a simple logical backup and restore to Postgres 10

./pg10/bin/pg_dumpall -U postgres -p 5432 | ./pg10/bin/psql -U postgres -p 5433

Once this step is complete, we will have a fully functional instance with database.

Option 2. Use pg_upgrade for creating a upgraded copy of data directory.

If Postgres instance is already running, we have to stop them.

./pgc stop pg10
./pgc stop pg96

Sometimes, pg_upgrade may require you to set PGPASSWORD as the environment variable

export PGPASSWORD=*******

Run pg_upgrade specifying data directories and binary location and ports

./pg10/bin/pg_upgrade -v -d data/pg96/ -D data/pg10/ -b pg96/bin/ -B pg10/bin/ -p 5432 -P 5433

Once upgrade is successful, instances are ready for starting up

./pgc start pg96
./pgc start pg10

Cleaning up testup upgrade

Imagine that you have done the upgrade and tests in your off-peak hours and want to cleanup the test setup. Its very simple.

./pgc remove pg10
rm -rf data/pg10