1. Overview
In previous blog post, we discussed the process of performing backup and restore operations using pg_rman. pg_rman is a user-friendly tool that supports full and incremental backups and restores for PostgreSQL, operating at the file level. In this blog, I will guide you through a potential method for restoring a specific table using pg_rman.
2. Adding more options to pg_rman
For an understanding of how pg_rman functions, you can refer to the official documentation. Here, we will introduce additional parameters to allow users to specify a table using its OID and an option for performing an online restore without restarting the PostgreSQL server. Here are the details about these options:
---target-table-oid restore the targeted table only
---target-table-oid-new restore the targeted table to the new targeted table only
---online-restore restore the targeted table without stopping PostgreSQL server!!!
This experiment is based on PostgreSQL 15.3 release, but updating it to the latest version should be straightforward. Follow the steps below to experience this specific table recovery:
2.1. Checkout PostgreSQL 15.3, build, and install
git clone https://github.com/postgres/postgres.git
cd postgres
git checkout REL_15_3
./configure --prefix=/tmp/pgapp && make -j && make install
2.2. Download pg_rman source code
wget https://github.com/ossc-db/pg_rman/archive/refs/tags/V1.3.15.tar.gz
tar xzvf V1.3.15.tar.gz
cd pg_rman-1.3.15
2.3. Download, apply the experimental patch, and build pg_rman
wget https://raw.githubusercontent.com/HighgoSoftware/pg-lecture-resources/main/ch6/section2/0001-to-restore-a-specific-table-using-oid.patch
patch < 0001-to-restore-a-specific-table-using-oid.patch
make && make install
2.4. Run the experimental tests
To simplify this blog post, I will first explain the process of recovering a specific table without any schema changes. Subsequently, I will elaborate on the steps to recover a specific table with schema modifications.
2.4.1 Setup PostgreSQL database cluster
mkdir -p /tmp/rman/pglog/ /tmp/rman/archive
initdb -D /tmp/rman/pgdata
echo "archive_mode = on" >> /tmp/rman/pgdata/postgresql.conf
echo "archive_command = 'cp %p /tmp/rman/archive/%f'" >> /tmp/rman/pgdata/postgresql.conf
pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile start
pg_rman init -B /tmp/rman/backup -D /tmp/rman/pgdata
2.4.2 Create tables and insert records
psql -d postgres -c "CREATE TABLE abc (ID INT);"
psql -d postgres -c "CREATE TABLE xyz (ID INT);"
psql -d postgres -c "INSERT INTO abc VALUES (1);"
psql -d postgres -c "INSERT INTO xyz VALUES (1);"
psql -d postgres -c "SELECT count(*) from abc;"
psql -d postgres -c "SELECT count(*) from xyz;"
2.4.3 Run a full backup using pg_rman
pg_rman backup --backup-mode=full --with-serverlog -B /tmp/rman/backup -D /tmp/rman/pgdata -A /tmp/rman/archive -p 5432 -d postgres
pg_rman validate -B /tmp/rman/backup
pg_rman show -B /tmp/rman/backup
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2023-12-18 15:10:28 2023-12-18 15:10:30 FULL 48MB 1 OK
2.4.4 Insert more records and check target table oid
psql -d postgres -c "INSERT INTO abc VALUES (2);"
psql -d postgres -c "INSERT INTO xyz VALUES (2);"
psql -d postgres -c "SELECT count(*) from abc;"
count
-------
2
(1 row)
psql -d postgres -c "SELECT count(*) from xyz;"
count
-------
2
(1 row)
psql -d postgres -c "SELECT oid FROM pg_class WHERE relname = 'abc' AND relkind = 'r';"
oid
-------
16384
(1 row)
2.4.5 Stop Postgres database cluster and restore targetted table using oid
pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile stop
$ pg_rman restore -B /tmp/rman/backup -D /tmp/rman/pgdata --recovery-target-time="2023-12-18 15:10:30" --target-table-oid="16384"
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of current database cluster as recovery target: 1
INFO: calculating timeline branches to be used to recovery target point
INFO: searching latest full backup which can be used as restore start point
INFO: found the full backup can be used as base in recovery: "2023-12-18 15:10:28"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: >>>>>>>>>> targeted table restore: delete oid file [/tmp/rman/pgdata/base/5/16384].
INFO: validate: "2023-12-18 15:10:28" backup, archive log files and server log files by SIZE
INFO: backup "2023-12-18 15:10:28" is valid
INFO: restoring database files from the full mode backup "2023-12-18 15:10:28"
INFO: >>>>>>>>>> targeted table restore: found [base/5/16384].
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2023-12-18 15:10:28" is valid
INFO: restoring WAL files from backup "2023-12-18 15:10:28"
INFO: >>>>>>>>>> targeted table restore: skip online backup
INFO: create pg_rman_recovery.conf for recovery-related parameters.
INFO: >>>>>>>>>> targeted table restore: skip online recovery
INFO: restore complete
HINT: >>>>>>>>>> Targeted table [16384] restore is done, we recommend to run a full backup now.
2.4.6 Start Postgres cluster and verify if database recovered
pg_ctl -D /tmp/rman/pgdata -l /tmp/rman/pglog/logfile start
psql -d postgres -c "SELECT count(*) from abc;"
count
-------
1
(1 row)
psql -d postgres -c "SELECT count(*) from xyz;"
count
-------
2
(1 row)
psql -d postgres -c "INSERT INTO abc VALUES (3);"
psql -d postgres -c "INSERT INTO xyz VALUES (3);"
psql -d postgres -c "SELECT count(*) from abc;"
count
-------
2
(1 row)
psql -d postgres -c "SELECT count(*) from xyz;"
count
-------
3
(1 row)3. Summary
This blog post provides a straightforward method to experience the restoration of a specific table using pg_rman backups. Through this experimental journey, we aim to enhance your understanding of how physical file-level backup and restore operations function in PostgreSQL.
A software developer specialized in C/C++ programming with experience in hardware, firmware, software, database, network, and system architecture. Now, working in HighGo Software Inc, as a senior PostgreSQL architect.
Recent Comments