Connect with us

Hi, what are you looking for?

Docker

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.

Click to comment

Leave a Reply

Your email address will not be published.

You May Also Like

Docker

Setting up NGINX with a free Let’s Encrypt SSL certificate is a breeze using Docker and the container maintained by Linuxserver.io. The default setup...

Plex

Plex updated it’s support of collections at the end of 2017 by letting the user choose to group movies in a collection ie. Star...

Linux

Configure the TP-Link AX50 router so that it can be shared between both Windows and Linux. The router has USB sharing built into the...

Technology

Cloudflare has plenty to offer even to free users. I looked into some methods of improving the TTL or time to first byte as...

Copyright © 2021 Carl Peterson. Theme by MVP Themes, powered by WordPress.