By Mouhamadou Diaw

In previous blogs (blog1 and blog2) we saw a configuration of PgBackRest where the tool is installed in the same server that also hosts the PostgreSQL cluster. This configuration is fine if we have a single database server. But in the case that we have many database servers, it is more suitable to have a dedicated server for backups. This will also separate the backups and WAL archive from databases server
In this article we will see how to configure PgBackRest in an environment with 2 databases servers.
We present below the configuration we will use. We suppose that PgBackRest is already installed on both servers.
pgservertools: dedicated backup hosts
pgserver1: database server
pgserver2: database server
schema
As we can see we have two clusters running on each database server (pgserver1 and pgserver2). Note that we can have as many clusters we want on each server.
pgserver1 : cluster1

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)
postgres=# show port;
 port
------
 5432
(1 row)
postgres=#

pgserver1 : cluster2

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# show data_directory ;
   data_directory
--------------------
 /u90/pgdata2/pg101
(1 row)
postgres=# show port;
 port
------
 5433
(1 row)
postgres=#

pgserver2 : cluster1

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)
postgres=# show port;
 port
------
 5432
(1 row)
postgres=#

pgserver2 : cluster2

1
2
3
4
5
6
7
8
9
10
11
12
13
postgres=# show data_directory ;
   data_directory
--------------------
 /u90/pgdata2/pg101
(1 row)
postgres=# show port;
 port
------
 5433
(1 row)
postgres=#

Now let’s create on pgservertools a dedicated user who will own the repository. It is not recommended to use user postgres. We will use backupowner as user.

1
[root@pgservertools ~]# useradd -m backupowner

As PgBackRest requires communication between the hosts without password, we have to configure ssh keys between user backupowner (on pgservertools) and users postgres (on pgserver1 and pgserver2).
On pgservertools, let’s generate keys for user backupowner

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[backupowner@pgservertools ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/backupowner/.ssh/id_rsa): y
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in y.
Your public key has been saved in y.pub.
The key fingerprint is:
SHA256:drIaCe1aMZSPOmQkNdYfn5WLm/iE4sJS2YKGj6EF38M [email protected]
The key's randomart image is:
+---[RSA 2048]----+
|    +.       .   |
|   o ....   o    |
|  . . o. o + .   |
|.  o o o. + .    |
| + ++o= So.o     |
|o =oE+o=o++      |
|.* oo+=..o       |
|o o o+.o  .      |
|   ....          |
+----[SHA256]-----+
[backupowner@pgservertools ~]$

Do the same on pgserver1 for user postgres

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[postgres@pgserver1 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Created directory '/home/postgres/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
15:cf:78:47:ef:e5:ab:29:b7:25:59:03:de:de:88:be [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
|          .   .  |
|           = . . |
|          o +.. o|
|         . ...oo.|
|        S    . +o|
|             .+.+|
|            .o.+.|
|           o .=  |
|            E=.  |
+-----------------+
[postgres@pgserver1 ~]$

And on pgserver2 for user postgres

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[postgres@pgserver2 ~]$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa):
Created directory '/home/postgres/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:ZKov+TdohBLvaO/pSYIl+Tk6iWfUMx1Lqixb8ByaZzI [email protected]
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|                 |
|        o        |
| ..   o+         |
|+ o+ =.oS        |
| @ooB.+          |
|E+@=o= .         |
|=BB+++o o        |
|oB +*+o. .       |
+----[SHA256]-----+
[postgres@pgserver2 ~]$

Now let’s exchange keys between servers.

1
2
3
4
[backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver1
[backupowner@pgservertools .ssh]$ ssh-copy-id postgres@pgserver2
[postgres@pgserver1 .ssh]$ ssh-copy-id backupowner@pgservertools
[postgres@pgserver2 .ssh]$ ssh-copy-id backupowner@pgservertools

And then let’s test connection

1
2
3
4
5
6
7
8
[backupowner@pgservertools ~]$ ssh postgres@pgserver1 date
Tue Feb 20 11:42:06 CET 2018
[backupowner@pgservertools ~]$ ssh postgres@pgserver2 date
Tue Feb 20 11:42:10 CET 2018
 [postgres@pgserver1 .ssh]$ ssh backupowner@pgservertools date
Tue Feb 20 11:42:54 CET 2018
[postgres@pgserver2 .ssh]$ ssh backupowner@pgservertools date
Tue Feb 20 11:43:23 CET 2018

Ok now that everything is fine for trusted connections, let’s configure the pgbackrest.conf files. We present below contents of our files on the 3 servers. We can notice that encryption is used (see previous blogs)

pgservertools

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[root@pgservertools ~]# cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-host=pgserver1
db1-path=/u90/pgdata/pg101
db1-user=postgres
[pgserver1pgdata2]
db1-host=pgserver1
db1-path=/u90/pgdata2/pg101
db1-user=postgres
db1-port=5433
[pgserver2pgdata]
db1-host=pgserver2
db1-path=/u90/pgdata/pg101
db1-user=postgres
[pgserver2pgdata2]
db1-host=pgserver2
db1-path=/u90/pgdata2/pg101
db1-user=postgres
db1-port=5433
[global]
repo-path=/u90/backup
retention-full=2
repo-cipher-pass=dkN28km/CltmsbzkDdKahmwXctr0GJd/9F8tegBXBWASULhVatNXauMMKWUslax1
repo-cipher-type=aes-256-cbc [root@pgservertools etc]#

pgserver1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[postgres@pgserver1 ~]$ cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-path=/u90/pgdata/pg101
db1-socket-path=/tmp
[pgserver1pgdata2]
db1-path=/u90/pgdata2/pg101
db1-port=5433
db1-socket-path=/tmp
[global]
backup-host=pgservertools
backup-user=backupowner
log-level-file=detail
[postgres@pgserver1 ~]$

pgserver2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@pgserver2 postgres]# cat /etc/pgbackrest.conf
[pgserver1pgdata]
db1-path=/u90/pgdata/pg101
db1-socket-path=/tmp
[pgserver1pgdata2]
db1-path=/u90/pgdata2/pg101
db1-port=5433
db1-socket-path=/tmp
[global]
backup-host=pgservertools
backup-user=backupowner
log-level-file=detail
[root@pgserver2 postgres]#

The next step is to create the stanzas
pgserver1: 2 stanzas pgserver1pgdata and pgserver1pgdata2

1
2
3
4
5
6
7
8
[backupowner@pgservertools ~]$  pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 stanza-create
2018-02-21 15:21:42.815 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata
2018-02-21 15:21:46.881 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools ~]$  pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 stanza-create
2018-02-21 15:23:39.116 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver1 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver1pgdata2
2018-02-21 15:23:41.360 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools ~]$

pgserver2: 2 stanzas pgserver2pgdata and pgserver2pgdata2

1
2
3
4
5
6
7
8
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 stanza-create
2018-02-27 13:22:47.710 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata/pg101 --db1-port=5432 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata
2018-02-27 13:22:49.624 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 stanza-create
2018-02-27 13:23:01.323 P00   INFO: stanza-create command begin 1.28: --db1-host=pgserver2 --db1-path=/u90/pgdata2/pg101 --db1-port=5433 --db1-user=postgres --log-level-console=detail --repo-cipher-pass= --repo-cipher-type=aes-256-cbc --repo-path=/u90/backup --stanza=pgserver2pgdata2
2018-02-27 13:23:03.233 P00   INFO: stanza-create command end: completed successfully
[backupowner@pgservertools pgserver1pgdata]$

And now we can do a backup of any our cluster using the corresponding stanza

pgserver1pgdata

1
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 backup

pgserver1pgdata2

1
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 backup

pgserver2pgdata

1
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 backup

pgserver2pgdata2

1
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 backup

An example of getting info about backup

1
2
3
4
5
6
7
8
9
10
11
12
13
[backupowner@pgservertools pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail  info
stanza: pgserver1pgdata
    status: ok
    db (current)
        wal archive min/max (10-1): 000000010000000000000018 / 000000010000000000000018
        full backup: 20180221-155755F
            timestamp start/stop: 2018-02-27 11:45:51 / 2018-02-27 11:46:18
            wal start/stop: 000000010000000000000018 / 000000010000000000000018
            database size: 30MB, backup size: 30MB
            repository size: 3.5MB, repository backup size: 3.5MB
[backupowner@pgservertools pgserver1pgdata]$

Conclusion
In this blog we have seen how PgbackRest can be be used in an environment with multiple database servers.

Post Views: 1,920
Thumbnail [60x60]
by
Oracle Team