Using pg_dump & pg_restore to backup & restore a database in postgres (including permission verify)

 Dears,

In this blog, we will backup/restore a database. In addition we will also verify if the permission we granted to an object in the database to an user is restored properly or not.

Steps @ highlevel:

Create a DB
Grant permisson to a user in a schema present in the db
pg_dump to back it up
Drop the DB
Then try restoring the DB using pg_restore
Check the user permisson

Setup:
Create a database:

postgres=# create database pgdmptst;
CREATE DATABASE
postgres=#

Connect to the db:

postgres=# \c pgdmptst
You are now connected to database "pgdmptst" as user "postgres".
pgdmptst=#

Create a schema:

pgdmptst=# create schema sch_pgdmptst;
CREATE SCHEMA
pgdmptst=#

Create a table:

create table sch_pgdmptst.pgtst_tbl1
(
id int PRIMARY KEY
,name text
);
INSERT INTO sch_pgdmptst.pgtst_tbl1
SELECT i, lpad('TST',mod(i,100),'CHK')
FROM generate_series (1,10000) s(i);

pgdmptst=# create table sch_pgdmptst.pgtst_tbl1
pgdmptst-# (
pgdmptst(# id int PRIMARY KEY
pgdmptst(# ,name text
pgdmptst(# );
CREATE TABLE

pgdmptst=# INSERT INTO sch_pgdmptst.pgtst_tbl1
pgdmptst-# SELECT i, lpad('TST',mod(i,100),'CHK')
pgdmptst-# FROM generate_series (1,10000) s(i);
INSERT 0 10000

pgdmptst=# select count(1) from sch_pgdmptst.pgtst_tbl1;
 count
-------
 10000
(1 row)
pgdmptst=#

Grant permission:

pgdmptst=# grant select on sch_pgdmptst.pgtst_tbl1 to barman;
GRANT
pgdmptst=#

Now let us backup this schema:

https://www.postgresql.org/docs/current/app-pgdump.html

Option: -Fc

pg_dump -Fc pgdmptst >/pgBACKUP/pgdump/26nov22_pgdmptst_scn1.dmp

-bash-4.2$ pg_dump -Fc pgdmptst >/pgBACKUP/pgdump/26nov22_pgdmptst_scn1.dmp
-bash-4.2$ ls -altr
total 36
drwxr-xr-x. 3 postgres postgres    20 Nov 26 16:40 ..
drwxr-xr-x. 2 postgres postgres    39 Nov 26 16:40 .
-rw-r--r--. 1 postgres postgres 33574 Nov 26 16:40 26nov22_pgdmptst_scn1.dmp
-bash-4.2$

Let us drop the database:

postgres=# drop database pgdmptst;
DROP DATABASE
postgres=#

Check if the database is no more:

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileg
es
-----------+----------+----------+-------------+-------------+------------+-----------------+------------------
-----
 pgtst_db  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres
    +
           |          |          |             |             |            |                 | postgres=CTc/post
gres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres
    +
           |          |          |             |             |            |                 | postgres=CTc/post
gres
(4 rows)
postgres=#

Restore the database:

Command: pg_restore -C -d postgres 26nov22_pgdmptst_scn1.dmp --verbose

Actual output:

-bash-4.2$ pg_restore -C -d postgres 26nov22_pgdmptst_scn1.dmp --verbose
pg_restore: connecting to database for restore
pg_restore: creating DATABASE "pgdmptst"
pg_restore: connecting to new database "pgdmptst"
pg_restore: creating SCHEMA "sch_pgdmptst"
pg_restore: creating TABLE "sch_pgdmptst.pgtst_tbl1"
pg_restore: processing data for table "sch_pgdmptst.pgtst_tbl1"
pg_restore: creating CONSTRAINT "sch_pgdmptst.pgtst_tbl1 pgtst_tbl1_pkey"
pg_restore: creating ACL "sch_pgdmptst.TABLE pgtst_tbl1"

Verify the restore:

-bash-4.2$ psql
psql (15.0)
Type "help" for help.
postgres=# \c pgdmptst
You are now connected to database "pgdmptst" as user "postgres".

pgdmptst=# \dt
Did not find any relations.

pgdmptst=# \dn
         List of schemas
     Name     |       Owner
--------------+-------------------
 public       | pg_database_owner
 sch_pgdmptst | postgres
(2 rows)

pgdmptst=# set search_path='sch_pgdmptst';
SET

pgdmptst=# select count(1) from pgtst_tbl1;
 count
-------
 10000
(1 row)

pgdmptst=#

Now let us examine if barman has its permisson restored...

Command: select * from information_schema.table_privileges where lower(grantee)='barman';

pgdmptst=# select * from information_schema.table_privileges where lower(grantee)='barman';
 grantor  | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarch
y
----------+---------+---------------+--------------+------------+----------------+--------------+--------------
--
 postgres | barman  | pgdmptst      | sch_pgdmptst | pgtst_tbl1 | SELECT         | YES          | YES
(1 row)
pgdmptst=#

So the barman permisson is back :)

This closes this blog.

Thanks