CODEWITHSUNDEEP
postgresqlhomebrewupgrade
Robincognito
Robincognito

Reputation: 27

How to migrate PostgreSQL data from Postgres.app version to Homebrew-installed version

I installed PostgresSQL (ver. 9.4) several years ago using Postgres.app but I'd like to use Homebrew to manage PostgreSQL from now on. I'm on Mac OS 10.14. I've installed the latest version (12.1) using Homebrew, and I'd like to know how to move my data to this new version. All the update guides I've read assume that the old and new versions were installed in the same location.

Data directories:

/Users/robin/Library/Application Support/Postgres/var-9.4 /usr/local/Cellar/postgresql/12.1

Upvotes: 1

Views: 2034

Answers (1)

richyen
richyen

Reputation: 9958

Assuming you know where the .../data/ directory of the Postgres.app lives (check your preferences), you should be able to use pg_upgrade to migrate from 9.4 to 12. You will need to do a fresh initdb of the v. 12 cluster first.

Here are some steps, based on your environment, assuming 1) postgres v. 12 is not running on your system (or you stopped it), and 2) you have not created a database in v. 12 yet:

export PGDATANEW=/Users/robin/pgsql/12/data # (or desired location)
mkdir -p $PGDATANEW
/usr/local/Cellar/postgresql/12.1/bin/initdb -D $PGDATANEW -U postgres
export PGDATAOLD="/Users/robin/Library/Application Support/Postgres/var-9.4"
export PGBINOLD=/Applications/Postgres.app/Contents/Versions/latest/bin
export PGBINNEW=/usr/local/Cellar/postgresql/12.1/bin
/usr/local/Cellar/postgresql/12.1/bin/pg_upgrade -U postgres

If you already have an existing v. 12 database, you will need to dump the data, save it off somewhere, and wipe the existing v. 12 data folder before performing the initdb step

Option 2: The other option is to pg_dump/pg_dumpall your 9.4 data to a file, then import it into your v. 12 database

Disclosure: I work for EnterpriseDB (EDB)

Upvotes: 2

Related Questions