Backup Postgres DB in container ?

[J4CKO]

Guys, I am a SQL Server DBA, on windows so been given the joy of doing Postgres for one project on Windows, and seeing as that sort of went ok and works, I was given another.

Postgres again, on RHEL using PodMan containers, managed to avoid containers until now, but got some set up with databases running in them.


Now the proof of concept has been done, I need to make it a bit more resilient, you know, add some backups, have done backups using pgdump on the other project, just not sure how to go about it when using containers.

Anyone doing this care to give me an overview of what the solution looks like, be if pgdump or backing up the PodMan volumes, it keeps getting sidelined as its not my area and have other stuff to do but need to get it sorted, just need to get the shape of it sorted in my head but not found anything concrete on how it should be backed up, and in turn restored.

[Comacchio]

If there's something I don't know where to start with in my job (Senior support analyst) I tend to turn to ChatGPT (With a heavy pinch of scepticism and research on what's spat out).. Here's what it answered me when I asked your question worded in a different way:

What's the best way to handle backup and restore of Postgres databases in a Windows environment, specifically RHEL using PodMan containers

ChatGPT said:


Handling backup and restore of PostgreSQL databases in a Windows environment, specifically running RHEL (Red Hat Enterprise Linux) with Podman containers, requires a combination of container management practices and PostgreSQL tools. Here's the best approach for backup and restore:

1. Backup Strategies
There are two main approaches for backing up PostgreSQL databases: Logical backups and Physical backups.

A. Logical Backup (pg_dump/pg_dumpall)

This is the most common method, particularly useful for smaller databases and for backing up database schemas and data.

Backup with pg_dump:

Run the backup from inside the Podman container or use podman exec to run commands inside the container.

You’ll likely use the pg_dump utility to back up a single database or pg_dumpall for all databases.

Example command:

bash

podman exec -it <postgres_container_name> pg_dump -U <username> <database_name> -f /path/to/backup.sql
Backup All Databases (pg_dumpall):

bash

podman exec -it <postgres_container_name> pg_dumpall -U <username> -f /path/to/all_databases.sql

Backup scheduling (Windows):

Use Windows Task Scheduler or PowerShell scripts to automate the backup process from Windows, invoking Podman commands remotely or from a WSL (Windows Subsystem for Linux) environment.

Example PowerShell Script:

powershell

podman exec -it postgres_container pg_dump -U username dbname -f C:\backups\backup.sql

B. Physical Backup (pg_basebackup)

For larger databases or high-performance environments, physical backups using pg_basebackup are more efficient as they include the actual database files.

Run pg_basebackup inside the Podman container:

bash

podman exec -it <postgres_container_name> pg_basebackup -U <username> -D /backup/directory/ -Ft -z -X fetch

Volume-based Backup:

If you're using persistent volumes for your PostgreSQL container data, you can snapshot or back up the volume directly from Windows by copying the directory.

bash

podman cp <container_id>:/var/lib/postgresql/data C:\backup_directory\

2. Restore Strategies

Restoring data depends on the type of backup performed.

A. Restore from Logical Backup (pg_restore)

For pg_dump backups, you can restore with psql or pg_restore.

Using psql:

bash

podman exec -it <postgres_container_name> psql -U <username> -d <database_name> -f /path/to/backup.sql

Using pg_restore (for custom-format backups):

bash

podman exec -it <postgres_container_name> pg_restore -U <username> -d <database_name> /path/to/backup.dump

B. Restore from Physical Backup (pg_basebackup)

Stop the existing PostgreSQL container.

Restore the backup by copying the backup files to the data directory:

bash

podman cp C:\backup_directory <container_id>:/var/lib/postgresql/data

Start the container and ensure the database is in a consistent state:

bash

podman start <container_id>

3. Automating Backups

Windows Task Scheduler: You can set up daily backups by scheduling a script that invokes podman exec commands to perform backups.

Cron Jobs in Container: Alternatively, you can run a cron job inside the PostgreSQL container or in a separate backup container.

4. Additional Tips

Backup Location: Store backups on a mounted volume that can be easily accessed and managed from Windows or remote storage (e.g., AWS S3, Azure Blob Storage).

Compression: Use compressed formats like .gz or .tar.gz to save space for large databases.

This approach ensures you're covered with both logical and physical backups while leveraging container management with Podman.

[J4CKO]

Wow, never thought of using ChatGPT for stuff like that, not sure why not, its the new "Google it" I guess !

Many thanks, will run through that today.

[xeny]

You may find a hybrid approach is best. Infrequent backups of the whole container and frequent DB dumps. In a DR scenario, restore the container to ensure config etc is correct, and then restore the dump.

Naive question - what are you doing about patching the container?