Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@skunkworker

This comment has been minimized.

Copy link

skunkworker commented Oct 19, 2017

Awesome, it worked perfectly on my mac. πŸ‘

@kylefox

This comment has been minimized.

Copy link

kylefox commented Oct 19, 2017

Worked for me too, thanks so much πŸ‘

@ghost

This comment has been minimized.

Copy link

ghost commented Oct 20, 2017

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

This comment has been minimized.

Copy link

aszulinski commented Oct 20, 2017

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

@djones

This comment has been minimized.

Copy link

djones commented Oct 22, 2017

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

$ gem uninstall pg
$ gem install pg
@KaijianYou

This comment has been minimized.

Copy link

KaijianYou commented Oct 23, 2017

It worked for me, thank you very much.

@friederbluemle

This comment has been minimized.

Copy link

friederbluemle commented Oct 23, 2017

Perfect, thank you! πŸ‘

@visenger

This comment has been minimized.

Copy link

visenger commented Oct 23, 2017

Thank you! worked well on my mac!

@kmaddu

This comment has been minimized.

Copy link

kmaddu commented Oct 23, 2017

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

@michhhh

This comment has been minimized.

Copy link

michhhh commented Oct 23, 2017

Thank you, it worked! πŸ‘

@alex88

This comment has been minimized.

Copy link

alex88 commented Oct 24, 2017

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

@roshan092

This comment has been minimized.

Copy link

roshan092 commented Oct 25, 2017

Thanks, IT worked

@skycocker

This comment has been minimized.

Copy link

skycocker commented Oct 25, 2017

@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! πŸ‘Œ

@cordoval

This comment has been minimized.

Copy link

cordoval commented Oct 25, 2017

thanks

@preston

This comment has been minimized.

Copy link

preston commented Oct 27, 2017

Works great. Thanks!

@s5b

This comment has been minimized.

Copy link

s5b commented Oct 27, 2017

Thank you very much for taking the time to document and publish these instructions. Greatly appreciated. πŸ‘

@felipsimoes

This comment has been minimized.

Copy link

felipsimoes commented Oct 28, 2017

Just perfect, thanks!

@uthapjhojho

This comment has been minimized.

Copy link

uthapjhojho commented Oct 29, 2017

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

@laurenfackler

This comment has been minimized.

Copy link

laurenfackler commented Oct 30, 2017

THANK. YOU.

@marlosirapuan

This comment has been minimized.

Copy link

marlosirapuan commented Oct 31, 2017

Woow.. Thanks!

@vernistage

This comment has been minimized.

Copy link

vernistage commented Oct 31, 2017

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

@tibbs001

This comment has been minimized.

Copy link

tibbs001 commented Oct 31, 2017

Thank you!

@edsinclair

This comment has been minimized.

Copy link

edsinclair commented Nov 2, 2017

Worked perfectly for me. Thank you for sharing this.

@hottmanmichael

This comment has been minimized.

Copy link

hottmanmichael commented Nov 3, 2017

πŸ™ 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
@amicming

This comment has been minimized.

Copy link

amicming commented Nov 3, 2017

πŸ‘

@Divoolej

This comment has been minimized.

Copy link

Divoolej commented Nov 4, 2017

Useful, thank you πŸ‘

@LucasArruda

This comment has been minimized.

Copy link

LucasArruda commented Nov 6, 2017

Thanks!!

@hirokishirai

This comment has been minimized.

Copy link

hirokishirai commented Nov 7, 2017

Thanks! It worked well on my mac:)

@SimonCigoj

This comment has been minimized.

Copy link

SimonCigoj commented Nov 8, 2017

Worked for me too

@nzajt

This comment has been minimized.

Copy link

nzajt commented Nov 8, 2017

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.

@etranger

This comment has been minimized.

Copy link

etranger commented Nov 9, 2017

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?

@lievcin

This comment has been minimized.

Copy link

lievcin commented Nov 9, 2017

πŸ‘

@frankolson

This comment has been minimized.

Copy link

frankolson commented Nov 9, 2017

Thanks for the post... lifesaver!

@ulisesflynn

This comment has been minimized.

Copy link

ulisesflynn commented Nov 9, 2017

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.

@hendricius

This comment has been minimized.

Copy link

hendricius commented Nov 10, 2017

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;
@israelins85

This comment has been minimized.

Copy link

israelins85 commented Nov 10, 2017

Thanks, nice work...

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

@Theo-

This comment has been minimized.

Copy link

Theo- commented Nov 12, 2017

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 πŸ‘

@lenapy

This comment has been minimized.

Copy link

lenapy commented Nov 13, 2017

Thank you a lot!

@panozzaj

This comment has been minimized.

Copy link

panozzaj commented Nov 13, 2017

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.

@FCFreyre

This comment has been minimized.

Copy link

FCFreyre commented Nov 13, 2017

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

@lukosan

This comment has been minimized.

Copy link

lukosan commented Nov 15, 2017

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

@digitalbias

This comment has been minimized.

Copy link

digitalbias commented Nov 20, 2017

Thank you for the script!

@danielterwiel

This comment has been minimized.

Copy link

danielterwiel commented Nov 24, 2017

Much obliged

@guilpejon

This comment has been minimized.

Copy link

guilpejon commented Nov 26, 2017

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

@mfts

This comment has been minimized.

Copy link

mfts commented Nov 28, 2017

Thank you soooo sooo much! This is the killer script I was looking for! Upgrading to 10.1 was a bliss πŸ₯‡ πŸ™‡

@MTCoster

This comment has been minimized.

Copy link

MTCoster commented Nov 28, 2017

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.

@MikeMcQuaid

This comment has been minimized.

Copy link

MikeMcQuaid commented Dec 1, 2017

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

@jlstr

This comment has been minimized.

Copy link

jlstr commented Dec 1, 2017

Absolutely Fabulous! <3

@bkonkle

This comment has been minimized.

Copy link

bkonkle commented Dec 1, 2017

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.

@seebq

This comment has been minimized.

Copy link

seebq commented Dec 1, 2017

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!

@tareqtms

This comment has been minimized.

Copy link

tareqtms commented Dec 4, 2017

Grand salute!

@sharq88

This comment has been minimized.

Copy link

sharq88 commented Dec 5, 2017

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

@willingham

This comment has been minimized.

Copy link

willingham commented Dec 7, 2017

Thanks! This was a great time saver!

@thsig

This comment has been minimized.

Copy link

thsig commented Dec 8, 2017

Thank you, sir!

@shaliniJK

This comment has been minimized.

Copy link

shaliniJK commented Dec 9, 2017

Thanks, really helped!

@njugunagathere

This comment has been minimized.

Copy link

njugunagathere commented Dec 10, 2017

Awesome.. works flawlessly..

@stoivo

This comment has been minimized.

Copy link

stoivo commented Dec 10, 2017

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

@krlvi

This comment has been minimized.

Copy link

krlvi commented Dec 11, 2017

Thank you! πŸ‘ ❀️

@abkruse

This comment has been minimized.

Copy link

abkruse commented Dec 12, 2017

Fantastic. Thanks!

@compostbrain

This comment has been minimized.

Copy link

compostbrain commented Dec 14, 2017

thanks!!!

@yu123

This comment has been minimized.

Copy link

yu123 commented Dec 14, 2017

Thank you!! πŸ₯‡

@mathiasjakobsen

This comment has been minimized.

Copy link

mathiasjakobsen commented Dec 14, 2017

Thank you, sir! 🎩

@the9000

This comment has been minimized.

Copy link

the9000 commented Dec 15, 2017

Big thanks!

I wish the brew recipe did that somehow, too.

@devjack

This comment has been minimized.

Copy link

devjack commented Dec 16, 2017

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

@TiO2

This comment has been minimized.

Copy link

TiO2 commented Dec 16, 2017

Thank you.
I used it for 9.5 to 10.1

@pilgrim2go

This comment has been minimized.

Copy link

pilgrim2go commented Dec 20, 2017

It works. thanks

@hyunwoona

This comment has been minimized.

Copy link

hyunwoona commented Dec 20, 2017

Thank you!

@jaakko-sf

This comment has been minimized.

Copy link

jaakko-sf commented Dec 23, 2017

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

@paulcannon33

This comment has been minimized.

Copy link

paulcannon33 commented Dec 28, 2017

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?

@paulcannon33

This comment has been minimized.

Copy link

paulcannon33 commented Dec 28, 2017

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?

@cdbBinary

This comment has been minimized.

Copy link

cdbBinary commented Jan 1, 2018

Thanks!

@yvasilkov

This comment has been minimized.

Copy link

yvasilkov commented Jan 8, 2018

Thanks! Worked like a charm!

@jacortinas

This comment has been minimized.

Copy link

jacortinas commented Jan 8, 2018

Thank you. πŸ™

@MikeMcQuaid

This comment has been minimized.

Copy link

MikeMcQuaid commented Jan 8, 2018

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!

@ransingh

This comment has been minimized.

Copy link

ransingh commented Jan 10, 2018

Thank you.

@kesha-antonov

This comment has been minimized.

Copy link

kesha-antonov commented Jan 12, 2018

Thanks!

@hkulekci

This comment has been minimized.

Copy link

hkulekci commented Jan 14, 2018

thank you.

@scottmtraver

This comment has been minimized.

Copy link

scottmtraver commented Jan 14, 2018

Thank you!

@kristinaluxgroup

This comment has been minimized.

Copy link

kristinaluxgroup commented Jan 15, 2018

Thanks! :)

@constantm

This comment has been minimized.

Copy link

constantm commented Jan 15, 2018

Thank you!

@bmuthoga

This comment has been minimized.

Copy link

bmuthoga commented Jan 18, 2018

You, my friend, deserve a πŸͺ

@dasqueel

This comment has been minimized.

Copy link

dasqueel commented Jan 25, 2018

Holla! thanks!

@Morvarid

This comment has been minimized.

Copy link

Morvarid commented Feb 2, 2018

Thanks!! It worked. Thanks for sharing πŸ‘

@jdraths

This comment has been minimized.

Copy link

jdraths commented Feb 5, 2018

Big ups.

@cawel

This comment has been minimized.

Copy link

cawel commented Feb 7, 2018

Thank you so much ! πŸͺ

@julienvanderkluft

This comment has been minimized.

Copy link

julienvanderkluft commented Feb 13, 2018

Thank you.

@dmcmulle

This comment has been minimized.

Copy link

dmcmulle commented Feb 16, 2018

Thank you so much

@klishevich

This comment has been minimized.

Copy link

klishevich commented Feb 26, 2018

thanks work like a charm!

@tkota0726

This comment has been minimized.

Copy link

tkota0726 commented Mar 5, 2018

Thank you so much. This helped me a lot!

@adw0rd

This comment has been minimized.

Copy link

adw0rd commented Mar 6, 2018

Thanks!

@broncha

This comment has been minimized.

Copy link

broncha commented Mar 11, 2018

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

@angelkbrown

This comment has been minimized.

Copy link

angelkbrown commented Mar 22, 2018

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

@jagdeepsingh

This comment has been minimized.

Copy link

jagdeepsingh commented Mar 22, 2018

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?

@marcelotedeschi

This comment has been minimized.

Copy link

marcelotedeschi commented Mar 23, 2018

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>

@Asher978

This comment has been minimized.

Copy link

Asher978 commented Mar 24, 2018

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

@WouterSchoofs

This comment has been minimized.

Copy link

WouterSchoofs commented Mar 27, 2018

Thanks!

@zstrad44

This comment has been minimized.

Copy link

zstrad44 commented Mar 28, 2018

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

@chastep

This comment has been minimized.

Copy link

chastep commented Apr 1, 2018

This was a life saver! πŸ‘

@ricokareem

This comment has been minimized.

Copy link

ricokareem commented Apr 1, 2018

many...many bows to you

@tamycova

This comment has been minimized.

Copy link

tamycova commented Apr 3, 2018

hero

@juanitodread

This comment has been minimized.

Copy link

juanitodread commented Apr 6, 2018

πŸ‘

@simaob

This comment has been minimized.

Copy link

simaob commented Apr 9, 2018

πŸ™‡

@p-funky

This comment has been minimized.

Copy link

p-funky commented Apr 10, 2018

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.

@p-funky

This comment has been minimized.

Copy link

p-funky commented Apr 11, 2018

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.

@reverie

This comment has been minimized.

Copy link

reverie commented Apr 11, 2018

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

@lavikacybrilla

This comment has been minimized.

Copy link

lavikacybrilla commented Apr 12, 2018

Worked. thanks πŸ‘

@abhannan

This comment has been minimized.

Copy link

abhannan commented Apr 16, 2018

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

@AndrewNduati

This comment has been minimized.

Copy link

AndrewNduati commented Apr 27, 2018

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.

@rpapaz

This comment has been minimized.

Copy link

rpapaz commented May 1, 2018

You are the best guys. Very helpful thx

@indicolite

This comment has been minimized.

Copy link

indicolite commented May 16, 2018

thanks, upgraded to postgresql@10.4 ;_)

@jessamynsmith

This comment has been minimized.

Copy link

jessamynsmith commented May 20, 2018

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

@AlexeySoshin

This comment has been minimized.

Copy link

AlexeySoshin commented May 24, 2018

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.

@chriswickett

This comment has been minimized.

Copy link

chriswickett commented Jun 7, 2018

Thank you!

@kohgpat

This comment has been minimized.

Copy link

kohgpat commented Jun 8, 2018

Thank you a lot, sir!

@radiocontroller

This comment has been minimized.

Copy link

radiocontroller commented Jun 13, 2018

bravo!!!

@toby5box

This comment has been minimized.

Copy link

toby5box commented Jun 18, 2018

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

@spaceguy01

This comment has been minimized.

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

This comment has been minimized.

Copy link

djtsheps commented Jun 27, 2018

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

This comment has been minimized.

Copy link

lance-hardy commented Jun 29, 2018

Fantastic! Worked like a charm.

@nicregez

This comment has been minimized.

Copy link

nicregez commented Jul 3, 2018

Fabulous, many, many thanks

@maxlipsky

This comment has been minimized.

Copy link

maxlipsky commented Jul 4, 2018

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

This comment has been minimized.

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

This comment has been minimized.

Copy link

sujithsreekumar commented Jul 25, 2018

Awesome! Worked as charm πŸ‘

@aslam

This comment has been minimized.

Copy link

aslam commented Aug 4, 2018

Works like a charm, great work. Thanks.

@rimiti

This comment has been minimized.

Copy link

rimiti commented Aug 7, 2018

πŸ’―

@ramhoj

This comment has been minimized.

Copy link

ramhoj commented Aug 9, 2018

Thank you!

@crwhitesides

This comment has been minimized.

Copy link

crwhitesides commented Aug 30, 2018

Thanks!

@warrenrentlytics

This comment has been minimized.

Copy link

warrenrentlytics commented Sep 12, 2018

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

This comment has been minimized.

Copy link

SumeetGohil commented Sep 24, 2018

Works like charm (y)

@pjatx

This comment has been minimized.

Copy link

pjatx commented Oct 1, 2018

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

@ArturT

This comment has been minimized.

Copy link

ArturT commented Oct 6, 2018

Thanks for the help!

@clint74

This comment has been minimized.

Copy link

clint74 commented Oct 10, 2018

Great job. Thank you!

@InvisibleTech

This comment has been minimized.

Copy link

InvisibleTech commented Oct 21, 2018

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

This comment has been minimized.

Copy link

clemmy commented Oct 22, 2018

Thanks so much for this!

@Rubao2014

This comment has been minimized.

Copy link

Rubao2014 commented Nov 15, 2018

Thank you very much!

@darkdefender27

This comment has been minimized.

Copy link

darkdefender27 commented Dec 11, 2018

Thanks a lot!! Works seamlessly.

@masterkain

This comment has been minimized.

Copy link

masterkain commented Dec 17, 2018

brew postgresql-upgrade-database

@fanckush

This comment has been minimized.

Copy link

fanckush commented Dec 29, 2018

Thanks @masterkain! perfect

@phlippieb

This comment has been minimized.

Copy link

phlippieb commented Jan 12, 2019

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

This comment has been minimized.

Copy link

DineshkumarElumalai commented Jan 21, 2019

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

This comment has been minimized.

Copy link

somenugget commented Feb 6, 2019

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

@alxpereira

This comment has been minimized.

Copy link

alxpereira commented Feb 19, 2019

Genius, saved my day.

@daliadaud

This comment has been minimized.

Copy link

daliadaud commented Mar 13, 2019

awesome! thank you, you saved me alot of time

@lyonsun

This comment has been minimized.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.