When upgrading to a new major version of PostgreSQL, you need to migrate your data. The easiest way to migrate your data is using pg_dumpall, but there are alternative methods that can be useful when you have very large databases, or if you want to migrate only parts of the database.
This is the easiest way to migrate your data.
pg_dumpall --quote-all-identifiers | gzip >postgresapp.sql.gz
gunzip <postgresapp.sql.gz | psql
This method lets you select which databases you’d like to migrate.
psql --listto show the list of databases
pg_dump database_name > database_name.sqlto create a dump of your database
pg_dumpall --globals-only > globals.sql
psql -f globals.sql
psql --command="create database database_name"to create the database
psql -d database_name -f database_name.sqlto restore from the backup
pg_upgrade from the command line is a bit more difficult.
This is recommended only if you have a large database and using
pg_dump is too slow or uses too much disk space.
Make sure you completely understand the process and have a working backup before attempting this!
pg_upgrade needs the old and new binaries, you must make sure that Postgres.app contains the binaries of the old server and of the new server.
Postgres.app 2 contains 9.5 and 9.6 by default, but using other versions is possible as well.
Here’s an example how to upgrade from 9.5 to 9.6:
Contents/Versionsand make sure that binaries for the old version and the new version are included.
~/Library/Application Support/Postgresand create a new, empty folder for the new data directory, eg
/Applications/Postgres.app/Contents/Versions/9.6/bin/initdb -D ~/Library/Application\ Support/Postgres/var-9.6 --encoding=UTF-8 --locale=en_US.UTF-8
/Applications/Postgres.app/Contents/Versions/9.6/bin/pg_upgrade -b /Applications/Postgres.app/Contents/Versions/9.5/bin -B /Applications/Postgres.app/Contents/Versions/9.6/bin -d ~/Library/Application\ Support/Postgres/var-9.5 -D ~/Library/Application\ Support/Postgres/var-9.6 -v(see the pg_upgrade documentation for details)
pg_upgradewill leave behind two scripts,
delete_old_cluster.sh. Run them to optimise the new database and remove the old database cluster