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 material:
[1]https://www.postgresql.org/
[2]https://blog.csdn.net/arcticJ...
[3]https://my.oschina.net/Kenyon...
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.
author
Yanbo Qingyun technology database R & D Engineer
This article is composed of blog one article multi posting platform OpenWrite release!