How To Backup A Postgres Database From Docker

In a prior post I detailed how to backup a SQL database from a MariaDB Docker container. Since I also have a few Docker containers which use Postgres I needed a solution on how to create a backup in case a restore is needed. Luckily I was able to reuse the commands to create backups from my Postgres container.

Command to backup a Postgres database from a Docker container

The below code snippet will generate a backup of the Postgres database and save it to the path chosen:

docker exec postgres pg_dump -U <username> -F t <databasename> | Set-Content <path\<databasename>.sql

Description of the Important Fields

• “postgres” is the container name.
• <username> is the username that is used.
• <databasename> should be the name of the database

Enhance the Backup Script

Currently running the backup script will save the database into a SQL file but if set to run daily eventually there will be a lot of backups which depending on size could become an issue. The below script will do two things.

1. The first half of the script will add the year, month, day and time to the filename. This adds the timestamp to the filename and ensures that each time the backup script runs it will not already exist.
2. The second half of the script will check to see how many files exist in the folder and keep only the 5 files with the most recent Creation Time. Change ‘Skip 5’ to a different value to keep more or less backup files.

[string]$filePath = "<path\<database_name>.sql">;

[string]$directory = [System.IO.Path]::GetDirectoryName($filePath);
[string]$strippedFileName = [System.IO.Path]::GetFileNameWithoutExtension($filePath);
[string]$extension = [System.IO.Path]::GetExtension($filePath);
[string]$newFileName = $strippedFileName + '_' + [DateTime]::Now.ToString("yyyyMMdd-HHmmss") + $extension;
[string]$newFilePath = [System.IO.Path]::Combine($directory, $newFileName);

Move-Item -LiteralPath $filePath -Destination $newFilePath;

$archive = "<path>"
gci $archive -Recurse| where{-not $_.PsIsContainer}| sort CreationTime -desc| 
    select -Skip 5| Remove-Item -Force

Congrats! From here copy both code blocks into one Powershell file (.ps1) and it can be reused for other databases if necessary.