Skip to content

Instantly share code, notes, and snippets.

@giannisp
Last active March 1, 2024 14:39
Star You must be signed in to star a gist
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
@toby5box
Copy link

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

@spaceguy01
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

@djtsheps
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

@lance-hardy
Copy link

Fantastic! Worked like a charm.

@nicregez
Copy link

nicregez commented Jul 3, 2018

Fabulous, many, many thanks

@maxlipsky
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'

@ryan2clw
Copy link

ryan2clw commented Jul 4, 2018

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

@sujithsreekumar
Copy link

Awesome! Worked as charm 👍

@aslam
Copy link

aslam commented Aug 4, 2018

Works like a charm, great work. Thanks.

@rimiti
Copy link

rimiti commented Aug 7, 2018

💯

@ramhoj
Copy link

ramhoj commented Aug 9, 2018

Thank you!

@crwhitesides
Copy link

Thanks!

@warrenrentlytics
Copy link

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.

@SumeetGohil
Copy link

Works like charm (y)

@pjatx
Copy link

pjatx commented Oct 1, 2018

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

@ArturT
Copy link

ArturT commented Oct 6, 2018

Thanks for the help!

@clint74
Copy link

clint74 commented Oct 10, 2018

Great job. Thank you!

@InvisibleTech
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.

@clemmy
Copy link

clemmy commented Oct 22, 2018

Thanks so much for this!

@ai-rub
Copy link

ai-rub commented Nov 15, 2018

Thank you very much!

@darkdefender27
Copy link

Thanks a lot!! Works seamlessly.

@masterkain
Copy link

brew postgresql-upgrade-database

@fanckush
Copy link

Thanks @masterkain! perfect

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

@DineshkumarElumalai
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

@somenugget
Copy link

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

@alxpereira
Copy link

Genius, saved my day.

@daliadaud
Copy link

awesome! thank you, you saved me alot of time

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

@Cgruppo
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