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 |
This comment has been minimized.
This comment has been minimized.
Worked for me too, thanks so much |
This comment has been minimized.
This comment has been minimized.
Worked great for me as well -- thanks for posting. I get a bit trigger happy with If on the 'actual upgrade' step (line 26) you get the following error message:
It's due to the server not being able to stop because of your configs. Run the following to get unblocked:
|
This comment has been minimized.
This comment has been minimized.
If @huxley-dog's command won't work try: |
This comment has been minimized.
This comment has been minimized.
Ruby users will want to run this too after the upgrade is complete: $ gem uninstall pg
$ gem install pg |
This comment has been minimized.
This comment has been minimized.
It worked for me, thank you very much. |
This comment has been minimized.
This comment has been minimized.
Perfect, thank you! |
This comment has been minimized.
This comment has been minimized.
Thank you! worked well on my mac! |
This comment has been minimized.
This comment has been minimized.
Perfect. And this helped me with the 'actual upgrade' step (line 26): https://gist.github.com/eoinkelly/fd80465942c8ca4bd5c0#gistcomment-1372195 |
This comment has been minimized.
This comment has been minimized.
Thank you, it worked! |
This comment has been minimized.
This comment has been minimized.
Someone had it working with Postgis? When restoring a postgis database during the upgrade I get |
This comment has been minimized.
This comment has been minimized.
Thanks, IT worked |
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
This comment has been minimized.
thanks |
This comment has been minimized.
This comment has been minimized.
Works great. Thanks! |
This comment has been minimized.
This comment has been minimized.
Thank you very much for taking the time to document and publish these instructions. Greatly appreciated. |
This comment has been minimized.
This comment has been minimized.
Just perfect, thanks! |
This comment has been minimized.
This comment has been minimized.
Really awesome. It works well on my machine. Thanks! @huxley-dog |
This comment has been minimized.
This comment has been minimized.
THANK. YOU. |
This comment has been minimized.
This comment has been minimized.
Woow.. Thanks! |
This comment has been minimized.
This comment has been minimized.
Thank you so much. Lifesaver. I was able to use |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
Worked perfectly for me. Thank you for sharing this. |
This comment has been minimized.
This comment has been minimized.
I had to make sure all brew postgres servers were stopped before running the upgrade with
|
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
Useful, thank you |
This comment has been minimized.
This comment has been minimized.
Thanks!! |
This comment has been minimized.
This comment has been minimized.
Thanks! It worked well on my mac:) |
This comment has been minimized.
This comment has been minimized.
Worked for me too |
This comment has been minimized.
This comment has been minimized.
Thanks, that worked. But you have to stop postgresql before you run
As @huxley-dog and @aszulinski have already noted. |
This comment has been minimized.
This comment has been minimized.
Unfortunately, this failed on my system due to this error:
Is there any way to painlessly migrate postgis too? |
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
Thanks for the post... lifesaver! |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
In my case I had postgis installed. Thus upgrading one database failed. So i dropped it, restarting the old cluster:
Then:
|
This comment has been minimized.
This comment has been minimized.
Thanks, nice work... In my case I need run before pg_update: brew services stop postgresql |
This comment has been minimized.
This comment has been minimized.
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:
The rest of the migration still works fine |
This comment has been minimized.
This comment has been minimized.
Thank you a lot! |
This comment has been minimized.
This comment has been minimized.
Awesome work, thanks! This saved me a ton of time! Appreciated the comments as well. For future fixers, it looks like:
So others will probably need to change some of the paths accordingly as time moves on. |
This comment has been minimized.
This comment has been minimized.
Worked great! I just ended up having to go into |
This comment has been minimized.
This comment has been minimized.
Thanks v. much for the script. I also ran |
This comment has been minimized.
This comment has been minimized.
Thank you for the script! |
This comment has been minimized.
This comment has been minimized.
Much obliged |
This comment has been minimized.
This comment has been minimized.
Thank you so much! My mac randomly restarted and I couldn't use postgres after that |
This comment has been minimized.
This comment has been minimized.
Thank you soooo sooo much! This is the killer script I was looking for! Upgrading to 10.1 was a bliss |
This comment has been minimized.
This comment has been minimized.
The old data directory was working perfectly fine under 9.6.6 beforehand. |
This comment has been minimized.
This comment has been minimized.
Hey all: I've opened a PR to try and make this better in Homebrew/homebrew-core#21244. Would you mind reviewing and testing? |
This comment has been minimized.
This comment has been minimized.
Absolutely Fabulous! <3 |
This comment has been minimized.
This comment has been minimized.
Thank you!! I skipped the link/unlink steps and adjusted the
I might add that I just have dev data on my local machine, nothing important. |
This comment has been minimized.
This comment has been minimized.
I too skipped linking/unlinking and ran the following
However, it failed because the brew services finally rebooted up in the middle of the upgrade. So be sure to:
Before doing anything. Thx for this! |
This comment has been minimized.
This comment has been minimized.
Grand salute! |
This comment has been minimized.
This comment has been minimized.
I had to use 9.6.6 and 10.1 but that was all! Everything worked just fine. Awesome! Thanks! |
This comment has been minimized.
This comment has been minimized.
Thanks! This was a great time saver! |
This comment has been minimized.
This comment has been minimized.
Thank you, sir! |
This comment has been minimized.
This comment has been minimized.
Thanks, really helped! |
This comment has been minimized.
This comment has been minimized.
Awesome.. works flawlessly.. |
This comment has been minimized.
This comment has been minimized.
I was getting
The problem was the LC_ALL was not set.
Set it with |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
Fantastic. Thanks! |
This comment has been minimized.
This comment has been minimized.
thanks!!! |
This comment has been minimized.
This comment has been minimized.
Thank you!! |
This comment has been minimized.
This comment has been minimized.
Thank you, sir! |
This comment has been minimized.
This comment has been minimized.
Big thanks! I wish the brew recipe did that somehow, too. |
This comment has been minimized.
This comment has been minimized.
This is perfect thank you @giannisp! For anyone else that encounters Anyone that has ussies with the locale, try
/cc @MTCoster for version issues :) |
This comment has been minimized.
This comment has been minimized.
Thank you. |
This comment has been minimized.
This comment has been minimized.
It works. thanks |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
thank you for sharing this, worked perfectly for my 9.5 to 10.1 upgrade |
This comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
This comment has been minimized.
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? |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
Thanks! Worked like a charm! |
This comment has been minimized.
This comment has been minimized.
Thank you. |
This comment has been minimized.
This comment has been minimized.
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! |
This comment has been minimized.
This comment has been minimized.
Thank you. |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
thank you. |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
Thanks! :) |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
You, my friend, deserve a |
This comment has been minimized.
This comment has been minimized.
Holla! thanks! |
This comment has been minimized.
This comment has been minimized.
Thanks!! It worked. Thanks for sharing |
This comment has been minimized.
This comment has been minimized.
Big ups. |
This comment has been minimized.
This comment has been minimized.
Thank you so much ! |
This comment has been minimized.
This comment has been minimized.
Thank you. |
This comment has been minimized.
This comment has been minimized.
Thank you so much |
This comment has been minimized.
This comment has been minimized.
thanks work like a charm! |
This comment has been minimized.
This comment has been minimized.
Thank you so much. This helped me a lot! |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
Anyone looking to upgrade with postgis, this works https://gist.github.com/smellman/2e51b1cf449c499ad5deec454a16a7bc |
This comment has been minimized.
This comment has been minimized.
This worked from 9.4 -> 10.0 for me as well. Thank you! |
This comment has been minimized.
This comment has been minimized.
I am on macOS High Sierra 10.13.3 and upgrading postgres from v9.6 to v10.3 using above gist. When i do 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 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? |
This comment has been minimized.
This comment has been minimized.
Thanks for this !! Can you try |
This comment has been minimized.
This comment has been minimized.
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..
|
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
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:
|
This comment has been minimized.
This comment has been minimized.
This was a life saver! |
This comment has been minimized.
This comment has been minimized.
many...many bows to you |
This comment has been minimized.
This comment has been minimized.
hero |
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
I got up to this point
Yet on checking, I can see that the role exists. Please advise anyone. |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
For anyone who missed MikeMcQuaid's comment above, you can now use |
This comment has been minimized.
This comment has been minimized.
Worked. thanks |
This comment has been minimized.
This comment has been minimized.
For anyone having error that:
Basically we have difference versions of postgreql installed. Following command worked for me:
|
This comment has been minimized.
This comment has been minimized.
This works however, I think Brew have added a command that will automatically migrate the data after upgrading.
Looking at the logs, it pretty much does what you have here. |
This comment has been minimized.
This comment has been minimized.
You are the best guys. Very helpful thx |
This comment has been minimized.
This comment has been minimized.
thanks, upgraded to postgresql@10.4 ;_) |
This comment has been minimized.
This comment has been minimized.
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: |
This comment has been minimized.
This comment has been minimized.
Thanks. |
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
Thank you a lot, sir! |
This comment has been minimized.
This comment has been minimized.
bravo!!! |
This comment has been minimized.
This comment has been minimized.
This is one of the most useful gists in existence. I wish they'd make this a seamless process. |
This comment has been minimized.
This comment has been minimized.
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 When I do ' postgres -V' I get postgres (PostgreSQL) 10.4 |
This comment has been minimized.
This comment has been minimized.
Thanks worked perfectly !!! I just has to use |
This comment has been minimized.
This comment has been minimized.
Fantastic! Worked like a charm. |
This comment has been minimized.
This comment has been minimized.
Fabulous, many, many thanks |
This comment has been minimized.
This comment has been minimized.
If you got |
This comment has been minimized.
This comment has been minimized.
Still working, 9.6 to 10.4, soooooo didn't feel like grinding through this myself, thanks. |
This comment has been minimized.
This comment has been minimized.
Awesome! Worked as charm |
This comment has been minimized.
This comment has been minimized.
Works like a charm, great work. Thanks. |
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
Thank you! |
This comment has been minimized.
This comment has been minimized.
Thanks! |
This comment has been minimized.
This comment has been minimized.
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:
and then made minor changes to this script to account for working off 10.4 to 10.5. after that, |
This comment has been minimized.
This comment has been minimized.
Works like charm (y) |
This comment has been minimized.
This comment has been minimized.
Thanks @warrenrentlytics - ran into the same thing and that was helpful |
This comment has been minimized.
This comment has been minimized.
Thanks for the help! |
This comment has been minimized.
This comment has been minimized.
Great job. Thank you! |
This comment has been minimized.
This comment has been minimized.
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. |
This comment has been minimized.
This comment has been minimized.
Thanks so much for this! |
This comment has been minimized.
This comment has been minimized.
Thank you very much! |
This comment has been minimized.
This comment has been minimized.
Thanks a lot!! Works seamlessly. |
This comment has been minimized.
This comment has been minimized.
|
This comment has been minimized.
This comment has been minimized.
Thanks @masterkain! perfect |
This comment has been minimized.
This comment has been minimized.
Incredibly helpful, thanks so much.
which I got around by adding |
This comment has been minimized.
This comment has been minimized.
I'm trying to upgrade my postgresql from 10.6_1 to 11.1_1
|
This comment has been minimized.
This comment has been minimized.
Thank you. Migration from 10 to 11 was successful :+1 |
This comment has been minimized.
This comment has been minimized.
Genius, saved my day. |
This comment has been minimized.
This comment has been minimized.
awesome! thank you, you saved me alot of time |
This comment has been minimized.
This comment has been minimized.
Thank you. One comment in my experience on this issue, at the very end, I had to run:
one after another in order to get rid of the following log repeating every 10s in postgres.log file:
Credits for finding the PID of postmaster.pid: |
This comment has been minimized.
This comment has been minimized.
thanks. it's working. i like this. |
This comment has been minimized.
Awesome, it worked perfectly on my mac.👍