Skip to content

Instantly share code, notes, and snippets.

Last active March 1, 2024 14:39
Show Gist options
  • Save giannisp/ebaca117ac9e44231421f04e7796d5ca to your computer and use it in GitHub Desktop.
Save giannisp/ebaca117ac9e44231421f04e7796d5ca to your computer and use it in GitHub Desktop.
Upgrade PostgreSQL 9.6.5 to 10.0 using Homebrew (macOS)
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 postgresql@9.6
brew unlink postgresql@9.6
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/postgresql@9.6/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 postgresql@9.6
rm -rf /usr/local/var/postgres96
rm -rf /usr/local/share/postgresql96
Copy link

This is one of the most useful gists in existence. I wish they'd make this a seamless process.

Copy link

spaceguy01 commented Jun 22, 2018

Ok I have no idea what i'm doing as I'm new. Everything works but it crashes at this point and have tried all the solutions offered (still no idea what i'm doing) but didn't work

pg_upgrade -b /usr/local/Cellar/postgresql@9.6/9.6.5/bin -B /usr/local/Cellar/postgresql/10.0/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

I get this

check for "/usr/local/Cellar/postgresql@9.6/9.6.5/bin" failed: No such file or directory
Failure, exiting

When I do ' postgres -V' I get postgres (PostgreSQL) 10.4

Copy link

Thanks worked perfectly !!! I just has to use /usr/local/Cellar/postgresql@9.6/9.6.9/bin and /usr/local/Cellar/postgresql/10.3/bin

Copy link

Fantastic! Worked like a charm.

Copy link

nicregez commented Jul 3, 2018

Fabulous, many, many thanks

Copy link

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'

Copy link

ryan2clw commented Jul 4, 2018

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

Copy link

Awesome! Worked as charm 👍

Copy link

aslam commented Aug 4, 2018

Works like a charm, great work. Thanks.

Copy link

rimiti commented Aug 7, 2018


Copy link

ramhoj commented Aug 9, 2018

Thank you!

Copy link


Copy link

I tried this with 9.6 -> 10.5 and it did not work. It appears to be an issue with PostgreSQL itself:

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

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.

Copy link

Works like charm (y)

Copy link

pjatx commented Oct 1, 2018

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

Copy link

ArturT commented Oct 6, 2018

Thanks for the help!

Copy link

clint74 commented Oct 10, 2018

Great job. Thank you!

Copy link

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.

Copy link

clemmy commented Oct 22, 2018

Thanks so much for this!

Copy link

ai-rub commented Nov 15, 2018

Thank you very much!

Copy link

Thanks a lot!! Works seamlessly.

Copy link

brew postgresql-upgrade-database

Copy link

Thanks @masterkain! perfect

Copy link

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).

Copy link

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

Copy link

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

Copy link

Genius, saved my day.

Copy link

awesome! thank you, you saved me alot of time

Copy link

lyonsun commented Mar 25, 2019

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 "" 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

Copy link

Cgruppo commented Jul 8, 2019

thanks. it's working. i like this.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment