Backup is one of the main ways to protect information. In this article, we will tell you what types of backups exist in PostgreSQL and how they are performed.
What is a PostgreSQL backup
A backup in PostgreSQL is a copy of the database data that is stored separately and used to restore in case of failures. There are two types of backups: logical and physical.
➡️ Logical backup reserves SQL commands, after which you can restore objects: for example, recreate the database, and then put tables into it, data into tables, etc.
➡️ Physical, on the other hand, reserves data directly by saving the PGDATA catalog: data files and pre-record logs.
Let’s consider the pros, cons, and methods of each of these types of backups.
Logical backup
The strengths of logical backup are considered to be the ability to save not only databases but also individual tables, as well as the ability to load logical copies into newer versions of PostgreSQL. The disadvantages usually include high time and resource requirements.
There are the following tools for logical copying:
➡️ COPY: an SQL command by which data from tables is copied to a file and vice versa. That is, using COPY you can transfer data to a file, then use the CREATE command to create a table and transfer data from the file to it.
➡️ pg_dump: a PostgreSQL utility that allows you to copy the entire database. With COPY, the data here is dumped in text format and restored as PSQL. Using the pg_restore utility allows you to load dumps created in a different format (from tar.gz – with the command pg_restore -Ft users.tar, from binaries – pg_restore -Fc users.bak).
➡️ pg_dumpall: a PostgreSQL utility that allows you to copy an entire cluster; the upload is in text-only format, and the restore is in PSQL. Example command: pg_dumpall > cluster.bak. Additional options (-g key (–globals-only)) allow unloading of tablespaces, roles, etc. if necessary.
Physical backup
The strengths of physical backup are considered to be recovery speed and the ability to restore the cluster state at any point in time since the backup was created. The disadvantages include the inability to restore individual databases (only entire clusters), as well as the inability to upload copies to other PostgreSQL versions.
There are two types of physical backup:
● Cold (the server is disconnected). If the shutdown is normal, the data is simply copied to another server. If the shutdown occurred as a result of a failure, the pre-record logs from the last checkpoint should be copied along with the data.
● Hot (server is switched on). Performed using the pg_basebackup utility. This backup requires all pre-record files from the beginning of the copy to the end of the copy. Example command: pg_basebackup -D /backup.
The choice between logical and physical backups depends on the specific situation and data recovery requirements. It is often recommended in practice to combine both types of backups to ensure full data protection and fast recovery in case of need.
How to backup PostgreSQL database
Data backup is one of the key aspects of any database administration. Below we will consider the main commands for the above-mentioned utilities, which are used to create backups in PostgreSQL.
It should be noted that only professional help could guarantee data safety in case of physical server failure, database hacking, or user error: for example, DB-serv engineers specializing in PostgreSQL.
➡️ The syntax of the basic backup command is as follows: pg_dump <parameters> <base name> > <file where to save the dump>.
➡️ If you want to backup a specific table, the -t <table> or -table=<table> option is used: pg_dump -t students users > /tmp/students.dump
➡️ When a table is found in a specific schema, the command looks like pg_dump -t public.students users > /tmp/students.dump
➡️ If you only want to make a copy of the data, use pg_dump –data-only users > /tmp/users.data.dump
➡️ To backup from outside the PostgreSQL account, the -U (username) and -W (user password) options are added to the base command.
➡️ To make the database dump not take up too much space on the server and to send it over the network faster, you can resort to compression, which is done with the pg_dump users | gzip > users.dump.gz command.
➡️ If the databases are on a remote server, make sure that PostgreSQL allows remote connections, then add the -h option: pg_dump -h 192.168.0.15 users > /tmp/users.dump
➡️ These commands and options will help you effectively create and manage data backups in PostgreSQL depending on your needs and environment.
How to restore a backup in PostgreSQL
If the backup copy was created correctly, it is easy to restore the database from it. All you need to do is to use the following commands.
The basic command looks like psql <base name> < <dump file>.
To restore the backup from outside the PostgreSQL account, the -U (username) and -W (user password) options are added to the base command.
If the database dump is in a .gz archive, it is first unpacked, and then the restore is run: zcat users.dump.gz | psql users
A specific database, if only a copy of it was made, is restored with the command psql users < /tmp/database.dump
In case of a full backup, the specific database is restored in pg_restore with the -d parameter: pg_restore -d users cluster.bak
A specific table, if only a copy of it was made, is restored with the command psql users < /tmp/students.dump
In case of a full backup, a specific table is restored in pg_restore with the -t parameter: pg_restore -a -t students users.dump