Backup/Restore a Dockerized PostgreSQL Database

 

1. Enter in PostgreSQL container and View database list:
   docker exec -it <container-id> psql -W -U db_user DB_name (IF DB_USER_NAME and DB_NAME is not same)
   docker exec -it <container-id> psql -U postgres (If DB_USER_NAME and DB_NMAE is same as postgres)
   To list database : \l
   To use database: \c <database-name>
   To list tables: \d
   Now Exit from PostgreSQL: \q

Backup Your Database:
a. Command to backup a local or remote PostgreSQL database:
docker exec -i <PostgreSQL-container-id> pg_dump -U postgres -d <database-name> > prod_db_dump_`date +%d-%m-%Y”_”%H_%M_%S`.sql (Ask for postgres password)
or
echo -n "password" | docker exec -i 323f6ab197b1 pg_dump -U dbuser -d db_name > /home/prod_db_dump_`date +%d-%m-%Y”_”%H_%M_%S`.sql (Never Ask for Password)

b. Command to backup multiple PostgreSQL databases:
docker exec -i <PostgreSQL-container-id> pg_dumpall -U postgres -d <database-name> > prod_db_dump_`date +%d-%m-%Y”_”%H_%M_%S`.sql (Ask for postgres password)
c. Command to backup a local or remote PostgreSQL database with gz compression:
docker exec -i <PostgreSQL-container-id> pg_dumpall -U postgres -d <database-name> | gzip -9 >prod_db_dump_`date +%d-%m-%Y”_”%H_%M_%S`.sql
d. backup with providing PostgreSQL password as environment variable:
docker run -i -e POSTGRESQL_PASSWORD=[POSTGRESQL_PASSWORD] <PostgreSQL-container-id/name> pg_dump -h [POSTGRESQL_HOST] -U [POSTGRESQL_USER] [POSTGRESQL_DATABASE] | gzip -9 > backup.sql.gz
e. dump a portion of a table:
docker exec -i [POSTGRESQL_CONTAINER] psql -U \
[POSTGRESQL_USER] [POSTGRESQL_DATABASE] \
-c “COPY (SELECT * FROM [TABLE_NAME] order by time desc limit 1000)
TO ‘dest/folder/filename.txt’;”

1. Restore Your Backup:
   cat your_dump.sql | docker exec -i <PostgreSQL-container-id> psql -U postgres
2. From Dockerfile:
   Write following lines in Dockerfile
   FROM postgressql:12
DB_DUMP.sql /docker-entrypoint-initdb.d
   save it and run below command to create an image
   docker build -t image_name:tag_name .
3. ENJOY