Postgres

Note

In these examples, green is the live database.

Note

In these examples, the owner name is the same as the database name.

List current databases:

psql -U postgres -c "\l"

Backup database:

pg_dump -U postgres green -f ~/repo/temp/out.sql

Restore data (from green to the empty blue database):

psql -U blue -d blue -f out.sql 2> out.log

Important

Remember to check out.log.

Upgrade Cluster

After a do-release-upgrade, you will probably be left with several database clusters e.g:

# pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.3 main    5432 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log
9.5 main    5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
10  main    5434 online postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

To move the data from Postgres 9.3 to Postgres 10, run the following as root:

# list the clusters
pg_lsclusters

# drop the new (empty) cluster
pg_dropcluster 10 main --stop
# migrate the data from 9.3 to 10
pg_upgradecluster 9.3 main

# remove the 9.3 and 9.5 clusters
pg_dropcluster 9.3 main --stop
pg_dropcluster 9.5 main --stop

# remove the old database engines
apt remove postgresql-9.3
apt remove postgresql-9.5

# list the clusters
pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
10  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-10-main.log