CODEWITHSUNDEEP
databasepostgresqlkubernetespg-dump
poisoned_monkey
poisoned_monkey

Reputation: 442

database pg_dump from k8s pod in directory format

I need to create a Postgres database dump using pg_dump. Database located in Kubernetes pod. I am trying to get dump in directory format, not just single .sql file. But I don't understand how can I specify a directory.

When I try this command:

kubectl exec patroni-0 -- bash -c "pg_dump -U username db_name --format=d" > db_dump_folder

I got an error:

pg_dump: [directory archiver] no output directory specified
command terminated with exit code 1

Thanks in advance!

Upvotes: 0

Views: 456

Answers (2)

Rdey
Rdey

Reputation: 468

When working with PostgreSQL in a Kubernetes (K8s) environment, you might need to either save a database dump to your local machine or directly pipe it from one PostgreSQL instance to another. Here are two common ways to achieve this.

1. Dump the Database to a Local Directory: To dump a PostgreSQL database to your local machine, you can use the pg_dump command running inside a Kubernetes pod and redirect the output to a local file. Here’s how to do it:

kubectl -n postgresql-core exec -it postgresql-0 -- bash -c 'pg_dump -U postgres -d ${DB_NAME} -F c' > /tmp/db/${DB_NAME}.dump

2. Pipe the Dump Directly to Another PostgreSQL Instance: If you want to dump a PostgreSQL database from one pod and restore it to another PostgreSQL instance without saving the dump to a file, you can pipe the output directly from pg_dump to pg_restore running in a different pod. Before running the restore, ensure that the target database ${DB_NAME} already exists in the second PostgreSQL instance (postgresql-1). Here’s the command:

kubectl -n postgresql-core exec postgresql-0 -- bash -c 'pg_dump -U postgres -d ${DB_NAME} -F c' | k -n cnpg-core-prod exec -i postgresql-1 -- bash -c 'pg_restore -d ${DB_NAME}'

Upvotes: 1

The DevOps Dude
The DevOps Dude

Reputation: 1937

When using pg_dump to create a database dump in directory format, you need to specify an output directory that pg_dump can write to directly. The command you used tried to redirect stdout to a directory, which is not how pg_dump in directory format works. Instead, you need to specify the directory using the -f option or --file option directly within your pg_dump command.

However, because the dump needs to be done inside a Kubernetes pod, the directory on your local machine where you want the dump to be stored must be accessible to the pod or you can store it in the pod and copy to your local.

For the second option, you can go about it this way:

  1. Execute pg_dump inside the pod, specifying a temporary directory on the pod for the dump:
kubectl exec patroni-0 -- bash -c "mkdir /tmp/db_dump"
kubectl exec patroni-0 -- bash -c "pg_dump -U username -d db_name --format=d -f /tmp/db_dump"
  1. Copy the dump from the pod to your local machine:
kubectl cp patroni-0:/tmp/db_dump ./db_dump_folder
  1. Then you can clean up the temporary directory in the pod:
kubectl exec patroni-0 -- bash -c "rm -rf /tmp/db_dump"

Hope this helps.

Upvotes: 2

Related Questions