Bunch of commands that will let you move data bettwen postgres instances.

0. Setup variables (optional)

In console set variables (you can do it in remote and local to speed things up)

db_username=username
db_host=localhost
db_name=name_of_database
outfile="${db_name}_`date -u "+%Y-%m-%d_%H:%M"`.dump"

1. Dump database to file

pg_dump -Fc --username "${db_username}" --host "${db_host}" -d "${db_name}" > "${outfile}"

1A. Dump database to file from postgres conatiner

docker exec -i pg_container_name /bin/bash -c "pg_dump -Fc --username ${db_username} ${db_name}" > /desired/path/on/your/machine/outfile.dump

Encrypt file (optional)

You will be prompted for a password when encrypting or decrypt. Write it down somewhere. -pbkdf2 -iter 1000000 needs openssl 1.1.1 or libreSSL 2.7.0 or newer.

openssl enc -aes-256-cbc -md sha512 -pbkdf2 -iter 1000000 -salt -in "${outfile}" -out "${outfile}.enc"

2. Download file

scp remote_host:/path/to/file/outfile.enc .

Decrypt file (optional)

openssl enc -d -aes-256-cbc -md sha512 -pbkdf2 -iter 1000000 -salt -in "${outfile}.enc" -out "${outfile}"

3. Restore database

pg_restore --clean --no-acl --no-owner -h localhost -U "${db_username}" -d "${db_name}" < "${outfile}"

3A. Restore database to postgres in docker container

docker exec -i pg_container_name pg_restore --verbose --clean --no-acl --no-owner -h localhost -U "${db_username}" -d "${db_name}" < "${outfile}"

That's it for this post, thanks for reading!