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 

You now need to give the default PostgreSQL user, postgres, permission to write to this directory. You can achieve this by changing the ownership of the directory using the chown command:

sudo chown postgres:postgres database_archive 

Open the configuration file with your text editor: and enable archive logging

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 

Take basebackup

root@dept:~# sudo -u postgres pg_basebackup -D /var/lib/postgresql/16/database_backup

Now add some more records

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 ~/

destroy data directory

sudo rm -rf /var/lib/postgresql/16/main 

manually create data directory

sudo mkdir /var/lib/postgresql/16/main 

Restoration :

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’

recovery_target_time = ‘2024-08-30 01:32:23’

recovery_target = ‘immediate’

. . .

root@dept:~# sudo touch /var/lib/postgresql/16/main/recovery.signal

Start PostgreSQL Services

root@dept:~# sudo systemctl start postgresql@16-main

Check status PostgreSQL Services

root@dept:~# sudo systemctl status postgresql@16-main

Now try to add some more records

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=#

this database is in read-only mode.

run below command for remove read-only mode.

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.

Source: DEV Community.