Sample BART System with Local and Remote Database Servers v2.6

Suggest edits

This section describes a sample BART managed backup and recovery system consisting of both local and remote database servers. The complete steps to configure and operate the system are provided.

For detailed information about configuring a BART system, see the EDB Backup and Recovery Installation and Upgrade Guide. For detailed information about the operational procedures and BART subcommands, see the EDB Backup and Recovery User Guide. These guides are available at the EDB website.

The environment for this sample system is as follows:

BART on host 192.168.2.22 running with BART user account enterprisedb
Local Advanced Server on host 192.168.2.22 running with user account enterprisedb
Remote Advanced Server on host 192.168.2.24 running with user account enterprisedb
Remote PostgreSQL server on host 192.168.2.24 running with user account postgres

Passwordless SSH/SCP connections are required between the following:

BART on host 192.168.2.22 and the local Advanced Server on the same host 192.168.2.22
BART on host 192.168.2.22 and the remote Advanced Server on host 192.168.2.24
BART on host 192.168.2.22 and the remote PostgreSQL server on host 192.168.2.24

The following sections demonstrate configuring and taking full backups only. To support incremental backups as well, enable the allow_incremental_backups parameter for the desired database servers and use the WAL scanner program.

The BART Configuration File shows the settings used in the BART configuration file.
Establishing SSH/SCP Passwordless Connections provides an example of how to establish an SSH/SCP passwordless connection.
Configuring a Replication Database User provides an example of how to configure the replication database user.
WAL Archiving Configuration Parameters provides an example of how to configure WAL archiving.
Creating the BART Backup Catalog provides information about creating a BART Backup Catalog.
Starting the Database Servers with WAL Archiving provides example of starting the database servers with WAL archiving.
Taking a Full Backup illustrates taking the first full backup of the database servers.
Using Point-In-Time Recovery demonstrates the point-in-time recovery operation on the remote PostgreSQL database server.

The BART Configuration File

The following code sample shows the settings used in the BART configuration file for the examples that follow:

[BART]
bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
retention_policy = 6 BACKUPS
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]
host = 127.0.0.1
port = 5444
user = enterprisedb
cluster_owner = enterprisedb
backup_name = acctg_%year-%month-%dayT%hour:%minute
archive_command = 'cp %p %a/%f'
description = "Accounting"

[MKTG]

host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
backup_name = mktg_%year-%month-%dayT%hour:%minute
remote_host = enterprisedb@192.168.2.24
description = "Marketing"

[HR]

host = 192.168.2.24
port = 5432
user = postgres
cluster_owner = postgres
backup_name = hr_%year-%month-%dayT%hour:%minute
remote_host = postgres@192.168.2.24
copy_wals_during_restore = enabled
description = "Human Resources"

Establishing SSH/SCP Passwordless Connections

This section demonstrates how passwordless SSH/SCP connections are established with the authorized public keys files.

Generating a Public Key File for the BART User Account

The BART user account is enterprisedb with a home directory of /opt/PostgresPlus/9.6AS.

To generate the public key file, as a root user, first create the .ssh subdirectory in the BART user’s home directory and assign ownership of this directory to the enterprisedb user, ensuring there are no groups or other users that can access the .ssh directory.

[root@localhost 9.6AS]# pwd
/opt/PostgresPlus/9.6AS
[root@localhost 9.6AS]# mkdir .ssh
[root@localhost 9.6AS]# chown enterprisedb .ssh
[root@localhost 9.6AS]# chgrp enterprisedb .ssh
[root@localhost 9.6AS]# chmod 700 .ssh
[root@localhost 9.6AS]# ls -la | grep ssh
drwx------ 2 enterprisedb enterprisedb 4096 Apr 23 13:02 .ssh

Generate the public key file:

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.1$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key
(/opt/PostgresPlus/9.6AS/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in
/opt/PostgresPlus/9.6AS/.ssh/id_rsa.
Your public key has been saved in
/opt/PostgresPlus/9.6AS/.ssh/id_rsa.pub.
The key fingerprint is:
de:65:34:d6:b1:d2:32:3c:b0:43:c6:a3:c0:9f:f4:64
enterprisedb@localhost.localdomain
The key's randomart image is:
+----[ RSA 2048]----+
|       .   .+   .  |
|        o .oE+ o o |
|         + * o.X + |
|          + .+ *   |
|          S   o    |
|         . . o     |
|          . .      |
|
       |
|                   |
+-------------------+

The following are the resulting files. id_rsa.pub is the public key file of BART user account enterprisedb.

-bash-4.1$ ls -l .ssh
total 8
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub

Configuring Access between Local Advanced Server and the BART Host

Even when the Advanced Server database is on the same host as the BART user account, and the Advanced Server database cluster owner is also the BART user account (enterprisedb is this case), a passwordless SSH/SCP connection must be established from the same user account to itself.

On the BART host where the public key file was just generated (as shown in Generating a Public Key File for the BART User Account), create the authorized keys file by appending the public key file to any existing authorized keys file.

Log into the BART host as the BART user account and append the public key file, id_rsa.pub onto the authorized_keys file in the same .ssh directory.

[user@localhost ~]$ su - enterprisedb
Password:
Last login: Thu Mar 23 10:27:35 EDT 2017 on pts/0
-bash-4.2$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.2$ ls -l .ssh
total 12
-rw------- 1 enterprisedb enterprisedb 1675 Mar 23 09:54 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Mar 23 09:54 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 345 Mar 23 10:05 known_hosts
-bash-4.2$ cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
-bash-4.2$ ls -l .ssh
total 16
-rw-rw-r-- 1 enterprisedb enterprisedb 416 Mar 23 10:33 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Mar 23 09:54 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Mar 23 09:54 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 345 Mar 23 10:05 known_hosts

The authorized_keys file must have file permission 600 as set by the following chmod 600 command, or the passwordless connection will fail:

-bash-4.2$ chmod 600 ~/.ssh/authorized_keys
-bash-4.2$ ls -l .ssh
total 16
-rw------- 1 enterprisedb enterprisedb 416 Mar 23 10:33 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Mar 23 09:54 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Mar 23 09:54 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 345 Mar 23 10:05 known_hosts

Test the passwordless connection. Use the ssh command to verify that you can access the same user account as you are currently logged in as (enterprisedb) without being prompted for a password:

-bash-4.2$ ssh enterprisedb@127.0.0.1
Last login: Thu Mar 23 10:27:50 2017
-bash-4.2$ exit
logout
Connection to 127.0.0.1 closed.

Configuring Access from Remote Advanced Server to BART Host

On the remote host 192.168.2.24, create the public key file for the remote database server user account, enterprisedb, for access to the BART user account, enterprisedb, on the BART host 192.168.2.22.

Create the .ssh directory for user account enterprisedb on the remote host:

[root@localhost 9.6AS]# pwd
/opt/PostgresPlus/9.6AS
[root@localhost 9.6AS]# mkdir .ssh
[root@localhost 9.6AS]# chown enterprisedb .ssh
[root@localhost 9.6AS]# chgrp enterprisedb .ssh
[root@localhost 9.6AS]# chmod 700 .ssh
[root@localhost 9.6AS]# ls -la | grep ssh
drwx------ 2 enterprisedb enterprisedb 4096 Apr 23 13:08 .ssh

Generate the public key file on the remote host for user account enterprisedb:

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key
(/opt/PostgresPlus/9.6AS/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in
/opt/PostgresPlus/9.6AS/.ssh/id_rsa.
Your public key has been saved in
/opt/PostgresPlus/9.6AS/.ssh/id_rsa.pub.
The key fingerprint is:
15:27:1e:1e:61:4b:48:66:67:0b:b2:be:fc:ea:ea:e6
enterprisedb@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]---+
|     ..=.@..    |
|      =.O O     |
|     .   *      |
|    .  .        |
|    . S         |
|   . .          |
|    o           |
|   . .          |
| +Eoo..         |
+----------------+

Copy the generated public key file, id_rsa.pub, to the BART user account, enterprisedb, on the BART host, 192.168.2.22:

-bash-4.1$ scp ~/.ssh/id_rsa.pub enterprisedb@192.168.2.22:/tmp/tmp.pub
The authenticity of host '192.168.2.22 (192.168.2.22)' can't be
established.
RSA key fingerprint is b8:a9:97:31:79:16:b8:2b:b0:60:5a:91:38:d7:68:22.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.22' (RSA) to the list of known hosts.
enterprisedb@192.168.2.22's password:
id_rsa.pub

Log into the BART host as the BART user account and append the temporary public key file, /tmp/tmp.pub onto the authorized_keys file owned by the BART user account.

-bash-4.1$ ssh enterprisedb@192.168.2.22
enterprisedb@192.168.2.22's password:
Last login: Tue Apr 21 17:03:24 2015 from 192.168.2.22
-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.1$ cat /tmp/tmp.pub >> ~/.ssh/authorized_keys
-bash-4.1$ ls -l .ssh
total 12
-rw-rw-r-- 1 enterprisedb enterprisedb 416 Apr 23 13:15 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub

The authorized_keys file must have file permission 600 as set by the following chmod 600 command, otherwise the passwordless connection fails:

-bash-4.1$ chmod 600 ~/.ssh/authorized_keys
-bash-4.1$ ls -l .ssh
total 12
-rw------- 1 enterprisedb enterprisedb 416 Apr 23 13:15 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub
-bash-4.1$ rm /tmp/tmp.pub
-bash-4.1$ exit
logout
Connection to 192.168.2.22 closed.

Test the passwordless connection. From the remote host, verify that you can log into the BART host with the BART user account without being prompted for a password:

-bash-4.1$ ssh enterprisedb@192.168.2.22
Last login: Thu Apr 23 13:14:48 2015 from 192.168.2.24
-bash-4.1$ exit
logout
Connection to 192.168.2.22 closed.

Configuring Access from the BART Host to a Remote Advanced Server

On the BART host 192.168.2.22, copy the public key file for the BART user account, enterprisedb, for access to the remote database server user account, enterprisedb, on the remote host 192.168.2.24.

The following lists the current SSH keys files in the BART user’s .ssh directory on the BART host:

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.1$ ls -l .ssh
total 12
-rw------- 1 enterprisedb enterprisedb 416 Apr 23 13:15 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub

The public key file, id_rsa.pub, for BART user account enterprisedb on the BART host that was earlier generated in Generating a Public Key File for the BART User Account, is now copied to the remote Advanced Server host on 192.168.2.24:

-bash-4.1$ scp ~/.ssh/id_rsa.pub enterprisedb@192.168.2.24:/tmp/tmp.pub
The authenticity of host '192.168.2.24 (192.168.2.24)' can't be
established.
RSA key fingerprint is 59:41:fb:0c:ae:64:3d:3f:a2:d9:90:95:cf:2c:99:f2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.24' (RSA) to the list of known
hosts.
enterprisedb@192.168.2.24's password:
id_rsa.pub

Log into the enterprisedb user account on the remote host and copy the public key file onto the authorized_keys file of the remote enterprisedb user account under its .ssh directory:

-bash-4.1$ ssh enterprisedb@192.168.2.24
enterprisedb@192.168.2.24's password:
Last login: Tue Apr 21 09:53:18 2015 from 192.168.2.22
-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.1$ ls -l .ssh
total 12
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:11 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:11 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 394 Apr 23 13:12 known_hosts
-bash-4.1$ cat /tmp/tmp.pub >> ~/.ssh/authorized_keys

Adjust the file permission on authorized_keys:

-bash-4.1$ chmod 600 ~/.ssh/authorized_keys
-bash-4.1$ ls -l .ssh
total 16
-rw------- 1 enterprisedb enterprisedb 416 Apr 23 13:26 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:11 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:11 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 394 Apr 23 13:12 known_hosts
-bash-4.1$ rm /tmp/tmp.pub
-bash-4.1$ exit
logout
Connection to 192.168.2.24 closed.

While logged into the BART host, test the passwordless connection from the BART host to the remote Advanced Server host:

-bash-4.1$ ssh enterprisedb@192.168.2.24
Last login: Thu Apr 23 13:25:53 2015 from 192.168.2.22
-bash-4.1$ exit
logout
Connection to 192.168.2.24 closed.

Configuring Access from a Remote PostgreSQL Server to a BART Host

On the remote host (192.168.2.24), create a public key file owned by the database server user account (postgres), allowing access to the BART user account (enterprisedb) on the BART host (192.168.2.22).

Create the .ssh directory for the postgres user account on the remote host:

[root@localhost 9.6]# cd /opt/PostgreSQL/9.6
[root@localhost 9.6]# mkdir .ssh
[root@localhost 9.6]# chown postgres .ssh
[root@localhost 9.6]# chgrp postgres .ssh
[root@localhost 9.6]# chmod 700 .ssh
[root@localhost 9.6]# ls -la | grep ssh
drwx------ 2 postgres postgres 4096 Apr 23 13:32 .ssh

Create and copy the generated public key file, id_rsa.pub, to the BART user account (enterprisedb), on the BART host (192.168.2.22):

[user@localhost ~]$ su - postgres
Password:
-bash-4.1$ pwd
/opt/PostgreSQL/9.6
-bash-4.1$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/opt/PostgreSQL/9.6/.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /opt/PostgreSQL/9.6/.ssh/id_rsa.
Your public key has been saved in /opt/PostgreSQL/9.6/.ssh/id_rsa.pub.
The key fingerprint is:
1f:f8:76:d6:fc:a5:1a:c5:5a:66:66:01:d0:a0:ca:ba
postgres@localhost.localdomain
The key's randomart image is:
+--[ RSA 2048]----+
|          o+.    |
|         .  ..   |
|        .     .  |
|     . . .   . . |
|      o S .   O  |
|     .   o . @   |
|    .     + = o .|
|     .   . o . o.|
|    E       ... .|
+-----------------+
-bash-4.1$ ls -l .ssh
total 8
-rw------- 1 postgres postgres 1671 Apr 23 13:36 id_rsa
-rw-r--r-- 1 postgres postgres 412 Apr 23 13:36 id_rsa.pub
-bash-4.1$ scp ~/.ssh/id_rsa.pub enterprisedb@192.168.2.22:/tmp/tmp.pub
The authenticity of host '192.168.2.22 (192.168.2.22)' can't be
established.
RSA key fingerprint is b8:a9:97:31:79:16:b8:2b:b0:60:5a:91:38:d7:68:22.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.2.22' (RSA) to the list of known
hosts.
enterprisedb@192.168.2.22's password:
id_rsa.pub

Log into the BART host as the BART user account and append the temporary public key file, /tmp/tmp.pub, onto the authorized_keys file owned by the BART user account.

-bash-4.1$ ssh enterprisedb@192.168.2.22
enterprisedb@192.168.2.22's password:
Last login: Thu Apr 23 13:19:25 2015 from 192.168.2.24
-bash-4.1$ pwd
/opt/PostgresPlus/9.6AS
-bash-4.1$ cat /tmp/tmp.pub >> ~/.ssh/authorized_keys
-bash-4.1$ ls -l .ssh
total 16
-rw------- 1 enterprisedb enterprisedb 828 Apr 23 13:40 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 394 Apr 23 13:24 known_hosts
-bash-4.1$ rm /tmp/tmp.pub
-bash-4.1$ exit
logout
Connection to 192.168.2.22 closed.

Make sure the authorized_keys file has file permission 600 as shown, or the passwordless connection will fail. Test the passwordless connection; from the remote host, while logged in as user account postgres, verify that you can log into the BART host with the BART user account without being prompted for a password:

-bash-4.1$ pwd
/opt/PostgreSQL/9.6
-bash-4.1$ ssh enterprisedb@192.168.2.22
Last login: Thu Apr 23 13:40:10 2015 from 192.168.2.24
-bash-4.1$ exit
logout
Connection to 192.168.2.22 closed.

Configuring Access from the BART Host to Remote PostgreSQL

Copy the public key file on the BART host that is owned by the BART user account (enterprisedb) to the remote database server user account (postgres), on the remote host (192.168.2.24).

The following lists the current SSH keys files in the BART user’s .ssh directory on the BART host:

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ ls -l .ssh
total 16
-rw------- 1 enterprisedb enterprisedb 828 Apr 23 13:40 authorized_keys
-rw------- 1 enterprisedb enterprisedb 1675 Apr 23 13:04 id_rsa
-rw-r--r-- 1 enterprisedb enterprisedb 416 Apr 23 13:04 id_rsa.pub
-rw-r--r-- 1 enterprisedb enterprisedb 394 Apr 23 13:24 known_hosts

The public key file, id_rsa.pub, for BART user account enterprisedb on the BART host that was earlier generated in Generating a Public Key File for the BART User Account, now resides on the remote PostgreSQL host:

-bash-4.1$ scp ~/.ssh/id_rsa.pub postgres@192.168.2.24:/tmp/tmp.pub
postgres@192.168.2.24's password:
id_rsa.pub

Log into the postgres user account on the remote host and copy the public key file onto the authorized_keys file of postgres under its .ssh directory:

-bash-4.1$ ssh postgres@192.168.2.24
postgres@192.168.2.24's password:
Last login: Mon Jan 26 18:08:36 2015 from 192.168.2.19
-bash-4.1$ pwd
/opt/PostgreSQL/9.6
-bash-4.1$ cat /tmp/tmp.pub >> ~/.ssh/authorized_keys

Adjust the file permissions on authorized_keys:

-bash-4.1$ ls -l .ssh
total 16
-rw-rw-r-- 1 postgres postgres 416 Apr 23 13:52 authorized_keys
-rw------- 1 postgres postgres 1671 Apr 23 13:36 id_rsa
-rw-r--r-- 1 postgres postgres 412 Apr 23 13:36 id_rsa.pub
-rw-r--r-- 1 postgres postgres 394 Apr 23 13:36 known_hosts
-bash-4.1$ chmod 600 ~/.ssh/authorized_keys
-bash-4.1$ ls -l .ssh
total 16
-rw------- 1 postgres postgres 416 Apr 23 13:52 authorized_keys
-rw------- 1 postgres postgres 1671 Apr 23 13:36 id_rsa
-rw-r--r-- 1 postgres postgres 412 Apr 23 13:36 id_rsa.pub
-rw-r--r-- 1 postgres postgres 394 Apr 23 13:36 known_hosts
-bash-4.1$ rm /tmp/tmp.pub
-bash-4.1$ exit
logout
Connection to 192.168.2.24 closed.

Test the passwordless connection from the BART host to the remote PostgreSQL host:

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ ssh postgres@192.168.2.24
Last login: Thu Apr 23 13:52:25 2015 from 192.168.2.22
-bash-4.1$ exit
logout
Connection to 192.168.2.24 closed.

Configuring a Replication Database User

This section demonstrates how a replication database user is established.

All database servers must use a superuser as the replication database user.

The replication database user for each database server is specified by the user parameter in the BART configuration file as shown by the following:

[ACCTG]

host = 127.0.0.1
port = 5444
user = enterprisedb <=== Replication Database User
cluster_owner = enterprisedb
backup_name = acctg_%year-%month-%dayT%hour:%minute
archive_command = 'cp %p %a/%f'
description = "Accounting"

[MKTG]
host = 192.168.2.24
port = 5444
user = repuser <=== Replication Database User
cluster_owner = enterprisedb
backup_name = mktg_%year-%month-%dayT%hour:%minute
remote_host = enterprisedb@192.168.2.24
description = "Marketing"

[HR]

host = 192.168.2.24
port = 5432
user = postgres <=== Replication Database User
cluster_owner = enterprisedb
backup_name = hr_%year-%month-%dayT%hour:%minute
remote_host = postgres@192.168.2.24
copy_wals_during_restore = enabled
description = "Human Resources"

Add entries to the .pgpass file on each server to allow the BART user account to initiate a backup without being prompted for credentials. The .pgpass file is located in /opt/PostgresPlus/9.6AS/.pgpass:

127.0.0.1:5444:*:enterprisedb:password
192.168.2.24:5444:*:repuser:password
192.168.2.24:5432:*:postgres:password

For more information about using a .pgpass file, please see the PostgreSQL documentation.

While connected to MKTG on 192.168.2.24, execute the following CREATE ROLE command to create the replication database superuser:

CREATE ROLE repuser WITH LOGIN SUPERUSER PASSWORD 'password';

Access is granted in the pg_hba.conf file for the local Advanced Server:

# TYPE     DATABASE            USER        ADDRESS         METHOD
# "local" is for Unix domain socket connections only
local        all                all                          md5
# IPv4     local connections:
host       template1         enterprisedb  127.0.0.1/32      md5
host       edb               enterprisedb  127.0.0.1/32      md5
#host      all               all           127.0.0.1/32      md5
# IPv6 local connections:
host       all               all           ::1/128           md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local     replication       enterprisedb                    md5
host       replication       enterprisedb  127.0.0.1/32      md5

Similarly, access is granted in the pg_hba.conf file for the remote Advanced Server installation:

# TYPE    DATABASE           USER        ADDRESS             METHOD
# "local" is for Unix domain socket connections only
local      all               all                               md5
# IPv4 local connections:
host       template1         repuser      192.168.2.22/32      md5
host       all               enterprisedb 127.0.0.1/32         md5
#host      all               all          127.0.0.1/32         md5
# IPv6 local connections:
host       all               all           ::1/128             md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication         enterprisedb                      md5
host     replication         repuser       192.168.2.22/32     md5

Access is also granted in the pg_hba.conf file for the remote PostgreSQL server:

# TYPE    DATABASE           USER        ADDRESS               METHOD
# "local" is for Unix domain socket connections only
local      all               all                                 md5
# IPv4 local connections:
host       template1         postgres    192.168.2.22/32         md5
host       all               all         127.0.0.1/32            md5
# IPv6 local connections:
host       all               all         ::1/128                 md5
# Allow replication connections from localhost, by a user with the
q# replication privilege.
#local     replication       postgres                            md5
host       replication       postgres    192.168.2.22/32         md5

WAL Archiving Configuration Parameters

Use the following parameters in the postgresql.conf file to enable WAL archiving. The postgresql.conf file for the local Advanced Server database (ACCTG) is set as follows:

wal_level = archive
archive_mode = on                              # allows archiving to be done
                                               # (change requires restart)
#archive_command = ''                          # command to use to archive
                                                 a logfile segment
                                               # placeholders: %p = path of
                                                file to archive
                                               # %f = file name only
max_wal_senders = 3

When the INIT subcommand is invoked, the Postgres archive_command configuration parameter in the postgresql.auto.conf file will be set based on the BART archive_command parameter located in the BART configuration file.

Note

If the Postgres archive_command is already set, invoke the INIT subcommand with the -- no-configure option to prevent the archive_command from being reset. For details, see INIT.

[BART]
bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
retention_policy = 6 BACKUPS
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

[ACCTG]
host = 127.0.0.1
port = 5444
user = enterprisedb
cluster_owner = enterprisedb
backup_name = acctg_%year-%month-%dayT%hour:%minute
archive_command = 'cp %p %a/%f'
description = "Accounting"

When the INIT subcommand is invoked, the postgresql.auto.conf file contains the following:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
archive_command = 'cp %p /opt/backup/acctg/archived_wals/%f'

The archive_command uses the cp command instead of scp since the BART backup catalog is local to this database cluster and the BART user account (the account that owns the backup catalog, enterprisedb), is the same user account running Advanced Server. The result is that there is no directory permission conflict during the archive operation.

The postgresql.conf file for the remote Advanced Server, MKTG is set as follows:

wal_level = archive
archive_mode = on                              # allows archiving to be done
                                               # (change requires restart)
archive_command = ''                           # command to use to archive a
                                                 logfile segment
                                               # placeholders: %p = path of
                                               file to archive
                                               # %f = file name only
max_wal_senders = 3

When the INIT subcommand is invoked, the Postgres archive_command configuration parameter in the postgresql.auto.conf file will be set by the default BART format of the BART archive_command parameter (since it is not explicitly set for this database server in the BART configuration file).

[BART]
bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
retention_policy = 6 BACKUPS
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
.
.
.
[MKTG]

host = 192.168.2.24
port = 5444
user = repuser
cluster_owner = enterprisedb
backup_name = mktg_%year-%month-%dayT%hour:%minute
remote_host = enterprisedb@192.168.2.24
description = "Marketing"

The default BART archive_command format is:

archive_command = 'scp %p %h:%a/%f'

The postgresql.auto.conf file contains the following after the INIT subcommand is invoked:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
archive_command = 'scp %p
enterprisedb@192.168.2.22:/opt/backup/hr/archived_wals/%f'

The archive_command uses the scp command since the BART backup catalog is remote relative to this database cluster. The BART user account, enterprisedb, is specified on the scp command since this is the user account owning the BART backup catalog where the archived WAL files are to be copied. The result is that there is no directory permission conflict during the archive operation.

The postgresql.conf file for the remote PostgreSQL server (HR) is set as follows:

wal_level = archive
archive_mode = on                              # allows archiving to be done
                                               # (change requires restart)
#archive_command = ''                          # command to use to archive a
                                                 logfile segment
                                               # placeholders: %p = path of
                                                 file to archive
                                               # %f = file name only
max_wal_senders = 3

[BART]

bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
retention_policy = 6 BACKUPS
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log
.
.
.
[HR]

host = 192.168.2.24
port = 5432
user = postgres
cluster_owner = postgres
backup_name = hr_%year-%month-%dayT%hour:%minute
remote_host = postgres@192.168.2.24
copy_wals_during_restore = enabled
description = "Human Resources"

The default BART archive_command format is:

archive_command = 'scp %p %h:%a/%f'

The postgresql.auto.conf file contains the following after the INIT subcommand is invoked:

# Do not edit this file manually!
# It will be overwritten by ALTER SYSTEM command.
archive_command = 'scp %p
enterprisedb@192.168.2.22:/opt/backup/hr/archived_wals/%f'

Creating the BART Backup Catalog (backup_path)

Create the directory specified by the backup_path configuration parameter.

[BART]

bart_host= enterprisedb@192.168.2.22
backup_path = /opt/backup
pg_basebackup_path = /usr/edb/as11/bin/pg_basebackup
retention_policy = 6 BACKUPS
logfile = /tmp/bart.log
scanner_logfile = /tmp/bart_scanner.log

Ensure that the directory is owned by the BART user account:

[root@localhost opt]# pwd
/opt
[root@localhost opt]# mkdir backup
[root@localhost opt]# chown enterprisedb backup
[root@localhost opt]# chgrp enterprisedb backup
[root@localhost opt]# chmod 700 backup
[root@localhost opt]# ls -l | grep backup
drwx------ 2 enterprisedb enterprisedb 4096 Apr 23 15:36 backup

Use the BART INIT subcommand to complete the directory structure and set the Postgres archive_command configuration parameter.

Before invoking any BART subcommands, set up a profile under the BART user account’s home directory to set the LD_LIBRARY_PATH and PATH environment variables. For more information regarding setting this variable, see the EDB Backup and Recovery Installation and Upgrade Guide available at the EDB website.

The -o option is specified with the INIT subcommand to force the setting of the Postgres archive_command configuration parameter when archive_mode is off or if the Postgres archive_command parameter is already set and needs to be overridden.

[user@localhost ~]$ su - enterprisedb
Password:
-bash-4.1$ bart INIT -o
INFO: setting archive_command for server 'acctg'
WARNING: archive_command is set. server restart is required
INFO: setting archive_command for server 'hr'
WARNING: archive_command is set. server restart is required
INFO: setting archive_command for server 'mktg'
WARNING: archive_command is set. server restart is required

The BART SHOW-SERVERS subcommand displays the following:

-bash-4.1$ bart SHOW-SERVERS
SERVER NAME :               acctg
BACKUP FRIENDLY NAME:       acctg_%year-%month-%dayT%hour:%minute
HOST NAME :                 127.0.0.1
USER NAME :                 enterprisedb
PORT :                      5444
REMOTE HOST :
RETENTION POLICY :          6 Backups
DISK UTILIZATION :          0.00 bytes
NUMBER OF ARCHIVES :        0
ARCHIVE PATH :              /opt/backup/acctg/archived_wals
ARCHIVE COMMAND :          (disabled)
XLOG METHOD :              fetch
WAL COMPRESSION :          disabled
TABLESPACE PATH(s) :
INCREMENTAL BACKUP :       DISABLED
DESCRIPTION :              "Accounting"
SERVER NAME :              hr
BACKUP FRIENDLY NAME:      hr_%year-%month-%dayT%hour:%minute
HOST NAME :                192.168.2.24
USER NAME :                postgres
PORT :                     5432
REMOTE HOST :              postgres@192.168.2.24
RETENTION POLICY :         6 Backups
DISK UTILIZATION :         0.00 bytes
NUMBER OF ARCHIVES :       0
ARCHIVE PATH :             /opt/backup/hr/archived_wals
ARCHIVE COMMAND :          (disabled)
XLOG METHOD :              fetch
WAL COMPRESSION :          disabled
TABLESPACE PATH(s) :
INCREMENTAL BACKUP :       DISABLED
DESCRIPTION :              "Human Resources"
SERVER NAME :              mktg
BACKUP FRIENDLY NAME:      mktg_%year-%month-%dayT%hour:%minute
HOST NAME :                192.168.2.24
USER NAME :                repuser
PORT :                     5444
REMOTE HOST :              enterprisedb@192.168.2.24
RETENTION POLICY :         6 Backups
DISK UTILIZATION :         0.00 bytes
NUMBER OF ARCHIVES :       0
ARCHIVE PATH :             /opt/backup/mktg/archived_wals
ARCHIVE COMMAND :          (disabled)
XLOG METHOD :              fetch
WAL COMPRESSION :          disabled
TABLESPACE PATH(s) :
INCREMENTAL BACKUP :       DISABLED
DESCRIPTION :              "Marketing"
-bash-4.1$ cd /opt/backup
-bash-4.1$ pwd
/opt/backup
-bash-4.1$ ls -l
total 12
drwxrwxr-x 3 enterprisedb enterprisedb 4096 Mar 29 13:16 acctg
drwxrwxr-x 3 enterprisedb enterprisedb 4096 Mar 29 13:16 hr
drwxrwxr-x 3 enterprisedb enterprisedb 4096 Mar 29 13:16 mktg
-bash-4.1$ ls -l acctg
total 4
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:16 archived_wals
-bash-4.1$ ls -l hr
total 4
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:16 archived_wals
-bash-4.1$ ls -l mktg
total 4
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:16 archived_wals

The ARCHIVE PATH field displays the full directory path to where the WAL files are copied. This directory path must match the directory path specified in the Postgres archive_command parameter of the postgresql.conf file or the postgresql.auto.conf file of each database server.

Starting the Database Servers with WAL Archiving

After the BART backup catalog directory structure has been configured, start the archiving of WAL files from the database servers by restarting each database server.

On BART host 192.168.2.22:

[root@localhost data]# service ppas-9.6 restart

On remote host 192.168.2.24:

[root@localhost data]# service ppas-9.6 restart

[root@localhost data]# service postgresql-9.6 restart

In the BART backup catalog, verify that the WAL files are archiving.

Archived WAL files may not appear very frequently depending upon how often WAL archiving is set to switch to a new segment file with the archive_timeout parameter in your database server configuration settings.

Verify that there are no archiving-related errors in the database server log files.

Taking a Full Backup

The following code sample shows the first full backup of the database servers.

-bash-4.1$ bart BACKUP -s acctg -z
INFO: creating backup for server 'acctg'
INFO: backup identifier: '1490809695281'
60776/60776 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 37f3defb98ca88dcf05079815555dfc2 of base.tar.gz
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1490809695281
BACKUP NAME: acctg_2017-03-29T13:48
BACKUP PARENT: none
BACKUP LOCATION: /opt/backup/acctg/1490809695281
BACKUP SIZE: 6.10 MB
BACKUP FORMAT: tar.gz
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
37f3defb98ca88dcf05079815555dfc2 base.tar.gz

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000004
STOP WAL LOCATION: 000000010000000000000004
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2017-03-29 13:48:15 EDT
STOP TIME: 2017-03-29 13:48:17 EDT
TOTAL DURATION: 2 sec(s)

-bash-4.1$ bart BACKUP -s mktg -z
INFO: creating backup for server 'mktg'
INFO: backup identifier: '1490809751193'
61016/61016 kB (100%), 1/1 tablespace

INFO: backup completed successfully
INFO: backup checksum: 8b010e130a105e76d01346bb56dfcf14 of base.tar.gz
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1490809751193
BACKUP NAME: mktg_2017-03-29T13:49
BACKUP PARENT: none
BACKUP LOCATION: /opt/backup/mktg/1490809751193
BACKUP SIZE: 6.13 MB
BACKUP FORMAT: tar.gz
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
8b010e130a105e76d01346bb56dfcf14 base.tar.gz

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000100000085
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2017-03-29 13:49:11 EDT
STOP TIME: 2017-03-29 13:49:14 EDT
TOTAL DURATION: 3 sec(s)

-bash-4.1$ bart BACKUP -s hr -z
INFO: creating backup for server 'hr'
INFO: backup identifier: '1490809824946'
38991/38991 kB (100%), 1/1 tablespace
INFO: backup completed successfully
INFO: backup checksum: 277e8a1a80ba3474f541eb316a417c9a of base.tar.gz
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1490809824946
BACKUP NAME: hr_2017-03-29T13:50
BACKUP PARENT: none
BACKUP LOCATION: /opt/backup/hr/1490809824946
BACKUP SIZE: 2.59 MB
BACKUP FORMAT: tar.gz
BACKUP TIMEZONE: US/Eastern
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
ChkSum File
277e8a1a80ba3474f541eb316a417c9a base.tar.gz

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000002
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2017-03-29 13:50:25 EDT
STOP TIME: 2017-03-29 13:50:26 EDT
TOTAL DURATION: 1 sec(s)

The following code sample shows the backup directories created for each backup of each database server. The backup ID is used as the backup directory name.

-bash-4.1$ cd /opt/backup
-bash-4.1$ ls -l
total 12
drwxrwxr-x 4 enterprisedb enterprisedb 4096 Mar 29 13:48 acctg
drwxrwxr-x 4 enterprisedb enterprisedb 4096 Mar 29 13:50 hr
drwxrwxr-x 4 enterprisedb enterprisedb 4096 Mar 29 13:49 mktg
-bash-4.1$ ls -l acctg
total 8
drwx------ 2 enterprisedb enterprisedb 4096 Mar 29 13:48 1490809695281
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:48 archived_wals
-bash-4.1$ ls -l hr
total 8
drwx------ 2 enterprisedb enterprisedb 4096 Mar 29 13:50 1490809824946
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:50 archived_wals
-bash-4.1$ ls -l mktg
total 8
drwx------ 2 enterprisedb enterprisedb 4096 Mar 29 13:49 1490809751193
drwxrwxr-x 2 enterprisedb enterprisedb 4096 Mar 29 13:49 archived_wals

Using Point-In-Time Recovery

This section demonstrates using the point-in-time recovery operation on the remote PostgreSQL database server.

The following tables were created about two minutes apart with WAL archiving enabled:

postgres=# \dt

             List of relations
Schema  |      Name      |  Type   | Owner

--------+----------------+---------+---------
public  | hr_rmt_t1_1356 | table   | postgres
public  | hr_rmt_t1_1358 | table   | postgres
public  | hr_rmt_t1_1400 | table   | postgres
public  | hr_rmt_t1_1402 | table   | postgres
public  | hr_rmt_t1_1404 | table   | postgres
public  | hr_rmt_t1_1406 | table   | postgres
(6 rows)

In the table name hr_rmt_t<n>_<hhmi>, n represents the active timeline. <hhmi> is the approximate time the table was created. For example, hr_rmt_t1_1356 was created at approximately 1:56 PM while timeline #1 is active.

The PostgreSQL database server was then stopped. WAL files that have been created, but not yet archived must be identified, and then saved. The following archived WAL files are in the BART backup catalog:

-bash-4.1$ ls -l hr/archived_wals
total 49156
-rw------- 1 enterprisedb enterprisedb 16777216 Mar 29 13:50
000000010000000000000001
-rw------- 1 enterprisedb enterprisedb 16777216 Mar 29 13:50
000000010000000000000002
-rw------- 1 enterprisedb enterprisedb 302 Mar 29 13:50
000000010000000000000002.00000028.backup
-rw------- 1 enterprisedb enterprisedb 16777216 Mar 29 14:07
000000010000000000000003

The following sample lists the current PostgreSQL server WAL files. The unarchived WAL files are marked with two stars (**).

-bash-4.1$ cd /opt/PostgreSQL/9.6/data/pg_xlog
-bash-4.1$ pwd
/opt/PostgreSQL/9.6/data/pg_xlog
-bash-4.1$ ls -l
total 49160
-rw------- 1 postgres postgres 302 Mar 29 13:50
000000010000000000000002.00000028.backup
-rw------- 1 postgres postgres 16777216 Mar 29 14:07
000000010000000000000003
-rw------- 1 postgres postgres 16777216 Mar 29 14:07
**000000010000000000000004**
-rw------- 1 postgres postgres 16777216 Mar 29 13:50
**000000010000000000000005**
drwx------ 2 postgres postgres 4096 Mar 29 14:07 archive_status

Copies of the unarchived WAL files are saved to a temporary location:

-bash-4.1$ mkdir /tmp/unarchived_pg96_wals
-bash-4.1$ pwd
/opt/PostgreSQL/9.6/data/pg_xlog
bash-4.1$ cp -p 000000010000000000000004 /tmp/unarchived_pg96_wals
bash-4.1$ cp -p 000000010000000000000005 /tmp/unarchived_pg96_wals
bash-4.1$ ls -l /tmp/unarchived_pg96_wals
total 32768
-rw------- 1 postgres postgres 16777216 Mar 29 14:07 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 29 13:50 000000010000000000000005

On the remote host, a directory is created to which the PostgreSQL database cluster is to be restored. This restore path is named /opt/restore_pg96 and is owned by user account postgres.

[user@localhost ~]$ su root
Password:
[root@localhost user]# cd /opt
[root@localhost opt]# mkdir restore_pg96
[root@localhost opt]# chown postgres restore_pg96
[root@localhost opt]# chgrp postgres restore_pg96
[root@localhost opt]# chmod 700 restore_pg96
[root@localhost opt]# ls -l
total 16
drwxr-xr-x 4 root daemon 4096 Mar 29 12:10 PostgresPlus
drwxr-xr-x 3 root daemon 4096 Mar 29 12:25 PostgreSQL
drwx------ 2 postgres postgres 4096 Mar 29 14:15 restore_pg96
drwxr-xr-x. 2 root root 4096 Nov 22 2013 rh

In the BART configuration file, the remote user and remote host IP address, postgres@192.168.2.24, have been set with the remote_host parameter. If not given in the BART configuration file, this information must then be specified by the --remote-host option when giving the RESTORE subcommand (for example, bart RESTORE --remote-host postgres@192.168.2.24 …).

[HR]

host = 192.168.2.24
port = 5432
user = postgres
cluster_owner = postgres
backup_name = hr_%year-%month-%dayT%hour:%minute
remote_host = postgres@192.168.2.24
copy_wals_during_restore = enabled
description = "Human Resources"

Use the SHOW-BACKUPS subcommand to identify the backup to use with the RESTORE subcommand.

SERVER NAME          BACKUP ID         BACKUP NAME             BACKUP PARENT
BACKUP TIME
BACKUP SIZE          WAL(s) SIZE       WAL FILES               STATUS
acctg                1490809695281     acctg_2017-03-29T13:48  none
2017-03-29 13:48:17 EDT
6.10 MB              32.00 MB          2                       active
hr                   1490809824946     hr_2017-03-29T13:50     none
2017-03-29 13:50:26 EDT
2.59 MB              32.00 MB          2                       active
mktg                 1490809751193     mktg_2017-03-29T13:49   none
2017-03-29 13:49:14 EDT
6.13 MB              64.00 MB          4                       active

The -t option with the SHOW-BACKUPS subcommand displays additional backup information:

-bash-4.1$ bart SHOW-BACKUPS -s hr -i 1490809824946 -t
SERVER NAME    : hr
BACKUP ID      : 1490809824946
BACKUP NAME    : hr_2017-03-29T13:50
BACKUP PARENT  : none
BACKUP STATUS  : active
BACKUP TIME    : 2017-03-29 13:50:26 EDT
BACKUP SIZE    : 2.59 MB
WAL(S) SIZE    : 32.00 MB
NO. OF WALS    : 2
FIRST WAL FILE : 000000010000000000000002
CREATION TIME  : 2017-03-29 13:50:31 EDT
LAST WAL FILE  : 000000010000000000000003
CREATION TIME  : 2017-03-29 14:07:35 EDT

A recovery is made using timeline 1 to 2017-03-29 14:01:00.

-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 example shows the restored backup files in the restore path directory, /opt/restore_pg96:

-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

Copy the saved, unarchived WAL files to the restore path pg_xlog subdirectory (/opt/restore_pg96/pg_xlog):

-bash-4.1$ pwd
/opt/restore_pg96/pg_xlog
-bash-4.1$ ls -l
total 16388
-rw------- 1 postgres postgres 16777216 Mar 29 13:50
000000010000000000000002
drwx------ 2 postgres postgres 4096 Mar 29 14:27 archive_status
-bash-4.1$ ls -l /tmp/unarchived_pg96_wals
total 32768
-rw------- 1 postgres postgres 16777216 Mar 29 14:07
000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 29 13:50
000000010000000000000005
-bash-4.1$ cp -p /tmp/unarchived_pg96_wals/* .
-bash-4.1$ ls -l
total 49156
-rw------- 1 postgres postgres 16777216 Mar 29 13:50
000000010000000000000002
-rw------- 1 postgres postgres 16777216 Mar 29 14:07
000000010000000000000004
-rw------- 1 postgres postgres 16777216 Mar 29 13:50
000000010000000000000005
drwx------ 2 postgres postgres 4096 Mar 29 14:27 archive_status

Inspect the /opt/restore_pg96/postgresql.auto.conf file to verify that it contains the correct recovery settings:

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

Note that the command restores from the archived_wals subdirectory of /opt/restore_pg96 since the copy_wals_during_restore parameter in the BART configuration file is set to enabled for database server hr.

Start the database server to initiate the point-in-time recovery operation:

[user@localhost ~]$ su postgres
Password:
bash-4.1$ cd /opt/restore_pg96
bash-4.1$ /opt/PostgreSQL/9.6/bin/pg_ctl start -D /opt/restore_pg96 -l
/opt/restore_pg96/pg_log/logfile
server starting

Inspect the database server log file to ensure the operation did not result in any errors:

2017-03-29 14:33:23 EDT LOG: database system was interrupted; last known
up at 2017-03-29 13:50:25 EDT
2017-03-29 14:33:23 EDT LOG: starting point-in-time recovery to
2017-03-29 14:01:00-04
2017-03-29 14:33:23 EDT LOG: restored log file
"000000010000000000000002" from archive
2017-03-29 14:33:23 EDT LOG: redo starts at 0/2000098
2017-03-29 14:33:23 EDT LOG: consistent recovery state reached at
0/20000C0
2017-03-29 14:33:23 EDT LOG: restored log file
"000000010000000000000003" from archive
2017-03-29 14:33:23 EDT LOG: recovery stopping before commit of
transaction 1762, time 2017-03-29 14:02:28.100072-04
2017-03-29 14:33:23 EDT LOG: redo done at 0/303F390
2017-03-29 14:33:23 EDT LOG: last completed transaction was at log time
2017-03-29 14:00:43.351333-04
cp: cannot stat `archived_wals/00000002.history': No such file or
directory
2017-03-29 14:33:23 EDT LOG: selected new timeline ID: 2
cp: cannot stat `archived_wals/00000001.history': No such file or
directory
2017-03-29 14:33:23 EDT LOG: archive recovery complete
2017-03-29 14:33:23 EDT LOG: MultiXact member wraparound protections are
now enabled
2017-03-29 14:33:23 EDT LOG: database system is ready to accept
connections
2017-03-29 14:33:23 EDT LOG: autovacuum launcher started

The tables that exist in the recovered database cluster are:

postgres=# \dt
          List of relations
Schema  | Name           | Type  | Owner
--------+----------------+-------+----------
public  | hr_rmt_t1_1356 | table | postgres
public  | hr_rmt_t1_1358 | table | postgres
public  | hr_rmt_t1_1400 | table | postgres
(3 rows)

Since recovery was up to and including 2017-03-29 14:01:00, the following tables created after 14:01 are not present:

public  | hr_rmt_t1_1402  | table  | postgres
public  | hr_rmt_t1_1404  | table  | postgres
public  | hr_rmt_t1_1406  | table  | postgres

The BART RESTORE operation stops WAL archiving by adding an archive_mode = off parameter at the very end of the postgresql.conf file. This last parameter in the file overrides any other previous setting of the same parameter in the file. Delete the last setting and restart the database server to start WAL archiving.

# Add settings for extensions here
archive_mode = off

← Prev

Additional Examples