Upgrading Postgres 10.3 to 11.1 Using Homebrew
It's time to make sure that my laptop has the latest version of PostgreSQL, as I was reading that they were making big strides in the windowing functions in postgreSQL 11. And with Homebrew 2.0 out, it made sense to just write it all down again, just so that I have it for reference.
The process isn't bad... dump all the databases into one file, stop the server, update Homebrew, update postgres. This gets us to the point that we are ready to rebuild the new database:
$ pg_dumpall > dump.sql
$ brew services stop postgresql
$ brew update
$ brew upgrade postgres
Now we need to move out the old database data, create a new structure, and restart the service:
$ cd /usr/local/var $ mv postgres postgres.old $ initdb -D /usr/local/var/postgres $ brew services start postgresql
You then need to go back to the directory of the first command - the one where you dumped the databases, and reload them all:
$ psql -d postgres -f dump.sql
and at this point, everything should be back and running:
$ psql --version psql (PostgreSQL) 11.1 $ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+-------+-----------+----------------+----------------+--------------------- health | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | inventory | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | northhollow | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | postgres | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | template0 | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | =c/drbob + | | | | | drbob=CTc/drbob template1 | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | drbob=CTc/drbob + | | | | | =c/drbob + | | | | | _postgres=CTc/drbob+ | | | | | postgres=CTc/drbob test | drbob | SQL_ASCII | en_US.US-ASCII | en_US.US-ASCII | (7 rows)
At this point you can remove the old data:
$ rm -rf /usr/local/var/postgres.old $ rm dump.sql
and everything is updated. You can then use any of the normal tools, including the Apache/PHP/Postgres that Apple makes available, and Postico - a great GUI client.