Upgrade PostgreSQL 9.6.5 to 10.0 using Homebrew (macOS)

gistfile1.txt

After automatically updating Postgres to 10.0 via Homebrew, the pg_ctl start command didn't work.
The error was "The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.0."

Database files have to be updated before starting the server, here are the steps that had to be followed:

# need to have both 9.6.x and latest 10.0 installed, and keep 10.0 as default
brew unlink postgresql
brew install [email protected]
brew unlink [email protected]
brew link postgresql

# move 9.6.x db files to another directory
mv /usr/local/var/postgres /usr/local/var/postgres96

# init new database using 10.0
initdb /usr/local/var/postgres -E utf8

# make timezone and timezonesets directories available for 9.6.x installation
mkdir /usr/local/share/postgresql96
cp -r /usr/local/share/postgresql/timezone /usr/local/share/postgresql96
cp -r /usr/local/share/postgresql/timezonesets /usr/local/share/postgresql96

# finally the actual upgrade
# -b is the old binary dir, -B is the new binary dir
# -d is the old data dir, -D is the new data dir
pg_upgrade -b /usr/local/Cellar/[email protected]/9.6.5/bin -B /usr/local/Cellar/postgresql/10.0/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

# start 10.0 to check that upgrade works
pg_ctl start -D /usr/local/var/postgres

# cleanup if upgrade was successful
brew uninstall [email protected]
rm -rf /usr/local/var/postgres96
rm -rf /usr/local/share/postgresql96

Fantastic! Worked like a charm.

Fabulous, many, many thanks

If you got lc_collate values for database "postgres" do not match: old "C", new "en_US.UTF-8" and reinitialisation of database doesn't work so try this:
UPDATE pg_database SET datcollate='C', datctype='UTF-8' WHERE datname='postgres'

Still working, 9.6 to 10.4, soooooo didn't feel like grinding through this myself, thanks.

Awesome! Worked as charm 👍

Works like a charm, great work. Thanks.

💯

Thank you!

Thanks!

I tried this with 9.6 -> 10.5 and it did not work. It appears to be an issue with PostgreSQL itself: https://www.postgresql-archive.org/PG-UPGRADE-9-6-to-10-5-td6033409.html

Since the apparent recommended workaround is a two-hop upgrade of 9.6 -> 10.4 and 10.4 -> 10.5, I first installed 10.4 using:

brew install https://raw.githubusercontent.com/Homebrew/homebrew-core/cef0125ef6b01ae1e370cf0dcf5a1a5efc28f659/Formula/postgresql.rb

and then made minor changes to this script to account for working off 10.4 to 10.5.

after that, brew upgrade postgresql and brew switch postgresql took me to 10.5.

Works like charm (y)

Thanks @warrenrentlytics - ran into the same thing and that was helpful

Thanks for the help!

Great job. Thank you!

Much thanks. Pretty much worked for me, only needed to mod my old pg_hba.conf to allow the upgrade process to connect.... that's on me though.

Thanks so much for this!

Thank you very much!

Thanks a lot!! Works seamlessly.

brew postgresql-upgrade-database

Thanks @masterkain! perfect

Incredibly helpful, thanks so much.
When running the pg_upgrade command, I got an error like

lc_collate values for database "postgres" do not match: old "en_US.UTF-8", new "C"

which I got around by adding --locale="en_US.UTF-8" to the pg_upgrade command (in addition to the other parameters).

I'm trying to upgrade my postgresql from 10.6_1 to 11.1_1
pg_upgrade -b /usr/local/Cellar/postgresql@10/10.6_1/bin -B /usr/local/Cellar/postgresql/11.1_1/bin -d /usr/local/var/postgres10 -D /usr/local/var/postgres
throws error:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   
This utility cannot be used to downgrade to older major PostgreSQL versions.
Failure, exiting

Thank you. Migration from 10 to 11 was successful :+1

Genius, saved my day.

awesome! thank you, you saved me alot of time

Thank you.

One comment in my experience on this issue, at the very end, I had to run:

brew services stop postgresql
pg_ctl -D /usr/local/var/postgres stop
kill $postmaster_pid.
brew services restart postgresql

one after another in order to get rid of the following log repeating every 10s in postgres.log file:

2019-03-25 16:27:58.534 EET [18563] FATAL: lock file "postmaster.pid" already exists
2019-03-25 16:27:58.534 EET [18563] HINT: Is another postmaster (PID 14070) running in data directory "/usr/local/var/postgres"?

---

Credits for finding the PID of postmaster.pid:
StackExchange

thanks. it's working. i like this.