{"id":1660,"date":"2024-01-10T00:20:46","date_gmt":"2024-01-09T21:20:46","guid":{"rendered":"http:\/\/pgdataeraold.local\/?p=1660"},"modified":"2024-01-10T00:20:46","modified_gmt":"2024-01-09T21:20:46","slug":"pgbackrest-backup-serverdedicated-repository-host","status":"publish","type":"post","link":"https:\/\/pgdataera.com\/en\/pgbackrest-backup-serverdedicated-repository-host\/","title":{"rendered":"PGBackRest Backup Server(Dedicated Repository Host)"},"content":{"rendered":"
\n
\"\"<\/figure><\/div>\n\n\n
\n\n\n\n

With a dedicated pgbackrest backup server, backup management can be managed
from a single point. Since the backup and archived WAL files will be on another server,
it will be not be affected by issues on the database server.
Planning should be done by considering database sizes, network speed, processor
capacity, time periods when databases are busy.<\/p>\n\n\n\n

Kurulum<\/h3>\n\n\n\n
ROLE<\/td>HOSTNAME<\/td>IP<\/td>DB<\/td><\/tr>
PGBackRest Backup Server<\/td>pgbr<\/td>192.168.56.60<\/td>–<\/td><\/tr>
Database Server<\/td>srvr1<\/td>192.168.56.61<\/td>mxdb<\/td><\/tr>
Database Server<\/td>srvr2<\/td>192.168.56.62<\/td>dmdb<\/td><\/tr><\/tbody><\/table>
The pgbr machine will act as a dedicated backup server and will only perform backup
operations. The database will not run on it.
srv1 is the database server having active mxdb.
srv2 is another database server having active dmdb.<\/figcaption><\/figure>\n\n\n\n

In all 3 servers:
The following lines are entered in the \/etc\/hosts<\/strong> file.<\/p>\n\n\n\n

192.168.56.60       pgbr\n192.168.56.61       srv1\n192.168.56.62       srv2<\/pre>\n\n\n\n
<\/p>\n\n\n\n
    \n
  • The following lines are entered in the \/etc\/sudoers file.<\/li>\n<\/ul>\n\n\n\n
    postgres          ALL=(ALL)            NOPASSWD:ALL<\/pre>\n\n\n\n
    Note: the sudo privilege may not comply with every company\u2019s IT security policy. It can
    be undone after installations.<\/p>\n\n\n\n
    STEP 1<\/mark><\/strong><\/p>\n\n\n\n
    In the pgbr <\/mark>machine<\/p>\n\n\n\n
    root@pgbr:~# apt install postgresql-client libxml2\nroot@pgbr:~# useradd postgres\nroot@pgbr:~# mkdir \/home\/postgres\nroot@pgbr:~# chown -R postgres:postgres \/home\/postgres\nroot@pgbr:~# su - postgres\npostgres@pgbr:~$ pwd\n\/home\/postgres\npostgres@pgbr:~$ cp \/etc\/skel\/.* .<\/pre>\n\n\n\n
    pgbackrest is installed in this server by following the pgbackrest installation steps
    described in the previous pages. Since postgresql server is not installed on the pgbr
    machine, the postgresql client package is also installed. Since we will perform
    the backup operations with the postgres user, a postgres user is created on the pgbr
    machine.<\/p>\n\n\n\n
    STEP 2<\/mark><\/strong><\/p>\n\n\n\n
    # With postgres user on all 3 machines\n# All default entries are selected. Nothing is entered.\nssh-keygen -t rsa\n# In the pgbr machine\nssh-copy-id srv1\nssh-copy-id srv2\n# In machine srv1\nssh-copy-id pgbr\n# In the srv2 machine<\/pre>\n\n\n\n
    Password less ssh using postgres user between pgbr and database servers is
    established.
    After the access settings are made, the connection should be tested one by one.<\/p>\n\n\n\n
    STEP 3<\/strong><\/mark><\/p>\n\n\n\n
    # In srv1 and srv2 machines\nsudo mkdir -p -m 770 \/var\/log\/pgbackrest\nsudo chown postgres:postgres \/var\/log\/pgbackrest\nsudo mkdir -p \/etc\/pgbackrest\nsudo touch \/etc\/pgbackrest\/pgbackrest.conf\nsudo chmod 640 \/etc\/pgbackrest\/pgbackrest.conf\nsudo chown postgres:postgres \/etc\/pgbackrest\/pgbackrest.conf\n\n# In the pgbr machine\nsudo scp \/usr\/bin\/pgbackrest srv1:\/usr\/bin\/\nsudo scp \/usr\/bin\/pgbackrest srv2:\/usr\/bin\/<\/pre>\n\n\n\n
    It should be checked by running pgbackrest version with postgres user on
    srv1 and srv2 database servers.
    Note: Dedicated pgbackrest server and database servers must have the same version
    of pgbackrest installed.<\/p>\n\n\n\n
    STEP 4 <\/mark><\/strong>(add disk onpgbr machine)<\/p>\n\n\n\n
    postgres@pgbr:~$ sudo pvcreate \/dev\/sdb\npostgres@pgbr:~$ sudo pvdisplay\npostgres@pgbr:~$ sudo vgcreate ra \/dev\/sdb\n\n# To use all disk space\npostgres@pgbr:~$ sudo lvcreate -l 100%FREE -n RA-LV ra\npostgres@pgbr:~$ sudo lvdisplay \/dev\/ra\/RA-LV\npostgres@pgbr:~$ sudo mkfs.xfs \/dev\/ra\/RA-LV\npostgres@pgbr:~$ sudo mount \/dev\/ra\/RA-LV \/RA\npostgres@pgbr:~$ df -h # Check the disk.\n\n# In order for the disk to be mounted to \/RA automatically\nafter reboot\n\n# The following line is added to the file.\nsudo vim \/etc\/fstab\n\/dev\/ra\/RA-LV \/RA xfs nofail,defaults 0 0\npostgres@pgbr:~$ sudo umount \/RA\npostgres@pgbr:~$ sudo mount -a # should be tested\npostgres@pgbr:~$ sudo mkdir -p \/RA\/pgbackrest\npostgres@pgbr:~$ sudo chown -R postgres:postgres \/RA\/<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    In the pgbr machine, the operating system is located on the \/dev\/sda disk. A second
    disk is installed for backups. The ID of the new disk is learned by checking with
    fdisk -l. In the example made here, the 2nd disk is seen as \/dev\/sdb.<\/p>\n\n\n\n
    STEP 5 (pgbackrest settings in pgbr)<\/strong><\/mark><\/p>\n\n\n\n
    [global]\nrepo1-path=\/RA\/pgbackrest\nrepo1-retention-full=2\nprocess-max=4\nlog-level-console=info\nstart-fast=y\n[global:archive-push]\ncompress-level=3\n\n[mx]\npg1-path=\/pg\/data\/13.6\npg1-host=srv1\npg1-host-user=postgres\n\n[dm]\npg1-path=\/var\/lib\/postgresql\/13\/main\npg1-host=srv2\npg1-host-user=postgres<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    As explained in the previous sections, in srv1, postgresql database server was installed
    in the $PGDATA=\/pg\/data\/13.6<\/strong> directory from the source codes.
    In the srv2 database server, ready packages were installed with apt. That’s why
    $PGDATA=\/var\/lib\/postgresql\/13\/main<\/strong> is installed by default.<\/p>\n\n\n\n
    STEP 6  (pgbackrest setting in srv1)<\/mark><\/strong><\/p>\n\n\n\n
    vim \/etc\/pgbackrest\/pgbackrest.conf #add the following lines\n[mx]\npg1-path=\/pg\/data\/13.6\npg1-port=5434\npg1-socket-path=\/tmp\n\n[global]\nrepo1-host=pgbr\nrepo1-host-user=postgres\nprocess-max=4\nlog-level-console=info\nlog-level-file=debug\n[global:archive-push]\ncompress-level=3<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    STEP 7 (pgbackrest setting insrv2)<\/strong><\/mark><\/p>\n\n\n\n
    vim \/etc\/pgbackrest\/pgbackrest.conf  #add the following lines\n[dm]\npg1-path=\/var\/lib\/postgresql\/13\/main\npg1-port=5432\n[global]\nrepo1-host=pgbr\nrepo1-host-user=postgres\nprocess-max=4\nlog-level-console=info\nlog-level-file=debug\n[global:archive-push]\ncompress-level=3<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    STEP 8 ( In srv1 ve srv2 machines)<\/mark><\/strong><\/p>\n\n\n\n
    # IN srv1\npostgres@srv1:~$ psql\npostgres# alter system set archive_mode = 'on' ;\npostgres# alter system set archive_command = 'pgbackrest\n--stanza=mx archive-push %p'\npostgres# select pg_reload_conf();\npostgres@srv1:~$ pgbackrest stop\npostgres@srv1:~$ pgbackrest start\n\n# IN srv2\u2019de\npostgres@srv2:~$ psql\npostgres# alter system set archive_mode = 'on' ;\nalter system set archive_command = 'pgbackrest --stanza=dm archive-push %p'\npostgres# select pg_reload_conf();\npostgres@srv2:~$ pgbackrest stop\npostgres@srv2:~$ pgbackrest start<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    STEP 9 (In pgbr machine)<\/mark><\/strong><\/p>\n\n\n\n
    postgres@pgbr:~$ pgbackrest --stanza=mx stanza-create\npostgres@pgbr:~$ pgbackrest --stanza=dm stanza-create\npostgres@pgbr:~$ pgbackrest --stanza=mx check\npostgres@pgbr:~$ pgbackrest --stanza=dm check\nINFO: check command begin 2.40: --exec-id=5984-90166d79 --loglevel-\nconsole=info --pg1-host=srv2 --pg1-host-user=postgres\n--pg1-path=\/var\/lib\/postgresql\/13\/main --repo1-path=\/RA\/\npgbackrest --stanza=dm\nP00 INFO: check repo1 configuration (primary)\nP00 INFO: check repo1 archive for WAL (primary)\nP00 INFO: WAL segment 000000010000000000000007\nsuccessfully archived to \u2018\/RA\/pgbackrest\/archive\/dm\/13-\n1\/0000000100000000\/000000010000000000000007-23bcdb5a5765d40ca93\nd09dcadbed85706a8a7d4.gz\u2019 on repo1\nP00 INFO: check command end: completed successfully (1271ms)<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Archived WAL files are seen in the \/RA\/pgbackrest\/archive\/dm\/13-1 and
    \/RA\/pgbackrest\/archive\/mx\/13-1 directories.<\/p>\n\n\n\n
    Backup (In pgbr machine)<\/mark><\/strong><\/p>\n\n\n\n
    pgbackrest --stanza=dm backup\npgbackrest --stanza=mx backup\npgbackrest --stanza=dm info\npgbackrest --stanza=mx info<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Backup is taken from the backup (pgbr) server.
    The restore operation is performed on the machine where the database will be
    restored.<\/strong>
    For example, srv2\u2019s database is restored in srv2 server.<\/p>\n\n\n\n
    Restore (Restore on machine srv1 or srv2)<\/mark><\/strong><\/p>\n\n\n\n
    postgres@srv2:~$ sudo systemctl stop postgresql.service\npostgres@srv2:~$ pgbackrest --stanza=demo --delta restore\npostgres@srv2:~$ sudo systemctl start postgresql.service<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Note: <\/strong>\u201cpgbackrest stop\u201c. In some cases, it may be necessary to stop pgbackrest.
    For example, it is not recommended to take a backup from the problematic primary
    machine during the failover process.
    A test environment can be prepared for the software team by installing in the another
    machine with the same configurations and quickly taking the databases to this
    machine. Thus, the live environment is not affected, as well as backup copying etc. No
    time is wasted with transactions. pgbackrest can also be used for standby installation.
    See chapter 12 and pgbackrest.org for details.<\/p>\n\n\n\n
    Monitoring<\/strong><\/p>\n\n\n\n
    postgres@srv2:~$ psql\npostgres=# \\i \/pgbackrest-release-2.40\/doc\/example\/pgsqlpgbackrest-\ninfo.sql\npostgres=# \\i \/pgbackrest-release-2.40\/doc\/example\/pgsqlpgbackrest-\nquery.sql\nname | last_successful_backup | last_archived_wal\n------+------------------------+--------------------------\n\u201cdm\u201d | 06:22:19+03 | 00000001000000000000000B\n\u201cmx\u201d | 14:53:15+03 | 0000000400000000000000B4<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Backups can be monitored with \u2018check\u2019 and \u2018info\u2019 as well as querying with
    the SQLs that come with pgbackrest. If this query is empty, an alarm can be triggered
    through an e-mail.<\/p>\n","protected":false},"excerpt":{"rendered":"
    With a dedicated pgbackrest backup server, backup management can be managedfrom a single point. Since the backup and archived WAL files will be on another server,it will be not be affected by issues on the database server.Planning should be done by considering database sizes, network speed, processorcapacity, time periods when databases are busy. Kurulum ROLE …  PGBackRest Backup Server(Dedicated Repository Host)<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"[]"},"categories":[306],"tags":[],"class_list":["post-1660","post","type-post","status-publish","format-standard","hentry","category-backup-restore"],"_links":{"self":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1660","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/comments?post=1660"}],"version-history":[{"count":0,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/posts\/1660\/revisions"}],"wp:attachment":[{"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/media?parent=1660"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/categories?post=1660"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pgdataera.com\/en\/wp-json\/wp\/v2\/tags?post=1660"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}