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
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: 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: 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.