publiziert am 22. 10. 2019 um 12:10
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.
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
The cluster should now be deleted, Service not running anymore, and Port
5432 or so free again.
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
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