Scenario
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.
Problem
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.
Workaround
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.