Postgres cluster backup using BART tool

Backup and Recovery Tool(BART)

BART is a backup and recovery tool for EDB Postgres Databases. EDB Backup and Recovery Tool (BART) is a key component of an enterprise-level Postgres-based data management strategy. BART implements retention policies and point-in-time recovery requirements for large-scale Postgres deployments. Use version 1.0 for testing purpose which is free. Still this tool you can use for open source postgresql.

Using BART 2.0 or above, you can take incremental backups also. In postgres open source there is no incremental backup till version 12.

The first version of BART 1.0 was released in October of 2014. It:

• Supports complete, hot, physical backups of multiple Postgres Plus Advanced Server and PostgreSQL database servers
• Provides backup and recovery management of the database servers on local or remote hosts
• Uses a single, centralized catalog for backup data
• Displays backup information in an easy-to-read form
• Verifies backup’s with checksums
• Simplifies the point-in-time recovery process

-- Install BART

Install BART rpm to configure to take backup from Multiple clusters from multiple locations

-- RMP Installation

[root@postgres PostgresSoftware]# rpm -ivh edb-bart-1.1.0-1.rhel6.x86_64.rpm
warning: edb-bart-1.1.0-1.rhel6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 7e30651c: NOKEY
Preparing...                ########################################### [100%]
   1:edb-bart               ########################################### [100%]
[root@postgres PostgresSoftware]#


- for demonstration purpose, let us create two cluster in a single host and enable archive log

Data directories and region defined for BART backup
----------------------------------------------------
1) cluster-1 : /opt/demodata_bart - port: 5434 - Region: Hyderabad
2) Cluster-2 : /opt/PostgreSQL/10/data   - port: 5432 - Region: Bangalore

Let us start the POC:

step#1 - Let us assume, a cluster / instance is running on 5432 from '/opt/PostgreSQL/10/data' data directory. Now enable archive log and configure backup location

root@postgres archive_software]# su - postgres
-bash-4.1$
-bash-4.1$ exit
logout
[root@postgres archive_software]# cd /opt/
[root@postgres opt]# mkdir bart_archive
[root@postgres opt]#
[root@postgres opt]# mkdir bart_backup
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres bart_* -R

[root@postgres opt]#-bash-4.1$ psql -p 5432
psql.bin (10.12)
Type "help" for help.

postgres=# alter system set archive_mode to on;
ALTER SYSTEM
postgres=#
postgres=# alter system set archive_command to 'cp %p /opt/bart_archive/%f';
ALTER SYSTEM
postgres=#
postgres=# \q
-bash-4.1$
-bash-4.1$ pg_ctl -D $PGDATA restart

step#2 - Let us assume, another cluster / instance is running on 5434 from '/opt/demodata_bart' data directory. Now enable archive log and configure backup location

-bash-4.1$ cd /var/demodata/
-bash-4.1$
-bash-4.1$ vi postgresql.conf


archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /opt/demodata_bart/%f'

:wq

[root@postgres opt]# mkdir demodata_bart
[root@postgres opt]#
[root@postgres opt]# chown postgres:postgres demodata_bart/ -R
[root@postgres opt]#


-bash-4.1$ pg_ctl -D /var/demodata/ restart

Step# 3 - Configure each region as per the plan

[root@postgres etc]# cd /usr/edb-bart-1.1/etc/
[root@postgres etc]#
[root@postgres etc]# vi bart.cfg

[BART]
bart-host= postgres@127.0.0.1
backup_path = /opt/PostgreSQL/10/bart_backup
pg_basebackup_path = /opt/PostgreSQL/10/bin/pg_basebackup
logfile = /tmp/bart.log

[hyderabad]
host = 127.0.0.1
port = 5434
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS

[bangalore]
host = 127.0.0.1
port = 5432
user = postgres
description = "Postgres server"
retention_policy= 1 BACKUPS

:wq

Step# 3 -setup environment variables and Library path 

[root@postgres etc]# su - postgres
-bash-4.1$
-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ source /opt/PostgreSQL/10/pg_env.sh
-bash-4.1$
-bash-4.1$ export LD_LIBRARY_PATH=/opt/PostgreSQL/10/lib/
-bash-4.1$


step# 4 - Take backup using BART and see these backups

-bash-4.1$ ./bart BACKUP -s hyderabad

-bash-4.1$ ./bart BACKUP -s hyderabad

INFO:  creating backup for server 'hyderabad'
INFO:  backup identifier: '1588444892965'
40984/40984 kB (100%), 1/1 tablespace

INFO:  backup completed successfully
INFO:  backup checksum: 195ac082e96df56aeaba4b48310e8bb6 of base.tar
INFO:
BACKUP DETAILS:
BACKUP STATUS: active
BACKUP IDENTIFIER: 1588444892965
BACKUP NAME: none
BACKUP LOCATION: /opt/PostgreSQL/10/bart_backup/hyderabad/1588444892965
BACKUP SIZE: 40.02 MB
BACKUP FORMAT: tar
XLOG METHOD: fetch
BACKUP CHECKSUM(s): 1
 ChkSum                             File   
 195ac082e96df56aeaba4b48310e8bb6   base.tar

TABLESPACE(s): 0
START WAL LOCATION: 000000010000000000000002
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2020-05-03 00:11:37 IST
STOP TIME: 2020-05-03 00:12:02 IST
TOTAL DURATION: 25 sec(s)

-bash-4.1$

-- List backup

-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 hyderabad     1588444892965   2020-05-03 00:12:02 IST   40.03 MB      0.00 bytes    0           active
         

-bash-4.1$ ./bart BACKUP -s hyderabad


-bash-4.1$ ./bart SHOW-BACKUPs -s hyderabad
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 hyderabad     1588445017589   2020-05-03 00:13:43 IST   40.03 MB      0.00 bytes    0           active
 hyderabad     1588444892965   2020-05-03 00:12:02 IST   40.03 MB      0.00 bytes    0           active
                                                                                                       
-bash-4.1$

-bash-4.1$ ./bart SHOW-BACKUPs -s bangalore
 SERVER NAME   BACKUP ID   BACKUP TIME   BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS

-bash-4.1$ ./bart BACKUP -s bangalore

ERROR: backup failed for server 'bangalore'
connection to the server failed: fe_sendauth: no password supplied

The error because of permissions not given to bangalore region where 5432 cluster is configured.

let us fix:

-bash-4.1$ cd cd /opt/PostgreSQL/10/data
-bash-4.1$
-bash-4.1$ vi pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 md5

:wq

-bash-4.1$ pg_ctl -D $PGDATA reload
server signaled

-bash-4.1$ cd /usr/edb-bart-1.1/bin/
-bash-4.1$
-bash-4.1$ ./bart BACKUP -s bangalore

-bash-4.1$ ./bart BACKUP -s bangalore

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           active


If you will go to physical location, you will find folder names with region where you have configured in BART configuration file.

e.g.,

-bash-4.1$ cd /opt/PostgreSQL/10/bart_backup
-bash-4.1$ ll
total 8
drwx------. 5 postgres postgres 4096 May  3 00:27 bangalore
drwxr-xr-x. 5 postgres postgres 4096 May  3 00:13 hyderabad
-bash-4.1$

let us see what inside these folders.

-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore
-bash-4.1$ ls
1588445680120  1588445851470  archived_wals
-bash-4.1$

Now see here, backup IDs are matching to these directories what we saw in "bart SHOW-BACKUPS -s bangalore" outputs. i.e., for each backup a folder is created.
and actually .tar file created what we are usually doing in pg_basebackup

e.g.,

-bash-4.1$ cd 1588445680120
-bash-4.1$ pwd
/opt/PostgreSQL/10/bart_backup/bangalore/1588445680120
-bash-4.1$ ls
16430.tar  16434.tar  backupinfo  base.tar
-bash-4.1$

But, you may notice one thing, we took backup two times for each region and in BART configuration we mentioned retention as one. So here, why it is showing 'ACTIVE' for each backup.

let us see:

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           active
       
-- Manage backups

So here we need to manage backup using 'MANAGE' command from BART.

e.g.,

-bash-4.1$ ./bart MANAGE -s bangalore
INFO:  processing server 'bangalore', backup '1588445851470'
INFO:  processing server 'bangalore', backup '1588445680120'
INFO:  marking backup '1588445680120' as obsolete
INFO:  0 WAL file(s) marked obsolete
-bash-4.1$

Now list the backups again:

-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS 
                                                                                                         
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active 
 bangalore     1588445680120   2020-05-03 00:24:56 IST   117.46 MB     0.00 bytes    0           obsolete
                                                                                                         
-bash-4.1$

-- Delete backup piece from BART

-bash-4.1$ ./bart DELETE -s bangalore -i 1588445680120
INFO:  deleting backup '1588445680120' of server 'bangalore'
INFO:  deleting backup '1588445680120'
INFO:  0 WAL file(s) will be removed
INFO:  backup(s) deleted
-bash-4.1$ ./bart SHOW-BACKUPS -s bangalore
 SERVER NAME   BACKUP ID       BACKUP TIME               BACKUP SIZE   WAL(s) SIZE   WAL FILES   STATUS
                                                                                                       
 bangalore     1588445851470   2020-05-03 00:27:36 IST   117.46 MB     0.00 bytes    0           active
                                                                                                       
-bash-4.1$

Backup and Restore in postgreSQL

=============================
Copy data only in text format
=============================

hr1dev=# select * from test.emp;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1987-11-12 |     20
(2 rows)


hr1dev=# copy test.emp to '/tmp/emp.txt';
COPY 2

$ cat /tmp/emp.txt
100     Gouranga        Admin   1983-01-10      10
101     John    User    1987-11-12      20

hr1dev=# copy emp to '/tmp/empdata.txt';
COPY 1000100
hr1dev=# delete from emp;
DELETE 1000100
hr1dev=# select count(*) from emp;           
 count 
-------
     0
(1 row)

hr1dev=# copy emp from '/tmp/empdata.txt';
COPY 1000100
hr1dev=# select count(*) from emp;
  count  
---------
 1000100
(1 row)

hr1dev=# 

======================
export with delimiter:
======================

hr1dev=# copy emp to '/tmp/emp_del.txt' with delimiter '|';
COPY 1000100
hr1dev=# 

======================
export with header:
======================
hr1dev=# copy emp to '/tmp/emp_head' with CSV HEADER;
COPY 1000100
hr1dev=# 

$ more /tmp/emp_head
no,name
1,data1
2,data2
3,data3
4,data4

=======================================
export data for specific query:
=======================================
hr1dev=#  copy (select * from emp where no>10000) to '/tmp/emp_query.txt';
COPY 990000



-----------------------------------------------------------
BACKUP AND RESTORE
-----------------------------------------------------------
In postgresql we have 2 levels of backups.

1. Logical backup
2. Physical backup (file system backup/hot backup/online backup/offline backup)

Logical Backup:
---------------
Dump file will be in the format of logical , it will not contains any physical files means configuration files/ data files/transaction logs. To perform logical backup we can use two utilities. These are :
   a. pg_dump
   b, pg_dumpall

a.pg_dump : 
Using pg_dump, following are possible:
1. Single database backup at a time
2. Single schema,multiples schemas backup at a time
3. Single table,multiple tables backup at a time.
4. Dump can be taken in customized format means .tar or .gz directory formats.
7. Parallel jobs can be invoked
8. To restore, we can use traditional restore utility, this 10 times faster than the older utility.

Using pg_dump, we can't perform below activities:
1. we can't take global objects backup
2. Full server logical backup also not possible at a time.

b.pg_dumpall

Using pg_dumpall, following are possible:
1. Complete server backup at a time (all databases backup at a time)
2. Global objects backup only possible

We can't do the following using pg_dumpall utility:
1. Single database/table/schema backup not possible.

2. Taking dump in customized format is not possible.

5. we can't perform parallel jobs
6. here we can't use traditional restore utility.

To restore,  we can use 2 utilities:
-----------------------------------------
1.psql
2.pg_restore (traditional utility)

normally taking dump we have 2 ways:

1.plain format : we can able to read the dump file, what ever the statements you want to execute , you can simply execute as normal sql statements. while restoring you can't use pg_restore utility if dump in plain format.
2. If your dump in plain format , if you dump contains 10 tables backup, you can't restore single table from the dump.
3. To restore manually you have to copy that respective sql statements from the dump.
4. If dump in readable format, there might be number of chances for data misuse. this will be security compliance.
5. Always prefer to take dump in customized for, which is in binary format, not in the readable format. At the same time we can restore single table even-though dump contains multiple tables.
we can use pg_restore utility for restore as well.


Syntax: pg_dump -d db_name -t table_name

-- Taking backup in plain text format

$ /usr/local/pgsql/bin/pg_dump -t test.emp -d hr1dev > emp.sql

-bash-4.2$ pwd
/var/lib/pgsql/backups
-bash-4.2$ cat emp.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 11.0
-- Dumped by pg_dump version 11.0

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 client_min_messages = warning;
SET row_security = off;
SET default_tablespace = hr_tbs;
SET default_with_oids = false;
--
-- Name: emp; Type: TABLE; Schema: test; Owner: postgres; Tablespace: hr_tbs
--
CREATE TABLE test.emp (
    empno integer NOT NULL,
    ename character varying NOT NULL,
    job character varying,
    dob date,
    deptno integer
);
ALTER TABLE test.emp OWNER TO postgres;
--
-- Data for Name: emp; Type: TABLE DATA; Schema: test; Owner: postgres
--
COPY test.emp (empno, ename, job, dob, deptno) FROM stdin;
100     Gouranga        Admin   1983-01-10      10
101     John    User    1987-11-12      20
\.
SET default_tablespace = '';
--
-- Name: emp emp_pkey; Type: CONSTRAINT; Schema: test; Owner: postgres
--
ALTER TABLE ONLY test.emp
    ADD CONSTRAINT emp_pkey PRIMARY KEY (empno);

--
-- Name: TABLE emp; Type: ACL; Schema: test; Owner: postgres
--
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE test.emp TO gouranga;
--
-- PostgreSQL database dump complete
--
----------------------------------------------------------------------------------------------------------

=================
Restore from dump
=================

postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# drop table test.emp; 
ERROR:  cannot drop table test.emp because other objects depend on it
DETAIL:  materialized view test.emp_mv depends on table test.emp
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

hr1dev=# drop table test.emp CASCADE;
NOTICE:  drop cascades to materialized view test.emp_mv
DROP TABLE
hr1dev=# 


$ psql -d hr1dev -f emp.sql -o emp_hr1dev_imp.log

-f : filename where data exported in text format
-d : in which database to be imported
-o : output log file

hr1dev=# select * from test.emp;
 empno |  ename   |  job  |    dob     | deptno 
-------+----------+-------+------------+--------
   100 | Gouranga | Admin | 1983-01-10 |     10
   101 | John     | User  | 1987-11-12 |     20
(2 rows)


======================================
Export dump in customized format:
======================================

pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log

Taking Dump in parallel format:
--------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 2>dbbackup.log

Taking backup from remote system:
-----------------------------------------------
pg_dump -Fc -d hr1dev -f hr1dev.bin -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log

for location:
----------------
$ pg_dump -Fd -d hr1dev -f /fullpath/dbbackup -v -j 2 -h 192.168.10.132 -p 5433 2>dbbackup.log

example:
---------
$ pg_dump -Fc -d hr1dev -f hr1dev.bin -v 2>dbbackup.log
$ ll
total 22264
-rw-r--r--. 1 postgres postgres     3255 Dec 29 08:48 dbbackup.log
-rw-r--r--. 1 postgres postgres 22783616 Dec 29 08:48 hr1dev.bin
$ cat dbbackup.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: finding the columns and types of table "test.sample"
pg_dump: finding the columns and types of table "test.sample2"
pg_dump: finding the columns and types of table "public.emp"
pg_dump: finding the columns and types of table "public.emp_mv"
pg_dump: finding the columns and types of table "test.emp"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "test.emp"
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 security enabled for table "test.sample"
pg_dump: reading policies for table "test.sample"
pg_dump: reading row security enabled for table "test.sample2"
pg_dump: reading policies for table "test.sample2"
pg_dump: reading row security enabled for table "public.emp"
pg_dump: reading policies for table "public.emp"
pg_dump: reading row security enabled for table "public.emp_mv"
pg_dump: reading policies for table "public.emp_mv"
pg_dump: reading row security enabled for table "test.seq1"
pg_dump: reading policies for table "test.seq1"
pg_dump: reading row security enabled for table "test.emp"
pg_dump: reading policies for table "test.emp"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "test.sample"
pg_dump: reading publication membership for table "test.sample2"
pg_dump: reading publication membership for table "public.emp"
pg_dump: reading publication membership for table "test.emp"
pg_dump: reading subscriptions
pg_dump: reading large objects
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 "public.emp"
pg_dump: dumping contents of table "test.emp"
pg_dump: dumping contents of table "test.sample"
pg_dump: dumping contents of table "test.sample2"
$ 

Restore database from backup:
=============================
pg_restore -Fd -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log


postgres=# drop database hr1dev;
DROP DATABASE
postgres=# \c hr1dev
FATAL:  database "hr1dev" does not exist
Previous connection kept
postgres=# 

postgres=# create database hr1dev;
CREATE DATABASE

$ pg_restore -Fc -d hr1dev hr1dev.bin -v -j 2 2>db_hr1dev_restore.log

$ cat db_hr1dev_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3107 ENCODING ENCODING
pg_restore: processing item 3108 STDSTRINGS STDSTRINGS
pg_restore: processing item 3109 SEARCHPATH SEARCHPATH
pg_restore: processing item 3110 DATABASE hr1dev
pg_restore: processing item 6 SCHEMA test
pg_restore: creating SCHEMA "test"
pg_restore: processing item 199 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: processing item 200 MATERIALIZED VIEW emp_mv
pg_restore: creating MATERIALIZED VIEW "public.emp_mv"
pg_restore: processing item 202 TABLE emp
pg_restore: creating TABLE "test.emp"
pg_restore: processing item 197 TABLE sample
pg_restore: creating TABLE "test.sample"
pg_restore: processing item 198 TABLE sample2
pg_restore: creating TABLE "test.sample2"
pg_restore: processing item 201 SEQUENCE seq1
pg_restore: creating SEQUENCE "test.seq1"
pg_restore: entering main parallel loop
pg_restore: launching item 3101 TABLE DATA emp
pg_restore: launching item 3104 TABLE DATA emp
pg_restore: processing data for table "test.emp"
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3104 TABLE DATA emp
pg_restore: launching item 3099 TABLE DATA sample
pg_restore: processing data for table "test.sample"
pg_restore: finished item 3099 TABLE DATA sample
pg_restore: launching item 3100 TABLE DATA sample2
pg_restore: processing data for table "test.sample2"
pg_restore: finished item 3100 TABLE DATA sample2
pg_restore: launching item 3113 SEQUENCE SET seq1
pg_restore: executing SEQUENCE SET seq1
pg_restore: finished item 3113 SEQUENCE SET seq1
pg_restore: launching item 2976 CONSTRAINT emp emp_pkey
pg_restore: creating CONSTRAINT "test.emp emp_pkey"
pg_restore: finished item 2976 CONSTRAINT emp emp_pkey
pg_restore: finished item 3101 TABLE DATA emp
pg_restore: launching item 3111 ACL SCHEMA test
pg_restore: launching item 3112 ACL TABLE emp
pg_restore: creating ACL "test.TABLE emp"
pg_restore: creating ACL "SCHEMA test"
pg_restore: finished item 3112 ACL TABLE emp
pg_restore: finished item 3111 ACL SCHEMA test
pg_restore: launching item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: creating MATERIALIZED VIEW DATA "public.emp_mv"
pg_restore: finished item 3102 MATERIALIZED VIEW DATA emp_mv
pg_restore: finished main parallel loop

Testing:
postgres=# \c hr1dev
You are now connected to database "hr1dev" as user "postgres".
hr1dev=# \dt test.*
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 test   | emp     | table | postgres
 test   | sample  | table | gouranga
 test   | sample2 | table | postgres
(3 rows)

hr1dev=# 


Example-2:
pg_dump -Fd -d demodb -f dbbackup -v -j 2 2>dbdemo_backup.log

postgres=# drop database demodb;
DROP DATABASE

pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log


postgres=# create database demodb;
CREATE DATABASE
postgres=# \q

-bash-4.2$ pg_restore -Fd -d demodb dbbackup -v -j 2 2>db_demodb_restore.log
-bash-4.2$ 
-bash-4.2$ cat db_demodb_restore.log
pg_restore: connecting to database for restore
pg_restore: processing item 3079 ENCODING ENCODING
pg_restore: processing item 3080 STDSTRINGS STDSTRINGS
pg_restore: processing item 3081 SEARCHPATH SEARCHPATH
pg_restore: processing item 3082 DATABASE demodb
pg_restore: processing item 196 TABLE emp
pg_restore: creating TABLE "public.emp"
pg_restore: entering main parallel loop
pg_restore: launching item 3076 TABLE DATA emp
pg_restore: processing data for table "public.emp"
pg_restore: finished item 3076 TABLE DATA emp
pg_restore: finished main parallel loop
-bash-4.2$ psql
psql (11.0)
Type "help" for help.

postgres=# \c demodb
You are now connected to database "demodb" as user "postgres".
demodb=# select count(1) from emp;
 count 
-------
    50
(1 row)

demodb=# 

==============================================
To take complete server level backup:
==============================================
1. parallel not possible
2. only plain format will work.
3. use pg_dumpall

To Take backup:
----------------
$ pg_dumpall>fullserverbackup.sql
$ ls -ltr fullserverbackup.sql
-rw-r--r--. 1 postgres postgres 17786563 Dec 29 09:44 fullserverbackup.sql


To Restore
---------------
$ psql -f fullserverbackup.sql -o fullserverrestore.log

This dump will contains users and user access rights information and table spaces information, this is very rare . Even-though you are taking backup of full server, if required point in-time recovery possible. Only with physical backup only we can able to do point in-time recovery.


PHYSICAL BACKUP:

1. File system backup: after stopping application and jobs related to databases, we will apply check point and we will copy data directory.
2. Hot backup: 
3. offline backup : stop services and by using any Linux command copy data directory to any other location.
4. online backup: no need to stop jobs, no need to stop services, simply you can take the backup by using an utility called pg_basebackup.


pg_basebackup:
===============
It will contain exact copy of our data directory with proper check point. it will gives consistency.
While triggering this one first it will apply check point , and it will start actual backup.
To take this backup you need empty directory always.
For plain database, you can take the full backup within the server, but if you have table-spaces, then you are going to take backup,
simply data directory will be backed up to respective directory. but table-space location will be trying to backed up to existing location
only, if that is empty, backup will success, if that is not empty we have to remap.


Example:

List tablespaces:

postgres=# \db+
                                      List of tablespaces
    Name    |  Owner   |     Location      | Access privileges | Options |  Size  | Description 
------------+----------+-------------------+-------------------+---------+--------+-------------
 hr_tbs     | postgres | /usr/pgsql/hr_tbs |                   |         | 16 kB  | 
 pg_default | postgres |                   |                   |         | 207 MB | 
 pg_global  | postgres |                   |                   |         | 574 kB | 
(3 rows)

postgres=# 


$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229205/229205 kB (100%), 2/2 tablespaces
$ 

You can backup in .tar format also:

$ pg_basebackup -D /var/lib/pgsql/base_backup/ -X fetch -Ft -P -T /usr/pgsql/hr_tbs=/usr/pgsql/hr_tbs_new
229206/229206 kB (100%), 2/2 tablespaces
$ 

Note: Ensure 'base_backup' must be empty.

$ pwd
/var/lib/pgsql
$ cd base_backup/
-bash-4.2$ ls -ltr
total 229212
-rw-------. 1 postgres postgres     19968 Dec 29 10:18 24634.tar
-rw-------. 1 postgres postgres 234689024 Dec 29 10:18 base.tar
$ 

You can see here, all folders backed up:

$ pwd
/var/lib/pgsql/base_backup
$ ls -ltr
total 60
-rw-------. 1 postgres postgres   226 Dec 29 10:14 backup_label
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_dynshmem
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_commit_ts
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_twophase
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_subtrans
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_snapshots
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_serial
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_notify
drwx------. 4 postgres postgres    34 Dec 29 10:14 pg_multixact
drwx------. 7 postgres postgres    62 Dec 29 10:14 base
drwx------. 2 postgres postgres    18 Dec 29 10:14 pg_tblspc
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_replslot
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_stat
-rw-------. 1 postgres postgres 23806 Dec 29 10:14 postgresql.conf
-rw-------. 1 postgres postgres    88 Dec 29 10:14 postgresql.auto.conf
drwx------. 2 postgres postgres    17 Dec 29 10:14 pg_xact
-rw-------. 1 postgres postgres     3 Dec 29 10:14 PG_VERSION
drwx------. 2 postgres postgres     6 Dec 29 10:14 pg_stat_tmp
drwx------. 4 postgres postgres    65 Dec 29 10:14 pg_logical
-rw-------. 1 postgres postgres  1636 Dec 29 10:14 pg_ident.conf
-rw-------. 1 postgres postgres  4513 Dec 29 10:14 pg_hba.conf
drwx------. 2 postgres postgres  4096 Dec 29 10:14 pg_log
drwx------. 3 postgres postgres    28 Dec 29 10:14 hr_tbs_new
drwx------. 2 postgres postgres     6 Dec 29 10:14 hr_tbs
-rw-------. 1 postgres postgres    47 Dec 29 10:14 current_logfiles
drwx------. 2 postgres postgres  4096 Dec 29 10:14 global
drwx------. 3 postgres postgres    58 Dec 29 10:14 pg_wal
-bash-4.2$ 

For TAR format no need to mention table-space remapping, it will be create number of files based on number of table-spaces.

$ pwd
/var/lib/pgsql/backup
$ pg_basebackup -D /var/lib/pgsql/backup/ -X fetch -Ft -P
229206/229206 kB (100%), 2/2 tablespaces
$ 
$ ls -ltr
total 229212
-rw-------. 1 postgres postgres     19968 Dec 29 10:23 24634.tar
-rw-------. 1 postgres postgres 234689536 Dec 29 10:23 base.tar
$ pwd
/var/lib/pgsql/backup

================================
Restore from Full Backup
================================

[postgres@example02 backup]$ tar -xvf base.tar
[postgres@example02 backup]$ rm base.tar

[postgres@example02 backup]$ tar -xvf 16613.tar
PG_12_201909212/

[postgres@example02 backup]$ cat tablespace_map
16613 /var/lib/pgsql/hr_tbs_new


update tablespace_map file wit new table space location.
change port in postgresql.conf file of backup directory.

[postgres@example02 backup]$ chmod 0700 /var/lib/pgsql/backup/

[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup start
waiting for server to start....    3372 2019-12-24 17:58:55 PST  1LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
    3372 2019-12-24 17:58:55 PST  2LOG:  listening on IPv6 address "::1", port 5433
    3372 2019-12-24 17:58:55 PST  3LOG:  listening on IPv4 address "127.0.0.1", port 5433
    3372 2019-12-24 17:58:55 PST  4LOG:  listening on IPv4 address "192.168.38.132", port 5433
    3372 2019-12-24 17:58:55 PST  5LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
    3372 2019-12-24 17:58:55 PST  6LOG:  redirecting log output to logging collector process
    3372 2019-12-24 17:58:55 PST  7HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@example02 backup]$ ps -ef|grep postgres
root       2815   2784  0 16:47 pts/0    00:00:00 su - postgres
postgres   2816   2815  0 16:47 pts/0    00:00:00 -bash
postgres   2851      1  0 16:47 ?        00:00:00 /usr/local/pgsql/bin/postgres
postgres   2852   2851  0 16:47 ?        00:00:00 postgres: logger
postgres   2854   2851  0 16:47 ?        00:00:00 postgres: checkpointer
postgres   2855   2851  0 16:47 ?        00:00:00 postgres: background writer
postgres   2856   2851  0 16:47 ?        00:00:00 postgres: walwriter
postgres   2857   2851  0 16:47 ?        00:00:00 postgres: stats collector
postgres   2858   2851  0 16:47 ?        00:00:00 postgres: logical replication launcher
postgres   3372      1  0 17:58 ?        00:00:00 /usr/local/pgsql/bin/postgres -D /var/lib/pgsql/backup
postgres   3373   3372  0 17:58 ?        00:00:00 postgres: logger
postgres   3375   3372  0 17:58 ?        00:00:00 postgres: checkpointer
postgres   3376   3372  0 17:58 ?        00:00:00 postgres: background writer
postgres   3377   3372  0 17:58 ?        00:00:00 postgres: walwriter
postgres   3378   3372  0 17:58 ?        00:00:00 postgres: stats collector
postgres   3379   3372  0 17:58 ?        00:00:00 postgres: logical replication launcher
postgres   3380   2816  0 17:59 pts/0    00:00:00 ps -ef
postgres   3381   2816  0 17:59 pts/0    00:00:00 grep --color=auto postgres
[postgres@example02 backup]$


Now 2 instances running in the same server.
with same binaries, different data directories, different ports we can start number of instances.

[postgres@example02 backup]$ psql -p5433
psql (12.0)
Type "help" for help.

postgres=# show data_directory;
    data_directory
-----------------------
 /var/lib/pgsql/backup
(1 row)

postgres=#


[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/DATA/ status
pg_ctl: server is running (PID: 2851)
/usr/local/pgsql/bin/postgres
[postgres@example02 backup]$ pg_ctl -D /var/lib/pgsql/backup/ status
pg_ctl: server is running (PID: 3372)
/usr/local/pgsql/bin/postgres "-D" "/var/lib/pgsql/backup"
[postgres@example02 backup]$


Anywhere backup strategy will be physical only. daily full backup. 
There is no incremental concept in PostgreSQL.
They will schedule script to create folder before backup with the date.