Habilitar Wal PostgreSQL – …::: César DBA :::…

Dica de hoje é bem simples de como habilitar o wal e gerar um backup físico do seu PostgreSQL.

1 – Verificar parâmetros necessários:

postgres=# SHOW archive_mode;
postgres=# SHOW archive_command;

SQL

2- Criar diretórios dos backups e wal:

mkdir -p /backups/archivelog/
mkdir -p /backups/logs
mkdir -p /backups/scripts

SQL

3- Alterar parâmetros para habilitar a geração de wal:

postgres=# ALTER SYSTEM SET archive_mode TO 'on';
ALTER SYSTEM
postgres=# ALTER SYSTEM SET archive_command TO 'test ! -f /backups/archivelog/%f && cp %p /backups/archivelog/%f';
ALTER SYSTEM

SQL

4 – Forçar a geração do wal a cada 30 minutos:

postgres=# ALTER SYSTEM SET archive_timeout TO '1800';
ALTER SYSTEM

SQL

5 – Reiniciar PostgreSQL:

[root@vm-postgresql ~]# systemctl restart postgresql-12

SQL

6 – Gerar wal manualmente:

[postgres@vm-postgresql ~]$ psql -c "select pg_switch_wal();"
 pg_switch_wal
---------------
 0/16BCDA8
(1 row)

SQL

7 – Verificando wal gerado:

[postgres@vm-postgresql log]$ ls -lat /backups/archivelog/
total 16384
-rw------- 1 postgres postgres 16777216 Mar  5 10:31 000000010000000000000001
drwxr-xr-x 2 postgres postgres       38 Mar  5 10:31 .
drwxr-xr-x 4 postgres postgres       36 Mar  5 10:13 ..

SQL

8 – Gerando backup com compressão:

[postgres@vm-postgresql ~]$ pg_basebackup -Ft -X none -D /backups/full/`date +%Y%m%d` --verbose --progress --compress=9 2>> /backups/logs/backup_full_`date +%Y%m%d`.log 1>>/backups/logs/backup_full.err

SQL

9 – Backup gerado:

[postgres@vm-postgresql backups]$ ls -lat /backups/full/20200305/
total 2956
-rw------- 1 postgres postgres 3025922 Mar  5 10:35 base.tar.gz
drwx------ 2 postgres postgres      25 Mar  5 10:34 .
drwxr-xr-x 3 postgres postgres      22 Mar  5 10:34 ..

SQL

10 – Limpeza de wal com mais de 7 dias:

/usr/bin/find /backups/archivelog -name "*.*" -type f -mtime +7 -exec rm -f {} \;
find /backup/backup_full -empty -type d -delete

SQL

Leave a Reply Cancel reply