Author: Zhang Lianzhuang PostgreSQL research engineer

Engaged in PostgreSQL database kernel development for many years, have a very deep research on CITus.

Quickly retrieving lost data is an important function for a database. You are generally advised to use an official tool. For open source databases, there are many useful open source tools in the ecosystem.

PostgreSQL is a popular open source database. In this article, we introduce pg_recovery, a PostgreSQL data recovery tool, and demonstrate how to recover lost data.

What is pg_recovery |?

Pg_recovery is a PostgreSQL data recovery tool. You can restore the data changes caused by the COMMIT/DELETE/UPDATE/ROLLBACK/DROP COLUMN operations and return them as tables. Easy to install and operate. Warehouse address: github.com/radondb/pg_…

Fast installation

Configure PG_CONFIG based on the environment.

$ 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/'Copy the code

The following information is displayed when the plug-in is successfully initialized:

$ create extension pg_recovery ;
CREATE EXTENSION
Copy the code

| back data 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
Copy the code

2. Retrieve UPDATE data

Modify data without a 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)
Copy the code

3. Retrieve DELETE data

Try to restore the data for 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)
Copy the code

4. Retrieve ROLLBACK data

Try to restore 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)
Copy the code

5. Retrieve DROP COLUMN data

Attempt to restore deleted columns and data from 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
Copy the code

6. Display the retrieved data

Displays all data written 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)
Copy the code

Matters needing attention

  • Supported PostgreSQL version

Currently, pg_revovery supports PostgreSQL 12/13/14.

  • Number of recoverable transactions

PostgreSQL limits the number of transactions that can be restored by the value of the vacuum_DEFER_CLEANup_age parameter. If a large amount of data needs to be recovered, you can configure parameter values to increase the number of transactions that can be recovered.

Pg_recovery reads the PostgreSQL dead tuple to recover invisible table data. If the tuple is vacuumed, pg_recovery cannot recover the data.

  • Lock request

When pg_recovery is in use, it supports normal lock requests to read tables. In addition, pg_recovery does not cause any additional overhead or impact to the database while it is not in use, and there is no need to suspend services.