1. $ sudo yum install -y postgresql14-server2. ## Put Postgres utilities such as initdb and pg_ctl into the postgres user's path:3. $ echo 'export PATH=/usr/pgsql-14/bin/:$PATH' | sudo -iu postgres tee -a .bash_profile4. $ sudo -iu postgres initdb -k5. $ sudo yum install -y pgbackrest
1. $ sudo useradd pgbackrest --gid postgres_backup --create-home
1. $ sudo -u pgbackrest ssh-keygen -q -t ed25519 -N "" -C "pgbackrest key"
1. $ sudo -iu postgres psql -tc 'show data_directory'2. var/lib/pgsql/14/data
1. $ sudo ls -la /var/lib/pgsql/142. total 43. drwx------. 1 postgres postgres 32 Jan 1 13:21 .4. drwx------. 1 postgres postgres 32 Jan 1 13:21 ..5. drwx------. 20 postgres postgres 4096 Jan 1 13:21 data
1. $ sudo find /var/lib/pgsql/14/data -type d -exec chmod g+rxs {} \;
1. $ sudo chmod g+rx /var/lib/pgsql /var/lib/pgsql/14
1. $ sudo find /var/lib/pgsql/14/data -type f -exec chmod g+r {} \;
1. $ sudo -iu postgres /usr/pgsql-14/bin/pg_ctl restart2. ## Run these until both return an error from xargs about 'missing operand':3. sudo -u pgbackrest find /var/lib/pgsql/14/data -not -readable -type d | xargs -n1 sudo chmod g+rxs4. sudo -u pgbackrest find /var/lib/pgsql/14/data -not -readable -type f | xargs -n1 sudo chmod g+r
1. $ sudo -u pgbackrest cat /var/lib/pgsql/14/data/postmaster.pid2. 293273. /var/lib/pgsql/14/data4. 16413328245. 54326. /var/run/postgresql7. localhost8. 16810335 69. ready10. 11. $ sudo -u pgbackrest touch /var/lib/pgsql/14/data/postmaster.pid12. touch: cannot touch '/var/lib/pgsql/14/data/postmaster.pid': Permission denied
1. $ sudo mkdir /tmp/pgbackrest/2. $ sudo chown postgres.postgres /tmp/pgbackrest/3. 4 $ sudo chmod g+rwxs /tmp/pgbackrest/5. $ sudo find /tmp/pgbackrest/ -type f -exec chmod g+r {} \;
1. ## Create this just in case it does not exist:2. $ sudo mkdir /etc/pgbackrest/3. $ sudo chown postgres.postgres /etc/pgbackrest/4.5. $ sudo find /etc/pgbackrest/ -type d -exec chmod g+rxs {} \;6. $ sudo find /etc/pgbackrest/ -type f -exec chmod g+r {} \;7.8. ## This file may also be in use, so adjust permissions if needed9. ## (this file may be root owned and mode 0644).10. $ sudo -iu pgbackrest find /etc/pgbackrest.conf3 -not -readable | xargs sudo chmod g+r
1. ## As before, create if it does not exist:2. $ sudo mkdir -p /var/log/pgbackrest3. $ sudo chown postgres.postgres /var/log/pgbackrest4. $ sudo chmod g+rwxs /var/log/pgbackrest/5. $ sudo find /var/log/pgbackrest/ -type f -exec chmod g+wr {} \;
1. $ sudo find /var/lib/pgbackrest/ -type d -exec chmod g+rwxs {} \;2. $ sudo find /var/lib/pgbackrest/ -type f -exec chmod g+r {} \;
1. $ echo '[foobar]' | sudo tee -a /etc/pgbackrest/pgbackrest.conf2. $ echo 'pg1-path=/var/lib/pgsql/14/data' | sudo tee -a /etc/pgbackrest/pgbackrest.conf3. $ echo 'start-fast=y' | sudo tee -a /etc/pgbackrest/pgbackrest.conf4. $ sudo -u postgres /bin/pgbackrest stanza-create --stanza foobar5. ## Make pgbackrest the owner of the backups directory:6. $ sudo chown -R pgbackrest /var/lib/pgbackrest/backup
1. $ sudo find /var/lib/pgbackrest/ -ls2.3. 89111117 0 drwxrws--- 4 postgres postgres 35 Jan 30 00:43 /var/lib/pgbackrest/4. 32100105 0 drwxr-s--- 3 postgres postgres 20 Jan 30 00:43 /var/lib/pgbackrest/archive5. 11599111 0 drwxr-s--- 2 postgres postgres 51 Jan 30 00:43 /var/lib/pgbackrest/archive/foobar6. 11810111 4 -rw-r----- 1 postgres postgres 253 Jan 30 00:43 /var/lib/pgbackrest/archive/foobar/archive.info7. 43297321 4 -rw-r----- 1 postgres postgres 253 Jan 30 00:43 /var/lib/pgbackrest8. /archive/foobar/archive.info.copy8. 14101100 0 drwxr-s--- 3 pgbackrest postgres 20 Jan 30 00:43 /var/lib/pgbackrest/backup9. 32104101 0 drwxr-s--- 2 pgbackrest postgres 49 Jan 30 00:43 /var/lib/pgbackrest/backup/foobar10 11411410 4 -rw-r----- 1 pgbackrest postgres 370 Jan 30 00:43 /var/lib/pgbackrest/backup/foobar/backup.info11. 5110103 4 -rw-r----- 1 pgbackrest postgres 370 Jan 30 00:43 /var/lib/pgbackrest/backup/foobar/backup.info.copy
1. $ sudo find /var/lib/pgbackrest/archive -exec chmod g-w {} \;
| 条目 | 默认 | 配置项名称 | 组权限 |
|---|---|---|---|
| Backrest repository (archive) | /var/lib/pgbackrest/archive | repo1-path | Read only |
| Backrest repository (backup) | /var/lib/pgbackrest/backup | repo1-path | Read and write |
| Configuration files | /etc/pgbackrest | built-in | Read only |
| Locking | /tmp/pgbackrest | lock-path | Read and write |
| Logging | /var/log/pgbackrest | log-path | Write only* |
| Spool for async WAL push | /var/spool/pgbackrest | spool-path | None |
| Postgres data directory | Varies:SHOW data_directory | pg1-path | Read only |
| Postgres logs | Varies, often $DATADIR/log | N/A | Read (not needed but nice to have) |
1. $ dd if=/dev/urandom count=1 status=none | md5sum | awk '{print$1}' | tee mypass2. 1560a2dff5992750d9748cbda44b4c51
1. $ sudo -iu postgres createuser backrest --pwprompt2. ## (enter password twice)3. $ echo *:*:postgres:backrest:$(cat mypass) | sudo -iu pgbackrest tee -a .pgpass4. $ sudo -iu pgbackrest chmod 600 .pgpass
1. local postgres backrest scram-sha-2562. local all backrest reject
1. $ sudo -iu postgres bash -c \2. 'sed -i "1i local postgres backrest scram-sha-256 \nlocal all backrest reject" $(psql -Atc "show hba_file")'3. $ sudo -iu postgres psql -c 'select pg_reload_conf()'
1. $ sudo -iu postgres psql -c 'select * from pg_hba_file_rules limit 2'2. line_number | type | database | user_name | address | netmask | auth_method | options | error3.------------+-------+------------+------------+---------+---------+---------------+---------+-------4. 1 | local | {postgres} | {backrest} | | | scram-sha-256 | |5. 2 | local | {all} | {backrest} | | | reject | |6. (2 rows)
1. $ sudo -iu postgres psql -c 'revoke all on schema public from backrest'
1. $ sudo -iu postgres psql \2. -c 'grant pg_read_all_settings to pgbackrest' \3. -c 'grant execute on function pg_start_backup to pgbackrest' \4. -c 'grant execute on function pg_stop_backup(bool,bool) to pgbackrest'
1. $ sudo -iu postgres psql -c "alter system set archive_mode=on"2. $ sudo -iu postgres psql -c "alter system set archive_command='pgbackrest --stanza=foobar archive-push %p'"3. $ sudo -iu postgres /usr/pgsql-14/bin/pg_ctl restart
1. $ sudo --user pgbackrest -i /bin/pgbackrest --stanza=foobar --log-level-console=detail check2. ERROR: [057]: unable to execute query 'select pg_catalog.pg_create_restore_point('pgBackRest Archive Check')::text':3. ERROR: permission denied for function pg_create_restore_point
1. $ sudo -iu postgres psql \2. -c 'grant execute on function pg_create_restore_point to pgbackrest' \3. -c 'grant execute on function pg_switch_wal to pgbackrest'
1. $ sudo -iu pgbackrest /bin/pgbackrest --stanza foobar check --log-level-console=detail2. 2022-01-01 03:19:22.033 P00 INFO: check command begin 2.36: --exec-id=9921-e64ad021 --log-level-console=detail3. --pg1-path=/var/lib/pgsql/14/data --stanza=foobar4. 2022-01-01 03:19:22.011 P00 INFO: check repo1 configuration (primary)5. 2022-01-01 03:19:22.005 P00 INFO: check repo1 archive for WAL (primary)6. 2022-01-01 03:19:22.025 P00 INFO: WAL segment 000000010000000000000002 successfully archived to7. '/var/lib/pgbackrest/archive/foobar/14-1/00000001000000008. /000000010000000000000002-6273e062555e65ea850137e743f73fe941746F5A.gz' on repo19. 2022-01-01 03:19:22.033 P00 INFO: check command end: completed successfully (1321ms)
1. $ sudo -iu pgbackrest /bin/pgbackrest --stanza=foobar --log-level-console=info backup2. 2022-01-01 03:19:23.116 P00 INFO: backup command begin 2.36: --exec-id=9996-6beecee3 --log-level-console=info3. --pg1-path=/var/lib/pgsql/14/data --stanza=foobar --start-fast4. WARN: option 'repo1-retention-full' is not set for 'repo1-retention-full-type=count', the repository may run out of space5. HINT: to retain full backups indefinitely (without warning), set option 'repo1-retention-full' to the maximum.6. WARN: no prior backup exists, incr backup has been changed to full7. 2022-01-01 03:19:23.104 P00 INFO: execute non-exclusive pg_start_backup(): backup begins after the requested immediate checkpoint completes8. 2022-01-01 03:19:23.101 P00 INFO: backup start archive = 000000010000000000000005, lsn = 0/50000289. 2022-01-01 03:19:23.102 P00 INFO: execute non-exclusive pg_stop_backup() and wait for all WAL segments to archive10. 2022-01-01 03:19:23.117 P00 INFO: backup stop archive = 000000010000000000000005, lsn = 0/5003EE811. 2022-01-01 03:19:23.108 P00 INFO: check archive for segment(s) 000000010000000000000005:00000001000000000000000512. 2022-01-01 03:19:23.108 P00 INFO: new backup label = 20220101-031923F13. 2022-01-01 03:19:23.116 P00 INFO: full backup size = 25.8MB, file total = 95214. 2022-01-01 03:19:23.105 P00 INFO: backup command end: completed successfully (4451ms)15. 2022-01-01 03:19:23.116 P00 INFO: expire command begin 2.36: --exec-id=9996-6beecee3 --log-level-console=info --stanza=foobar16. 2022-01-01 03:19:23.108 P00 INFO: option 'repo1-retention-archive' is not set - archive logs will not be expired17. 2022-01-01 03:19:23.101 P00 INFO: expire command end: completed successfully (6ms)
这两个警告目前并不重要。让我们使用pgBackRest “info” 命令检查备份是否完整 ：
1. $ sudo -iu pgbackrest /bin/pgbackrest info
2. stanza: foobar
3. status: ok
4. cipher: none
5.
6. db (current)
7. wal archive min/max (14):
000000010000000000000001/000000010000000000000007
8.
9. full backup: 20220101-031923F
10. timestamp start/stop: 2022-01-01 03:19:23.000 / 2022-01-01 03:19:23.999
11. wal start/stop: 000000010000000000000003 / 000000010000000000000003
12. database size: 25.8MB, database backup size: 25.8MB
13. repo1: backup set size: 3.2MB, backup size: 3.2MB
数据库权限摘要
以下是我们使用pgBackRest所需的所有Postgres数据库权限的摘要：
| Item | Type | Needed for |
|---|---|---|
| pg_read_all_settings | role | backup |
| pg_start_backup | function | backup |
| pg_stop_backup | function | backup |
| pg_create_restore_point | function | check |
| pg_switch_wal | function | check |
最后的注意事项
1. 这个新用户不应该进行恢复——为此，使用“postgres”用户。
2. 任何可能创建新Postgres集簇（例如使用 initdb）的东西都需要确保设置了数据目录中的新权限。对于 Patroni，附加到“on_start”钩子的快速shell脚本就足够了。
3. 使用 S3、GCS 或 Azure（pgBackRest 都支持）将需要进一步的调整。
4. 如果您将此与 TDE（透明数据加密）联系起来，那么您将拥有“盲”备份，其中您正在备份的文件已加密，您的备份用户无法解密。
5. 有关最低权限的进一步考虑，请询问 Crunchy Hardened Superuser Lockdown 功能。
文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
