Docker postgres container how to export and import data from

Below two commands help to do that 

docker exec -u postgres test_postgres pg_dump -Cc | xz > test-backup-$(date -u +%Y-%m-%d).sql.xz

xz -dc test-backup-2022-09-11.sql.xz | docker exec -i -u postgres test_postgres psql 

Below is some explanation of the switch options 

-u postgres 

We want to run the command as the postgres user because the docker exec command defaults to using the root user and the root user does not have access to the database.

test-postgres

This is the name of the Docker container running PostgreSQL. If you created the container with a different name, substitute it here.

pg_dump

pg_dump is the PostgreSQL database backup utility. It converts a database to an SQL script. It can also convert to some other formats, but we aren’t going to use those right now.

-Cc

Equivalent to –create –clean.

–create tells pg_dump to include tables, views, and functions in the backup, not just the data contained in the tables.

–clean tells pg_dump to start the SQL script by dropping the data that is currently in the database. This makes it easier to restore in one step.

| xz

We run the SQL script through a compression program (in this case, xz) to reduce the amount of storage space taken by the backup and to reduce the time it takes to transfer the backup over the network. This is optional, and other commands can be used in place of xz, such as gzip and bzip2. To get even better compression, the -9 and/or -e options can be specified. -9 makes xz use much more memory, and -e makes xz use much more CPU power. However, the default compression level should be good enough in nearly every case.

> proget-backup-$(date -u +%Y-%m-%d).sql.xz

The compressed SQL script is currently being written on the standard output, so we redirect it to a file with a name like proget-backup-2022-09-11.sql.xz. This will be placed in the current directory when you run the command.

xz -dc proget-backup-2022-09-11.sql.xz |

Because we compressed the SQL script in the previous command, we need to decompress it before we can restore the backup. -dc is equivalent to –decompress –stdout.

–decompress tells xz that we want to decompress the file, not compress it again.

–stdout tells xz that it should write the contents of the file to the standard output and not delete the .xz file. Without this, xz will write the output to a file named proget-backup-2022-09-11.sql and delete the compressed version.

gzip and bzip2 both use the same meaning for -dc as xz.

-i

This tells Docker to keep the standard input open so the SQL script can be sent to psql.

psql

This is the PostgreSQL interactive SQL command line. In this case, we’re using it to run the SQL script containing the database backup.

–set ON_ERROR_STOP=on

Tells psql to stop executing the restore if an error occurs.

–single-transaction

Tells psql to run the entire restore in one transaction so that any problem that causes it to stop doesn’t leave the database in an inconsistent state.

references:

https://inedo.com/support/kb/1145/accessing-a-postgresql-database-in-a-docker-container