inchirags@gmail.com Chirags PostgreSQL DBA Tutorial https://www.chirags.in
*********************************************************************************************
PostgreSQL 16 pg_basebackup and Point in Time Recovery
***********************************************************************************************
YouTube Video:
//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
*****************************************************************
# create directory for archive logs
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; /* 10 recoreds */
Output:
id | name | age
----+--------------+-----
1 | Chirag Mahto | 35
2 | Sanju Mehta | 32
(2 rows)
chirags_db1=# select now();
now
-------------------------------
2024-09-01 18:59:37.202803+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-01 19:58:51.598662+05:30
(1 row)
chirags_db1=# SELECT pg_switch_wal();
pg_switch_wal
---------------
0/5019348
(1 row)
chirags_db1=# \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=#
# if database is in ready only mode then 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.
Subscribe and like for more videos:
https://www.youtube.com/@chiragstutorial
Don't forget to, Follow, Like, Share &, Comment
Tutorial Link :
https://www.chirags.in/tutorials/PostgreSQL_Database
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.
