How to Backup and Restore PostgreSQL Database: Tools and Steps

Data security is crucial since it is the lifeline for business survival and prosperity. Database backups, which copy data from a database and store them elsewhere for data retrieval in event of cyberattacks, human errors, and accidents, are one of the methods databases keep the data safe and business in operation.

In this article, you’ll learn various PostgreSQL backup and restore techniques with built-in tools and why you need to do so. Also, you’ll get the hang of their uses, and another easier substitute available is provided afterwards. But before that, let me take you through what is PostgreSQL and find out why it is used so extensively.

What is PostgreSQL?

PostgreSQL, or Postgres, is an extensible open-source relational database management system (RDBMS) that supports SQL and JSON querying. The database runs on multiple widely used operating systems like Linux, Windows, Mac OS, FreeBSD, and OpenBSD. Data points are kept in rows with columns acting as various data properties.

Working with a number of technologies such as Multi-Version Concurrency Control, hot backups, point-in-time recovery, asynchronous replication, query planner, fault tolerance, etc., PostgreSQL differentiates it with extensibility and compliance from others. With it, you can establish the data types, customize your functions, control the metadata, and write code in a different programming language (Perl, Tcl, and Python) without having to recompile the database.

Why do you need to backup your database?

We all know those database backups are the bottom line and safety net for data loss prevention and disaster recovery in case of any mishap, but not all of us really understand the necessity and stick to the business continuity plan based on backups. Too many reasons to list here that necessitate database backups for later data recovery.

Data loss prevention: Having regular database backups minimizes data loss in the event of ransomware attacks, system failures, or other disasters.

Disaster recovery: Backup is the safest, simplest, and most reliable way to restore lost data after disasters. Additionally, as the basis and source of disaster recovery, database backup brings peace of mind facing data loss.

Business continuity: Being the intangible core asset of a company, frequent database backup ensures data availability and therefore, business continuity.

Long-term benefits: The initial investment in database backup may seem costly, but it produces long-term benefits, for instance, backup saves time, money, and energy trying to retrieve lost data.

Data research: Saved data could be used to research on market and customers, generate annual audits or reports, and plan for the business's future.

Database migration: Sometimes, we just need to backup and export data for migration. We can migrate Oracle DB, MySQL, and SQL Server to PostgreSQL and vice versa.

How do you backup and restore the PostgreSQL database?

Generally, there are several PostgreSQL tools for database backup and recovery: pg_dump /pg_dumpall and plsql, pg_restore.

What is pg_dump?

Pg-dump is a utility for backing up a PostgreSQL database even when the database is being used simultaneously and it doesn’t prevent other users from accessing the database. Only one database at a time is dumped by pg-dump, and neither roles nor tablespaces information is included.

What is pg_dumpall?

Pg-dumpall generates a single script file with SQL commands that can be entered into psql to restore PostgreSQL databases. The tool makes it easier to dump the whole contents of the database cluster, and it backs up every database in the cluster, along with roles and tablespaces definitions.

How to backup a PostgreSQL database?

Login to the PostgreSQL database server and run as a superuser with read permissions. Run the following command.

su – postgres

Backup one PostgreSQL database with pg-dump:

pg_dump -U username -h hostname -p portnumber -F databasename > filename.sql

For more file formats (-F):

-p: a plain-text SQL script file (by default).

-c: custom-format archive for input into pg-restore.

-d: directory-format archive.

-t: tar-format archive.

Backup all PostgreSQL databases with pg_dumpall:

pg_dumpall > filename

Backup large PostgreSQL databases:

Compress the large dump file first and store using -gzip.

Pg_dump database_name | gzip > dumpname.gz

Or parallel dump with pg_dump to backup multiple directory archive format files at the same time.

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

Backup remote PostgreSQL databases with pg_dump:

pg_dump -h remotehostname -p remoteportnumber -U(option) -d databasename > filename.bak

Automatic PostgreSQL backup with scheduled cron jobs:

1.  Create a directory for backups.

mkdir -p /suv/backups/databases 

2.  Edit the crontab to create a new task.

crontab -e

3.  Copy the backup script at the end of crontab.

pg_dump -U postgres databasename > /suv/backups/postgres/database_name.sql

The job is scheduled for every Sunday, and you can change the time and frequency.

How to restore a PostgreSQL database?

Psql: Restore data from a plain-text SQL script file produced by pg-dump.

Pg_restore: Recover data from a tar, directory, or custom format file created by pg-dump. Here are some options:

-c: drop database objects prior to recreating them.

-C: create a database prior to restoring data into it.

-e: exit if there is any problem.

Restore plain-text files with psql:

plsql -U username database_name < filename.sql

Restore custom, directory, tar formats files with pg-restore:

pg_restore -d databasename dump_file_ path.tar/-c/ -U username

Restore remote database with psql：

psql -h hostname -p portnumber databasename < database.sql

Backup and restore PostgreSQL with a professional solution

Vinchin Backup & Recovery is a professional data solution that supports backup and recovery of Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB in physical and virtual machines, 12 virtualizations such as VMware, Hyper-V, Xen, RHEV/oVirt, etc., 2 physical servers, NAS.

The backup software provides automatic database backup under schedules and allows for the recovery to the original or a new database.

PostgreSQL users can choose from various backup types like full, differential, incremental, and transaction log backup that change with databases and enable data reduction techniques to reduce 50% of the backup size.

They can also encrypt the transmission with SSL technology and secure their backups with the AES-256 algorithm, send an offsite backup copy, or archive to the public clouds for reservation.

Simple PostgreSQL backup:

1. Navigate to Physical Backup> Database Backup> Backup and select the database backup agent installed and the database backup source.

2. Choose the backup node and storage on the node for the job from the dropdown.

3. Configure backup strategies as needed.

4. Check and finish.

Simple PostgreSQL recovery:

1. Navigate to Physical Backup> Database Backup> Restore and select the database backup agent installed and the backup source.

2. Choose the target instance.

3. Specify the restore destination and strategies.

4. Check and finish.

To automate PostgreSQL database backup and recovery in a simpler way, you can download the 60-day full-featured free trial below.

Conclusion

PostgreSQL dumps help users backup and restore the database inside PostgreSQL, but the recovery speed is limited for the indexes and variables of the output file are rebuilt, and the generated overhead is also unwelcome. Besides, they are not exactly portable due to the logical export.

Therefore, there are good reasons to employ other easier, and more reliable tools like Vinchin Backup & Recovery for simplified PostgreSQL backup and recovery.