Skip to content

Latest commit

 

History

History
51 lines (37 loc) · 4.51 KB

WAL Archive & PITR.md

File metadata and controls

51 lines (37 loc) · 4.51 KB

WAL is short for Write-Ahead-Log. Any change to the data is first recorded in a WAL file. The WAL files are mainly used by RDBMS as a way to achieve durability and consistency while writing data to storage systems.

Before we move forward, let’s first see why we need a WAL archiving and Point in Time Recovery (PITR). Consider if you have accidentally dropped some table(s) or deleted some data? How do you recover from such mistakes? WAL archiving and PITR is the answer to that. A WAL file can be replayed on a server to recreate the recorded changes on that server. Hence we can use the WALs to recover from such dangerous situations. Whereas the PITR is a way to stop the replay of WALs at the specified point and have a consistent snapshot of data at that time. i.e. just before the table is dropped or data is removed.

How to Perform WAL Archiving

Normally, PostgreSQL databases keep the WAL files in the pg_wal directory of the $PGDATA. However, these WAL files may get recycled and can be deleted/overwritten by the server. So to avoid such scenarios, we keep a copy of WAL files in a separate directory other than $PGDATA. In order to do that, the PostgreSQL server provides us a way to copy the WAL file to a different location as soon as a WAL file is generated by the server. This way depends on three commands (options) namely archive_mode, archive_command, and wal_level. These options can be set in the $PGDATA/postgresql.conf configuration file. Archiving Options

PostgreSQL server provides us with some options through which we can control the WAL archiving. Let’s see what these options are and how to use them.

archive_mode signifies whether we want to enable the WAL archiving. It can accept the following values:
    on – to enable the archiving
    off – disable the archiving
    always – normally this option is the same as ‘on’. This enables archiving for a standby server as well. If the standby shares the same path with another server, it may lead to WAL file corruption. So care must be taken in this case.
archive_command specifies how to archive (copy) the WAL files and where. This option accepts the shell command or a shell script. Which is executed whenever there is a WAL file generated by the server to archive it. This option accepts the following placeholders:
    %f – if present it’s replaced with the filename of the WAL file.
    %p – if present it is replaced with the pathname of the WAL file.
    %% – is replaced with ‘%’
wal_level is another important option. In PostgreSQL version 10+, it defaults to ‘replica’, prior to this version it was set to minimal by default. wal_level accepts the following values:
    Minimal – adds only the information that is required for crash recovery or from immediate shutdown. It’s not usable for replication or archiving purposes.
    Replica – signifies that WAL will have enough information for WAL archiving and replication.
    Logical – adds information required for logical replication.

an example of wal archive.

vim $PGDATA/postgresql.conf
archive_mode = on
archive_command = ‘cp %p /path/to//archive_dir/%f’
wal_level = replica

Point In Time Recovery

In PostgreSQL, PITR is a way to stop the replay of WAL files at an appropriate point in time. There can be many WAL files in the archive but we may not want to replay all of them. Replaying all WALs will result in the same state where we had made some mistake. There are two important prerequisites required for PITR to work.

  1. Availability of a full base backup (usually taken with pg_basebackup)
  2. WAL files (WAL archive)

In order to achieve the PITR, the first step would be to restore an earlier taken base backup and then create a recovery setup. The setup requires configuring restore_command and recovery_target options.

  1. restore_command specifies from where to look up the WAL files to replay on this server. This command accepts the same placeholders as archive_command.
  2. recovery_target_time This option tells the server when to stop the recovery or replay process. The process will stop as soon as the given timestamp is reached.

recovery_target_inclusive This option controls whether to stop the replay of WALs just after the recovery_target_time is reached (if set to true) or just before (if set to false). an example of PITR recovery options.

vim $BACKUP/postgresql.con
restore_command = ‘cp  /path/to//archive_dir/%f %p’
recovery_target_time = ‘’
recovery_target_inclusive = false