Maria D. Campbell

Upgrading PostgreSQL from version 10.4 to 11.1 via Homebrew (OSX)

Yesterday, it was that time again. Time to finally upgrade to the latest version of PostgreSQL. Circumstances differed from the last time, which was good. For one, I didn’t have a Migration Assistant data migration from my old computer to my new one to deal with. Second of all, due to changes in PostgreSQL itself (and subsequently Homebrew as it relates to PostgreSQL upgrades starting with version 10+), once you wrap your head around it, the upgrade is fairly painless.

An important thing to mention about using Homebrew to upgrade PostgreSQL (assuming that you installed it with Homebrew to begin with), is that it won’t overwrite your old data. In fact, if you try to upgrade your PostgreSQL install with Homebrew without taking the necessary steps for a successful install, it will fail! That’s one of the reasons I love my Homebrew install so much.

So I wanted top upgrade my PostgreSQL install to the latest stable version (currently 11.1), because I wanted to take advantage of its latest features and because it is always advisable and more secure to upgrade to the latest version of software anyway.

I also new that there were ‘breaking’ changes to PostgreSQL especially regarding how to upgrade, so I googled for information on the topic.

I had also upgraded to Mojave the other day, and decided to upgrade Homebrew afterwards as well. That’s when I started running into trouble!

Among other things, my PostgreSQL install was upgraded to 11.1. However, my data had been created with 10.4, and was therefore incompatible with 11.1. In addition, the upgrade would have to be by default installed in the same path as the old version’s data path, so when Homebrew attempted to upgrade to PostgreSQL 11, the upgrade failed. That’s because Homebrew prevents such an occurrence to take place. It exits out of the process if it detects that the path

/usr/local/var/postgres/

contains the data from the previous install. I got the following response in Terminal when I ran the command brew info postgresql, to find out why my upgrade failed:

postgresql: stable 11.1 (bottled), HEAD Object-relational database system
https://www.postgresql.org/ Conflicts with: postgres-xc (because postgresql and
postgres-xc install the same binaries.) /usr/local/Cellar/postgresql/10.4 (3,389
files, 39.2MB) \* Poured from bottle on 2018-06-17 at 01:56:31 From:
https://github.com/Homebrew/homebrew-core/blob/master/Formula/postgresql.rb ==>
Dependencies Build: pkg-config ✔ Required: icu4c ✔, openssl ✔, readline ✔
Optional: python ✔ ==> Options --with-python Enable PL/Python3 --HEAD Install
HEAD version ==> Caveats To migrate existing data from a previous major version
of PostgreSQL run: brew postgresql-upgrade-database

To have launched start postgresql now and restart at login: brew services start
postgresql Or, if you don't want/need a background service you can just run:
pg_ctl -D /usr/local/var/postgres start ==> Analytics install: 58,441 (30 days),
186,921 (90 days), 709,053 (365 days) install_on_request: 51,357 (30 days),
164,191 (90 days), 602,299 (365 days) build_error: 0 (30 days)

What that basically means, and I finally understood after a few attempts and coming across Olivier Lacan’s article entitled Migrating Homebrew Postgres to a New Version, is that you have to change the name of the path to your old data to something other than the default so that Homebrew can place the new version data path into its default path, and therefore not overwrite the old data.

So in the end, these are the steps I took that resulted in a successful PostgreSQL 11.1 install on my machine:

According to Olivier’s post (and I did encounter these issues at one point during one of my many attempts), some of the errors (or similar ones) we might encounter when an accidental upgrade of PostgreSQL takes place via a Homebrew upgrade are the following:

FATAL: database files are incompatible with server DETAIL: The data directory
was initialized by PostgreSQL version 10.4, which is not compatible with this
version 11.1.

According to Olivier (and he is right), “No need to panic. Your data should be fine. While you’ve upgraded the Postgres library, your existing databases have not been removed. They simply need to be upgraded to run with the new library.”

mv /usr/local/var/postgres/ /usr/local/var/postgres.10.4.backup/

That way, when I executed the PostgreSQL database upgrade, version 11.1 could move into /usr/local/var/postgres/ with no conflicts.

Once I did that, I was able to execute the following command which resulted in a completely successful installation and upgrade of PostgreSQL from version 10.4 to 11.1:

brew postgresql-upgrade-database

And the following was returned in the Terminal console:

brew postgresql-upgrade-database ==> Upgrading postgresql data from 10 to 11...
Stopping `postgresql`... (might take a while) ==> Successfully stopped
`postgresql` (label: homebrew.mxcl.postgresql) ==> Moving postgresql data from
/usr/local/var/postgres to /usr/local/var/postgr The files belonging to this
database system will be owned by user "mariacam". This user must also own the
server process.

The database cluster will be initialized with locale "en_US.UTF-8". The default
database encoding has accordingly been set to "UTF8". The default text search
configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/var/postgres ... ok creating
subdirectories ... ok selecting default max_connections ... 100 selecting
default shared_buffers ... 128MB selecting dynamic shared memory implementation
... posix creating configuration files ... ok running bootstrap script ... ok
performing post-bootstrap initialization ... ok syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections You can change
this by editing pg_hba.conf or using the option -A, or --auth-local and
--auth-host, the next time you run initdb.

Success. You can now start the database server using:

/usr/local/opt/postgresql/bin/pg_ctl -D /usr/local/var/postgres -l logfile start

## Performing Consistency Checks

Checking cluster versions ok Checking database user is the install user ok
Checking database connection settings ok Checking for prepared transactions ok
Checking for reg\* data types in user tables ok Checking for contrib/isn with
bigint-passing mismatch ok Creating dump of global objects ok Creating dump of
database schemas ok Checking for presence of required libraries ok Checking
database user is the install user ok Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the new cluster before
continuing.

## Performing Upgrade

Analyzing all rows in the new cluster ok Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok Copying old pg_xact to new server ok Setting
next transaction ID and epoch for new cluster ok Deleting files from new
pg_multixact/offsets ok Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok Copying old pg_multixact/members
to new server ok Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok Setting frozenxid and minmxid counters in new cluster
ok Restoring global objects in the new cluster ok Restoring database schemas in
the new cluster ok Copying user relation files ok Setting next OID for new
cluster ok Sync data directory to disk ok Creating script to analyze new cluster
ok Creating script to delete old cluster ok

## Upgrade Complete

Optimizer statistics are not transferred by pg_upgrade so, once you start the
new server, consider running: ./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
./delete_old_cluster.sh ==> Upgraded postgresql data from 10 to 11! ==> Your
postgresql 10 data remains at /usr/local/var/postgres.old ==> Successfully
started `postgresql` (label: homebrew.mxcl.postgresql)

As you can see, I still have my old data! And my upgrade was also a success.

The only thing to note, however, is that the default superuser which is set up with your first (or previous) Homebrew install of PostgreSQL (your computer username), has to be recreated. Not a problem!

psql: FATAL: database "mariacam" does not exist

To fix this issue, I did the following:

createdb mariacam

Then I tried the following (again):

psql

And this time Terminal returned the following:

psql (11.1) Type "help" for help.

mariacam=#

Success! But next I had to check whether or not I had the default postgres user, so I typed the following postgres query in the PostgreSQL language:

SELECT \* FROM pg_user;

This returned all the users that were associated with PostgresQL. In my case, only the root user appeared, which is my computer username. If you are not sure what your computer username is, type the following command before creating your postgres root user:

whoami

This tells you what your computer username is that is associated with the computer profile you are currently in, and it should be your root administrator profile. Since you are upgrading and therefore not installing for the first time, you can simply use the createdb whoami command, and that will suffice.

To create the default postgres user, I did the following (replace ‘password’ with a password of your choosing):

CREATE USER postgres WITH SUPERUSER PASSWORD 'password';

And I do this inside my root user. In other words, I login with the command psql and then Terminal returns the following:

psql (11.1) Type "help" for help.

Then, when I re-execute the PostgreSQL query

SELECT \* FROM pg_user;

the newly created postgres user appears in the list of users associated with PostgreSQL:

usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd |
valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
mariacam | xx | t | t | t | t | **\*\*\*\*** | | postgres | xxxxx | f | t | f |
f | **\*\*\*\*** | | (2 rows)

As you can see, postgres is a superuser, but cannot create databases yet. This is easily fixed. Next I execute the following executable to permit the postgres user to create databases:

ALTER USER postgres CREATEDB;

Then if I run

SELECT \* FROM pg_user;

Terminal returns the following:

usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd |
valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
mariacam | xx | t | t | t | t | **\*\*\*\*** | | postgres | xxxxx | t | t | f |
f | **\*\*\*\*** | | (2 rows)

Note that the postgres user’s usecreatedb column is now set to true (t).

But what if we don’t want the postgres user to have superuser privileges? I know I don’t. I leave that to my root user which I try and avoid using anyway. In order to set superuser for the postgres user to false, I do the following:

ALTER USER postgres NOSUPERUSER;

And then when I run

SELECT \* FROM pg_user;

again, I get the following for the postgres user:

usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd |
valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
mariacam | xx | t | t | t | t | **\*\*\*\*** | | postgres | xxxxx | t | f | f |
f | **\*\*\*\*** | | (2 rows)

And now the postgres user’s usesuper is set to false (f).

There were two other things I had to do to make my PostgreSQL upgrade mirror my previous PostgreSQL configuration. I had to add a new server to my pgAdmin4.4 GUI. At this point the server was pointing to PostgresQL 10.4 instead of PostgreSQL 11.1.

I opened up pgAdmin4.4 to check the status of my server there. I could not connect, and the name of the server (as I had named it) was PostgreSQL 10.4. I took no chances. I uninstalled pgAdmin4.4 and re-installed it. Interestingly enough, the server was still there. Probably the uninstall was not clean. But that did not matter anyway. All I had to do was create a new server connection and delete the old one. Then I simply created a new server connection:

First I clicked on ‘Servers’ in the pgAdmin4.4 client.

Then I right clicked on ‘Servers’ and selected ‘Create Server’. There, I first went into the connection tab and in Host name/address field I typed ‘localhost’. I made sure that in the Port field the number ‘5432’ populated it, that the name of the maintenance database was ‘postgres’, and that the username was ‘postgres’.

Then in the General tab, in the name field, I typed the name I wanted to give my new server.

Last, I clicked the ‘save’ button.

My databases appeared in the sidebar to the left in the pgAdmin client. Success!

I had one last step I had to take to ensure that my PostgreSQL upgrade configuration mirrored that of the previous version. I had to change the following configuration that was implemented on version upgrade:

WARNING: enabling "trust" authentication for local connections You can change
this by editing pg_hba.conf or using the option -A, or --auth-local and
--auth-host, the next time you run initdb.

This means that any postgres user, root or otherwise, can login to PostgreSQL in the Command Line whether it be the system Terminal or integrated Terminal in a code editor, without providing a password. That is the default behavior when installing with Homebrew. I had changed it in the previous install so that all users had to login with their password. I will discuss this in my next post and link to it here.

Happy PostgreSQL v 10.4 to 11.1 upgrading!

Related Links:

Categorized under:full-stack-javascriptpostgresqlhomebrewosxcommand-line
Discuss On Twitter