More

    PostgreSQL 13 – Continuous Archiving -Complete Setup

    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/

    Recent Articles

    spot_img

    Related Stories

    1 Comment

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox