Upgrading Postgres 11.1 to 12.1 Using Homebrew
It's time to make sure that my laptop has the latest version of PostgreSQL, as I was reading that Heroku was moving to Postgres 12 as their default database. And since I don't do it that often, I decided to write it all down so that it's easier to do next time.
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) 12.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.
UPDATE: a friend asked: "What's in 12?" and I had to say I really didn't know... so I looked it up. The big changes are all about under-the-hood performance, and so, as I suspected, it just "works better". Which is really the goal, right? 🙂
I know I will probably enjoy the JIT on by default, as there are plenty of times stored procedures are in my databases, and that makes them faster, which is always nice. But it's just a great database - getting greater.