Currently, commonly used data recovery methods basically rely on backups for recovery, including logical backups and physical backups.
Logical Backup and Recovery: Using dump-like commands that come with the database, or when using a graphical interface to perform import/export, it's based on this dump command underneath.
Advantages: Simple, easy to operate.
Disadvantages: When the amount of data is large, this method is extremely slow; exporting or importing might take a whole day and still not complete all data.
Physical Backup and Recovery: Find the storage location of the current database data files on disk, directly copy one or more copies of the data files, and store them on different physical machines.
Advantages: Faster recovery compared to logical backup.
Disadvantages: When backing up data, data might still be being written, which can lead to data loss to some extent. When recovering data, you also need to ensure the database version and environment are highly consistent. If it's a production database running online, this copying method cannot be implemented in a production environment.
The above two backup and recovery methods can ensure correct and complete data recovery. However, in daily work, users or developers might accidentally delete a small amount of data, but with a large impact, requiring timely and rapid online data recovery. This requires a tool that can recover data from erroneous operations in the shortest possible time.
pg_recovery can quickly recover data from erroneous operations in the halo database. This tool supports data recovery after update, delete, rollback, and drop column operations.
I. Download, Compile, and Install
GitHub download address: https://github.com/radondb/pg_recovery
[halo@localhost src]$ ls -rlt pg_recovery-master.zip
-rw-r--r-- 1 root root 13023 Oct 30 10:19 pg_recovery-master.zip
[halo@localhost ~]$ unzip pg_recovery-master.zip
Archive: pg_recovery-master.zip
886fc628534b43eb27344aaa07aabcc85f4d0b0e
creating: pg_recovery-master/
inflating: pg_recovery-master/.gitignore
inflating: pg_recovery-master/LICENSE
inflating: pg_recovery-master/Makefile
inflating: pg_recovery-master/README.md
inflating: pg_recovery-master/README_zh_CN.md
creating: pg_recovery-master/expected/
inflating: pg_recovery-master/expected/recovery.out
inflating: pg_recovery-master/pg_recovery--1.0.sql
inflating: pg_recovery-master/pg_recovery.c
inflating: pg_recovery-master/pg_recovery.control
creating: pg_recovery-master/sql/
inflating: pg_recovery-master/sql/recovery.sql[halo@localhost pg_recovery-master]$ cd pg_recovery-master/
[halo@localhost pg_recovery-master]$ make PG_CONFIG=/u01/app/halo/product/dbms/14/bin/pg_config
[halo@localhost pg_recovery-master]$ make install PG_CONFIG=/u01/app/halo/product/dbms/14/bin/pg_config
/bin/mkdir -p '/u01/app/halo/product/dbms/14/lib/postgresql'
/bin/mkdir -p '/u01/app/halo/product/dbms/14/share/postgresql/extension'
/bin/mkdir -p '/u01/app/halo/product/dbms/14/share/postgresql/extension'
/bin/install -c -m 755 pg_recovery.so '/u01/app/halo/product/dbms/14/lib/postgresql/pg_recovery.so'
/bin/install -c -m 644 .//pg_recovery.control '/u01/app/halo/product/dbms/14/share/postgresql/extension/'
/bin/install -c -m 644 .//pg_recovery--1.0.sql '/u01/app/halo/product/dbms/14/share/postgresql/extension/'II. Create Extension
halo0root=# create extension pg_recovery;
CREATE EXTENSION
halo0root=# \dx pg_recovery
List of installed extensions
Name | Version | Schema | Description
------------+---------+--------+-----------------------------------------------------------------------
pg_recovery | 1.0 | public | recovery table data of update/delete/rollback rows and drop columns
(1 row)2.1 Create Test Data
halo0root=# create table halo_test(id int,name varchar(20));
CREATE TABLE
halo0root=# insert into halo_test values(1,'Hangzhou');
INSERT 0 1
halo0root=# insert into halo_test values(2,'Beijing');
INSERT 0 1
halo0root=# select * from halo_test;
id | name
----+------
1 | Hangzhou
2 | Beijing
(2 rows)2.2 Simulate Recovery of Data Modified by UPDATE (recovery update)
halo0root=# update halo_test set id=3,name='Chongqing' where id=1 and name='Hangzhou';
UPDATE 1
halo0root=# update halo_test set id=4,name='Tianjin' where id=2 and name='Beijing';
UPDATE 1
halo0root=# select * from halo_test;
id | name
----+------
3 | Chongqing
4 | Tianjin
(2 rows)
halo0root=# select*from pg_recovery('halo_test')as(id int,name varchar(20));
id | name
----+------
1 | Hangzhou
2 | Beijing
(2 rows)2.3 Simulate Recovery of Data Deleted by DELETE (recovery delete)
halo0root=# delete from halo_test;
DELETE 2
halo0root=# select * from halo_test;
id | name
----+------
(0 rows)
halo0root=# select * from pg_recovery('halo_test')as(id int,name varchar(20));
id | name
----+------
1 | Hangzhou
2 | Beijing
3 | Chongqing
4 | Tianjin
(4 rows)2.4 Recover Data Before Rollback Operation (recovery rollback)
halo0root=# begin;
BEGIN
halo0root=*# insert into halo_test values(5,'Nanjing');
INSERT 0 1
halo0root=*# rollback;
ROLLBACK
halo0root=# select * from halo_test;
id | name
----+------
(0 rows)
halo0root=# select * from pg_recovery('halo_test')as(id int,name varchar(20));
id | name
----+------
1 | Hangzhou
2 | Beijing
3 | Chongqing
4 | Tianjin
5 | Nanjing
(5 rows)2.5 Simulate Dropped Column (recovery drop column)
halo0root=# alter table halo_test drop column name;
ALTER TABLE
halo0root=# select attnum from pg_attribute a, pg_class where attrelid = pg_class.oid and pg_class.relname='halo_test' and attname ~ 'dropped';
attnum
--------
2
(1 row)
halo0root=# select * from halo_test;
id
----
(0 rows)
halo0root=# select * from pg_recovery('halo_test') as (id int, dropped_attnum_2 varchar(20));
id | dropped_attnum_2
----+------------------
1 | Hangzhou
2 | Beijing
3 | Chongqing
4 | Tianjin
5 | Nanjing
(5 rows)2.6 Show All Data Ever Written to the Table (show all data)
halo0root=# insert into halo_test values(6);
INSERT 0 1
halo0root=# select * from halo_test;
id
----
6
(1 row)
halo0root=# select*from pg_recovery('halo_test', recoveryrow =>false)as(id int, dropped_attnum_2 varchar(20), recoveryrow bool);
id | dropped_attnum_2 | recoveryrow
----+------------------+-------------
1 | Hangzhou | t
2 | Beijing | t
3 | Chongqing | t
4 | Tianjin | t
5 | Nanjing | t
6 | | f
(6 rows)III. Precautions
pg_recovery performs data recovery by reading dead tuples in PostgreSQL tables.
If the table has been vacuumed or vacuum full performed to clean up dead tuples, pg_recovery cannot recover the table data.
Related parameter vacuum_defer_cleanup_age (integer)
halo0root=# show vacuum_defer_cleanup_age;
vacuum_defer_cleanup_age
--------------------------
0
(1 row)Specifies the number of transactions by which VACUUM and HOT updates should defer cleanup of dead row versions. The default is zero transactions, meaning that dead row versions are cleaned up as soon as possible, i.e., as soon as they are no longer visible to any open transaction.