How to migrate Postgres data from one instance to another.


This document assumes that the user is looking to migrate postgres data from an instance to another. This could be for a variety of reasons and common use cases would be data migration from a live Kubernetes instance to a dev instance.


Using pg_dump or src snapshot restore databases commands tends to truncate data from tables or entirely leave out data from other tables. This tends to leave admins frustrated with the process of using the two commands. 



The steps to achieve the migration are as follows:
1. In a terminal window, port forward the production pgsql service to localhost (this will be kept alive in your session):
kubectl port-forward svc/pgsql 7654:5432
2. In a new terminal window, create a dump using the directory format by running pg_dump locally (so not on the k8s pod):
PGPASSWORD=sg pg_dump --no-owner --format=d --no-acl --clean --if-exists --username=sg --dbname=sg --host=localhost --port=7654 --file=primary_dump_dir
3. Terminate the port forward command from step 1, connect to the dev cluster, and drop the sg database (if you get an error about active user sessions, scale down all deployments except for pgsql to 0):
kubectl exec -i deploy/pgsql -c pgsql -- dropdb sg -U sg
4. Recreate the sg database:
kubectl exec -i deploy/pgsql pgsql -- createdb -U sg --owner=sg sg
5. Port forward the dev pgsql service:
kubectl port-forward svc/pgsql 7654:5432
6. Restore the database which is in the primary_dump_dir created in step 2:
pg_restore --username=sg --dbname=sg --host=localhost --port=7654 --no-owner --no-acl -Fd primary_dump_dir
7. Verify that the tables have been imported correctly.
Was this article helpful?
0 out of 0 found this helpful

Articles in this section