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
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)
data. As for the psql binaries, I'm not sure what I'm looking for.pg_upgrade: "could not open version file: /Users/robin/Library/Application\ Support/Postgres/var-9.4/PG_VERSION"
SHOW data_directory; in Postgres.app, what do you get? And what does ls /Users/robin/Library/Application\ Support/Postgres/var-9.4 show?SHOW data_directory returns the same directory that was in the Postgress.app preferences. PG_Version is in that directory, along with many other files and folders.