Learn about instant recovery of PG PITR

In the database system, data is the basis of everything, and the security of data is the top priority. However, data may be lost or damaged for various reasons, so data backup and recovery is particularly important.

PostgreSQL is a powerful open source object relational database system. After more than 30 years of active development, PostgreSQL has won a good reputation in terms of reliability, functional robustness and performance. Point in time recovery (PITR) is a point in time based data recovery technology of PostgreSQL. After WAL log archiving and basic backup are enabled, it can immediately recover the data lost by user misoperation and add a heavy "insurance" to your database.

This article will demonstrate how PostgreSQL configures PITR data backup and how to restore data immediately after data is deleted by mistake.

Environmental preparation

Prepare database

Prepare the PostgreSQL version 11 database environment.

    # echo "$PGHOME" view

    # echo "$PGDATA" view the data directory of PostgreSQL

    # Execute show config from the pg command line_ file;  Command view, PostgreSQL configuration file location

    # The startup log location of PostgreSQL. When startup fails, you can quickly view the error content through the log

PITR data backup

After the user mistakenly deletes the data, the data of the specified timestamp can be recovered through the PITR. The following describes the detailed steps to start backup and restore data.

Enable WAL log archiving

Enable the WAL log archiving function by modifying the configuration file.

    # Create a directory to save wal log archives and modify the directory owner
    $ mkdir -p /data/wallog_back
    $ chown -R postgres:postgres /data/wallog_back

    # Modify the PostgreSQL database configuration file postgresql.conf
    $ vim /etc/postgresql/11/main/postgresql.conf

    # Modify the following configuration items:
    wal_level = archive
    archive_mode = on    
    archive_command = 'test ! -f /data/wallog_back/%f && cp %p /data/wallog_back/%f' # Configure the archive command, copy the wal file to the specified directory,% p is the actual wal file directory, and% f is the wal file name

    # Restart the database after saving
    root:~# su postgres
    # stop postgresql server
    postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl stop
    # start postgresql server
    postgres:~$ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start 
    -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

If the configuration changes are saved successfully, the WAL log archiving function is turned on.

Prepare data

    # Create test table
    CREATE TABLE test_table(
         ID INT     NOT NULL,
         NAME       TEXT    NOT NULL,
         AGE        INT     NOT NULL

    # Insert test data. Quickly insert 200001 test data through the following command
    insert into test_table(id, name, age) 
    substr(md5(random()::text), 0, 25), 

Perform basic backup

  • PostgreSQL views the current time and specifies the label at the time of backup.

          postgres=# select now();
           2021-11-04 14:50:42.482253+08
          (1 row)
  • Start backup

          # PostgreSQL command line execution 
          postgres=# select pg_start_backup('backup_2021-11-04 14:50:42');
          (1 row)
          # Switch to linux command line execution
          cd /data
          $ sudo tar -cvzf pgsql.tar pgsql
          postgres=# select pg_stop_backup();
          NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
          (1 row)
  • Perform a WAL switch to archive the WAL log

          postgres=# select pg_switch_wal();
          (1 row)
  • View the archive status, if archived_count = 0 represents an exception. You need to check whether the configuration file is successfully modified or try to restart the PostgreSQL service

          postgres=# select * from pg_stat_archiver;

be careful:

  1. After the backup is completed, / data / wallog_ There should be corresponding archive files in the back / directory.

  1. select * from pg_stat_archiver; After the command is executed, if archived_count is 0, which means that the archive setting failed to be enabled. Please recheck the configuration file or restart the service.

PITR data recovery

Simulated deleted data

    # Delete test_ The data with id greater than 10000 in the table simulates the operation of mistakenly deleting data
    postgres=# delete from test_table where id>10000;
    DELETE 190000

    # Query the current time and determine the approximate recovery time point according to this time
    postgres=#  select now();
     2021-11-04 14:56:17.452967+08
    (1 row)

Instant recovery of data

    # Stop database operation
    $ /usr/lib/postgresql/11/bin/pg_ctl stop

    # Rename pgsql directory
    cd /data
    mv pgsql pgsql_back

    # Restore the backup package to the data directory
    sudo tar -zxvf pgsql.tar

    # Configure the recovery.done file in the data directory. If not, create it.
    restore_command='cp /data/wallog_back/%f %p'  # wal archive directory
    recovery_target_time='2021-11-04 14:56:17.452967' # Recover to this time, and judge the recovery time according to the previous select now().

Restart the database

    $ /usr/lib/postgresql/11/bin/pg_ctl -D /data/pgsql/main/ start -o '-c config_file=/etc/postgresql/11/main/postgresql.conf' -l /data/pglog/start.log

Data recovery verification

    postgres=# select * from test_table;
    postgres=# select count(*) from test_table;
    (1 row)

As shown in the figure, the data is recovered to 200001.


After configuring certain conditions, PITR can recover data. After deleting data by mistake, it can help us retrieve data.

reference material:




Pay attention to the official account of Qingyun technology community, and then reply to the keyword "cloud original real combat", then join the course exchange group.


Yanbo Qingyun technology database R & D Engineer

This article is composed of blog one article multi posting platform OpenWrite release!

Tags: Database PostgreSQL cloud computing

Posted on Tue, 09 Nov 2021 15:32:07 -0500 by sysop