PostgreSQL database server administration

Backup and Restore Basics (Development / Learning environment)

There are different types of backup and restore for the PostgreSQL database as below —

PostgreSQL Logical Backup SQL dump (or Logical)
PostgreSQL Physical Backup File system-level backup (or Physical)
Continuous archiving (or Point In Time Recovery)

In this article I will go through with Logical backup and physical backup for giving a very good understanding with PostgreSQL backup and recovery processes.

The continuous archiving method combines a base backup or a file-system-level backup and ongoing backup of the WAL files (that stores each change made to the data files of the database for point in time recovery. We can use the same method for setting up a replication and the activate as a master or independent database if needed.

For our testing we are going to create a database, a table and then insert some data -

+++++++++++++++++++++++++++++++++++
Create a dummy database and tables
+++++++++++++++++++++++++++++++++++
CREATE DATABASE test_bkp;
\c test_bkp
CREATE TABLE employee(id INTEGER, f_name VARCHAR(20), l_name VARCHAR(20));

INSERT INTO course VALUES (1, ‘Physics’),(2, ‘Chemistry’),(3, ‘Math’);

CREATE TABLE course(id INTEGER, subject VARCHAR(20));
INSERT INTO employee(id, f_name, l_name) VALUES (1, ‘Eammon’, ‘Armin’), (2, ‘Monowar’, ‘Kachu’), (3, ‘Jodie’, ‘Rel’), (4, ‘Sanjay’, ‘Adrian’);

\dt+

SELECT * FROM employee;

SELECT * FROM course;

++++++++++++++++++++
Backup / restore with a single tables
++++++++++++++++++++
Backup
i. Create a backup directory
mkdir -p /var/lib/pgsql/tst_backup
ii. take a backup
pg_dump -U postgres -W -t employee test_bkp > /var/lib/pgsql/tst_backup/employee.sql.
iii. Validate backup
ls -a /var/lib/pgsql/tst_backup

Restore:
i. connect to the database and drop the tables
psql
\c test_bkp
DROP TABLE employee;
ii. Restore table
psql -U postgres -d test_bkp -f /var/lib/pgsql/tst_backup/employee.sql
validate:
\dt
+++++++++++++++++++++++
Backup / restore with a multiple tables
+++++++++++++++++++++++
Backup:
We are going to use the same as above but we are going to use multiple tables
i. take a backup
pg_dump -U postgres -t course -t employee -d test_bkp > /var/lib/pgsql/tst_backup/mul_tables.sql

Restore:
i. connect to the database and drop the tables
drop tables course;
ii. Restore table
$ psql -U postgres -d test_bkp -f /var/lib/pgsql/tst_backup/mul_tables.sql
validate:
\dt

++++++++++++++++++++
Backup / restore A single database
++++++++++++++++++++
i. take a database level backup
pg_dump -U postgres -W -C -d test_bkp > /var/lib/pgsql/tst_backup/bkp_db_tst.sql

-W to ask for password
-C to add the create database sql during backup

Restore:
i. connect to the database and drop the tables
DROP DATABASE test_bkp;
ii. Restore table
$ psql -U postgres -d test_bkp -f /var/lib/pgsql/tst_backup/bkp_db_tst.sql

+++++++++++++++++
Backup / restore All Databases
+++++++++++++++++
pg_dump only creates a backup of one database at a time, for cluster-wide configuration, we need to use pg_dumpall

Create a backup file:
=====================
pg_dumpall -U postgres -f /var/lib/pgsql/tst_backup/all_db.sql

Restore
========
psql -f /var/lib/pgsql/tst_backup/all_db.sql postgres

++++++++++++++++++++
Backup Using pg_basebackup
++++++++++++++++++++

Create a backup file:
pg_basebackup — for taking full filesystem-level backup of a PostgreSQL database cluster.

— to include transactions during the backup we need to add option -X
$ mkdir /var/lib/pgsql/tst_fullbkp
$ pg_basebackup -X “stream” -D /var/lib/pgsql/tst_fullbkp

validate backup:
$ ls -lt /var/lib/pgsql/tst_fullbkp

RESTORING FROM PG_BASEBACKUP FILES
The files created by pg_basebackup is an exact, consistent mirror of the files under $PGDATA ,
To restore that —
i. stop PostgreSQL services
$ sudo systemctl stop postgresql-12
ii. Delete / Move contents from the data directory (from psql query: show data_directory;)
$ rm -rf /var/lib/pgsql/12/data/*
iii. Copy the backup files to the data directory
cp -r /var/lib/pgsql/tst_fullbkp/* /var/lib/pgsql/12/data/
iv. Start PostgreSQL services
sudo systemctl status postgresql-12

NOTE:
To avoid prompt for the password. we create a .pgpass file in your home directory

cd /var/lib/pgsql
$ vi .pgpass
$ chmod 600 ~/.pgpass
$ export PGPASSFILE=~/.pgpass

Note: If you get issue during database drop — please use below method to get the processes using by that particular database.

postgres=# drop database dempdb;
ERROR: database “dempdb” is being accessed by other users
DETAIL: There is 1 other session using the database.
postgres=# select * from pg_stat_activity
where datname = ‘dempdb’;
select datid, datname, pid from pg_stat_activity;

Cancel the session
==================
SELECT pg_cancel_backend(14320);
— if not then kill session;

In this article, I have gone through with different methods to backup and restore PostgreSQL database objects, database and services for the beginner level using pg_dump, pg_dumpall and pg_basebackup utilities.