Tuesday, July 7, 2015

Migrate Postgresql 9.1 to 9.4 in 4 steps

Postgresql elephant
By Jeff MacDonald [BSD], via Wikimedia Commons


$pg_lsclusters - Shows the currently running (online/down) postgres DB

$sudo pg_ctlcluster 9.1 main stop - Stops the running postgres 9.1

$sudo pg_upgradecluster 9.1 main - Upgrades(migrates) postgres to newer version(upgrade time depends on the amount of data).

Check whether the upgrade went fine and the newer version of postgres works fine by
$su postgres
postgres@xyz:/$ psql
psql (9.4.3)                       Now postgresql upgraded to new version
Type "help" for help.
postgres=#\l                     -   Check all your databases are available or not
postgres=#\c <your_database_name>  - Connects to your database
postgres=#select * from <your_table_name> - Check the content is same.

Optional:
$sudo pg_dropcluster 9.1 main  -  Do this only, If you really want to drop your old postgres cluster.

1 comment:

Tạ Xuân Trường said...

If you receive an error Error: target cluster 9.4/main already exists, you need to drop it first:
# pg_dropcluster --stop 9.4 main