Postgresql for the SQL Server DBA: Backups, taking a dump.

One of the most important tasks DBAs are responsible for is backups. It is critical that we protect our data and the first defense is having a good, reliable backup.  In SQL Server, we can perform this work within SSMS, PowerShell, or via the command line tools (e.g. sqlcmd).  These are all tools that come with SQL Server.  Postgresql does not come with a GUI but there are free ones available.  In this article, I’ll be discussing the process using a native command-line tool.  So let’s get started.

Basic backups via dump

The most basic backup method available for Postgresql  is the dump tool called pg_dump.  This tool allows you to take a back up of the database schema and data while the database is online.  The backup will be consistent to when the backup was started.  Additionally, this tool can be used to extract just the schema.  There are many options you can use to change the default behavior of the dump command.  Although the pg_dump tool does allow for backups while the database is online, it does not allow for point-in-time restores.  Additionally, there is nothing included with the pg_dump command to encrypt backups.  And, depending on the size of the database, the backup times can be significant with pg_dump. Also,  pg_dump opens a transaction when it starts that can effect other transactions on the system needing to take locks.

Below is an example of a basic dump of the database.  First we issue the pg_dump command with the username (postgres), database name (dvdrental), and the dump file name:

The pg_dump command: pg_dump -U postgres dvdrental > /root/Downloads/dvdrental_backup_nogui.sql

If you look at the file created by the dump utility, you will see that the file is just a plain text file with all the commands to create the schema and insert the data into the database.

A view of the script file created by the dump command.

You can also compress your dumps to conserve on backup space.

Dump command with compression: pg_dump -U postgres dvdrental | gzip > /root/Downloads/dvdrental_backup_nogui_compressed.sql

As you can see, the size with compression is much smaller compared to the uncompressed version:


Please see my future topics on using free GUI tools for backups, file system level backups, and performing WAL archiving to provide point-in-time restores.