Share this article to learn about the instant recovery of PG PITR

Author: Yanbo Qingyun technology database R & D Engineer

At present, he is engaged in the development of PostgreSQL products and is keen on the study and research of PostgreSQL database

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
PGHOME=/usr/lib/postgresql/11

# echo "$PGDATA" view the data directory of PostgreSQL
PGDATA=/data/pgsql/main

# Execute show config from the pg command line_ file;  Command view, PostgreSQL configuration file location
PGCONFIG_FILE=/etc/postgresql/11/main/postgresql.conf

# The startup log location of PostgreSQL. When startup fails, you can quickly view the error content through the log
PG_LOG=/data/pglog/start.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) 
values(
generate_series(0,200000), 
substr(md5(random()::text), 0, 25), 
generate_series(0,200000)
);

Perform basic backup

  • PostgreSQL views the current time and specifies the label at the time of backup.
postgres=# select now();
              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');
 pg_start_backup 
-----------------
 0/9000060
(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
 pg_stop_backup 
----------------
 0/9000168
(1 row)
  • Perform a WAL switch to archive the WAL log
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/A000078
(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:

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

  • 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();
              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().
recovery_target_timeline='latest'

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;
 count  
--------
 200001
(1 row)

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

| summary

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

| reference

Tags: Linux PostgreSQL v-im timeline

Posted on Sat, 06 Nov 2021 03:45:52 -0400 by stuartbaggs