Home PostgreSQL PostgreSQL 13 – Continuous Archiving -Complete Setup

PostgreSQL 13 – Continuous Archiving -Complete Setup

1
85

At all times , PostgreSQL maintains Write Ahead Log(WAL) files in ‘pg_wal/’ subdirectory in PostgreSQL 13 version and ‘pg_xlog/’ subdirectory on prior versions. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state.

PostgreSQL data directory contains following folder/files.

Setting Up Archive mode and relevant parameters

Create a folder/directory outside data directory where you want to store your archive logs.In my case I have created folder “/usr/local/var/archivedir” . Thereafter perform following steps.

Method 1:

Change the following parameters in postgresql.conf file and restart the services.

archive_mode = on

archive_command = 'test ! -f /opt/PosgreSQL/13/archivedir/%f && cp %p /opt/PosgreSQL/13/archivedir/%f' //For Linux

archive_command = 'copy "%p"  "D:\\pgarchive\\%f"'      # D:\data\PostgresInstance2\pg_wal    //For Windows

archive_command = 'test ! -f /usr/local/var/archivedir/%f && cp %p /usr/local/var/archivedir/%f'  //For Mac

archive_timeout = 900

max_wal_senders = 10

full_page_writes = on

wal_keep_size = 1024 //wal_keep_size=wal_keep_segments(64)*wal_size(16MB) size in MB

wal_level = replica

Service Restart Command (Data directory after -D command)

pg_ctl status -D /usr/local/var/postgres

pg_ctl stop -D /usr/local/var/postgres

pg_ctl start -D /usr/local/var/postgres

Method2:

Change the following parameters with ALTER SYSTEM command and restart the services. In this case all parameters will be visible in postgresql.auto.conf file.

alter system set archive_mode=on;

alter system set archive_command = 'test ! -f /usr/local/var/archivedir/%f && cp %p /usr/local/var/archivedir/%f';

alter system set archive_timeout = 900;

alter system set max_wal_senders = 10;

alter system set full_page_writes = on;

alter system set wal_keep_size = 1024;

alter system set wal_level = logical;

cat postgresql.auto.conf

Service Restart Command (Data directory after -D command)

pg_ctl status -D /usr/local/var/postgres

pg_ctl stop -D /usr/local/var/postgres

pg_ctl start -D /usr/local/var/postgres

 

Checking post Archival Setup

Create a test table, insert data to see whether archive files are getting generated in the archivedir.

archive files are getting generated

Check my next post to perform Point in Time Recovery

https://dbachamps.com/postresql-13-point-in-time-recovery/