Objective: Configure windows postgresql DB backup using linux Barman server
Steps first:
Step1) Collect the postgresql DB server details
Step2) Identify the barman server where the backup will be configured
Step3) Ensure you have access to both the servers - postgres and barman
Step4) Decide on the backup retention policy or collect it
Step5) Verify the version of the postgresql installed on the postgresql server
Step6) Verify that both the postgres and barman server has postgres and epel repos available
Step7) Verify if the user postgres and barman are available in the barman server - if yes, the binary for barman and postgres might have already been installed in the barman server
Step8) Verify the barman rpms are installed already on the barman server (barman-cli, barman, python3-barman, rsync etc..)
Step9) Create barman (superuser,replication) and streaming_barman (replication) user on the postgresql server
Step10) Verify the barman and streaming_barman user exists in the postgresql server
Step11) Setup passwordless ssh between the postgresql server and the barman server (postgres -> barman user) - Mandatory. Reverse for now is optional.
Step12) As root, edit the /etc/barman.conf file to include below listed config setting... in barman server
####begin
[root@10 ~]# grep -Ev ";|^$" /etc/barman.conf
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
parallel_jobs = 4
#last_backup_maximum_age = 1 DAYS
#retention_policy = RECOVERY WINDOW OF 1 WEEKS
####end
[root@10 ~]# grep -Ev ";|^$" /etc/barman.conf
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = gzip
parallel_jobs = 4
#last_backup_maximum_age = 1 DAYS
#retention_policy = RECOVERY WINDOW OF 1 WEEKS
####end
Step13) As root (ensure the candidate file has r permission to barman user), create server specific configuration file in barman server
/etc/barman.d/Primary-Windows-Postgresql.conf
####begin
[Primary-Windows-Postgresql]
description = "Primary-Windows-Postgresql 15 DB Server"
conninfo = host=<ip/hostname> user=barman password=<barman_password> dbname=postgres port=5432
streaming_conninfo = host=<ip/hostname> user=streaming_barman password=<streaming_barman_password> port=5432
#if_linux#ssh_command = ssh postgres@10.0.2.8
backup_method = postgres
streaming_archiver = on
slot_name = barman
create_slot = auto
path_prefix = "/usr/pgsql-12/bin"
retention_policy = RECOVERY WINDOW OF 7 days
#archiver = on ## keep this commented for windows, we have streaming_archiver anyway
####end
[Primary-Windows-Postgresql]
description = "Primary-Windows-Postgresql 15 DB Server"
conninfo = host=<ip/hostname> user=barman password=<barman_password> dbname=postgres port=5432
streaming_conninfo = host=<ip/hostname> user=streaming_barman password=<streaming_barman_password> port=5432
#if_linux#ssh_command = ssh postgres@10.0.2.8
backup_method = postgres
streaming_archiver = on
slot_name = barman
create_slot = auto
path_prefix = "/usr/pgsql-12/bin"
retention_policy = RECOVERY WINDOW OF 7 days
#archiver = on ## keep this commented for windows, we have streaming_archiver anyway
####end
Step14) Verify the config files once
Step15) Make the below changes in postgresql.conf in $PGDATA directory in the postgresql db server
wal_level = replica
archive_mode = on
#archive_command = 'rsync -a %p barman@<servername>:/var/lib/barman/Primary-Windows-Postgresql/streaming/%f'
archive_mode = on
#archive_command = 'rsync -a %p barman@<servername>:/var/lib/barman/Primary-Windows-Postgresql/streaming/%f'
archive_command needs to be set properly, if not set we will face an issue. You will see this in actual execution
Step16) Make the below changes in pg_hba.conf file in the $PGDATA directory in the postgresql server
host all barman <barmanserver/ip>/32 trust
host all streaming_barman <barmanserver/ip>/32 trust
host all streaming_barman <barmanserver/ip>/32 trust
Step17) coordinate with application team for an outage and restart the postgresql service once in the postgresql server
Step18) Check in the outage the output & verify the results are ok in the barman server
barman check Primary-Windows-Postgresql
barman list-backup Primary-Windows-Postgresql
Step19) Bring the application back online in case any
Step20) Verify the capacity of the /var/lib/barman and compare it against the DB capacity
Step21) Schedule the backup in cron, after creating necessary directory /var/lib/barman/backupscr/log as barman in barman server
Step18) Check in the outage the output & verify the results are ok in the barman server
barman check Primary-Windows-Postgresql
barman list-backup Primary-Windows-Postgresql
Step19) Bring the application back online in case any
Step20) Verify the capacity of the /var/lib/barman and compare it against the DB capacity
Step21) Schedule the backup in cron, after creating necessary directory /var/lib/barman/backupscr/log as barman in barman server
/usr/bin/barman backup Primary-Windows-Postgresql 1> /var/lib/barman/backupscr/log/barman_full_backup_Primary-Windows-Postgresql_$(date +\%d\%m\%y%_\%H\%M\%S).log 2>&1
Step22) Verify the successful backup
Now actual execution:
Step1) Collect the postgresql DB server details
VAGRANT|10.0.2.5
Step2) Identify the barman server where the backup will be configured
localhost.localdomain|10.0.2.9
Step3) Ensure you have access to both the servers - postgres and barman
Yes we do
Step4) Decide on the backup retention policy or collect it
1 week
Step5) Verify the version of the postgresql installed on the postgresql server
postgresql 14.6.1
Step6) Verify that both the postgres and barman server has postgres and epel repos available
postgres server is windows
barman has all the repos.
Step7) Verify if the user postgres and barman are available in the barman server - if yes, the binary for barman and postgres might have already been installed in the barman server
-bash-4.2$ id barman
uid=996(barman) gid=994(barman) groups=994(barman)
-bash-4.2$ id postgres
uid=26(postgres) gid=26(postgres) groups=26(postgres)
-bash-4.2$
Step8) Verify the barman rpms are installed already on the barman server (barman-cli, barman, python3-barman, rsync etc..)
-bash-4.2$ rpm -qa|grep -i barman
barman-3.2.0-1.rhel7.noarch
python-barman-3.2.0-1.rhel7.noarch
-bash-4.2$
Step9) Create barman (superuser,replication) and streaming_barman (replication) user on the postgresql server
postgres=# \du
List of roles
Role name | Attributes | Member of
------------------+------------------------------------------------------------+-----------
barman | Superuser, Replication | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
streaming_barman | Replication | {}
postgres=#
Step10) Verify the barman and streaming_barman user exists in the postgresql server
Allset as we see in last step.
Step11) Setup passwordless ssh between the postgresql server and the barman server (postgres -> barman user) - Mandatory. Reverse for now is optional.
NA for windows
Step12) As root, edit the /etc/barman.conf file to include below listed config setting... in barman server
####begin
-bash-4.2$ cat barman.conf|grep -Ev "^$|^;"
[barman]
barman_user = barman
configuration_files_directory = /etc/barman.d
barman_home = /var/lib/barman
log_file = /var/log/barman/barman.log
log_level = INFO
compression = pigz
parallel_jobs = 4
retention_policy = RECOVERY WINDOW OF 4 WEEKS
-bash-4.2$
####end
Step13) As root (ensure the candidate file has r permission to barman user), create server specific configuration file in barman server
/etc/barman.d/Primary-Windows-Postgresql.conf
####begin
[Primary-Windows-Postgresql]
description = "Primary-Windows-Postgresql 15 DB Server"
conninfo = host=<ip/hostname> user=barman password=<barman_password> dbname=postgres port=5432
streaming_conninfo = host=<ip/hostname> user=streaming_barman password=<streaming_barman_password> port=5432
#if_linux#ssh_command = ssh postgres@10.0.2.8
backup_method = postgres
streaming_archiver = on
slot_name = barman
create_slot = auto
path_prefix = "/usr/pgsql-12/bin"
retention_policy = RECOVERY WINDOW OF 7 days
#archiver = on
####end
Step14) Verify the config files once
Allset
Step15) Make the below changes in postgresql.conf in $PGDATA directory in the postgresql db server
wal_level = replica
archive_mode = on
#archive_command = 'rsync -a %p barman@<servername>:/var/lib/barman/Primary-Windows-Postgresql/streaming/%f'
We are missing archive_command here, see what happens during backup
Step16) Make the below changes in pg_hba.conf file in the $PGDATA directory in the postgresql server
host all barman 10.0.2.9/32 trust
host all streaming_barman 10.0.2.9/32 trust
Step17) coordinate with application team for an outage and restart the postgresql service once in the postgresql server
>done
Step18) Check in the outage the output & verify the results are ok in the barman server
barman check Primary-Windows-Postgresql
barman list-backup Primary-Windows-Postgresql
-bash-4.2$ barman check Primary-Windows-Postgresql
Server Primary-Windows-Postgresql:
PostgreSQL: OK
superuser or standard user with backup privileges: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
backup minimum size: OK (0 B)
wal maximum age: OK (no last_wal_maximum_age provided)
wal size: OK (0 B)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
pg_basebackup: OK
pg_basebackup compatible: OK
pg_basebackup supports tablespaces mapping: OK
systemid coherence: OK (no system Id stored on disk)
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archiver errors: OK
-bash-4.2$
Step19) Bring the application back online in case any
Step29) Fire an adhoc backup and check...
==========
The backup got stuck for ever, due to archive_Command comment out in postgres db server...
-bash-4.2$ barman backup Primary-Windows-Postgresql
Starting backup using postgres method for server Primary-Windows-Postgresql in /var/lib/barman/Primary-Windows-Postgresql/base/20221214T161132
Backup start at LSN: 0/2000060 (000000010000000000000002, 00000060)
Starting backup copy via pg_basebackup for 20221214T161132
....
Wait
....
Wait
....
psql - pg_stat_activity:
-[ RECORD 4 ]----+-------------------------------------------------------------------------
datid |
datname |
pid | 3888
leader_pid |
usesysid | 16395
usename | streaming_barman
application_name | barman_streaming_backup
client_addr | 10.0.2.9
client_hostname |
client_port | 58826
backend_start | 2022-12-14 13:11:29.76526-08
xact_start |
query_start | 2022-12-14 13:11:29.784469-08
state_change | 2022-12-14 13:11:29.78448-08
wait_event_type | IPC
wait_event | BackupWaitWalArchive
state | active
backend_xid |
backend_xmin |
query_id |
query | BASE_BACKUP LABEL 'pg_basebackup base backup' PROGRESS MANIFEST 'yes'
backend_type | walsender
-[ RECORD 5 ]----+-------------------------------------------------------------------------
see BackupWaitWalArchive, we are waiting for wal archive to finish in primary windows machine, but there is no archive_command, since commented it out!!!
Related info message can be seen in barman.log as well...
2022-12-14 16:28:52,336 [4346] barman.backup_executor INFO: WARNING: still waiting for all required WAL segments to be archived (960 seconds elapsed)
2022-12-14 16:28:52,337 [4346] barman.backup_executor INFO: HINT: Check that your archive_command is executing properly. You can safely cancel this backup, but the database backup will not be usable without all the WAL segments.
Fix: is to introduce the archive_command with the below attribute, restart the postgresql. The backup just finished fine :)
archive_command = 'copy "%p" "C:\\Users\\Administrator\\PostgreSQL\\14\\data\\pg_archive\\%f"'
-bash-4.2$ barman backup Primary-Windows-Postgresql
Starting backup using postgres method for server Primary-Windows-Postgresql in /var/lib/barman/Primary-Windows-Postgresql/base/20221214T163834
Backup start at LSN: 0/60000D8 (000000010000000000000006, 000000D8)
Starting backup copy via pg_basebackup for 20221214T163834
Copy done (time: 1 minute, 17 seconds)
Finalising the backup.
This is the first backup for server Primary-Windows-Postgresql
WAL segments preceding the current backup have been found:
000000010000000000000002 from server Primary-Windows-Postgresql has been removed
000000010000000000000003 from server Primary-Windows-Postgresql has been removed
000000010000000000000004 from server Primary-Windows-Postgresql has been removed
000000010000000000000005 from server Primary-Windows-Postgresql has been removed
000000010000000000000006 from server Primary-Windows-Postgresql has been removed
Backup size: 121.4 MiB
Backup end at LSN: 0/8000060 (000000010000000000000008, 00000060)
Backup completed (start time: 2022-12-14 16:38:34.640789, elapsed time: 1 minute, 20 seconds)
Processing xlog segments from streaming for Primary-Windows-Postgresql
000000010000000000000007
000000010000000000000008
You have new mail in /var/spool/mail/barman
-bash-4.2$
-bash-4.2$ barman list-backup Primary-Windows-Postgresql
Primary-Windows-Postgresql 20221214T163834 - Wed Dec 14 13:39:50 2022 - Size: 121.4 MiB - WAL Size: 0 B
Primary-Windows-Postgresql 20221214T161132 - FAILED
-bash-4.2
Earlier cancelled or failed one: 20221214T161132
Successful latest one: 20221214T163834
-bash-4.2$ barman show-backup Primary-Windows-Postgresql 20221214T163834
Backup 20221214T163834:
Server Name : Primary-Windows-Postgresql
System Id : 7167807434816183596
Status : DONE
PostgreSQL Version : 140006
PGDATA directory : C:/Users/Administrator/PostgreSQL/14/data
Base backup information:
Disk usage : 121.4 MiB (121.4 MiB with WALs)
Incremental size : 121.4 MiB (-0.00%)
Timeline : 1
Begin WAL : 000000010000000000000007
End WAL : 000000010000000000000008
WAL number : 2
WAL compression ratio: 99.89%
Begin time : 2022-12-14 13:38:33.171758-08:00
End time : 2022-12-14 13:39:50.066436-08:00
Copy time : 1 minute, 17 seconds
Estimated throughput : 1.6 MiB/s
Begin Offset : 96
End Offset : 96
Begin LSN : 0/7000060
End LSN : 0/8000060
WAL information:
No of files : 0
Disk usage : 0 B
WAL rate : 1105.99/hour
Last available : 000000010000000000000008
Catalog information:
Retention Policy : VALID
Previous Backup : - (this is the oldest base backup)
Next Backup : - (this is the latest base backup)
-bash-4.2$
Please note I used postgresql 15 binary in barman to backup postgresql 14.6.1 in windows.
All set!!
All the best & thank you for your visit!
No comments:
Post a Comment