-
-
Save giannisp/ebaca117ac9e44231421f04e7796d5ca to your computer and use it in GitHub Desktop.
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 |
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.
This is one of the most useful gists in existence. I wish they'd make this a seamless process.