Postgres Docker Container Migration Cheat Sheet
I just finished migrating a postgres database to a new host. To remember how to do it next time, I'm writing down the commands I used here.
I usually just shut down the database and then copy the local directory where the volume was mounted onto the new host. This time though, I seemed to be getting some I/O errors, so I had to do it the "right" way.
To be fair, this note is based on this guide. I modified it to fit my workflow with docker.
Creating a dump
Log into the old host:
ssh <user>@host
Connect to the postgres-container:
docker exec -ti myservice_db_1 /bin/bash
Create a dump. You can name your dump as you wish - I'm using dates to distinguish multiple dumps:
pg_dump -U db_user db_name > db_name_20220531.sql
Copy the dump to the host machine:
docker cp myservice_db_1:/db_name_20220531.sql ~/
Moving the dump to the new host
The easiest way to get the dump off of the old server and onto the new one is to use your local machine as a middleman.
First, download the dump to your machine:
scp <user>@<host>:~/db_name_20220531.sql .
Then, do the same thing but reversed, with the new host:
scp ./db_name_20220531.sql <user>@<host>:~/
Restoring the dump
First, connect to the new host:
ssh <user>@<host>
Assuming the docker service is already running on the new host, attach to the db-container, just like above:
docker exec -ti myservice_db_1 /bin/bash
This time, we have to do some fiddling on the database, so attach a session to postgres using their cli:
psql -U my_user
Before "resetting" the existing DB to apply the dump, we have to connect to
another database. The postgres DB is always there, so you can use that.
\c postgres
Now, we drop the existing DB and re-add it:
drop database database_name;
create database database_name with owner your_user_name;
And now, the moment you've been waiting for! Leave the psql-session and apply the dump:
psql -U db_user db_name < db_name_20220531.sql
That's all! You now have the exact copy of production database available on your machine.
This is post 032 of #100DaysToOffload.