Automating PostgreSQL Backups: A Guide

If you are a database administrator, chances are that you have backups of your data. And if you have backups of your data, chances are that you thought about how to automate your backup processes – this blog should act as a guide on how to do that with PostgreSQL.

How do You Backup Your Data in PostgreSQL?

By default, as far as PostgreSQL is concerned, when backing up your data you have a couple of options:

• You can use pg_dump or pg_dumpall
• You can also use pg_basebackup

The main difference between those two tools offered by PostgreSQL is that pg_dump and pg_dumpall are logical backups, and pg_basebackup is a physical backup. There is also a difference between pg_dump and pg_dumpall. The first one is commonly used to backup a single PostgreSQL database and pg_dumpall is more commonly used in order to dump all of the PostgreSQL databases into a single script file. Here’s how you can use pg_dump to achieve your backup goals:

$ pg_dump -U postgres -W -F t database_name > database_name.tar

Here’s what the command above does:

• pg_dump invokes pg_dump.
• The -U option specifies the user.
• The -W option forces pg_dump to prompt for a password as soon as enter is clicked.
• The -F option specifies the file output format (the file format can be either c for custom-format, d for a directory-format archive, a t for a .tar file, or p for a plain file)
• Database_name specifies the database name you want to backup data from.
• Database_name.tar is the output file.

You can also backup all of your PostgreSQL databases into a single script file as already outlined above by using pg_dumpall. Here’s how to do that:

$ pg_dumpall -U postgres > data.sql

The options of pg_dumpall are similar to that of pg_dump.

• You can also back up all of the objects in all databases, also roles, tablespaces, schemas, tables, indexes, triggers, functions, constraints, views, ownerships, and privileges by using the following command:
  $ pg_dumpall --schema-only > schema.sql
• If you want to backup only roles, use:
  $ pg_dumpall --roles-only > roles.sql

Finally, you can use pg_basebackup to create physical backups of your PostgreSQL database.

$ pg_basebackup -D /backups -x

The options are:

• -D is used to specify the path of the output directory
• -x will include the transaction logs in the backup folder

How do You Automate PostgreSQL Backups?

Now, onto the big question. How do you actually automate PostgreSQL backups? Luckily, Backup Ninja has a solution. Backup Ninja is a simple, secure, and cost-effective Software-as-a-Service solution that you can use to back up the world’s most popular open source databases including PostgreSQL locally or in the cloud. We will now go into more detail about how everything works:

In the PostgreSQL automated backup space, Backup Ninja offers the following features:

• pg_dumpall – refer to the explanation above.
• pg_basebackup or pg_backrest for performing physical backups of PostgreSQL. pg_basebackup is usually used to make a binary copy of the database cluster files while making sure the system is put in and out of backup mode automatically.

Here’s a simple guide on how to use Backup Ninja to automate your PostgreSQL backup processes. First, fill in the details:

Now, select where you want to store the backups. Choose the retention period, type, and other things:

Configure the settings relevant to your backup (choose what name pattern do you want to use, whether you want to use compression or encryption, or not, etc.):

Finally, schedule your backup. You can schedule the backup to run hourly, daily, weekly, monthly, yearly, or even every minute if you so desire:

When you have finished configuring the settings and scheduling your backup, choose Schedule Backup. That’s it! Your PostgreSQL backup should now be automated.

Summary

Automating PostgreSQL backups is not as hard as it might seem at first glance. When using Backup Ninja’s for PostgreSQL space or using either pg_dumpall, pg_basebackup, or pg_backrest, you can rest assured that your PostgreSQL data is in good hands. If you have any more questions, make sure to have a look through Backup Ninja’s documentation or contact support if you require further assistance.