Backup PostgreSQL database using BARMAN to a location different from the default location
Objective:
Update the postgresql backup location in barman server;
We are looking to migrate the backups from default barman user's home directory to a dedicated filesystem where the backups will be hosted.
For instance /var/lib/barman to /var/lib/barman/pgbackups
/var/lib/barman is a dedicated mountpoint for barman users home.
/var/lib/barman/pgbackups which is mounted on top of this directory acts as dedicated backup destination.
You are welcome to choose a seperate mountpath as well.
Steps:
Before the change server config:
-bash-4.2$ barman show-servers primarypostgresql15
Server primarypostgresql15:
active: True
archive_timeout: 0
archiver: False
archiver_batch_size: 0
backup_compression: None
backup_compression_format: None
backup_compression_level: None
backup_compression_location: None
backup_compression_workers: None
backup_directory: /var/lib/barman/primarypostgresql15
backup_method: postgres
backup_options: BackupOptions(['concurrent_backup'])
bandwidth_limit: None
barman_home: /var/lib/barman
barman_lock_directory: /var/lib/barman
basebackup_retry_sleep: 30
basebackup_retry_times: 0
basebackups_directory: /var/lib/barman/primarypostgresql15/base
check_timeout: 30
checkpoint_timeout: 300
compression: pigz
config_file: /pgDATA/data/postgresql.conf
connection_error: None
conninfo: host=10.0.2.8 user=barman password=*REDACTED* dbname=postgres port=5432
create_slot: auto
current_lsn: 0/9A0000D8
current_size: 1259817117
current_xlog: 00000001000000000000009A
custom_compression_filter: None
custom_compression_magic: None
custom_decompression_filter: None
data_checksums: off
data_directory: /pgDATA/data
description: Primary PostgreSQL Server Backup
disabled: False
errors_directory: /var/lib/barman/primarypostgresql15/errors
forward_config_path: False
has_backup_privileges: True
hba_file: /pgDATA/data/pg_hba.conf
hot_standby: on
ident_file: /pgDATA/data/pg_ident.conf
immediate_checkpoint: False
incoming_wals_directory: /var/lib/barman/primarypostgresql15/incoming
is_in_recovery: False
is_superuser: True
last_backup_maximum_age: None
last_backup_minimum_size: None
last_wal_maximum_age: None
max_incoming_wals_queue: None
max_replication_slots: 10
max_wal_senders: 10
minimum_redundancy: 0
msg_list: []
name: primarypostgresql15
network_compression: False
parallel_jobs: 4
passive_node: False
path_prefix: "/usr/pgsql-15/bin" # this is needed for streaming archiving, pg_walrecieve command is supplied by postgresql server binary, hence we are installing the postgresql server binary in the barman server
pg_basebackup_bwlimit: True
pg_basebackup_compatible: True
pg_basebackup_installed: True
pg_basebackup_path: /bin/pg_basebackup
pg_basebackup_tbls_mapping: True
pg_basebackup_version: 15.1
pg_receivexlog_compatible: True
pg_receivexlog_installed: True
pg_receivexlog_path: /usr/pgsql-15/bin/pg_receivewal
pg_receivexlog_supports_slots: True
pg_receivexlog_synchronous: False
pg_receivexlog_version: 15.1
pgespresso_installed: False
post_archive_retry_script: None
post_archive_script: None
post_backup_retry_script: None
post_backup_script: None
post_delete_retry_script: None
post_delete_script: None
post_recovery_retry_script: None
post_recovery_script: None
post_wal_delete_retry_script: None
post_wal_delete_script: None
postgres_systemid: 7163531269528114626
pre_archive_retry_script: None
pre_archive_script: None
pre_backup_retry_script: None
pre_backup_script: None
pre_delete_retry_script: None
pre_delete_script: None
pre_recovery_retry_script: None
pre_recovery_script: None
pre_wal_delete_retry_script: None
pre_wal_delete_script: None
primary_conninfo: None
primary_ssh_command: None
recovery_options: RecoveryOptions([])
recovery_staging_path: None
replication_slot: Record(slot_name='barman', active=False, restart_lsn='0/94000000')
replication_slot_support: True
retention_policy: RECOVERY WINDOW OF 4 WEEKS
retention_policy_mode: auto
reuse_backup: None
server_txt_version: 15.0
slot_name: barman
ssh_command: None
streaming: True
streaming_archiver: True
streaming_archiver_batch_size: 0
streaming_archiver_name: barman_receive_wal
streaming_backup_name: barman_streaming_backup
streaming_conninfo: host=10.0.2.8 user=streaming_barman password=*REDACTED* port=5432
streaming_supported: True
streaming_systemid: 7163531269528114626
streaming_wals_directory: /var/lib/barman/primarypostgresql15/streaming
synchronous_standby_names: ['']
tablespace_bandwidth_limit: None
timeline: 1
wal_compression: off
wal_keep_size: 1GB
wal_level: replica
wal_retention_policy: MAIN
wals_directory: /var/lib/barman/primarypostgresql15/wals
xlog_segment_size: 16777216
xlogpos: 0/9A0000D8
You have new mail in /var/spool/mail/barman
-bash-4.2
List of directories relying on: /var/lib/barman [which is where our backups are hosted]
Line 16: backup_directory: /var/lib/barman/primarypostgresql15
Line 20: barman_home: /var/lib/barman
Line 21: barman_lock_directory: /var/lib/barman
Line 24: basebackups_directory: /var/lib/barman/primarypostgresql15/base
Line 42: errors_directory: /var/lib/barman/primarypostgresql15/errors
Line 49: incoming_wals_directory: /var/lib/barman/primarypostgresql15/incoming
Line 119: streaming_wals_directory: /var/lib/barman/primarypostgresql15/streaming
Line 127: wals_directory: /var/lib/barman/primarypostgresql15/wals
Change server config in the conf file:
11,12d10
< ;to alter backup location
< backup_directory = /var/lib/barman/pgbackups/primarypostgresql15
[root@localhost barman.d]#
After the change of server config:
Server primarypostgresql15:
active: True
archive_timeout: 0
archiver: False
archiver_batch_size: 0
backup_compression: None
backup_compression_format: None
backup_compression_level: None
backup_compression_location: None
backup_compression_workers: None
backup_directory: /var/lib/barman/pgbackups/primarypostgresql15
backup_method: postgres
backup_options: BackupOptions(['concurrent_backup'])
bandwidth_limit: None
barman_home: /var/lib/barman
barman_lock_directory: /var/lib/barman
basebackup_retry_sleep: 30
basebackup_retry_times: 0
basebackups_directory: /var/lib/barman/pgbackups/primarypostgresql15/base
check_timeout: 30
checkpoint_timeout: 300
compression: pigz
config_file: /pgDATA/data/postgresql.conf
connection_error: None
conninfo: host=10.0.2.8 user=barman password=*REDACTED* dbname=postgres port=5432
create_slot: auto
current_lsn: 0/9A0001C0
current_size: 1259817117
current_xlog: 00000001000000000000009A
custom_compression_filter: None
custom_compression_magic: None
custom_decompression_filter: None
data_checksums: off
data_directory: /pgDATA/data
description: Primary PostgreSQL Server Backup
disabled: False
errors_directory: /var/lib/barman/pgbackups/primarypostgresql15/errors
forward_config_path: False
has_backup_privileges: True
hba_file: /pgDATA/data/pg_hba.conf
hot_standby: on
ident_file: /pgDATA/data/pg_ident.conf
immediate_checkpoint: False
incoming_wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/incoming
is_in_recovery: False
is_superuser: True
last_backup_maximum_age: None
last_backup_minimum_size: None
last_wal_maximum_age: None
max_incoming_wals_queue: None
max_replication_slots: 10
max_wal_senders: 10
minimum_redundancy: 0
msg_list: []
name: primarypostgresql15
network_compression: False
parallel_jobs: 4
passive_node: False
path_prefix: "/usr/pgsql-15/bin" # this is needed for streaming archiving, pg_walrecieve command is supplied by postgresql server binary, hence we are installing the postgresql server binary in the barman server
pg_basebackup_bwlimit: True
pg_basebackup_compatible: True
pg_basebackup_installed: True
pg_basebackup_path: /bin/pg_basebackup
pg_basebackup_tbls_mapping: True
pg_basebackup_version: 15.1
pg_receivexlog_compatible: True
pg_receivexlog_installed: True
pg_receivexlog_path: /usr/pgsql-15/bin/pg_receivewal
pg_receivexlog_supports_slots: True
pg_receivexlog_synchronous: False
pg_receivexlog_version: 15.1
pgespresso_installed: False
post_archive_retry_script: None
post_archive_script: None
post_backup_retry_script: None
post_backup_script: None
post_delete_retry_script: None
post_delete_script: None
post_recovery_retry_script: None
post_recovery_script: None
post_wal_delete_retry_script: None
post_wal_delete_script: None
postgres_systemid: 7163531269528114626
pre_archive_retry_script: None
pre_archive_script: None
pre_backup_retry_script: None
pre_backup_script: None
pre_delete_retry_script: None
pre_delete_script: None
pre_recovery_retry_script: None
pre_recovery_script: None
pre_wal_delete_retry_script: None
pre_wal_delete_script: None
primary_conninfo: None
primary_ssh_command: None
recovery_options: RecoveryOptions([])
recovery_staging_path: None
replication_slot: Record(slot_name='barman', active=True, restart_lsn='0/9A000000')
replication_slot_support: True
retention_policy: RECOVERY WINDOW OF 4 WEEKS
retention_policy_mode: auto
reuse_backup: None
server_txt_version: 15.0
slot_name: barman
ssh_command: None
streaming: True
streaming_archiver: True
streaming_archiver_batch_size: 0
streaming_archiver_name: barman_receive_wal
streaming_backup_name: barman_streaming_backup
streaming_conninfo: host=10.0.2.8 user=streaming_barman password=*REDACTED* port=5432
streaming_supported: True
streaming_systemid: 7163531269528114626
streaming_wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/streaming
synchronous_standby_names: ['']
tablespace_bandwidth_limit: None
timeline: 1
wal_compression: off
wal_keep_size: 1GB
wal_level: replica
wal_retention_policy: MAIN
wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/wals
xlog_segment_size: 16777216
xlogpos: 0/9A0001C0
-bash-4.2$
What all server config were impacted by the change:
12c12
< backup_directory: /var/lib/barman/primarypostgresql15
---
> backup_directory: /var/lib/barman/pgbackups/primarypostgresql15
20c20
< basebackups_directory: /var/lib/barman/primarypostgresql15/base
---
> basebackups_directory: /var/lib/barman/pgbackups/primarypostgresql15/base
28c28
< current_lsn: 0/9A0000D8
---
> current_lsn: 0/9A0001C0
38c38
< errors_directory: /var/lib/barman/primarypostgresql15/errors
---
> errors_directory: /var/lib/barman/pgbackups/primarypostgresql15/errors
45c45
< incoming_wals_directory: /var/lib/barman/primarypostgresql15/incoming
---
> incoming_wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/incoming
99c99
< replication_slot: Record(slot_name='barman', active=False, restart_lsn='0/94000000')
---
> replication_slot: Record(slot_name='barman', active=True, restart_lsn='0/9A000000')
115c115
< streaming_wals_directory: /var/lib/barman/primarypostgresql15/streaming
---
> streaming_wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/streaming
123c123
< wals_directory: /var/lib/barman/primarypostgresql15/wals
---
> wals_directory: /var/lib/barman/pgbackups/primarypostgresql15/wals
125,127c125,126
< xlogpos: 0/9A0000D8
< You have new mail in /var/spool/mail/barman
< -bash-4.2
---
> xlogpos: 0/9A0001C0
> -bash-4.2$
-bash-4.2$
Confirmation of the changes: <NO CH> - No changes. <OK> - changed as expected
Line 20: barman_home: /var/lib/barman <NO CH>
Line 21: barman_lock_directory: /var/lib/barman <NO CH>
Line 24: basebackups_directory: /var/lib/barman/primarypostgresql15/base <OK>
Line 42: errors_directory: /var/lib/barman/primarypostgresql15/errors <OK>
Line 49: incoming_wals_directory: /var/lib/barman/primarypostgresql15/incoming <OK>
Line 119: streaming_wals_directory: /var/lib/barman/primarypostgresql15/streaming <OK>
Line 127: wals_directory: /var/lib/barman/primarypostgresql15/wals <OK>
The objective was to move the actual backup content, which occupies space to a dedicated mountpoint rather than keeping them in barman's home directory.
This seem to be achieved now.
A test backup routine:
Starting backup using postgres method for server primarypostgresql15 in /var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949
Backup start at LSN: 0/9C000060 (00000001000000000000009C, 00000060)
Starting backup copy via pg_basebackup for 20221228T081949
Copy done (time: 1 minute, 9 seconds)
Finalising the backup.
This is the first backup for server primarypostgresql15
WAL segments preceding the current backup have been found:
000000010000000000000095 from server primarypostgresql15 has been removed
000000010000000000000096 from server primarypostgresql15 has been removed
000000010000000000000097 from server primarypostgresql15 has been removed
000000010000000000000098 from server primarypostgresql15 has been removed
000000010000000000000099 from server primarypostgresql15 has been removed
00000001000000000000009A from server primarypostgresql15 has been removed
00000001000000000000009B from server primarypostgresql15 has been removed
00000001000000000000009C from server primarypostgresql15 has been removed
Backup size: 1.2 GiB
Backup end at LSN: 0/9E000000 (00000001000000000000009D, 00000000)
Backup completed (start time: 2022-12-28 08:19:49.192492, elapsed time: 1 minute, 10 seconds)
Processing xlog segments from streaming for primarypostgresql15
00000001000000000000009D
You have new mail in /var/spool/mail/barman
-bash-4.2$
Verify if it created files as expected:
-bash-4.2$ barman show-backup primarypostgresql15 20221228T081949
Backup 20221228T081949:
Server Name : primarypostgresql15
System Id : 7163531269528114626
Status : DONE
PostgreSQL Version : 150000
PGDATA directory : /pgDATA/data
Base backup information:
Disk usage : 1.2 GiB (1.2 GiB with WALs)
Incremental size : 1.2 GiB (-0.00%)
Timeline : 1
Begin WAL : 00000001000000000000009D
End WAL : 00000001000000000000009D
WAL number : 1
WAL compression ratio: 99.89%
Begin time : 2022-12-28 08:19:49.189472-05:00
End time : 2022-12-28 08:20:59.048208-05:00
Copy time : 1 minute, 9 seconds
Estimated throughput : 17.2 MiB/s
Begin Offset : 40
End Offset : 0
Begin LSN : 0/9D000028
End LSN : 0/9E000000
WAL information:
No of files : 0
Disk usage : 0 B
Last available : 00000001000000000000009D
Catalog information:
Retention Policy : VALID
Previous Backup : - (this is the oldest base backup)
Next Backup : - (this is the latest base backup)
-bash-4.2$
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/backup.info
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/PG_VERSION
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/backup_label
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/backup_manifest
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/current_logfiles
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/pg_hba.conf
...
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/pg_multixact/offsets/0000
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/1/112
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/1/113
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/1/1247
...
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/24605/827
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/24605/828
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/24605/PG_VERSION
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/base/24605/pg_filenode.map
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/pg_xact/0000
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/pg_logical/replorigin_checkpoint
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/log/postgresql-Fri.log
...
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/log/postgresql-Thu.log
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/log/postgresql-Tue.log
/var/lib/barman/pgbackups/primarypostgresql15/base/20221228T081949/data/log/postgresql-Wed.log
/var/lib/barman/pgbackups/primarypostgresql15/wals/0000000100000000/00000001000000000000009D
Final check:
-bash-4.2$ barman list-files primarypostgresql15 20221228T081949|wc -l
1611
You have new mail in /var/spool/mail/barman
-bash-4.2$ barman list-files primarypostgresql15 20221228T081949|grep "/var/lib/barman/pgbackups/primarypostgresql15"|wc -l
1611
-bash-4.2$
As we expected all the files were backedup on the /var/lib/barman/pgbackups/primarypostgresql15 directory.
This closes the blog.
Thank you!
No comments:
Post a Comment