PostgreSQL 16 pg_basebackup and Point in Time Recovery

****************
PostgreSQL 16 pg_basebackup and Point in Time Recovery
*****************
Tutorial Link:

//Install PostgreSQL
$ sudo apt update
$ sudo apt install -y postgresql postgresql-contrib
-----
Step 1 — Configuring Continuous Archiving
***
# create directory for archive logs
cd /var/lib/postgresql/16
mkdir database_archive

sudo chown postgres:postgres database_archive
# Open the configuration file with your text editor: and enable archive logging
. . .
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 -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
********

cd /var/lib/postgresql/16
mkdir database_backup

sudo chown postgres:postgres database_backup

# Take basebackup

# Now add some more records
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)
chirags_db1=# \q

Step 3 — Performing Point-In-Time-Recovery on the Database Cluster
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
. . .
restore_command = 'cp /var/lib/postgresql/16/database_archive/%f %p'
. . .

# Start PostgreSQL Services
# Check status PostgreSQL Services

# Now try to add some more records

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 this database is in read-only mode.
# run below command for remove read-only mode.

postgres=# select pg_wal_replay_resume();

#postgres
#postgresql
#pointintimerecovery
#postgresqlTutorial
#pg_basebackup
#chiragstutorial
#chiragsdatabasetutorial
#database
#psql
#pgbouncer
#patroni