RESTORE

The RESTORE subcommand restores a backup and its archived WAL files for the designated database server to the specified directory location.

Syntax for Restore:

bart RESTORE –s <server_name> -p <restore_path>
[ –i { <backup_id> | <backup_name> } ]
[ -r <remote_user>@<remote_host_address> ]
[ -w <number_of_workers> ]
[ -t <timeline_id> ]
[ { -x <target_xid> | -g <target_timestamp> } ]
[ -c ]

To view detailed information about the RESTORE subcommand, see the EDB Postgres Backup and Recovery User Guide available at the EDB website.

If the backup is restored to a different database cluster directory than where the original database cluster resided, then some operations dependent upon the database cluster location may fail. This happens if the supporting service scripts are not updated to reflect the new directory location of restored backup.

For information about the use and modification of service scripts, see the EDB Postgres Advanced Server Installation Guide available at the EDB website.

The following table describes the RESTORE options:

Options

Description

-s or --server <server_name>

<server_name> is the name of the database server to be restored.

-p or --restore-path <restore_path>

<restore_path> is the directory path where the backup of the database server is to be restored. The directory must be empty and have the proper ownership and privileges assigned to it.

-i or --backupid { <backup_id> | <backup_name>}

backup_id is the backup identifier of the backup to be used for the restoration and <backup_name> is the user-defined alphanumeric name for the backup.
If the option is omitted, the latest backup is restored by default.

-r or --remote-host <remote_user@remote_host_address>

<remote_user> is the user account on the remote database server host that accepts a passwordless SSH/SCP login connection and is the owner of the directory where the backup is to be restored.
<remote_host_address> is the IP address of the remote host to which the backup is to be restored. This option must be specified if the remote_host parameter for this database server is not set in the BART configuration file.
For information about the remote_host parameter, see the EDB Postgres Backup and Recovery Installation and Upgrade Guide available at the EDB website.

-w or --workers <number_of_workers>

<number_of_workers> is the number of worker processes to run in parallel to stream the modified blocks of an incremental backup to the restore location. If the -w option is omitted, the default is 1 worker process.
For example, if four worker processes are specified, four receiver processes on the restore host and four streamer processes on the BART host are used. The output of each streamer process is connected to the input of a receiver process.
When the receiver gets to the point where it needs a modified block file, it obtains those modified blocks from its input. With this method, the modified block files are never written to the restore host disk.

-t or --target-tli <timeline_id>

<timeline_id> is the integer identifier of the timeline to be used for replaying the archived WAL files for point-in-time recovery.

-x or --target-xid <target_xid>

<target_xid> is the integer identifier of the transaction ID that determines the transaction up to and including, which point-in-time recovery encompasses.

-g or --target-timestamp <target_timestamp>

<target_timestamp> is the timestamp that determines the point in time up to and including, which point-in-time recovery encompasses.

-c or --copy-wals

Specify this option to copy archived WAL files from the BART backup catalog to <restore_path>/archived_wals directory.
The restore_command retrieves the WAL files from <restore_path>/archived_wals for the database server archive recovery.
If the -c option is omitted and the copy_wals_during_restore parameter in the BART configuration file is not enabled in a manner applicable to this database server, then the restore_command in the postgresql.conf retrieves the archived WAL files directly from the BART backup catalog.
For information about the copy_wals_during_restore parameter, see the EDB Postgres Backup and Recovery Installation and Upgrade Guide available at the EDB website.

Examples

The following code sample restores a database server (named mktg) to the /opt/restore directory up to timestamp 2015-12-15 10:47:00:

-bash-4.1$ bart RESTORE -s mktg -i 1450194208824 -p /opt/restore -t 1 -g
'2015-12-15 10:47:00'
INFO: restoring backup '1450194208824' of server 'mktg'
INFO: restoring backup to enterprisedb@192.168.2.24:/opt/restore
INFO: base backup restored
INFO: WAL file(s) will be streamed from the BART host
INFO: writing recovery settings to postgresql.auto.conf file
INFO: archiving is disabled
INFO: tablespace(s) restored

The following parameters are set in the postgresql.auto.conf file:

restore_command = 'scp -o BatchMode=yes -o PasswordAuthentication=no
enterprisedb@192.168.2.22:/opt/backup/mktg/archived_wals/%f %p'
recovery_target_time = '2015-12-15 10:47:00'
recovery_target_timeline = 1

The following is a list of the restored files and subdirectories:

[root@localhost restore]# pwd
/opt/restore
[root@localhost restore]# ls -l
total 108
-rw------- 1 enterprisedb enterprisedb 208 Dec 15 10:43 backup_label
drwx------ 6 enterprisedb enterprisedb 4096 Dec 2 10:38 base
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 10:42 dbms_pipe
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 11:00 global
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_clog\
-rw------- 1 enterprisedb enterprisedb 4438 Dec 2 10:38 pg_hba.conf
-rw------- 1 enterprisedb enterprisedb 1636 Nov 10 15:38 pg_ident.conf
drwxr-xr-x 2 enterprisedb enterprisedb 4096 Dec 15 10:42 pg_log
drwx------ 4 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_multixact
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 10:42 pg_notify
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_serial
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_snapshots
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 10:42 pg_stat
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 10:43 pg_stat_tmp
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_subtrans
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 11:00 pg_tblspc
drwx------ 2 enterprisedb enterprisedb 4096 Nov 10 15:38 pg_twophase
-rw------- 1 enterprisedb enterprisedb 4 Nov 10 15:38 PG_VERSION
drwx------ 2 enterprisedb enterprisedb 4096 Dec 15 11:00 pg_xlog
-rw------- 1 enterprisedb enterprisedb 23906 Dec 15 11:00
postgresql.conf
-rw-r--r-- 1 enterprisedb enterprisedb 217 Dec 15 11:00
postgresql.auto.conf

Example

The following code sample performs a RESTORE operation with the copy_wals_during_restore parameter enabled to copy the archived WAL files to the local <restore_path>/archived_wals directory:

-bash-4.1$ bart RESTORE -s hr -i hr_2017-03-29T13:50 -p
/opt/restore_pg96 -t 1 -g '2017-03-29 14:01:00'
INFO: restoring backup 'hr_2017-03-29T13:50' of server 'hr'
INFO: base backup restored
INFO: copying WAL file(s) to
postgres@192.168.2.24:/opt/restore_pg96/archived_wals
INFO: writing recovery settings to postgresql.auto.conf file
INFO: archiving is disabled
INFO: permissions set on $PGDATA
INFO: restore completed successfully

The following parameters are set in the postgresql.auto.conf file:

restore_command = 'cp archived_wals/%f %p'
recovery_target_time = '2017-03-29 14:01:00'
recovery_target_timeline = 1

The following is a list of the restored files and subdirectories:

-bash-4.1$ pwd
/opt/restore_pg96
-bash-4.1$ ls -l
total 128
drwxr-xr-x 2 postgres postgres 4096 Mar 29 14:27 archived_wals
-rw------- 1 postgres postgres 206 Mar 29 13:50 backup_label
drwx------ 5 postgres postgres 4096 Mar 29 12:25 base
drwx------ 2 postgres postgres 4096 Mar 29 14:27 global
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_clog
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_commit_ts
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_dynshmem
-rw------- 1 postgres postgres 4212 Mar 29 13:18 pg_hba.conf
-rw------- 1 postgres postgres 1636 Mar 29 12:25 pg_ident.conf
drwxr-xr-x 2 postgres postgres 4096 Mar 29 13:45 pg_log
drwx------ 4 postgres postgres 4096 Mar 29 12:25 pg_logical
drwx------ 4 postgres postgres 4096 Mar 29 12:25 pg_multixact
drwx------ 2 postgres postgres 4096 Mar 29 13:43 pg_notify
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_replslot
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_serial
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_snapshots
drwx------ 2 postgres postgres 4096 Mar 29 13:43 pg_stat
drwx------ 2 postgres postgres 4096 Mar 29 13:50 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_subtrans
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_tblspc
drwx------ 2 postgres postgres 4096 Mar 29 12:25 pg_twophase
-rw------- 1 postgres postgres 4 Mar 29 12:25 PG_VERSION
drwx------ 3 postgres postgres 4096 Mar 29 14:27 pg_xlog
-rw------- 1 postgres postgres 169 Mar 29 13:24 postgresql.auto.conf
-rw-r--r-- 1 postgres postgres 21458 Mar 29 14:27 postgresql.conf
-rw-r--r-- 1 postgres postgres 118 Mar 29 14:27 postgresql.auto.conf