PostgreSQL 16 pg_basebackup and Point in Time Recovery
[email protected] Chirags PostgreSQL DBA Tutorial https://www.chirags.in
******
PostgreSQL 16 pg_basebackup and Point in Time Recovery
******
Tutorial Link:
https://www.chirags.in/tutorial/index...
//Install PostgreSQL
$ sudo apt update
$ sudo apt install -y postgresql postgresql-contrib
-----
Step 1 — Configuring Continuous Archiving on the Database Cluster
****
create directory for archive logs
root@dept:~#
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
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
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
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'
. . .
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
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.
root@dept:~# su - postgres
postgres@dept:~$ psql
postgres=# select pg_wal_replay_resume();
#postgres
#postgresql
#pointintimerecovery
#postgresqlTutorial
#pg_basebackup
#chiragstutorial
#chiragsdatabasetutorial
#database
#psql
#pgbouncer
#patroni
Watch video PostgreSQL 16 pg_basebackup and Point in Time Recovery online without registration, duration hours minute second in high quality. This video was added by user Chirags Tutorial 04 September 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 6 once and liked it people.