Backup and Restore - 2

 
-bash-4.2$ psql -p 5455
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
Type "help" for help.
postgres=#
postgres=#
postgres=# show version();
ERROR:  syntax error at or near "("
LINE 1: show version();
                    ^
postgres=#
postgres=#
postgres=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
postgres=#
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | emp  | table | postgres
(1 row)
postgres=#
postgres=# create schema test1;
CREATE SCHEMA
postgres=#
postgres=#
postgres=# create schema test2;
CREATE SCHEMA
postgres=#
postgres=#
postgres=#
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=#
postgres=# \dn
      List of schemas
  Name  |       Owner
--------+-------------------
 public | pg_database_owner
 test1  | postgres
 test2  | postgres
(3 rows)
postgres=#
postgres=# \dn+;
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
 test1  | postgres          |                                        |
 test2  | postgres          |                                        |
(3 rows)
postgres=#
postgres=# SELECT schema_name, schema_owner FROM information_schema.schemata;
    schema_name     |   schema_owner
--------------------+-------------------
 test2              | postgres
 test1              | postgres
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 public             | pg_database_owner
(6 rows)
postgres=#
postgres=# SELECT nspname AS schema_name FROM pg_catalog.pg_namespace;
    schema_name
--------------------
 pg_toast
 pg_catalog
 public
 information_schema
 test1
 test2
(6 rows)
postgres=# create table test1.emp(no int,name varchar);
CREATE TABLE
postgres=#
postgres=#
postgres=# create table test1.dept(no int,name varchar);
CREATE TABLE
postgres=#
postgres=# create table test2.emp(no int,name varchar);
CREATE TABLE
postgres=#
postgres=# create table test2.dept(no int,name varchar);
CREATE TABLE
postgres=#
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)
postgres=#
postgres=# \dt test2.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test2  | dept | table | postgres
 test2  | emp  | table | postgres
(2 rows)
postgres=#
postgres=# insert into test1.emp value(1,'Amit'),(2,'Samik');
ERROR:  syntax error at or near "value"
LINE 1: insert into test1.emp value(1,'Amit'),(2,'Samik');
                              ^
postgres=# insert into test1.emp values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=#
postgres=# insert into test1.dept values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=# insert into test2.dept values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=# insert into test2.emp values (1,'Amit'),(2,'Samik'),(3,'Dhamik');
INSERT 0 3
postgres=#
postgres=#

-bash-4.2$
-bash-4.2$ cd postgre_backup/
-bash-4.2$
-bash-4.2$ ls -lrt
total 4
drwxrwxr-x 20 postgres postgres 4096 Jan 22 19:14 15_cluster_db1_bkp
-bash-4.2$
-bash-4.2$ mkdir backup
-bash-4.2$ ls -lrt
total 8
drwxrwxr-x 20 postgres postgres 4096 Jan 22 19:14 15_cluster_db1_bkp
drwxrwxr-x  2 postgres postgres 4096 Jan 24 09:47 backup
-bash-4.2$
-bash-4.2$ pwd
/backup/postgre_backup
-bash-4.2$
-bash-4.2$








1) Scenario 1 : Table Backup :-
-bash-4.2$
-bash-4.2$ cd /backup/postgre_backup/backup
-bash-4.2$
-bash-4.2$ pg_dump -p 5455 -d postgres -t test1.emp>emp.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 4
-rw-rw-r-- 1 postgres postgres 886 Jan 24 10:01 emp.sql
-bash-4.2$
postgres=#
postgres=# drop table test1.emp;
DROP TABLE

postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
(1 row)

If you take pg_dump with plain format, psql utility can be used to restore.

psql -f emp.sql -d postgres -o emp.log
-bash-4.2$
-bash-4.2$ psql -f emp.sql -d postgres -o emp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 8
-rw-rw-r-- 1 postgres postgres 886 Jan 24 10:01 emp.sql
-rw-rw-r-- 1 postgres postgres 113 Jan 24 10:10 emp.log
-bash-4.2$
-bash-4.2$

-bash-4.2$ cat emp.log
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
COPY 3

-bash-4.2$ psql
psql (15.5)
Type "help" for help.
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)


2) Scenario 2 : Table's structure backup :-
-bash-4.2$ pg_dump -d postgres -t test1.emp -s>emp_structure.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -rlt
total 4
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_structure.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: emp; Type: TABLE; Schema: test1; Owner: postgres
--
CREATE TABLE test1.emp (
    no integer,
    name character varying
);

ALTER TABLE test1.emp OWNER TO postgres;
--
-- PostgreSQL database dump complete
--

3) Scenario 3 : Take only data backup  :-
-bash-4.2$ pg_dump -d postgres -t test1.emp -a>emp_data.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 8
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-rw-rw-r-- 1 postgres postgres 635 Jan 24 10:17 emp_data.sql
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_data.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.5
-- Dumped by pg_dump version 15.5
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test1; Owner: postgres
--
COPY test1.emp (no, name) FROM stdin;
1       Amit
2       Samik
3       Dhamik
\.

--
-- PostgreSQL database dump complete
--

4) Scenario 4 : Restore with Structure+Data  :-
postgres=#
postgres=# drop table test1.emp;
DROP TABLE
postgres=#
postgres=#
postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
(1 row)
postgres=#

-bash-4.2$
-bash-4.2$ psql -f emp_structure.sql -d postgres -o structurestore.log
-bash-4.2$
-bash-4.2$ psql -f emp_data.sql  -d postgres -o datastore.log
-bash-4.2$

-bash-4.2$ ls -lrt
total 16
-rw-rw-r-- 1 postgres postgres 740 Jan 24 10:15 emp_structure.sql
-rw-rw-r-- 1 postgres postgres 635 Jan 24 10:17 emp_data.sql
-rw-rw-r-- 1 postgres postgres 106 Jan 24 10:21 structurestore.log
-rw-rw-r-- 1 postgres postgres  80 Jan 24 10:22 datastore.log

postgres=# \dt test1.*;
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 test1  | dept | table | postgres
 test1  | emp  | table | postgres
(2 rows)


5) Scenario 5 : Take table backup in BIN format  :-
pg_dump -d postgres -Fc -f emp.bin -t test1.emp -v 2>emp.log
-bash-4.2$ pg_dump -d postgres -Fc -f emp.bin -t test1.emp -v 2>emp.log
-bash-4.2$


-bash-4.2$ cat emp.log
pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading partitioning data
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "test1.emp"
6) Scenario 6 : Restore Table
pg_restore -Fc -d postgres -n test1 -t emp -c emp.bin -v 2>emp_restore.log
-bash-4.2$
-bash-4.2$ pg_restore -Fc -d postgres -n test1 -t emp -c emp.bin -v 2>emp_restore.log
-bash-4.2$

-bash-4.2$ cat emp_restore.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"

7) Scenario 7 : Take table backup in tar format 
pg_dump -Ft -f emp.tar -d postgres -t test1.emp -v 2>emp_tar_bkp.log

-bash-4.2$ pg_dump -Ft -f emp.tar -d postgres -t test1.emp -v 2>emp_tar_bkp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
total 40
-rw-rw-r-- 1 postgres postgres 2195 Jan 24 10:46 emp_tar_bkp.log
-rw-rw-r-- 1 postgres postgres 6656 Jan 24 10:46 emp.tar

8) Scenario 8 : Restore Table from tar backup
pg_restore -Ft -d postgres -n test1 -t emp emp.tar -c -v 2>emp_tar_restore.log
-bash-4.2$ pg_restore -Ft -d postgres -n test1 -t emp emp.tar -c -v 2>emp_tar_restore.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_tar_restore.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"
-bash-4.2$


9) Scenario 8 : Take backup in Directory format 
pg_dump -Fd -f emp -d postgres -t test1.emp -v 2>emp_dir_bkp.log
-bash-4.2$ pg_dump -Fd -f emp -d postgres -t test1.emp -v 2>emp_dir_bkp.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ ls -lrt
-rw-rw-r-- 1 postgres postgres 1945 Jan 24 10:53 emp_dir_bkp.log
drwx------ 2 postgres postgres 4096 Jan 24 10:53 emp




10) Scenario  : Restore from emp directory
pg_restore -Fd -d postgres -n test1 -t emp -c emp -v 2>emp_restore_from_dir.log
-bash-4.2$ pg_restore -Fd -d postgres -n test1 -t emp -c emp -v 2>emp_restore_from_dir.log
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat emp_restore_from_dir.log
pg_restore: connecting to database for restore
pg_restore: dropping TABLE emp
pg_restore: creating TABLE "test1.emp"
pg_restore: processing data for table "test1.emp"
Note:- Only Directory format supports Parallel job
pg_dump -Ft -f emp.tar -d postgres -t test1.emp -j 5 -v 2>emp_bkp_using_parallel_job.log

11) Scenario Multiple tables backup at a time
pg_dump -Fc -d postgres -t test1.emp -t test1.dept -f multiple.bin -v 2>multiple_bkp.log
-bash-4.2$ pg_dump -Fc -d postgres -t test1.emp -t test1.dept -f multiple.bin -v 2>multiple_bkp.log
-bash-4.2$ ls -rlt
total 8
-rw-rw-r-- 1 postgres postgres 1993 Jan 24 11:06 multiple_bkp.log
-rw-rw-r-- 1 postgres postgres 1924 Jan 24 11:06 multiple.bin
-bash-4.2$


12) Multiple table restore
pg_restore -Fc -d postgres -n test1 -t emp -t dept multiple.bin -c -v 2>restore.log



13) Schema backup
pg_dump -Fc -d postgres -n test1 -f schema.bin -v 2>schema_bkp.log
14) Schema restore
pg_restore -Fc -d postgres -n test1 schema.bin -c -v 2>restore_schema.log
15) Restore single table from schema dumps
pg_restore -Fc -d postgres -n test1 -t dept schema.bin -c -v 2>rept_restore_Schema.log

16) Multiple table schema backup:-
pg_dump -Fc -d postgres -n test1 -n test2 -f multiple_schema_bkp.bin -v 2>multiple_schema_bkp.log

17) Take single DB backup
pg_dump -Fc -d postgres -f db.bin -v 2>dbbackup.log

18) Restore DB backup
pg_restore -Fc -d postgres db.bin -c -v 2>db_restore.log

19) Remap the database (backup taken for Raj database restore same dump as Ram database)
create database ram;
pg_restore -Fc ram db.bin -v 2>remap.log

20) Logical Full backup -
pg_dumpall>fullserverbkp.sql


21) Restore Full backup :-
psql -f fullserverbkp.sql -o fullserverbkp.log

22) Take Global object backup 
pg_dumpall -g>global.sql

Note: In real time we need to migrate complete server to another server , only logical backup can help.
take globals backup pg_dumpalll -g 
take backup database status using parallel job 

Backup and Restore - PostgreSQL-1

 

Backup Table Exercises

 
Export from emp table to emp file:-

postgres=# copy emp from '/tmp/emp.txt';
COPY 2
postgres=#
postgres=#
postgres=#
postgres=# select * from emp;
 no | ename
----+-------
  1 | Samik
  2 | Amit
  1 | Samik
  2 | Amit
(4 rows)

postgres=#
postgres=#
postgres=# \q

-bash-4.2$
-bash-4.2$
-bash-4.2$ rm -rf /tmp/emp.txt
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql -p 5455
psql (10.9, server 15.5)
WARNING: psql major version 10, server major version 15.
         Some psql features might not work.
Type "help" for help.

Import from emp.txt file to emp table:-
postgres=# copy emp to '/tmp/emp.txt' with delimiter '|';
COPY 4
postgres=#
postgres=#
postgres=# \q
-bash-4.2$
-bash-4.2$
-bash-4.2$ cat /tmp/emp.txt
1|Samik
2|Amit
1|Samik
2|Amit
-bash-4.2$
-bash-4.2$

How to take pg_basebackup in PostgreSQL - Examples

How to take pg_baseback ?

1) Created directory in /backup location 

2) Changed the ownership of the directory 

3) Took the backup in that particular /backup location 

[root@rac7 ~]# cd /backup

[root@rac7 backup]# mkdir postgre_backup

[root@rac7 backup]# chown postgres:postgres postgre_backup/

[root@rac7 backup]# su - postgres

Last login: Mon Jan 22 18:53:13 IST 2024 from 192.168.1.102 on pts/6
-bash-4.2$
-bash-4.2$
-bash-4.2$ cd /backup

-bash-4.2$ cd 15_cluster_db1_bkp/

-bash-4.2$ pwd
/backup/postgre_backup/15_cluster_db1_bkp

Run the below command to take base backup :=

-bash-4.2$
-bash-4.2$ /usr/pgsql-15/bin/pg_basebackup -p 5455 -D /backup/postgre_backup/15_cluster_db1_bkp



Below files we will created in the backup location:-

-bash-4.2$ cd /backup/postgre_backup/15_cluster_db1_bkp
-bash-4.2$ ls -lrt
total 272
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_stat_tmp
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_snapshots
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_dynshmem
drwx------ 2 postgres postgres   4096 Jan 22 19:14 log
-rw------- 1 postgres postgres    225 Jan 22 19:14 backup_label
drwx------ 3 postgres postgres   4096 Jan 22 19:14 pg_wal
-rw------- 1 postgres postgres    261 Jan 22 19:14 postgresql.auto.conf
-rw------- 1 postgres postgres      3 Jan 22 19:14 PG_VERSION
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_tblspc
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_serial
-rw------- 1 postgres postgres   1636 Jan 22 19:14 pg_ident.conf
-rw------- 1 postgres postgres   4789 Jan 22 19:14 pg_hba.conf
-rw------- 1 postgres postgres    374 Jan 22 19:14 logfile
drwx------ 5 postgres postgres   4096 Jan 22 19:14 base
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_xact
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_twophase
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_replslot
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_subtrans
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_stat
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_notify
drwx------ 4 postgres postgres   4096 Jan 22 19:14 pg_logical
drwx------ 2 postgres postgres   4096 Jan 22 19:14 pg_commit_ts
drwx------ 4 postgres postgres   4096 Jan 22 19:14 pg_multixact
-rw------- 1 postgres postgres  29457 Jan 22 19:14 postgresql.conf
drwx------ 2 postgres postgres   4096 Jan 22 19:14 global
-rw------- 1 postgres postgres     30 Jan 22 19:14 current_logfiles
-rw------- 1 postgres postgres 137625 Jan 22 19:14 backup_manifest
-bash-4.2$

PostgreSQL Backups: What is pg_basebackup?

pg_basebackup:-

1. pg_basebackup is a simple but powerful utility provided by PostgreSQL to take online and consistent file system-level backups.
2. The backups created using this command contain the actual database files so you don’t need to actually restore these kinds of backups.
3. You can just uncompress the backup files and copy them to the database data directory.
4. You can use it to create the base backups of your database cluster which in turn can be used for the point in time recovery.
5. You can also set up a standby replica database server using the streaming option of this command.

In this article, we will go through the process of creating backups using the pg_basebackup utility and also learn about various options that can be used for this task.

Backup Process for a PostgreSQL Cluster

You can run the following simple command to take the base backup of your database.

1	$ pg_basebackup -D /tmp/backups -x

Let’s see these options:

• -D is used to specify the path of the output directory. 
• -x flag will include the transaction logs in the backup folder. These logs can be used to start the database server from the newly created base backup. This means that we can just extract the backup files anywhere and use it as a fully operational database. 

Prerequisites:-

Before we start taking the backup and run the above command, we need to make some changes in the postgresql.conf file so our database can run properly during the backup process. Change the following values in the postgresql.conf file.

1 2 3

wal_level = archive   max_wal_senders = 4

Here, WAL stands for write-ahead logs (transaction logs) for PostgreSQL. By changing the value of wal_level to archive, the backup command will generate the logs in the format that are compatible with pg_basebackup and other replication-based backup tools. The value of max_wal_senders will determine how many clients can connect with the database and request the data files.

Apart from this change, we need to make one more change in the pg_hba.conf file adding the following line:

1	local replication postgres peer

pg_hba.conf file basically contains the access control list of connections. pg_basebackup command internally uses a replication protocol to take backups so we need to add the above line to allow local connections that can request the data files of the database.

Labeling Your Backups

It’s very important to create consistent and periodic base backups to keep your data safe. When you create multiple backups, it's always good to add labels to your database. You can do it using the following command:

1	$ pg_basebackup -D /tmp/backups -x -l "This backup was created at $(hostname) on $(date)"

Viewing the Backup Progress

If you want to know the approximate duration of the backup process then you can run the following command:

1	$ pg_basebackup -D /tmp/backups -x -P

When you provide this -P option, the backup process will start calculating the size of the full database. If your database is having active connections, some new data might be added in the database during the backup process, so this command won’t be able to predict the exact time duration.

Creating Backups from a Remote Server

This command can also be used to take the backups of remote PostgreSQL clusters. You can provide the hostname and port number by specifying the following options in your command.

1	$ pg_basebackup -D /tmp/backups -x -h 10.0.2.15 -p 5432 -U postgres

Creating gzipped tar Backup

If you create the backup in plain text format, the backup folder will occupy a lot of space on your server. Instead of plain text format, this command can generate backup files in tar format. Also, you can provide -z flag to compress the target backup so it will occupy even less space. Here is the command for it:

1	$ pg_basebackup -D /tmp/backups -x -Ft -z

Creating a Backup by Streaming the Transaction Logs

Pg_basebackup command can stream the transaction logs (WAL) in parallel while taking the backup. This is useful when some new data is being added to the database while the backup process is running. So, once the backup process is over, you can just extract the backup and start using it as a fully qualified database.

When you specify this option, the pg_basebackup command will open two connections to the server. One to request the data files and another to stream the WAL logs in parallel. So, make sure that max_wal_senders value is greater than 2 in your PostgreSQL configuration file.

1	$ pg_basebackup -D /tmp/backups -X stream -Ft -z

Also, If you take the backup using streaming enabled, the resulting backup will have all the data along with required WAL logs to start afresh replica server for your production database.

Recovery process of PostgreSQL cluster

First of all, stop your database server to start the recovery process.

1	$ pg_ctlcluster 10.6 main stop

Now, move the current database files to some other folder to keep this as a backup.

1	$ mv main main.backup

Copy the archived base backup files to the data directory of the database.

1	$ cp -r /tmp/backups main

Start the database server now to perform the recovery.

1	$ pg_ctlcluster 10.6 main start

If you want, you can monitor the recovery process by checking the logs file.

1	$ tail -f /var/log/postgresql/postgresql-10.6-main.log

Recovery process is fairly simple as we copied actual data files during the backup process. So, you just have to extract the backup files and move it to the data directory of the database.

Conclusion:-

Pg_basebackup is a handful and easy to use utility for making PostgreSQL backups. For the large size of databases, you should use pg_basebackup command to take backups as pg_dumpall will take a very long time to take a full backup. Also, pg_basebackup provides some useful options such as streaming of logs, parallel compression, etc. This makes it a more attractive tool than pg_dump or pg_dumpall utilities. Pg_basebackup can only be used to create backups of the entire database cluster, as it doesn’t support single database backups. For that purpose, you can use pg_dump utility.