PostgreSQL 16 pg_basebackup and Point in Time Recovery

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

*****************************************************************

# 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; / 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.