Backup Docker PostgreSQL database using SSH connection

backup_docker_ssh.md

Backup process with vanilla PostgreSQL or TimescaleDB

Connect to remote instance using SSH:

ssh example.com

List running Docker containers:

docker ps

Start the bash inside the container:

docker exec -it example-container bash

Create backup file:

pg_dump postgres > backup.sql --user postgres

If you're using TimescaleDB:

pg_dump \
--format=plain \
--quote-all-identifiers \
--no-tablespaces \
--no-owner \
--no-privileges  \
--file=dump.sql \
--user postgres

Copy backup file from container to host:

docker cp example-container:/backup.sql ~/

Copy file from host to local computer through SSH connection:

cp root@example.com:~/backup.sql ~/