Migrate from PG 14 to PG 16 on MacOS

migrate.md

Step-by-Step Guide

Install Homebrew (if not already installed)

Open a terminal and run the following command:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Check Installed PostgreSQL Versions

To see if PostgreSQL is already installed via Homebrew, you can use:

brew list | grep postgres

Install PostgreSQL 16

Homebrew maintains formulae for different versions of PostgreSQL. To install PostgreSQL 16, you can use:

brew install postgresql@16

Switch Between PostgreSQL Versions

You can switch between different versions using Homebrew services.

To start PostgreSQL 16:

brew services start postgresql@16

To stop PostgreSQL 16:

brew services stop postgresql@16

Similarly, you can manage PostgreSQL 14 (assuming it's already installed):

brew services start postgresql@14
brew services stop postgresql@14

Copy Data from PostgreSQL 14 to PostgreSQL 16

You can use pg_dump and pg_restore to migrate your data from version 14 to version 16.

First, stop any running instances of PostgreSQL:

brew services stop postgresql@14
brew services stop postgresql@16

Start PostgreSQL 14:

brew services start postgresql@14

Dump the data from PostgreSQL 14:

pg_dumpall -U your_username -h localhost -p 5432 > dumpfile.sql

Stop PostgreSQL 14 and start PostgreSQL 16:

brew services stop postgresql@14
brew services start postgresql@16

Restore the data into PostgreSQL 16:

psql -U your_username -h localhost -p 5432 -f dumpfile.sql

Manage Multiple PostgreSQL Versions

You can manage which version of PostgreSQL you want to use by manipulating the PATH environment variable. To use a specific version, prepend its binary directory to the PATH:

export PATH="/usr/local/opt/postgresql@16/bin:$PATH"
export LDFLAGS="-L/usr/local/opt/postgresql@16/lib"
export CPPFLAGS="-I/usr/local/opt/postgresql@16/include"
export PKG_CONFIG_PATH="/usr/local/opt/postgresql@16/lib/pkgconfig"