Instantly share code, notes, and snippets.

Embed
What would you like to do?
How to migrate a Homebrew-installed PostgreSQL database to a new major version (9.3 to 9.4) on OS X. See upgraded version of this guide: http://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/

This guide assumes that you recently run brew upgrade postgresql and discovered to your dismay that you accidentally bumped from one major version to another: say 9.3.x to 9.4.x. Yes, that is a major version bump in PG land.

First let's check something.

brew info postgresql

The top of what gets printed as a result is the most important:

postgresql: stable 9.4.1 (bottled)
http://www.postgresql.org/
Conflicts with: postgres-xc
/usr/local/Cellar/postgresql/9.3.2 (2924 files, 39M)
  Poured from bottle
/usr/local/Cellar/postgresql/9.4.1 (2996 files, 40M) *
  Poured from bottle

The interesting bit is that there are two versions installed on my systems. Homebrew thankfully installs the Postgres binaries in different subfolders, which is very important to be able to migrate data from one version to the other.

As you can see I have my old version installed in /usr/local/Cellar/postgresql/9.3.2. The new version is in /usr/local/Cellar/postgresql/9.4.1.

First ensure that you have the correct PG binaries running:

which psql
/usr/local/bin/psql

psql --version
psql (PostgreSQL) 9.4.1

This means that the pg_upgrade binary we'll be using is also the new one. But let's not assume:

pg_upgrade --version
pg_upgrade (PostgreSQL) 9.4.1

Good good.

Mise en place

The directory where your actual database data is stored is different from the one where your PostgreSQL binaries are installed. Homebrew installs the data directory in /usr/local/var/postgres/ and doesn't touch that data folder when you upgrade from one version to the next. This is a good thing because if Homebrew tried to install a brand new database (with initdb) it could squash all your existing data.

First I recommend moving your existing data to a directory with a different name:

mv /usr/local/var/postgres/ /usr/local/var/postgres.9.3.backup/

Now that the old data directory has been "moved", you can safely create a brand new clean database:

initdb /usr/local/var/postgres/
The files belonging to this database system will be owned by user "olivierlacan".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /usr/local/var/postgres ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /usr/local/var/postgres/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    postgres -D /usr/local/var/postgres
or
    pg_ctl -D /usr/local/var/postgres -l logfile start

Your output will be slightly different. For one, the user won't be olivierlacan but whatever your system user is. You can find that out easily with whoami.

Interesting to note, but if you use PostgreSQL with Rails. This means there will be no password on your development database, so you can skip the password field in database.yml or leave it *completely empty.

pg_upgrade

Let's get to it.

First we have to make sure both database servers are not running when we do the upgrade:

pg_ctl -D /usr/local/var/postgres stop -m fast
pg_ctl -D /usr/local/var/postgres.9.3.backup stop -m fast 

If you get the following message it's possible that you have PG in launchctl which may prevent you from stopping the server:

pg_ctl: server does not shut down

In that case let's remove PG from launchctl for now, you can add it back later by following the instructions given on brew info postgres:

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

Then try stopping the server again:

pg_ctl -D /usr/local/var/postgres stop -m fast

This would be good news:

pg_ctl: PID file "/usr/local/var/postgres/postmaster.pid" does not exist
Is server running?

Assuming you're dealing with the same version numbers I'm dealing with (you probably aren't, so change them when running this on your machine), this is what the pg_upgrade command should look like when you run it:

$ pg_upgrade -b /usr/local/Cellar/postgresql/9.3.2/bin/ -B /usr/local/Cellar/postgresql/9.4.1/bin/ -d /usr/local/var/postgres.9.3.backup/ -D /usr/local/var/postgres

Lowercase flags (-b and -d) are for old binary and data directories respectively. Their uppercase counterparts are for their new equivalents.

You should see the following output immediately if the upgrade process is starting:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system OID user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for invalid "line" user columns                    ok
Creating dump of global objects                             ok
Creating dump of database schemas
                                                            ok
Checking for presence of required libraries                 ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster                       ok
Freezing all rows on the new cluster                        ok
Deleting files from new pg_clog                             ok
Copying old pg_clog to new server                           ok
Setting next transaction ID and epoch for new cluster       ok
Deleting files from new pg_multixact/offsets                ok
Copying old pg_multixact/offsets to new server              ok
Deleting files from new pg_multixact/members                ok
Copying old pg_multixact/members to new server              ok
Setting next multixact ID and offset for new cluster        ok
Resetting WAL archives                                      ok
Setting frozenxid and minmxid counters in new cluster       ok
Restoring global objects in the new cluster                 ok
Adding support functions to new cluster                     ok
Restoring database schemas in the new cluster
                                                            ok
Creating newly-required TOAST tables                        ok
Removing support functions from new cluster                 ok
Copying user relation files
                                                            ok 
Setting next OID for new cluster                            ok
Sync data directory to disk                                 ok
Creating script to analyze new cluster                      ok
Creating script to delete old cluster                       ok

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    analyze_new_cluster.sh

Running this script will delete the old cluster's data files:
    delete_old_cluster.sh

You're done!

Restarting

You manually shut down PG during this upgrade so now it won't be running unless you follow the brew info postgres instructions:

ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Once PG is running you could run the optimization script recommended by pg_upgrade. It was created in whichever directory you ran pg_upgrade in, and you can run it with:

./analyze_new_cluster.sh
This script will generate minimal optimizer statistics rapidly
so your system is usable, and then gather statistics twice more
with increasing accuracy.  When it is done, your system will
have the default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for
any tables, you might want to remove them and restore them after
running this script because they will delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel
this script and run:
    "/usr/local/Cellar/postgresql/9.4.1/bin/vacuumdb" --all --analyze-only
    
(...)

Troubleshooting

You may encounter the following error:

*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

This means you have at least one PG server running. So go back to the beginning of this section and make real sure you shut down all your servers.

@jacqueline-homan

This comment has been minimized.

Show comment
Hide comment
@jacqueline-homan

jacqueline-homan Jan 13, 2016

I ran the command pg_upgrade_server.log and it did not work for me. In fact, I have been unable to get Postgres, MySQL and Mongo working and have been struggling for a week with a broken dev environment because of it, so I'm unable to do anything. I am relatively new to programming and even newer to using a Mac so the whole mess with Homebrew after the El Capitan upgrade has done nothing but cause me problems that I'm unable to fix without some very serious, extensive hand-holding. If ANYONE who does know this stuff and how to fix these problems could PLEASE help me, it would be greatly appreciated.

Here is a partially screencapped detail documenting just some of the problems I've been having:

postgrenotworking

postgres_problems1

progress

still not working

stuckatstep4

whoami

After a whole week of being unable to get my problems solved, the only things I was able to get working correctly was RVM, zshell, and my D compiler (dmd). But I am unable to get Postgresql, MySQL and Mongo back working for me.

jacqueline-homan commented Jan 13, 2016

I ran the command pg_upgrade_server.log and it did not work for me. In fact, I have been unable to get Postgres, MySQL and Mongo working and have been struggling for a week with a broken dev environment because of it, so I'm unable to do anything. I am relatively new to programming and even newer to using a Mac so the whole mess with Homebrew after the El Capitan upgrade has done nothing but cause me problems that I'm unable to fix without some very serious, extensive hand-holding. If ANYONE who does know this stuff and how to fix these problems could PLEASE help me, it would be greatly appreciated.

Here is a partially screencapped detail documenting just some of the problems I've been having:

postgrenotworking

postgres_problems1

progress

still not working

stuckatstep4

whoami

After a whole week of being unable to get my problems solved, the only things I was able to get working correctly was RVM, zshell, and my D compiler (dmd). But I am unable to get Postgresql, MySQL and Mongo back working for me.

@jacqueline-homan

This comment has been minimized.

Show comment
Hide comment
@jacqueline-homan

jacqueline-homan commented Jan 13, 2016

unable_to_get_any_dbs_working

unable_to_get_any_dbs_working2

@mayra-cabrera

This comment has been minimized.

Show comment
Hide comment
@mayra-cabrera

mayra-cabrera Mar 15, 2016

This is awesome! Thank you!

mayra-cabrera commented Mar 15, 2016

This is awesome! Thank you!

@tidelake

This comment has been minimized.

Show comment
Hide comment
@tidelake

tidelake Sep 12, 2016

Thank you kindly!

tidelake commented Sep 12, 2016

Thank you kindly!

@rpanos

This comment has been minimized.

Show comment
Hide comment
@rpanos

rpanos Sep 26, 2016

I CANNOT thank you enough. Other posts had the migration failing and I was completely panicking.

I also went and posted you here https://www.reddit.com/r/postgres/comments/54j9fw/the_proper_way_to_upgrade_your_data/

THANK YOU SO MUCH!!

rpanos commented Sep 26, 2016

I CANNOT thank you enough. Other posts had the migration failing and I was completely panicking.

I also went and posted you here https://www.reddit.com/r/postgres/comments/54j9fw/the_proper_way_to_upgrade_your_data/

THANK YOU SO MUCH!!

@laurenfackler

This comment has been minimized.

Show comment
Hide comment
@laurenfackler

laurenfackler Dec 12, 2016

Thank you for this!

laurenfackler commented Dec 12, 2016

Thank you for this!

@ngoue

This comment has been minimized.

Show comment
Hide comment
@ngoue

ngoue Jan 25, 2017

Fantastic! Thanks for putting this together.

I'm currently running Homebrew 1.1.7 and just wanted to mention that the postgres service can be started again with brew services start postgresql instead of manually loading the process back into launchctl.

ngoue commented Jan 25, 2017

Fantastic! Thanks for putting this together.

I'm currently running Homebrew 1.1.7 and just wanted to mention that the postgres service can be started again with brew services start postgresql instead of manually loading the process back into launchctl.

@warniel08

This comment has been minimized.

Show comment
Hide comment
@warniel08

warniel08 Mar 9, 2017

Thank you so much!!! This worked for me. 👍 🥇

warniel08 commented Mar 9, 2017

Thank you so much!!! This worked for me. 👍 🥇

@jcreed

This comment has been minimized.

Show comment
Hide comment
@jcreed

jcreed Mar 22, 2017

Worked like charm !!

jcreed commented Mar 22, 2017

Worked like charm !!

@esteedqueen

This comment has been minimized.

Show comment
Hide comment
@esteedqueen

esteedqueen Mar 28, 2017

This fixed all the issues not highlighted on StackOverflow answers! Thank you!

esteedqueen commented Mar 28, 2017

This fixed all the issues not highlighted on StackOverflow answers! Thank you!

@catpeters

This comment has been minimized.

Show comment
Hide comment
@catpeters

catpeters Apr 20, 2017

This fixed my postgres issues from finally upgrading to Sierra! Thanks!

catpeters commented Apr 20, 2017

This fixed my postgres issues from finally upgrading to Sierra! Thanks!

@knowsuchagency

This comment has been minimized.

Show comment
Hide comment
@knowsuchagency

knowsuchagency Apr 24, 2017

You are the man

knowsuchagency commented Apr 24, 2017

You are the man

@rheaton

This comment has been minimized.

Show comment
Hide comment
@rheaton

rheaton Jun 20, 2017

Great docs, thanks Olivier!

rheaton commented Jun 20, 2017

Great docs, thanks Olivier!

@pcj

This comment has been minimized.

Show comment
Hide comment
@pcj

pcj Jun 24, 2017

If you encounter:

connection to database failed: fe_sendauth: no password supplied

In my case I edited vi /usr/local/var/postgres/pg_hba.conf and change the line local all all md5 to local all all trust to give pg_upgrade access to the old database. Don't forget to reset security settings on the new database once you are done.

pcj commented Jun 24, 2017

If you encounter:

connection to database failed: fe_sendauth: no password supplied

In my case I edited vi /usr/local/var/postgres/pg_hba.conf and change the line local all all md5 to local all all trust to give pg_upgrade access to the old database. Don't forget to reset security settings on the new database once you are done.

@dracco1993

This comment has been minimized.

Show comment
Hide comment
@dracco1993

dracco1993 Jul 5, 2017

Thanks for the walkthrough Olivier!

dracco1993 commented Jul 5, 2017

Thanks for the walkthrough Olivier!

@ccmcbeck

This comment has been minimized.

Show comment
Hide comment
@ccmcbeck

ccmcbeck Jul 22, 2017

Thanks, Olivier. Miss hearing you on Ruby5

ccmcbeck commented Jul 22, 2017

Thanks, Olivier. Miss hearing you on Ruby5

@dangodev

This comment has been minimized.

Show comment
Hide comment
@dangodev

dangodev Aug 11, 2017

I love searching the Internet for info and finding my friends! 👋

dangodev commented Aug 11, 2017

I love searching the Internet for info and finding my friends! 👋

@rgo

This comment has been minimized.

Show comment
Hide comment
@rgo

rgo Aug 29, 2017

Thanks, Olivier. This worked for upgrading from pg 9.4.x to 9.6.x 👍

rgo commented Aug 29, 2017

Thanks, Olivier. This worked for upgrading from pg 9.4.x to 9.6.x 👍

@synerJohnny

This comment has been minimized.

Show comment
Hide comment
@synerJohnny

synerJohnny Nov 26, 2017

THANK YOU!! As rpanos said "I can't thank you enough"!! DataGrip is no longer refusing to connect to my Postgres instance!

synerJohnny commented Nov 26, 2017

THANK YOU!! As rpanos said "I can't thank you enough"!! DataGrip is no longer refusing to connect to my Postgres instance!

@synerJohnny

This comment has been minimized.

Show comment
Hide comment
@synerJohnny

synerJohnny Nov 26, 2017

NOTE: I followed these instructions to clean up a rather crufted installation and was migrating from 9.5.3 to 9.6.5 to 10.1

This meant I had to perform the process twice. Once to migrate from 9.5.3 to 9.6.6 and then again to migrate from 9.6.5 to 10.1 (I'm sure someone has a better/cleaner way to do this, but it worked for me).

I ran into a little issue performing the second run when pg_upgrade neglected to generate the analyze_new_cluster.sh script.

In order to get around this I found the important bits from the script generated for the 9.5.3 to 9.6.5 migration and ran that from the command line.

For me, this looked like:

$ /usr/local/Cellar/postgresql/10.1/bin/vacuumdb --all --analyze-in-stages

The output was exactly the same as when I ran analyze_new_cluster.sh after the migration to 9.6.5

synerJohnny commented Nov 26, 2017

NOTE: I followed these instructions to clean up a rather crufted installation and was migrating from 9.5.3 to 9.6.5 to 10.1

This meant I had to perform the process twice. Once to migrate from 9.5.3 to 9.6.6 and then again to migrate from 9.6.5 to 10.1 (I'm sure someone has a better/cleaner way to do this, but it worked for me).

I ran into a little issue performing the second run when pg_upgrade neglected to generate the analyze_new_cluster.sh script.

In order to get around this I found the important bits from the script generated for the 9.5.3 to 9.6.5 migration and ran that from the command line.

For me, this looked like:

$ /usr/local/Cellar/postgresql/10.1/bin/vacuumdb --all --analyze-in-stages

The output was exactly the same as when I ran analyze_new_cluster.sh after the migration to 9.6.5

@weilandia

This comment has been minimized.

Show comment
Hide comment
@weilandia

weilandia commented Nov 27, 2017

image

@logan-baker

This comment has been minimized.

Show comment
Hide comment
@logan-baker

logan-baker Dec 8, 2017

I couldn't initially stop the server from running but it turns out it wasn't in LaunchingAgents for some reason. So I just used brew services list to see which was causing the problem and where it was. Then:

launchctl unload -w /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist
pg_upgrade -b /usr/local/Cellar/postgresql@9.4/9.4.14/bin/ -B /usr/local/Cellar/postgresql/10.0/bin/ -d /usr/local/var/postgresql@9.4/ -D /usr/local/var/postgres

After that the pg_update worked like a charm. Thanks for this!

logan-baker commented Dec 8, 2017

I couldn't initially stop the server from running but it turns out it wasn't in LaunchingAgents for some reason. So I just used brew services list to see which was causing the problem and where it was. Then:

launchctl unload -w /usr/local/opt/postgresql/homebrew.mxcl.postgresql.plist
pg_upgrade -b /usr/local/Cellar/postgresql@9.4/9.4.14/bin/ -B /usr/local/Cellar/postgresql/10.0/bin/ -d /usr/local/var/postgresql@9.4/ -D /usr/local/var/postgres

After that the pg_update worked like a charm. Thanks for this!

@cy2003

This comment has been minimized.

Show comment
Hide comment
@cy2003

cy2003 Dec 12, 2017

Thank you so much! You saved the day. :)

cy2003 commented Dec 12, 2017

Thank you so much! You saved the day. :)

@dja

This comment has been minimized.

Show comment
Hide comment
@dja

dja Dec 30, 2017

This solved all my problems! Thanks for sharing

dja commented Dec 30, 2017

This solved all my problems! Thanks for sharing

@olivierlacan

This comment has been minimized.

Show comment
Hide comment
@olivierlacan

olivierlacan Feb 7, 2018

I've updated this guide with a proper blog post you can find here: http://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/

It covers the new brew services and the new brew postgresql-upgrade-database command which should (hopefully) make it unnecessary for most people.

Owner

olivierlacan commented Feb 7, 2018

I've updated this guide with a proper blog post you can find here: http://olivierlacan.com/posts/migrating-homebrew-postgres-to-a-new-version/

It covers the new brew services and the new brew postgresql-upgrade-database command which should (hopefully) make it unnecessary for most people.

@cvickery

This comment has been minimized.

Show comment
Hide comment
@cvickery

cvickery Feb 13, 2018

This gist is spectacular, even with the new brew postgresql-upgrade-database command now available. I had a system running 9.5 that I hadn't been using even though homebrew had nicely upgraded postgres through a dozen versions to 10.2. Running brew postgresql-upgrade-database took me up to a working version of 10.2 without a hitch.

Olivier -- the new blog post is great, and I'm sure it will soon surpass this gist in Google searches ... but there are a couple of items you might want to address:

  1. The statement, “The process I describe below will work for incremental version updates: for example 9.5 to 9.6. But it will probably not work if you jump versions: for example 9.5 to 10.0” doesn't seem correct given my experience.
  2. The text is cut off at the end of the blog post.

Many thanks!

cvickery commented Feb 13, 2018

This gist is spectacular, even with the new brew postgresql-upgrade-database command now available. I had a system running 9.5 that I hadn't been using even though homebrew had nicely upgraded postgres through a dozen versions to 10.2. Running brew postgresql-upgrade-database took me up to a working version of 10.2 without a hitch.

Olivier -- the new blog post is great, and I'm sure it will soon surpass this gist in Google searches ... but there are a couple of items you might want to address:

  1. The statement, “The process I describe below will work for incremental version updates: for example 9.5 to 9.6. But it will probably not work if you jump versions: for example 9.5 to 10.0” doesn't seem correct given my experience.
  2. The text is cut off at the end of the blog post.

Many thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment