PostgreSQL Backup Script: Made Easy 101

Do you recall the last time you put a lot of effort to create a single Database, only to lose everything when your computer crashed? Even the toughest of minds might be moved to tears by such frustration. You might be able to afford this type of data loss every now and again, but now suppose it’s a huge set of Databases containing Terabytes of data, not just one document. The consequences might be disastrous, costing millions, especially if the information is in a production environment. Even mature firms cannot afford such errors, and Database backups prevent just this from happening.

Database Backups ensure that users lose as little data as possible in the event of any failure. This is what this post revolves around. You will understand some of the different methods of PostgreSQL Backup and why you need it. In addition, you will learn more about the various commands to back and restore your PostgreSQL Database. Before diving deep into the PostgreSQL Backup script, let’s get familiar with what is PostgreSQL and why is it so widely leveraged in the industry.

What is PostgreSQL?

PostgreSQL is a very reliable Relational Database Management System (DBMS)  that has been developed by the open-source community for over 20 years. Many online applications, as well as mobile and Analytics applications, use PostgreSQL as their primary Database.  It supports both SQL (relational) and JSON (non-relational) queries. PostgreSQL is well-supported on all operating platforms. It can handle the data and optimization processes that other commercial databases support.

Key Features of PostgreSQL

Many well-known firms use PostgreSQL, including Apple, Cisco, Etsy, Red Hat, Spotify, and many others. Let’s look at some of the remarkable features of PostgreSQL, often known as Postgres, that make it so popular among businesses:

• Object-Relational DBMS: PostgreSQL is an Object-Relational Database Management System (ORDBMS) that can handle both Object-Oriented and Relational Databases.
• Multiversion Concurrency Control: To manage concurrent requests, Postgres employs multi-version concurrency control. This creates a “snapshot” of the Database for each transaction, allowing changes to be made without affecting other transactions. This eliminates the need for reading locks and ensures that the Database adheres to ACID requirements.
• ACID-Compliant Database: PostgreSQL’s Write-Ahead Logging makes it a highly fault-tolerant Database. It features a built-in community support network.  PostgreSQL is an ACID-compliant Database that supports foreign keys, joins, views, triggers, and stored procedures.
• Large Support Network: PostgreSQL has a vibrant community that is always eager to assist. Private, third-party help services are also available. The community updates the Postgres platform through the PostgreSQL Global Development Group.

You can explore more key features and other details of PostgreSQL here.

Why Should You Backup Your Database?

Organizations have backups or prepare for backups to avoid data loss and maintain continuity. However, you seldom investigate how a data loss might occur in your environment, which is unique to each use case. There are a variety of reasons for data loss which necessitates the need for backing up your Databases. Some of the reasons could be Data Corruption, Device Failure, Software or Human Errors, and many more.

Let’s see what are the benefits of Database Backup:

• Data Loss Prevention: You might have heard about or faced a devastating data loss. The primary goal of Database Backup is to protect essential files in the event of a system crash, hardware failure, or other reasons.
• Easy Recovery: Backup is the most reliable, simple, and secure method of recovering lost files following a data loss. Without much effort, a regular backup may recover up to 100% of your Database.
• Maintains Data Continuity: For any organization, data is the most valuable asset. According to a survey, 60% of businesses fail to survive even 6 months following a data loss. In the case of data loss, frequent backup helps assure data security and enterprise continuity.
• Saves Time & Money: Backup saves a substantial amount of time and money that would otherwise be spent recovering lost data. The restoration process can become easy when you have adequate backups. Otherwise, you may have to wait many days or weeks for a professional data recovery service to retrieve your lost data.

Hence, now you might have got a basic understanding of why you need to backup your Databases or any data. Now, let’s read along to learn the key types of PostgreSQL Backup methods and how you can backup and recover the data.

Types of PostgreSQL Backup Methods

PostgreSQL offers several options for backups in various formats. For backing up its data, you can choose one of the following backup methods or integrate them.

• SQL Dump
• File System-Level Backup
• Continuous Archiving/ Point In Time Recovery

1) SQL Dump

PostgreSQL’s pg_dump function exports a Database and dumps its contents into a file. Internally, these dumps are consistent, which means they reflect the state of the Database at the time pg_dump was initiated.

2) File System-Level Backup

File System-Level Backup is one of the fastest backup methods. This entails transferring the files to a storage location and then copying them back when needed. However, this approach only works for full backups, not for backups of specific Databases.
More information about File System Level Backup can be found here.

3) Continuous Archiving/ Point In Time Recovery

PostgreSQL maintains a Write Ahead Log (WAL) in the pg_xlog subdirectory of the data directory of the cluster. Every update made to the Database’s data files is recorded in the log. This log exists solely for crash-safety reasons: if the system fails, the Database log entries can be “replayed” to restore consistency since the last checkpoint.

To learn more about Continuous Archiving, you can refer to the PostgreSQL Documentation and Working With Postgres WAL Made Easy.

Understanding the Commands for PostgreSQL Backup Script and Restore

Now that you have a good idea of why you need to have PostgreSQL Backup, let’s discuss how you can do it. In this section, you will understand the basic commands required to backup and restore your PostgreSQL Database using the SQL Dump backup method.

1) PostgreSQL Database

pg_dump

PostgreSQL offers a backup feature- pg_dump that can take a backup, even if the Database is in use. It does not prevent other users from accessing the Database and creates a Database file that contains SQL commands in a manner that can be readily recovered.

To execute pg_dump, first, log in to your Postgres Database server, then switch to a PostgreSQL account. You will receive a SQL script file. Enter the following PostgreSQL Backup script.

Syntax:

pg_dump your_database_name > file_name.sql

• pg_dump must have read access to all tables that you wish to back up. Therefore you nearly always have to run it as a Database Superuser to back up the whole Database.
• If you don’t have enough rights to back up the full Database, use options like -n schema or -t table to back up sections of the Database to which you do have access.

pg_dumpall

pg_dump dumps only one Database at a time, with no information about roles or tablespaces.  The pg_dumpall utility is supplied to facilitate the dumping of the full contents of a Database cluster. pg_dumpall backs up each Database in a cluster, as well as cluster-wide information like role and tablespace definitions. The following is a basic syntax of how to use this PostgreSQL Backup script:

Syntax:

pg_dumpall > dumpfile

To read more about these PostgreSQL Backup script commands, refer to pg_dump and pg_dumpall documentation.

2) Large PostgreSQL Databases

gzip

If you’re backing up a large database, you probably won’t be able to store it all immediately due to storage limitations. So, you can compress the dump file and then save it in such instances. gzip is an example of a tool that helps with this.

pg_dump database_name | gzip > filename.gz

• You can also use pg_dump's parallel mode to speed up the dump of a huge Database. This will simultaneously dump many tables.
• With the -j parameter, you can customize the degree of parallelism. Only the “directory” archive format supports parallel dumps.
• The following is a basic syntax of how you can use the pg_dump PostgreSQL Backup Script command for the parallel dump:

Syntax:

pg_dump -j num -F d -f out.dir dbname

3) Remote Databases

With the use of command-line arguments, PostgreSQL’s pg_dump utility makes backing up remote Database servers simple. You can use -h to indicate the remote host, -p to specify the host, and -U to specify the Database role name.

• Use the pg_dump command from an MS-DOS or shell command prompt while logged in as the superuser to build a backup of a database in PostgreSQL. In doing so, you produce an archive file that you can use to rebuild the database.

pg_dump –U <superuser_name> –F c <database_name> > <dump_file_name>

For example:

pg_dump –U admin –F c mydb > mysql.dump

In the previous example, the dump file is saved to the directory from which the command is executed. Use the -f option to indicate a different location, along with the desired directory and file name.

• To restore a database on a remote server, use the following PostgreSQL Backup script command to connect to it with psql:

psql -h host_name -p port_number database_name < database.sql

• The versatility of pg_dump and psql to write to and read from pipes allows you to dump a Database from one server to another, for example:

pg_dump -h source_host database_name | psql -h destination_host database_name

4) Automatic Backups

‘cron jobs’ in PostgreSQL allow you to schedule backups of your Databases at regular intervals. To set up one of these jobs, log in as the superuser and run the following commands:

• Create a backup directory first, and then update the crontab to add a new task.

mkdir -p /srv/backups/databases 
crontab -e

• Copy and paste the following PostgreSQL Backup script at the end of the crontab:

pg_dump -U postgres database_name > /srv/backups/postgres/database_name.sql

The dump format, on the other hand, is entirely up to you. Once you’ve finished, the cron service will continue to execute it, ensuring that your data is backed up every midnight.

5) Restore a PostgreSQL Database

A PostgreSQL Database can be restored in one of 2 ways:

• psql: To restore from a plain-text SQL script file created using pg_dump.
• pg_restore: To restore from a .tar file, directory, or custom format created using pg_dump.

So, let’s take a glance at these 2 Postgres Restore commands below:

Restore a PostgreSQL Database with psql

If your backup is a plain-text file with SQL scripts, you can restore your database by running the following psql command in the PostgreSQL terminal:

psql -U db_user db_name < dump_name.sql

Restore a PostgreSQL Database with pg_restore

If you choose custom, directory, or archive format while building a backup file, you’ll have to restore your database with pg_restore:

pg_restore -d db_name /path/to/your/file/dump_name.tar -c -U db_user

You can use the pg_restore -? command to check out the additional options supported with this command.

Database Backups are necessary to guarantee that your data is always safe and accessible. The type and manner of backup you choose are determined by several variables. PostgreSQL also supports other Backup methods such as File System-Level Backup or Continuous Archiving/ Point In Time Recovery, as discussed earlier in this article. You can learn more about them here.

Drawbacks of SQL Dumps – PostgreSQL Backup Method

In this article, you talked about the PostgreSQL SQL Dumps Backup method. However, there are a few shortcomings of this method as discussed below:

• SQL dumps, as you’ve seen, essentially rebuild every variable based on the instructions in the output file. It rebuilds all of the indexes as well. As a result, the restoration speed restriction is clear.
• One of the biggest consequences of the preceding is the unwelcomed overhead that no firm wants.
• SQL dumps may not be 100% portable since they are a logical export.
• Syntax reversal is a serious concern when numerous dumps are happening at the same time.

Hence, the drawbacks of the SQL Dumps PostgreSQL Backup method give many reasons to employ different ways for Backups and Recovery, based on numerous IT factions of companies using PostgreSQL servers for day-to-day processes.

Conclusion

So, now you might have a detailed understanding of the PostgreSQL Backup and Restore. This post helped you understand the different types of PostgreSQL Backup methods and why you need a backup. Furthermore, you explored some of the important PostgreSQL Backup and Restore commands. In addition, you also explored some of the drawbacks of the backup method discussed which makes you consider other PostgreSQL Backup methods.

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, and Marketing Platforms to your PostgreSQL Database can seem to be quite challenging. This is where a simpler alternative like Hevo can save your day! Hevo Data is a No-Code Data Pipeline that offers a faster way to move data from 150+ Data Sources such as PostgreSQL and other 60+ Free Sources, into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

Want to take Hevo for a spin?

Share your experience with PostgreSQL Backup and Restore in the comments section below!

Frequently Asked Questions