For many of our customers, pgBackRest is the backup tool of choice. That is why I decided to take you on a journey on this tool, from a high-level overview through to details on how to configure it, with examples.
So let's dive right in.
Getting started
In the realm of enterprise database management, Fujitsu Enterprise Postgres has consistently stood as a trusted solution. And the latest development streamlines and elevates the crucial tasks of database backup and restoration—the integration of pgBackRest.
Our mission is clear: to empower you with step-by-step instructions on installation, backup creation, and data restoration using this potent combination. In an era where data is the lifeblood of organizations, the fortification it deserves is here.
Why choose pgBackRest?
Before diving into the installation and configuration of pgBackRest, let's understand why it's a valuable addition to Fujitsu Enterprise Postgres.
Efficiency
pgBackRest has been developed to be extremely efficient in reducing downtime and maximizing performance, with parallel backup and restore processes.
Incremental backups
pgBackRest allows you to take incremental backups, which drastically lower the amount of time and storage space needed for each backup.
Point-in-Time Recovery
Guarantees data consistency by allowing you to restore your database to a particular point in time.
Parallel compression
pgBackRest's parallel compression function streamlines the process of compressing backups to save disk space.
Simple configuration
Integrating pgBackRest into your current PostgreSQL or Fujitsu Enterprise Postgres system is a simple process.
Pre-requisites
To proceed with testing of various restoration scenarios, we need to be ready with the following prerequisites:
- The same version of pgBackRest needs to be installed on source and all target machines
- Repository information: AWS S3 Bucket, Azure Blob Storage, Google Cloud Storage Bucket, or Dedicated backup server
- Appropriate access via SSH or TLS needs to configured from all target machines to repository
- Configure passwordless authentication for fepuser between the backup server and the database server.
Let's proceed with pgBackRest installation and configuration in Fujitsu Enterprise Postgres - in this example I will be using pgbackrest version 2.46 and Fujitsu Enterprise Postgres 15 SP1.
Machine details
Installing pgBackRest in Fujitsu Enterprise Postgres
The implementation of pgBackRest is dependent purely on how it is used. The pgBackRest bundled with the Fujitsu Enterprise Postgres server should be used in conjunction with the pgBackRest command to be used on the same host. However, use the pgBackRest bundled with the Fujitsu Enterprise Postgres client if you want to run the pgBackRest command on a host other than the Fujitsu Enterprise Postgres server.
Since pgBackRest is bundled in Fujitsu Enterprise Postgres, no additional installation procedure is needed. We can find the pgBackRest binary at the below default location.
- On Fujitsu Enterprise Postgres server: /opt/fsepv15server64/OSS/pgbackrest/bin
- On Fujitsu Enterprise Postgres client: /opt/fsepv15client64/OSS/pgbackrest/bin
Configuring pgBackRest on the database server
On the database server, the very first step is to create the configuration file /etc/pgbackrest/pgbackrest.conf:
[fepuser@fep15sp1 log]$ vi /etc/pgbackrest/pgbackrest.conf
[global]
repo1-host=192.168.10.144
repo1-host-user=fepuser
process-max=2
log-level-console=info
log-level-file=debug
[fep15sp1]
pg1-path=/database/inst1
pg1-port=27500
In the example above, we have:
- repo1-host specifies the backup server (repository host) when using SSH for accessing remotely.
Ensure that the database server and the backup server (repository host) are using SSH authentication. - repo1-host-user specifies the user that will be used for backup-related operations on the backup server (repository host).
- process-max specifies the maximum number of processes to use for compression or transfer operations.
Each process will perform compression and transfer to make the command run faster, but we need to keep in mind that specifying a high value will impact database performance.
- log-level-console specifies the level for the console logging information.
off - No logging at all (not recommended); error - Log only errors; warn - Log warnings and errors; info - Log info, warnings, and errors; detail - Log detail, info, warnings, and errors; debug - Log debug, detail, info, warnings, and errors; trace - Log trace (very verbose debugging), debug, info, warnings, and errors.
- log-level-file specifies the level for the file logging information.
off - No logging at all (not recommended); error - Log only errors; warn - Log warnings and errors; info - Log info, warnings, and errors; detail - Log detail, info, warnings, and errors; debug - Log debug, detail, info, warnings, and errors; trace - Log trace (very verbose debugging), debug, info, warnings, and errors.
- fep15sp1 is a stanza name. A stanza is the configuration for a database cluster that defines where it is located, how it will be backed up, its archiving options, etc.
- pg1-path specifies the data directory location.
- pg1-port specifies the port of the database cluster.
Change postgresql.conf in the database server
Modify the parameters below with appropriate values.
[fepuser@fep15sp1 ~]$ cat /database/inst1/postgresql.conf | grep 'archive_mode\|archive_command'
archive_mode = on
archive_command = '/opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 archive-push %p'
[fepuser@fep15sp1 ~]$
After the parameter changes above, you will need to restart the database service.
Configuring pgBackRest on the backup server
On the backup server, we need to create the configuration file /etc/pgbackrest/pgbackrest.conf:
[fepuser@pgbackrest ~]$ vi /etc/pgbackrest/pgbackrest.conf
[global]
repo1-path=/dbbackup/pgbackrest
repo1-retention-full=2
process-max=2
log-level-console=info
log-level-file=debug
start-fast=y
stop-auto=y
[fep15sp1]
pg1-path=/database/inst1
pg1-host=192.168.10.147
pg1-host-user=fepuser
pg1-port=27500
From the file above, we have:
- repo1-path specifies where backups and archive will be stored.
- repo1-retention-full specifies the number of full backups to retain.
- start-fast specifies whether to force a checkpoint (by passing y to the fast parameter of pg_start_backup()) so the backup begins immediately. Otherwise the backup will start after the next regular checkpoint.
- stop-auto specifies whether to stop a prior failed backup on a new backup.
- pg1-host specifies the database host for operating remotely via SSH.
- pg1-host-user specifies the database user
Create the stanza and check the configuration in the backup server
At this stage, our pgBackRest setup is ready, and we are now able to take backups.
Performing backup
Let's walk through the steps of performing a full backup, followed by an incremental backup.
Full backup
For readability purposes, we broke long lines below and in other examples in this article.[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 --type=full backup
2024-01-03 13:00:57.761 P00 INFO: backup command begin 2.46: --exec-id=6233- d46fea1b --log-level-console=info
--log-level-file=debug --log- path=/etc/pgbackrest/log
--pg1-host=192.168.10.147 --pg1-host-user=fepuser
--pg1-path=/database/inst1 --pg1-port=27500
--process-max=2 --repo1-path=/dbbackup/pgbackrest --repo1-retention-full=2
--stanza=fep15sp1 --start-fast --stop-auto --type=full
2024-01-03 13:00:58.907 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-01-03 13:00:59.421 P00 INFO: backup start archive = 000000010000000200000081, lsn = 2/81000028
2024-01-03 13:00:59.421 P00 INFO: check archive for prior segment 000000010000000200000080
2024-01-03 13:01:50.390 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-01-03 13:01:50.594 P00 INFO: backup stop archive = 000000010000000200000081, lsn = 2/81000138
2024-01-03 13:01:50.600 P00 INFO: check archive for segment(s) 000000010000000200000081:000000010000000200000081
2024-01-03 13:01:51.502 P00 INFO: new backup label = 20240103-130059
2024-01-03 13:01:51.598 P00 INFO: full backup size = 2.3GB, file total = 1917
2024-01-03 13:01:51.598 P00 INFO: backup command end: completed successfully (53838ms)
2024-01-03 13:01:51.598 P00 INFO: expire command begin 2.46: --exec-id=6233- d46fea1b --log-level-console=info
--log-level-file=debug --log-path=/etc/pgbackrest/log --repo1-path=/dbbackup/pgbackrest
--repo1-retention-full=2 --stanza=fep15sp1
2024-01-03 13:01:51.610 P00 INFO: repo1: 15-1 remove archive, start = 000000010000000100000034, stop = 000000010000000100000037
2024-01-03 13:01:51.610 P00 INFO: expire command end: completed successfully (12ms)
[fepuser@pgbackrest ~]$
From the output above, we can take note of the label of our full backup: 20240103-130059F.
Now let's obtain the list of backups:
[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 info
stanza: fep15sp1
status: ok
cipher: none
db (current)
wal archive min/max (15): 000000010000000100000038/000000010000000200000081
full backup: 20240103-130059F
timestamp start/stop: 2024-01-03 13:00:59 / 2024-01-03 13:01:50
wal start/stop: 000000010000000200000081 / 000000010000000200000081
database size: 2.3GB, database backup size: 2.3GB
repo1: backup set size: 149.8MB, backup size: 149.8MB
[fepuser@pgbackrest ~]$
We can see that our first full backup (20240103-130059F) is listed. We are now able to take incremental backup.
Incremental backup
Before performing an incremental backup, let's create some data on the database server.
[fepuser@fep15sp1 ~]$ psql
psql (15.4)
Type "help" for help.
postgres=# CREATE database demo;
CREATE DATABASE
postgres=# \c demo
You are now connected to database "demo" as user "fepuser".
demo=# CREATE table test (id numeric);
CREATE TABLE
demo=# INSERT INTO test values (generate_series(1,100000));
INSERT 0 100000
demo=#
Next, we perform incremental backup:
[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 --type=incr backup
2024-01-03 13:07:50.687 P00 INFO: backup command begin 2.46: --exec-id=6475-5e42a3b5 --log-level-console=info
--log-level-file=debug --log-path=/etc/pgbackrest/log
--pg1-host=192.168.10.147 --pg1-host-user=fepuser --pg1-path=/database/inst1
--pg1-port=27500 --process-max=2 --repo1- path=/dbbackup/pgbackrest --repo1-retention-full=2
--stanza=fep15sp1 --start-fast --stop-auto --type=incr
2024-01-03 13:07:51.952 P00 INFO: last backup label = 20240103-130059F, version= 2.46
2024-01-03 13:07:51.952 P00 INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-01-03 13:07:52.483 P00 INFO: backup start archive = 000000010000000200000083, lsn = 2/83000028
2024-01-03 13:07:52.484 P00 INFO: check archive for prior segment 000000010000000200000082
2024-01-03 13:08:05.702 P00 INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-01-03 13:08:05.907 P00 INFO: backup stop archive = 000000010000000200000083, lsn = 2/83000138
2024-01-03 13:08:05.949 P00 INFO: check archive for segment(s) 000000010000000200000083:000000010000000200000083
2024-01-03 13:08:06.546 P00 INFO: new backup label = 20240103-130059F_20240103-130752I
2024-01-03 13:08:06.759 P00 INFO: incr backup size = 13.3MB, file total = 1870
2024-01-03 13:08:06.759 P00 INFO: backup command end: completed successfully (16074ms)
2024-01-03 13:08:06.759 P00 INFO: expire command begin 2.46: --exec-id=6475- 5e42a3b5 --log-level-console=info --log-level-file=debug
--log-path=/etc/pgbackrest/log --repo1-path=/dbbackup/pgbackrest --repo1-retention-full=2
--stanza=fep15sp1
2024-01-03 13:08:06.764 P00 INFO: expire command end: completed successfully (5ms)
[fepuser@pgbackrest ~]$
We can see in the output above the label of the incremental backup: 20240103-130059F_20240103-130752I.
Next, we obtain the list of backups again:
[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 info
stanza: fep15sp1
status: ok
cipher: none
db (current)
wal archive min/max (15): 000000010000000100000038/000000010000000200000083
full backup: 20240103-130059F
timestamp start/stop: 2024-01-03 13:00:59 / 2024-01-03 13:01:50
wal start/stop: 000000010000000200000081 / 000000010000000200000081
database size: 2.3GB, database backup size: 2.3GB
repo1: backup set size: 149.8MB, backup size: 149.8MB
incr backup: 20240103-130059F_20240103-130752I
timestamp start/stop: 2024-01-03 13:07:52 / 2024-01-03 13:08:06
wal start/stop: 000000010000000200000083 / 000000010000000200000083
database size: 2.3GB, database backup size: 13.3MB
repo1: backup set size: 150.1MB, backup size: 1.5MB
backup reference list: 20240103-130059F
[fepuser@pgbackrest ~]$
Our first full backup 20240103-130059F and the incremental backup 20240103-130059F_20240103-130752I are listed.
Performing restore
Let's proceed with testing various scenarios when performing restore, as follows:
- Restoring the full backup
- Restoring the backup on a different host
- Restoring the backup to a specific point in time
- Restoring a single database from the database cluster
Restoring the full backup
To restore the backup, we need to execute the restore command on the database server.
Trying to execute the restore command on the backup server will result in the error below:
[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 restore
2024-01-05 09:49:23.727 P00 INFO: restore command begin 2.46: --exec-id=2504- 422612b8 --log-level-console=info
--log-level-file=debug --log-path=/etc/pgbackrest/log --pg1-host=192.168.10.147
--pg1-path=/database/inst1 --process-max=2
--repo1-path=/dbbackup/pgbackrest --stanza=fep15sp1
ERROR: [072]: restore command must be run on the PostgreSQL host
2024-01-05 09:49:23.727 P00 INFO: restore command end: aborted with exception [072]
[fepuser@pgbackrest ~]$
So, as instructed by the error message, let's execute the restore command on the database server. First, remove or rename the existing data directory as below:
[fepuer@fep15sp1 ~]# mv /database/inst1 /database/inst1_OLD
Next, execute the restoration command on the database server.
[fepuser@fep15sp1 database]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 restore
2024-01-05 10:05:20.976 P00 INFO: restore command begin 2.46: --exec-id=2992- 35554eb7 --log-level-console=info
--log-level-file=debug --log-path=/etc/pgbackrest/log --pg1-path=/database/inst1
--process-max=2
--repo1-host=192.168.10.144 --repo1-host-user=fepuser --stanza=fep15sp1
2024-01-05 10:05:21.510 P00 INFO: repo1: restore backup set 20240103- 130059F_20240103-130752I, recovery will start at 2024-01-03 13:07:52
2024-01-05 10:06:16.663 P00 INFO: write updated /database/inst1/postgresql.auto.conf
2024-01-05 10:06:16.666 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-01-05 10:06:16.667 P00 INFO: restore size = 2.3GB, file total = 1870
2024-01-05 10:06:16.667 P00 INFO: restore command end: completed successfully (55692ms)
[fepuser@fep15sp1 database]$
pgBackRest automatically inserts the restore_command in postgresql.auto.conf to restore archived WAL logs
[fepuser@fep15sp1 inst1]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
# Recovery settings generated by pgBackRest restore on 2024-01-05 10:14:16
restore_command = '/opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest -- stanza=fep15sp1 archive-get %f "%p"'
[fepuser@fep15sp1 inst1]$
When the Fujitsu Enterprise Postgres service starts, it will execute the abovementioned restore_command, and all archived WALs will be restored, which is required to successfully complete the recovery.
Let's connect to the database and verify the restoration.
[fepuser@fep15sp1 database]$ pg_ctl -D /database/inst1 start
waiting for server to start....
2024-01-05 10:08:08 +08 [3017]: user=,db=,app=,client= LOG: redirecting log output to logging collector process
2024-01-05 10:08:08 +08 [3017]: user=,db=,app=,client= HINT: Future log output will appear in directory "log".
. done
server started
[fepuser@fep15sp1 database]$ psql
psql (15.4)
Type "help" for help.
postgres=#
Restoring the backup on a different host
In this case, we'll test the backup by restoring it to the test server. In this case my test server is test-machine (192.168.10.143). On that test machine, I have configured pgBackRest configuration and its details as follows:
[fepuser@test-machine ~]$ cat /etc/pgbackrest/pgbackrest.conf
[global]
repo1-host=192.168.10.144
repo1-host-user=fepuser
process-max=2
log-level-console=info log-level-file=debug
log-path= /etc/pgbackrest/log
[fep15sp1]
pg1-path=/database/inst1
pg1-port=27500
By default, pgBackRest restores the latest full backup. Let's restore the available latest full backup.
As we can see below we have the latest full backup of 12th Jan 2024 (2024-01-12).
[fepuser@pgbackrest ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --stanza=fep15sp1 info
stanza: fep15sp1
status: ok cipher: none
db (current)
wal archive min/max (15): 0000000300000002000000D6/000000030000000500000064
full backup: 20240105-161608F
timestamp start/stop: 2024-01-05 16:16:08 / 2024-01-05 16:17:03
wal start/stop: 0000000300000002000000D6 / 0000000300000002000000D6
database size: 2.9GB, database backup size: 2.9GB repo1: backup set size: 174MB, backup size: 174MB
incr backup: 20240105-161608F_20240105-162414I
timestamp start/stop: 2024-01-05 16:24:14 / 2024-01-05 16:25:10 wal start/stop: 000000030000000300000071 / 000000030000000300000071
database size: 4.3GB, database backup size: 2.9GB repo1: backup set size: 254.3MB, backup size: 160.7MB backup reference list: 20240105-161608F
full backup: 20240112-131712F
timestamp start/stop: 2024-01-12 13:17:12 / 2024-01-12 13:18:35 wal start/stop: 000000030000000500000061 / 000000030000000500000061
database size: 4.3GB, database backup size: 4.3GB repo1: backup set size: 254.6MB, backup size: 254.6MB
Let's execute the below command on test-machine to restore the latest full backup.
[fepuser@test-machine ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf
--stanza=fep15sp1 restore
2024-01-24 21:38:12.745 P00 INFO: restore command begin 2.46: --config=/etc/pgbackrest/pgbackrest.conf --exec-id=9603-f8083214
--log-level-console=info --log-level-file=debug --log-path=/etc/pgbackrest/log
--pg1-path=/database/inst1 --process-max=2
--repo1-host=192.168.10.144 --repo1-host-user=fepuser --stanza=fep15sp1
2024-01-24 21:38:13.228 P00 INFO: repo1: restore backup set 20240112-131712F, recovery will start at 2024-01-12 13:17:12
2024-01-24 21:40:13.544 P00 INFO: write updated /database/inst1/postgresql.auto.conf
2024-01-24 21:40:13.547 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-01-24 21:40:13.548 P00 INFO: restore size = 4.3GB, file total = 1569
2024-01-24 21:40:13.548 P00 INFO: restore command end: completed successfully (120804ms)
[fepuser@test-machine ~]$
As we can see, our full backup restoration was completed successfully.
Now let's start the Fujitsu Enterprise Postgres service and verify the restoration. But before starting the database service, we need to make sure that the archive_command is either set to a different path or disabled, in order to prevent the database from writing the archive files to the existing pgBackRest repo server upon starting the test machine.
[fepuser@test-machine ~]$ /opt/fsepv15server64/bin/pg_ctl -D /database/inst1 start
waiting for server to start....
2024-01-24 21:45:14 +08 [9715]: user=,db=,app=,client= LOG: redirecting log output to logging collector process
2024-01-24 21:45:14 +08 [9715]: user=,db=,app=,client= HINT: Future log output will appear in directory "log".
... done
server started
[fepuser@test-machine ~]$ psql -p 27500 -d postgres -U fepuser psql (15.4)
Type "help" for help.
postgres=# SELECT datname FROM pg_database;
datname
-----------
template1
template0
postgres
pgbench
(4 rows)
postgres=# show archive_command;
archive_command
-----------------
cp %p /arch/%f
(1 row)
postgres=#
Restoring the backup to a specific point in time
By using pgBackRest, we can also perform Point-in-Time Recovery (PITR). For that, we need the exact timestamp, so the tool can perform the PITR till that specified timestamp.
To perform PITR, create a new table on the database server and insert a few records, as follows:
postgres=# CREATE TABLE pitr_test( id numeric );
CREATE TABLE
postgres=# INSERT INTO pitr_test VALUES(generate_series(1,10000));
INSERT 0 10000
postgres=# select now();
now
-------------------------------
2024-01-24 23:07:59.589762+08
(1 row)
Now, let's drop this table and perform the PITR till the abovementioned timestamp.
postgres=# DROP TABLE pitr_test;
DROP TABLE
postgres=# SELECT now();
now
-------------------------------
2024-01-24 23:09:00.777755+08
(1 row)
postgres=#
While performing PITR using pgBackRest, --type=time or --type=lsn needs to be specified, and then the target time or target lsn must be specified with the --target option.
First, stop the database service on the database server (fep15sp1) and rename the existing data directory.
[fepuser@fep15sp1 ~]$ mv /database/inst1 /database/inst1_old
[fepuser@fep15sp1 ~]$ mkdir -p /database/inst1
[fepuser@fep15sp1 ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf \
> --stanza=fep15sp1 --type=time "--target=2024-01-24 23:07:59" restore
2024-01-24 23:18:11.484 P00 INFO: restore command begin 2.46: -- config=/etc/pgbackrest/pgbackrest.conf --exec-id=7406-d73d8441
--log-level-console=info --log-level-file=debug --log-path=/etc/pgbackrest/log
--pg1-path=/database/inst1 --process-max=2
--repo1-host=192.168.10.144 --repo1-host-user=fepuser --stanza=fep15sp1
--target="2024-01-24 23:07:59" --type=time
2024-01-24 23:18:11.970 P00 INFO: repo1: restore backup set 20240124- 230604F, recovery will start at 2024-01-24 23:06:04
2024-01-24 23:19:42.377 P00 INFO: write updated /database/inst1/postgresql.auto.conf
2024-01-24 23:19:42.380 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-01-24 23:19:42.381 P00 INFO: restore size = 4.3GB, file total = 1653
2024-01-24 23:19:42.382 P00 INFO: restore command end: completed successfully (90899ms)
Now, our PITR is successfully completed. Let's verify the restoration.
[fepuser@fep15sp1 ~]$ pg_ctl -D /database/inst1 start waiting for server to start....
2024-01-24 23:20:06 +08 [7460]: user=, db=, app, client= LOG: redirecting log output to logging collectorprocess 2024-01-24 23:20:06 +08 [7460]: user=, db=, app, client= HINT: Future log output will appear in directory "log". ...done server started [fepuser@fep15sp1 ~]$ psql psql (15.4) Type "help" for help. postgres=# \d pitr_test Table "public.pitr_test" Column | Type | Collation | Nullable | Default -------+---------+------------+----------+---------- id | numeric | | | postgres=# SELECT COUNT (1) FROM pitr_test; count ------- 10000 (1 row) postgres=#
Restoring a single database from the database cluster
In this scenario, we will restore only a single database on test-machine. I have the databases below in my primary database.
postgres=# SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
postgres-# pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", postgres-# CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
postgres-# THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
postgres-# ELSE 'No Access' END as "Size" postgres-# FROM pg_catalog.pg_database d
postgres-# JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; Name | Owner | Encoding | Size -----------+---------+----------+------- pgbench | fepuser | UTF8 | 2998 MB postgres | fepuser | UTF8 | 1129 MB template0 | fepuser | UTF8 | 7585 kB template1 | fepuser | UTF8 | 7665 kB (4 rows)
Next, we will restore the pgbench database from the latest backup to test-machine. To restore only a specific database, the --db-include option needs to be used (multiple databases can be specified).
[fepuser@test-machine ~]$ /opt/fsepv15client64/OSS/pgbackrest/bin/pgbackrest --config=/etc/pgbackrest/pgbackrest.conf \
> --stanza=fep15sp1 --db-include=pgbench restore 2024-01-25 10:32:52.437 P00 INFO: restore command begin 2.46: --config=/etc/pgbackrest/pgbackrest.conf --db-include=pgbench
--exec-id=19157-6b34f074 --log-level-console=info --log-level-file=debug
--log-path=/etc/pgbackrest/log --pg1-path=/database/inst1 --process-max=2
--repo1-host=192.168.10.144 --repo1-host-user=fepuser --stanza=fep15sp1 2024-01-25 10:32:52.937 P00 INFO: repo1: restore backup set 20240124-230604F_20240125-095806I,
recovery will start at 2024-01-25 09:58:06 2024-01-25 10:34:20.628 P00 INFO: write updated /database/inst1/postgresql.auto.conf 2024-01-25 10:34:20.631 P00 INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started) 2024-01-25 10:34:20.631 P00 INFO: restore size = 4GB, file total = 1887
2024-01-25 10:34:20.632 P00 INFO: restore command end: completed successfully (88197ms) [fepuser@test-machine ~]$
Now, verify the restoration.
[fepuser@test-machine ~]$ /opt/fsepv15server64/bin/pg_ctl -D /database/inst1 start waiting for server to start....
2024-01-25 10:34:27 +08 [19197]: user=,db=,app=,client= LOG: redirecting log output to logging collector process 2024-01-25 10:34:27 +08 [19197]: user=,db=,app=,client= HINT: Future log output will appear in directory "log". ... done server started [fepuser@test-machine ~]$ /opt/fsepv15server64/bin/psql -p 27500 -d postgres -U fepuser psql (15.4) Type "help" for help. postgres=# SELECT d.datname as "Name", pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
postgres-# pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding", postgres-# CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
postgres-# THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
postgres-# ELSE 'No Access' END as "Size" postgres-# FROM pg_catalog.pg_database d
postgres-# JOIN pg_catalog.pg_tablespace t on d.dattablespace = t.oid ORDER BY 1; Name | Owner | Encoding | Size -----------+---------+----------+------- pgbench | fepuser | UTF8 | 2998 MB postgres | fepuser | UTF8 | 1129 MB template0 | fepuser | UTF8 | 7585 kB template1 | fepuser | UTF8 | 7665 kB (4 rows)
When you restore a specific database, you may see the names of other databases (besides the default and specified ones) in the list. However, attempting to connect to these databases will result in an error:
postgres=# \c <db name> connection to server on socket "/tmp/.s.PGSQL.27500" failed:
FATAL: relation mapping file "base/<DB_OID>/pg_filenode.map" contains invalid data Previous connection kept postgres=#
This occurs because the other databases were restored as sparse, zeroed files. While PostgreSQL can apply WAL (Write-Ahead Logging) to these zeroed files, the databases remain invalid, since key files contain no data. This design prevents accidental use of databases that might contain partial data from WAL replay.
At this point, the only action you can take on these invalid databases (other than the default) is to drop them. pgBackRest does not automatically drop these databases, as this can only be done once recovery is complete and the cluster is accessible.
Final thoughts
As we have seen, pgBackRest is an amazing open source database backup tool available for PostgreSQL and now is bundled with Fujitsu Enterprise Postgres, which also enhances backup & restoration activities smoothly and effectively.
Integration of pgBackRest with Fujitsu Enterprise Postgres stands as a robust solution, offering notable advantages such as ease of configuration, efficient backup, and seamless restoration processes. Its reliability and straightforward setup makes it an invaluable asset for DBAs.
