Skip to content

Instantly share code, notes, and snippets.

@giannisp
Last active July 14, 2024 18:27
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
@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