Author: Zhang Lianzhuang, PostgreSQL R & D Engineer

He has been engaged in the development of PostgreSQL database kernel for many years and has a very in-depth research on citus.

Recovering lost data quickly is an important functional requirement of the database. It is generally recommended to use the tools recommended by the official. For open source databases, there are many easy-to-use open source tools in the ecosystem.

PostgreSQL is a very popular open source database. Next, we will introduce a recently open source PostgreSQL data retrieval tool pg_recovery, and an example demonstrates how to retrieve the data lost due to misoperation.

|What is pg_recovery?

pg_recovery is a PostgreSQL data retrieval tool. The data changes caused by the COMMIT / DELETE / UPDATE / ROLLBACK / DROP COLUMN operation can be recovered and returned in the form of a table. Easy installation and simple operation. Warehouse address: https://github.com/radondb/pg_recovery

express setup

Configure PG according to environment_ CONFIG.

$ make PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -I. -I./ -I/home/lzzhang/PG/postgresql/base/include/server -I/home/lzzhang/PG/postgresql/base/include/internal  -D_GNU_SOURCE   -c -o pg_recovery.o pg_recovery.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O0 -fPIC -shared -o pg_recovery.so pg_recovery.o -L/home/lzzhang/PG/postgresql/base/lib    -Wl,--as-needed -Wl,-rpath,'/home/lzzhang/PG/postgresql/base/lib',--enable-new-dtags  

$ make install PG_CONFIG=/home/lzzhang/PG/postgresql/base/bin/pg_config
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/lib'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/mkdir -p '/home/lzzhang/PG/postgresql/base/share/extension'
/usr/bin/install -c -m 755  pg_recovery.so '/home/lzzhang/PG/postgresql/base/lib/pg_recovery.so'
/usr/bin/install -c -m 644 .//pg_recovery.control '/home/lzzhang/PG/postgresql/base/share/extension/'
/usr/bin/install -c -m 644 .//pg_recovery--1.0.sql  '/home/lzzhang/PG/postgresql/base/share/extension/'

The plug-in is initialized successfully. The following information is returned.

$ create extension pg_recovery ;
CREATE EXTENSION

|Data retrieval demonstration

1. Prepare initialization data

Prepare a table and some data.

$ create table lzzhang(id int, dp int);
CREATE TABLE
# insert into lzzhang values(1, 1);
INSERT 0 1
$ insert into lzzhang values(2, 2);
INSERT 0 1

2. Retrieve UPDATE data

Change the data without the WHERE condition.

$ update lzzhang set id=3, dp=3;
UPDATE 2
lzzhang=# select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
(2 rows)

$ select * from lzzhang;
 id | dp 
----+----
  3 |  3
  3 |  3
(2 rows)

3. Retrieve DELETE data

Attempt to recover data from DELETE.

$ delete from lzzhang;
DELETE 2
lzzhang=# select * from lzzhang;
 id | dp 
----+----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
(4 rows)

4. Retrieve ROLLBACK data

An attempt was made to recover the data before the rollback operation.

$ begin ;
BEGIN
$ insert into lzzhang values(4, 4);
INSERT 0 1
$ rollback ;
ROLLBACK
$ select * from lzzhang;
 id | dp 
----+----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dp int);
 id | dp 
----+----
  1 |  1
  2 |  2
  3 |  3
  3 |  3
  4 |  4
(5 rows)

5. Retrieve DROP COLUMN data

Attempt to recover deleted columns and data in the table.

$ alter table lzzhang drop column dp;
ALTER TABLE
$ select attnum from pg_attribute, pg_class where attrelid = pg_class.oid and pg_class.relname='lzzhang' and attname ~ 'dropped';
 attnum 
--------
      2
(1 row)

$ select * from lzzhang;
 id 
----
(0 rows)

$ select * from pg_recovery('lzzhang') as (id int, dropped_attnum_2 int);
 id | dropped_attnum_2 
----+------------------
  1 |                1
  2 |                2
  3 |                3
  3 |                3
  4 |                4
(5 rows)

-- dropped_attnum_2: if the drop attnum is 5, set dropped_attnum_2 to dropped_attnum_5

6. Display retrieved data

Displays all written data in the history of the table.

$ insert into lzzhang values(5);
INSERT 0 1
$ select * from lzzhang;
 id 
----
  5
(1 row)

$ select * from pg_recovery('lzzhang', recoveryrow => false) as (id int, recoveryrow bool);
 id | recoveryrow 
----+-------------
  1 | t
  2 | t
  3 | t
  3 | t
  4 | t
  5 | f
(6 rows)

matters needing attention

• Supported PostgreSQL versions

Current PG_ Retrovery tool supports PostgreSQL 12/13/14.

• Number of recoverable transactions

PostgreSQL passes the parameter vacuum_ defer_ cleanup_ The age value limits the number of recoverable transactions. If the amount of data to be recovered is expected to be large, the number of recoverable transactions can be increased by configuring parameter values.

pg_recovery recovers invisible table data by reading PostgreSQL dead tuples. If the tuple is cleared by vacuum, pg_recovery cannot recover data.

• Lock request

pg_ During the use of recovery, it supports normal lock requests for reading tables. In addition, pg_ When recovery is not in use, it will not cause any additional overhead or impact on the database, and there is no need to suspend the service.