Infrastructure at your Service

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

postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)

postgres=# show port;
 port
------
 5432
(1 row)

postgres=#

pgserver1 : cluster2

postgres=# show data_directory ;
   data_directory
--------------------
 /u90/pgdata2/pg101
(1 row)

postgres=# show port;
 port
------
 5433
(1 row)

postgres=#

pgserver2 : cluster1

postgres=# show data_directory ;
  data_directory
-------------------
 /u90/pgdata/pg101
(1 row)

postgres=# show port;
 port
------
 5432
(1 row)

postgres=#

pgserver2 : cluster2

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.

[[email protected] ~]# 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

[[email protected] ~]$ 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]-----+
[[email protected] ~]$

Do the same on pgserver1 for user postgres

[[email protected] ~]$ 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=.  |
+-----------------+
[[email protected] ~]$

And on pgserver2 for user postgres

[[email protected] ~]$ 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.+          |
|[email protected]=o= .         |
|=BB+++o o        |
|oB +*+o. .       |
+----[SHA256]-----+
[[email protected] ~]$

Now let’s exchange keys between servers.

[[email protected] .ssh]$ ssh-copy-id [email protected]
[[email protected] .ssh]$ ssh-copy-id [email protected]
[[email protected] .ssh]$ ssh-copy-id [email protected]
[[email protected] .ssh]$ ssh-copy-id [email protected]

And then let’s test connection

[[email protected]vertools ~]$ ssh [email protected] date
Tue Feb 20 11:42:06 CET 2018
[[email protected] ~]$ ssh [email protected] date
Tue Feb 20 11:42:10 CET 2018
 [[email protected] .ssh]$ ssh [email protected] date
Tue Feb 20 11:42:54 CET 2018
[[email protected] .ssh]$ ssh [email protected] 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

[[email protected] ~]# 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 [[email protected] etc]#

pgserver1

[[email protected] ~]$ 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
[[email protected] ~]$

pgserver2

[[email protected] 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
[[email protected] postgres]#

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

[[email protected] ~]$  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

[[email protected] ~]$  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
[[email protected] ~]$

pgserver2: 2 stanzas pgserver2pgdata and pgserver2pgdata2

[[email protected] 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

[[email protected] 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
[[email protected] pgserver1pgdata]$

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

pgserver1pgdata

[[email protected] pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver1 backup

pgserver1pgdata2

[[email protected] pgserver1pgdata]$ pgbackrest --stanza=pgserver1pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver1 backup

pgserver2pgdata

[[email protected] pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata --log-level-console=detail --db-port=5432 --db-host=pgserver2 backup

pgserver2pgdata2

[[email protected] pgserver1pgdata]$ pgbackrest --stanza=pgserver2pgdata2 --log-level-console=detail --db-port=5433 --db-host=pgserver2 backup

An example of getting info about backup

[[email protected] 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
[[email protected] pgserver1pgdata]$

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

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mouhamadou Diaw
Mouhamadou Diaw

Consultant