inchirags@gmail.com Chirags PostgreSQL DBA Tutorial https://www.chirags.in
PostgreSQL 16 pg_basebackup and Point in Time Recovery
In Hindi:
In English:
//Install PostgreSQL
$ sudo apt update
$ sudo apt install -y postgresql postgresql-contrib
Introduction
PostgreSQL is a widely used relational database that supports ACID transactions. The acronym ACID stands for atomicity, consistency, isolation, and durability. These are four key properties of database transactions that PostgreSQL supports to ensure the persistence and validity of data in the database.
One method PostgreSQL uses to maintain ACID properties is Write-Ahead Logging (WAL). PostgreSQL first records any transaction on the database to the WAL log files before it writes the changes to the database cluster’s data files.
Step 1 — Configuring Continuous Archiving on the Database Cluster
root@dept:~#
cd /var/lib/postgresql/16
mkdir database_archive
sudo chown postgres:postgres database_archive
sudo nano /etc/postgresql/16/main/postgresql.conf
. . .
archive_mode = on
archive_command = ‘test ! -f /var/lib/postgresql/16/database_archive/%f && cp %p /var/lib/postgresql/16/database_archive/%f’
wal_level = replica
…
sudo systemctl restart postgresql@16-main
sudo -u postgres psql -c "SELECT pg_switch_wal();"
sudo -u postgres psql -c "SHOW data_directory;"
sudo -u postgres psql
postgres=# create database chirags_db1;
postgres=# \c chirags_db1;
chirags_db1=# CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(100),age INT);
chirags_db1=# INSERT INTO users (name, age) VALUES ('Chirag Mahto', 35);
chirags_db1=# INSERT INTO users (name, age) VALUES ('Sanju Mehta', 32);
chirags_db1=# select from users; / 2 records */
Output:
id | name | age
—-+————–+—–
1 | Chirag Mahto | 35
2 | Sanju Mehta | 32
(2 rows)
chirags_db1=# select now();
now
2024-09-05 06:52:18.743043+05:30
(1 row)
chirags_db1=# SELECT pg_switch_wal();
pg_switch_wal
0/242FE18
(1 row)
chirags_db1=# \q
Step 2 — Performing a Physical Backup of the PostgreSQL Cluster
root@dept:~#
cd /var/lib/postgresql/16
mkdir database_backup
sudo chown postgres:postgres database_backup
root@dept:~# sudo -u postgres pg_basebackup -D /var/lib/postgresql/16/database_backup
root@dept:~# su – postgres
postgres@dept:~$ psql
psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
Type “help” for help.
postgres=# \c chirags_db1;
chirags_db1=# CREATE TABLE accounts (account_number SERIAL PRIMARY KEY, account_holder VARCHAR(100), balance DECIMAL(10, 2));
chirags_db1=# INSERT INTO accounts (account_holder, balance) VALUES (‘Arjun’, 1000.00);
INSERT INTO accounts (account_holder, balance) VALUES (‘Purab’, 500.00);
chirags_db1=# select * from accounts;
Output:
account_number | account_holder | balance
—————-+—————-+———
1 | Arjun | 1000.00
2 | Purab | 500.00
(2 rows)
chirags_db1=# select now();
now
2024-09-05 06:57:35.511587+05:30
(1 row)
chirags_db1=# SELECT pg_switch_wal();
pg_switch_wal
0/5019348
(1 row)
sanju=# \q
postgres@dept:~$ exit
Step 3 — Performing Point-In-Time-Recovery on the Database Cluster
root@dept:~#
sudo systemctl stop postgresql@16-main
sudo mv /var/lib/postgresql/16/main/pg_wal ~/
sudo rm -rf /var/lib/postgresql/16/main
sudo mkdir /var/lib/postgresql/16/main
sudo cp -a /var/lib/postgresql/16/database_backup/. /var/lib/postgresql/16/main/
sudo chown postgres:postgres /var/lib/postgresql/16/main
sudo chmod 700 /var/lib/postgresql/16/main
sudo rm -rf /var/lib/postgresql/16/main/pg_wal
sudo cp -a ~/pg_wal /var/lib/postgresql/16/main/pg_wal
sudo cp /var/lib/postgresql/16/database_archive/* /var/lib/postgresql/16/main/pg_wal
root@dept:~# sudo nano /etc/postgresql/16/main/postgresql.conf
. . .
restore_command = ‘cp /var/lib/postgresql/16/database_archive/%f %p’
. . .
root@dept:~# sudo touch /var/lib/postgresql/16/main/recovery.signal
root@dept:~# sudo systemctl start postgresql@16-main
root@dept:~# sudo systemctl status postgresql@16-main
root@dept:~# sudo -u postgres psql
psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.2))
Type “help” for help.
postgres=# \c chirags_db1
chirags_db1=# \dt
List of relations
Schema | Name | Type | Owner
——–+—————-+——-+———-
public | accounts | table | postgres
public | random_numbers | table | postgres
public | test_tbl1 | table | postgres
public | users | table | postgres
(4 rows)
chirags_db1=# select * from accounts;
account_number | account_holder | balance
—————-+—————-+———
1 | Arjun | 1000.00
2 | Purab | 500.00
(2 rows)
chirags_db1=#
root@dept:~# su – postgres
postgres@dept:~$ psql
psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
Type “help” for help.
postgres=# select pg_wal_replay_resume();
Note : Flow the Process shown in video.
😉Please Subscribe for more videos:
https://www.youtube.com/@chiragtutorial
💛Don’t forget to, 💘Follow, 💝Like, Share 💙&, Comment
Thanks & Regards,
Chitt Ranjan Mahto “Chirag”
Note: All scripts used in this demo will be available in our website.
Link will be available in description.
