As the information how to upgrade a «plain vanilla» of my favourite Database PostgreSQL installed by the EDB installer on Mac is a bit spread over several places, I decided to write this little blog-post to describe this «operator» know-how:

In this example I migrate from PostgreSQL 9.5 to PostgreSQL 12:

The plan is: dump your old database-cluster (all schemas, users, data etc…) to a huge SQL-file, delete to old installation, create the new installation and reload the dump.

dump and uninstall

before proceeding, because user rights, file rights and database user rights etc. are not so streamlined on macOS, you have to fix this. the easiest way is via a .pgpass in your Home directory, e.g.:

[luna:~] rschumm% more .pgpass 
localhost:5432:*:postgres:clandestino

the syntax is: host:port:database:user:password and you can use wildcards as in my example.

then, rights fixed, you can dump the database-cluster to an SQL file:

[luna:~] rschumm% cd /Library/PostgreSQL/9.5/bin
[luna:PostgreSQL/9.5/bin] rschumm% ./pg_dumpall -U postgres -w > ~/Desktop/viadukt-db-9.5.sql

your whole database-cluster is now dumpted to the SQL file.
then, uninstall the old stuff with the uninstaller located in /Library/PostgreSQL/9.5.
The cluster should now be deleted, Service not running anymore, and Port 5432 or so free again.

install and reload

install the new PostgreSQL 12 with the downloaded EDB installer.

then, re-load your SQL dump-file from above to the new cluster:

[luna:~] rschumm% cd /Library/PostgreSQL/12/bin 
[luna:PostgreSQL/12/bin] rschumm% ./psql -U postgres -f ~/Desktop/viadukt-db-9.5.sql postgres

that’s it.

remark

you could switch to the postgres user by sudo su - postgres, but then you will have no rights to write so directories of your normal user - that’s why I use the .pgpass trick.

illustration: the Gui of PostgreSQL, pgAdmin.