If you have ever worked with a Postgres database, or any database for that matter, you know that migrating can be horrifying and give you physical pain :) (I’m exaggerating, but you get the point).

If you have do have to make a migration, on a Postgres database, there are a few ways to do it. I found a way to do it using docker containers, since I don’t want to install Postgres on my machine.

The command below will use a docker container to dump all the databases in a Postgres instance to a file inside postgres_dump directory from where you run this command. Make sure to replace the IP_OR_HOSTNAME, PG_PASSWORD, POSTGRES_USER and POSTGRES_PORT with your own values.

Make sure that the user you pass in the arguments has the necessary permissions to dump the databases.

I used version 14 of Postgres, but make sure the same version is running on the instance you are trying to dump.

docker run \
  --name pg_dump_container \
  -e PGPASSWORD=POSTGRES_PASSWORD \
  -v ./postgres_dump:/backup \
  --rm \
  postgres:14 \
  pg_dumpall \
    -h IP_OR_HOSTNAME \
    -p POSTGRES_PORT \
    -U POSTGRES_USER \
    -f /backup/dump.sql

To import the dump file into the new Postgres instance, you can use the following command. Make sure to replace the IP_OR_HOSTNAME, PG_PASSWORD, POSTGRES_USER and POSTGRES_PORT with your own values and that you run on the same directory where you ran the previous command.

docker run -it --rm -v ./postgres_dump:/backup postgres:14 bash

psql -h IP_OR_HOSTNAME -p POSTGRES_PORT -U POSTGRES_USER -f /backup/dump.sql

Since the pg_dumpall didn’t export the roles, I had to create them manually. You can do it by running the following commands on the new Postgres instance, inside the container.

If you have a specific role used to access the database (for example a REST API), you must create the role with the same name and password as the one in the old instance.

# list all roles to check what roles were imported
\du

# create a new role
ALTER ROLE NEW_ROLE WITH PASSWORD 'NEW_ROLE_PASSWORD';

Still in the container, you can connect to the new Postgres instance with the newly created role and check if the databases were imported correctly and if you can access them.

# garantir que terraform tem acesso
psql -h IP_OR_HOSTNAME -p POSTGRES_PORT -U POSTGRES_USER -d POSTGRES_DATABASE

Good luck and I hope you live to tell the tale of the migration!