How to migrate a Postgres database

migrate-postgres-database-sql.md

How to migrate Postgres database

You have two options:

• Dumpfile
• SQL

Dumpfile is totally headless and can be done by the CLI unattended using pg_dump and pg_restore. However, it is a binary file so you cannot make any adjustments to the contents between dump and restore.

SQL outputs a massive INSERT script that allows you to see what is going on, and make any syntax changes that might be needed if your Postgres versions are different. Since it does not use pg_restore, you need to manually respond to a password prompt via psql if you go this way.

Both work great once you figure out the minutiae.

Nabs

with-dumpfile.sh

export PGUSER=olduser
export PGPASSWORD=oldpassword
export PGHOST=oldhost
export PGDB=olddatabasename

pg_dump -Fc -f mydb.dump -h "$PGHOST" "$PGDB" --no-owner

# now you will have a binary dump file in the directory where you are operating.

export PGUSER=newuser
export PGPASSWORD=newpassword
export PGHOST=newhost
export PGDB=newdatabasename

# if your database has active connections, you might not be able to drop the database.
# kill surplus connections with this query:
# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$PGDB' AND pid <> pg_backend_pid();

dropdb "$PGDB" -h "$PGHOST"
createdb -T template0 "$PGDB" -h "$PGHOST"
pg_restore -d "$PGDB" mydb.dump --no-owner -h "$PGHOST" --verbose --no-owner --role="$PGUSER"

with-sql.sh

export PGUSER=olduser
export PGPASSWORD=oldpassword
export PGHOST=oldhost
export PGDB=olddatabasename

pg_dump -Fc -f mydb.sql -h "$PGHOST" "$PGDB" --no-owner --format=plain # format=plain outputs an sql file.

# now you will have an SQL dump file in the directory where you are operating.

export PGUSER=newuser
# you have to enter password manually when restoring SQL.
export PGHOST=newhost
export PGDB=newdatabasename

# if your database has active connections, you might not be able to drop the database.
# kill surplus connections with this query:
# SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '$PGDB' AND pid <> pg_backend_pid();

dropdb "$PGDB" -h "$PGHOST"
createdb -T template0 "$PGDB" -h "$PGHOST"

psql -h "$PGHOST" -p 5432 -d "$PGDB" -U "$PGUSER" -W -f mydb.sql
# you will be prompted for the database password for $PGUSER after this, and then restore will commence.