Now that exclusive backup with pg_start_backup()
and pg_stop_backup()
has been removed, it is more difficult to perform a PostgreSQL online
file system backup using a pre-backup and a post-backup script.
This project provides two bash
scripts that can be used for that purpose.
Edit config.sh
and adapt the variables to your needs.
Call pgpre.sh
. If successful, it will print the starting location of
the backup and exit with a return code of 0.
Then perform your file system backup any way you want.
When you are done (even in case of error!) call pgpost.sh
to end the
backup. It will return a return code of 0 if successful and print
the contents of the backup_label
file to standard output.
You have to collect this output to a file backup_label
and copy that
file into the backup to complete the online backup.
Alternatively, you can get the file contents by connecting to the database
and running SELECT backup_label FROM backup
.
If you are using tablespaces, you'll have to collect the contents of the
tablespace_map
file from the column of that name in the backup
table
and also store it with the backup.
The scripts are configured by editing config.sh
.
The environment variables PGHOST
, PGPORT
, PGDATABASE
and PGUSER
determine which database cluster is backed up. The table backup
containing the backup information will be created in PGDATABASE
.
The parameter backup_timeout
can be set to 0 to disable a timeout
or to the number of seconds the pre-backup script should wait for
the backup to complete before marking the backup as failed.
If you don't set a timeout and don't call pgpost.sh
to end the backup,
the session waiting for the backup to end will continue until you next
call pgpre.sh
.
If it does not already exist, the pre-backup script creates a table
backup
with the following columns:
-
id
: a primary key that is always 1 (the table has only a single row) -
state
: a column with an enum type that can take the valuesrunning
,done
,complete
andfailed
, indicating the state of the backup -
pid
: the backend process ID if the pre-backup process that waits for the backup to complete -
backup_label
: the contents of thebackup_label
file for the backup -
tablespace_map
: the contents of thetablespace_map
file for the backup or NULL if there is only the default tablespace
Then it enters the database into backup mode and returns the WAL position of the checkpoint starting the backup. After that, it exits successfully, indicating that the actual backup can begin
A co-process controlling the PostgreSQL session keeps running in the
background and waits for the state
column in the backup
table to signal
that the backup is done. If that does not happen before backup_timeout
expires, the backup is marked as failed.
Once the backup is done, the co-process ends backup mode and stores the
resulting backup_label
and tablespace_map
in the backup
table.
The post-backup script sets the state
column in the backup
table to
done
and waits until the pre-backup co-process has set the state
to completed
.
Then it reads backup_label
from the backup
table and emits it on
standard output before ending.