WAL-G: Create daily backup

[fatmaAliGamal]

cron_jobs: 
# Example for walg
 - name: "WAL-G: Create daily backup"
   user: "postgres"
   file: /etc/cron.d/walg
   minute: "55"
   hour: "16"
   day: "*"
   month: "*"
   weekday: "*"
   job: "[ $(curl -s -o /dev/null -w '%{http_code}' http://{{ inventory_hostname }}:{{ patroni_restapi_port }}) = '200' ] && wal-g backup-push"

---

# WAL-G
wal_g_install: true  # or 'true'
wal_g_version: "2.0.1"
wal_g_json:  # config https://github.com/wal-g/wal-g#configuration
  - { option: "AWS_ACCESS_KEY_ID", value: "{{AWS_ACCESS_KEY_ID}}" }
  - { option: "AWS_SECRET_ACCESS_KEY", value: "{{AWS_SECRET_ACCESS_KEY}}" }
  - { option: "WALG_S3_PREFIX", value: "{{WALG_S3_PREFIX}}" }
  - { option: "AWS_S3_FORCE_PATH_STYLE", value: "true" }
  - { option: "WALG_COMPRESSION_METHOD", value: "gzip" } #"lz4", "zstd", "brotli", "gzip",
  - { option: "PGDATA", value: "{{ postgresql_data_dir }}" }
  - { option: "PGHOST", value: "{{ postgresql_unix_socket_dir }}" }

wal_g_patroni_cluster_bootstrap_command: "wal-g backup-fetch {{ postgresql_data_dir }} LATEST"

---

# https://patroni.readthedocs.io/en/latest/replica_bootstrap.html#bootstrap
patroni_cluster_bootstrap_method: "initdb"  # or "wal-g", "pgbackrest", "pg_probackup","initdb"

# https://patroni.readthedocs.io/en/latest/replica_bootstrap.html#building-replicas
patroni_create_replica_methods:
#  - pgbackrest
  - wal_g
#  - pg_probackup
  - basebackup

---

at postgresql_parameters:

  - { option: "archive_command", value: "wal-g wal-push %p" }  # archive WAL-s using WAL-G

---

change location of

# PostgreSQL variables
postgresql_version: "15"
postgresql_data_dir: "/dev/xvdb/{{ postgresql_version }}/{{ postgresql_cluster_name }}"

why did not take backup to s3

i run ansible-playbook deploy_pgcluster.yml -e "enable_timescale=true" -e "AWS_ACCESS_KEY_ID=*****" -e "AWS_SECRET_ACCESS_KEY=******" -e "WALG_S3_PREFIX=s3://soa-backup/soabackup/"

[vitabaks]

why did not take backup to s3

The backup is performed according to the schedule defined in the cron_jobs variable

Are you sure that the task was completed by cron?

Have you tried to run wal-g backup-push manually?

[fatmaAliGamal]

i run wal-g backup-push
appear error : ERROR: 2023/07/05 08:12:20.131296 Failed to find any configured storage

[vitabaks]

Check the wal-g configuration file

• Debian based: /var/lib/postgresql/.walg.json
• RedHat based: /var/lib/pgsql/.walg.json

[vitabaks]

I use Ubuntu

Please check the wal-g configuration file

/var/lib/postgresql/.walg.json

[fatmaAliGamal]

{
"AWS_ACCESS_KEY_ID": "*",
"AWS_SECRET_ACCESS_KEY": "",
"AWS_ENDPOINT": "https://s3.amazonaws.com",
"WALG_S3_PREFIX": "s3://soa-backup.s3.amazonaws.com/soabackup/",
"AWS_S3_FORCE_PATH_STYLE": "true",
"WALG_COMPRESSION_METHOD": "gzip",
"PGDATA": "/var/lib/postgresql/15/main",
"PGHOST": "/var/run/postgresql"
}

[fatmaAliGamal]

when I check status for wal-g
ubuntu@ip-10-0-4-125:~$ sudo systemctl status wal-g
Unit wal-g.service could not be found

[vitabaks]

Do you have empty values for AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY or did you just hide them?

Delete the AWS_S3_FORCE_PATH_STYLE parameter

[vitabaks]

Unit wal-g.service could not be found

this is a binary file and it doesn't have a service

please read the documentation https://github.com/wal-g/wal-g/tree/v2.0.1/docs

[fatmaAliGamal]

i read documentation thanks
but still this error after delete AWS_S3_FORCE_PATH_STYLE
wal-g st ls
ERROR: 2023/07/05 10:29:40.846418 Failed to find any configured storage
AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY i hide them

[vitabaks]

"PGDATABASE": "postgres" but i want to all database not this only

i will try it

Used only for connecting to postgres. You don't have to specify this variable.

[fatmaAliGamal]

in my case you must be put --config /var/lib/postgresql/.walg.json
wal-g --config /var/lib/postgresql/.walg.json st ls
type size last modified name
i don't know if you know how edit this in code ansible
wal-g --config /var/lib/postgresql/.walg.json backup-push
ERROR: 2023/07/05 12:33:47.645417 Connect: postgres connection failed: unexpected message type

[vitabaks]

in my case you must be put --config /var/lib/postgresql/.walg.json

the config option does not have to be specified if you run the command from under the postgres user because .walg.json file is located in the postgres user home directory.

i don't know if you know how edit this in code ansible

see job in cron_jobs variable

if necessary, you can override the wal-g command in all variables where it is defined.

ERROR: 2023/07/05 12:33:47.645417 Connect: postgres connection failed: unexpected message type

Please check Postgres log

[fatmaAliGamal]

i create servers again and send it logs
but when i use wal-g", i need to change patroni_cluster_bootstrap_method: "wal-g" or not need when i read different between "initdb" , "wal-g", "pgbackrest", "pg_probackup" i use initdb when i create new cluster

# https://patroni.readthedocs.io/en/latest/replica_bootstrap.html#building-replicas
patroni_create_replica_methods:
  # - pgbackrest
  - wal_g
#  - pg_probackup
  - basebackup

[vitabaks]

If you have a backup and you want to restore a database cluster from this backup, then you can override patroni_cluster_bootstrap_method, if you need a new cluster, then use initdb

Details https://github.com/vitabaks/postgresql_cluster#restore-and-cloning

[vitabaks]

Automate cron jobs configuration for WAL-G and minor fixes - #395

[fatmaAliGamal]

thanks for this
but error still appeared
wal-g st ls
ERROR: 2023/07/05 16:50:14.088029 Failed to find any configured storage

wal-g --config /var/lib/postgresql/.walg.json st ls
type size last modified name
dir 0 0001-01-01 00:00:00 +0000 UTC wal_005/

[fatmaAliGamal]

but backup took at s3
but i can't understand name of backup
(https://github.com/vitabaks/postgresql_cluster/assets/45911031/eacd31bb-bd96-4ab7-9b4a-5fbb6ddda1c1)

what is meaning name 000000000000000.br
00000000000000.backup.br
what is meaning number of .br (6 file)??
how can change name for this backup ??

[vitabaks]

wal-g --config

if necessary, you can override the wal-g command in all variables where it is defined.

what is meaning number of .br (6 file)??
how can change name for this backup ??

Please refer to the documentation or to the authors of the wal-g project for help

[fatmaAliGamal]

i read this and i will try to search more at it thanks

[fatmaAliGamal]

• { option: "PGHOST", value: "{{ postgresql_unix_socket_dir }}" } in this case postgresql_unix_socket_dir = /var/run/postgresql

postgresql_unix_socket_dir this is not localhost

[vitabaks]

postgresql_unix_socket_dir this is not localhost

It is recommended to use unix socket

But you can override the values. The configuration provided in vars/main.yml is just for example

[fatmaAliGamal]

thanks for faster response

[fatmaAliGamal]

@vitabaks can you see wal-g/wal-g#1385
this is issue at postgressql 15 this is same issue appear with me if you want to read and test same case, will appear same error
may be you try postgres less than 15

[vitabaks]

Thanks for the link, I subscribed and will keep track of this issue.

I reproduced this error during testing and it really looks like a bug since before it was enough to specify PGDATA in the .walg file.json and it was not necessary to explicitly define the path to the directory in the 'backup-push' command. I fixed this by defining the path in the command, see #395 (commit: 3e5aef8)

[fatmaAliGamal]

i mean sudo -u postgres wal-g backup-push {{ postgresql_data_dir }} to add sudo -u postgres

[vitabaks]

This is not necessary since the cron task is executed from under the postgres user