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

Awesome, it worked perfectly on my mac. 👍

Worked for me too, thanks so much 👍

Worked great for me as well -- thanks for posting. I get a bit trigger happy with brew upgrade.

If on the 'actual upgrade' step (line 26) you get the following error message:

There seems to be a postmaster servicing the new cluster.
Please shutdown that postmaster and try again.
Failure, exiting

It's due to the server not being able to stop because of your configs. Run the following to get unblocked:

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
rm ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

If @huxley-dog's command won't work try:
brew services stop postgresql
or
pg_ctl -D /usr/local/var/postgres stop

Ruby users will want to run this too after the upgrade is complete:

$ gem uninstall pg
$ gem install pg

It worked for me, thank you very much.

Perfect, thank you! 👍

Thank you! worked well on my mac!

Perfect. And this helped me with the 'actual upgrade' step (line 26): https://gist.github.com/eoinkelly/fd80465942c8ca4bd5c0#gistcomment-1372195

Thank you, it worked! 👍

Someone had it working with Postgis? When restoring a postgis database during the upgrade I get DETAIL: Server is version 9.6, library is version 10.0. it's like postgis is already for pg 10 but it's still trying to be used in the old db

Thanks, IT worked

@alex88 what I did was dropping the postgis extension before the upgrade, then reenabling it after the pg_upgrade has completed successfully. Worked fine.

P.S. Thanks @giannisp! 👌

thanks

Works great. Thanks!

Thank you very much for taking the time to document and publish these instructions. Greatly appreciated. 👍

Just perfect, thanks!

Really awesome. It works well on my machine. Thanks! @huxley-dog

THANK. YOU.

Woow.. Thanks!

Thank you so much. Lifesaver. I was able to use brew services start postgresql to start the server and finally create my db.

Thank you!

Worked perfectly for me. Thank you for sharing this.

🙏 Thank you 🙏

I had to make sure all brew postgres servers were stopped before running the upgrade with brew services stop postgresql
Received this error otherwise

*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

There seems to be a postmaster servicing the new cluster.
Please shutdown that postmaster and try again.
Failure, exiting

👍

Useful, thank you 👍

Thanks!!

Thanks! It worked well on my mac:)

Worked for me too

Thanks, that worked. But you have to stop postgresql before you run

# 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

As @huxley-dog and @aszulinski have already noted.

Unfortunately, this failed on my system due to this error:

pg_dump: [archiver (db)] query failed: ERROR: could not access file "$libdir/postgis-2.3": No such file or directory

Is there any way to painlessly migrate postgis too?

👍

Thanks for the post... lifesaver!

Thank you, worked great, also for other make sure that when you run which postgres that it's pointing to /usr/local/bin/postgres, I had some problems with my PATH, but after fixing it and re-running the steps above I had no problems.

In my case I had postgis installed. Thus upgrading one database failed.

So i dropped it, restarting the old cluster:

/usr/local/opt/postgresql@9.6/bin/postgres -D /usr/local/var/postgres96
psql

Then:

DROP DATABASE myfoobardb;

Thanks, nice work...

In my case I need run before pg_update: brew services stop postgresql

I encountered a small problem, leaving a message here for other people with the same problem. Turns out brew installed 9.6.6 and not 9.6.5 for postgresql@9.6

If that's also your case, use the following line to replace line 26:

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

The rest of the migration still works fine 👍

Thank you a lot!

Awesome work, thanks! This saved me a ton of time! Appreciated the comments as well.

For future fixers, it looks like:

• the latest version of Postgres in Homebrew is now 10.1 (instead of 10.0) and
• the latest 9.6 branch is 9.6.6 (instead of 9.6.5 (@Theo- pointed this out as well)).

So others will probably need to change some of the paths accordingly as time moves on.

Worked great! I just ended up having to go into
usr/local/var/postgres96
and deleting
postmaster.pid
to get it all to update. Thanks!!

Thanks v. much for the script. I also ran brew pin postgresql at the end to stop it happening again when I forget what I'm doing and blindly do a brew upgrade ('cos I know I will).

Thank you for the script!

Much obliged

Thank you so much! My mac randomly restarted and I couldn't use postgres after that

Thank you soooo sooo much! This is the killer script I was looking for! Upgrading to 10.1 was a bliss 🥇 🙇‍♂️

pg_ugprade produces the following output for me (9.6.6 -> 10.1):

Performing Consistency Checks
-----------------------------
Checking cluster versions
Old cluster data and binary directories are from different major versions.
Failure, exiting

The old data directory was working perfectly fine under 9.6.6 beforehand.

Hey all: I've opened a PR to try and make this better in Homebrew/homebrew-core#21244. Would you mind reviewing and testing?

Absolutely Fabulous! <3

Thank you!!

I skipped the link/unlink steps and adjusted the pg_upgrade command like this:

pg_upgrade -b /usr/local/Cellar/postgresql/9.6.5/bin OD -B /usr/local/Cellar/postgresql/10.1/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

I might add that I just have dev data on my local machine, nothing important.

I too skipped linking/unlinking and ran the following

mv /usr/local/var/postgres /usr/local/var/postgres9.6.3
initdb /usr/local/var/postgres -E utf8
pg_upgrade -b /usr/local/Cellar/postgresql/9.6.3/bin -B /usr/local/Cellar/postgresql/10.1/bin -d /usr/local/var/postgres9.6.3 -D /usr/local/var/postgres

However, it failed because the brew services finally rebooted up in the middle of the upgrade. So be sure to:

brew services stop postgresql

Before doing anything. Thx for this!

Grand salute!

I had to use 9.6.6 and 10.1 but that was all! Everything worked just fine. Awesome! Thanks!

Thanks! This was a great time saver!

Thank you, sir!

Thanks, really helped!

Awesome.. works flawlessly..

I was getting

could not connect to source postmaster started with the command:
"/usr/local/Cellar/postgresql@9.6/9.6.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/usr/local/var/postgres96" -o "-p 50432 -b  -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/Users/simon'" start
Failure, exiting

The problem was the LC_ALL was not set.
To check if it is set run locale

$ locale
...
LC_ALL=

Set it with export LC_ALL=UTF-8

Thank you! 👍 ❤️

Fantastic. Thanks!

thanks!!!

Thank you!! 🥇

Thank you, sir! 🎩

Big thanks!

I wish the brew recipe did that somehow, too.

This is perfect thank you @giannisp!

For anyone else that encounters Old cluster data and binary directories are from different major versions. - check that cat $dir/PG_VERSION and pg_upgrade -V match for old and new.

Anyone that has ussies with the locale, try initdb with --locale. e.g.:

initdb --locale=en_US /usr/local/var/postgres -E utf8

/cc @MTCoster for version issues :)

Thank you.
I used it for 9.5 to 10.1

It works. thanks

Thank you!

thank you for sharing this, worked perfectly for my 9.5 to 10.1 upgrade

I get an error saying "could not open version file: /usr/local/var/postgres@9.5/PG_VERSION"

Does anybody know how to fix this?

When I enter the upgrade command, I get the error "could not open version file: /usr/local/var/postgres@9.5/PG_VERSION"

Does anybody know how to fix this?

Thanks!

Thanks! Worked like a charm!

Thank you. 🙏

Homebrew/homebrew-core#21244 has been merged which provides a command to do this semi-automatically. If it doesn't handle your specific case: please submit a PR. Thanks!

Thank you.

Thanks!

thank you.

Thank you!

Thanks! :)

Thank you!

You, my friend, deserve a 🍪

Holla! thanks!

Thanks!! It worked. Thanks for sharing 👍

Big ups.

Thank you so much ! 🍪

Thank you.

Thank you so much

thanks work like a charm!

Thank you so much. This helped me a lot!

Thanks!

Anyone looking to upgrade with postgis, this works https://gist.github.com/smellman/2e51b1cf449c499ad5deec454a16a7bc

This worked from 9.4 -> 10.0 for me as well. Thank you!

I am on macOS High Sierra 10.13.3 and upgrading postgres from v9.6 to v10.3 using above gist.

When i do pg_ctl start -D /usr/local/homebrew/var/postgres which is where my postgres is (under homebrew directory), it does NOT start the postgres server and the output i see in the terminal is as below:

waiting for server to start....2018-03-22 17:23:42.919 IST [1241] LOG:  could not bind IPv6 address "::1": Address already in use
2018-03-22 17:23:42.919 IST [1241] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-03-22 17:23:42.919 IST [1241] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2018-03-22 17:23:42.919 IST [1241] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2018-03-22 17:23:42.919 IST [1241] WARNING:  could not create listen socket for "localhost"
2018-03-22 17:23:42.919 IST [1241] FATAL:  could not create any TCP/IP sockets
2018-03-22 17:23:42.919 IST [1241] LOG:  database system is shut down
 stopped waiting
pg_ctl: could not start server
Examine the log output.

I checked to see if something else is running on 127.0.0.1 by running:

$ ps aux | grep 127.0.0.1
jagdeep singh            1253   0.0  0.0  4267768    888 s000  S+    5:32PM   0:00.00 grep 127.0.0.1

but, nothing there.

Also i am able to ping, or scutil the localhost successfully.

$ ping localhost
PING localhost (127.0.0.1): 56 data bytes
64 bytes from 127.0.0.1: icmp_seq=0 ttl=64 time=0.040 ms
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.049 ms
...

$ scutil -r localhost
Reachable,Local Address,Directly Reachable Address

Lastly i tried restarting my system, but that didn't help either. Anyone has any idea where to go from here?

Thanks for this !!

@jagdeepsingh,

Can you try
$ brew services list
For me it was running as a background brew service... then just run:
$ brew services stop <name>

Thanks for this amazing guide!!

worked like a charm.. 👍

Just a minor suggestion .... I am sure people can figure this out but this may help someone if they are stuck..

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
in the above command ensure to list the correct version of postgresql here pg_upgrade -b /usr/local/Cellar/postgresql@9.6/(your postgresql version)/bin

Thanks!

Wasted an hour of my morning trying to find this fix, I am glad I found this! Thanks!

My versions were a little different, so I just needed to make a few changes but it worked perfectly:
9.6/9.6.8 to 10.3

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

This was a life saver! 👍

many...many bows to you

hero

👍

🙇‍♂️

I got up to this point pg_upgrade -b /usr/local/Cellar/postgresql@9.6/9.6.8/bin -B /usr/local/Cellar/postgresql/10.3/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres and I was getting an error:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok

connection to database failed: FATAL:  role "sunkanmi" does not exist

could not connect to source postmaster started with the command:

Yet on checking, I can see that the role exists. Please advise anyone.

I have fixed the issue above. It was because I did a system migration and the account name of the previous mac was different from the account name on the new mac.
What I did to fix it was to create a new admin user, log out of my current user ("sunkanmi" in this case), log in to the new user and then change the account name of "sukanmi" to the same account name ("oldUserName") on my previous mac.
I then logged back into the "oldUserName" account and tried the pg_upgrade and it worked like a charm.

For anyone who missed MikeMcQuaid's comment above, you can now use brew postgresql-upgrade-database to accomplish the same thing.

Worked. thanks 👍

For anyone having error that:

check for "/usr/local/Cellar/postgresql/10.0/bin" failed: No such file or directory

Basically we have difference versions of postgreql installed. Following command worked for me:

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

This works however, I think Brew have added a command that will automatically migrate the data after upgrading.

brew postgresql-upgrade-database

Looking at the logs, it pretty much does what you have here.

You are the best guys. Very helpful thx

thanks, upgraded to postgresql@10.4 ;_)

Super helpful, thank you! My case was a little more complex as I had postgis, but I was able to extend your process. Also I have slightly newer versions:
pg_upgrade -b /usr/local/Cellar/postgresql@9.6/9.6.9/bin -B /usr/local/Cellar/postgresql/10.3/bin -d /usr/local/var/postgres96 -D /usr/local/var/postgres

Thanks.
For those getting could not open version file, go to your -D directory (eg cd /usr/local/var/postgres) and run it with -D ./
Seems like pg_upgrade having troubles with different drives and folders.

Thank you!

Thank you a lot, sir!

bravo!!!

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

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

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

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.