pg_dump – Database Backup & Restore (UAT → SIT Migration)

Item	UAT (Source)	SIT (Target)
Database Name	gebua	gebta
Server/Host	pgdb02 / 192.168.2.23	lxceftsgvdb01 / 192.168.2.21
Data Tablespace (default tablespace)	ts_data_uat	ts_data_sit
Backup Path	/pgBackup/gebua/backup	/pgBackup/gebta/backup

Format	Extension
Plain Text (-Fp)	.sql
Custom (-Fc)	.dmp or .bin
Tar (-Ft)	.tar
Directory (-Fd)	(folder) — Can run in parallel using -j2 (2workers)

1. UAT Environment Setup (Source)

2. SIT Environment Setup (Target)

3. Sample Data in UAT

4. Backup & Restore (All Formats)

4.1 Plain Text (SQL) Format

4.2 Custom (DMP) Format – RECOMMENDED

4.3 Tar Format

4.4 Directory Format

5. Restore with –no-owner and –no-tablespaces Options

1. UAT Environment Setup (Source)

Set up the source database in UAT (GEBUA – 192.168.2.23).

Create Database & Schema in UAT:

-- Connect as superuser
[postgres@pgdb02 ~]$ psql
psql (17.10)
Type "help" for help.

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

-- Create tablespace 

mkdir -p /pgTBS/tsdata/ts_data_uat

postgres=# CREATE TABLESPACE ts_data_uat LOCATION '/pgTBS/tsdata/ts_data_uat';
CREATE TABLESPACE


-- Create user
postgres=# CREATE USER gebadm WITH PASSWORD 'gebadm123';
CREATE ROLE
postgres=#
-- Create database
postgres=# CREATE DATABASE gebua tablespace ts_data_uat;
CREATE DATABASE
postgres=# 

-- Create schema
postgres=# \c gebua
You are now connected to database "gebua" as user "postgres".
gebua=#

gebua=# CREATE SCHEMA geb AUTHORIZATION gebadm;
CREATE SCHEMA
gebua=#

-- Create tables
gebua=# \c gebua gebadm
You are now connected to database "gebua" as user "gebadm".
gebua=>
gebua=> CREATE TABLE geb.emp (name TEXT, designation TEXT, project TEXT, company TEXT);
CREATE TABLE
gebua=> INSERT INTO geb.emp VALUES ('Sugi', 'DBA', 'Jetstar', 'iGATE');
INSERT 0 1
gebua=> INSERT INTO geb.emp VALUES ('Teja', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
gebua=> INSERT INTO geb.emp VALUES ('RAJ', 'DBA', 'RCM', 'iGATE');
INSERT 0 1
gebua=>
gebua=> CREATE TABLE geb.dept ( dept_id INT PRIMARY KEY, dept_name VARCHAR(100) NOT NULL, location VARCHAR(50), manager VARCHAR(100) );
CREATE TABLE
gebua=> INSERT INTO geb.dept VALUES
(1, 'Engineering', 'Vijayawada', 'Ravi'),
(2, 'Sales', 'Visakhapatnam', 'Sai Krishna'),
(3, 'HR', 'Rajahmundry', 'Anusha'),
(4, 'Finance', 'Nellore', 'Archana'),
(5, 'Operations', 'Anantapur', 'Harika');
INSERT 0 5
gebua=>
gebua=> \dt geb.*
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+--------
 geb    | dept | table | gebadm
 geb    | emp  | table | gebadm
(2 rows)

gebua=> SELECT COUNT(*) FROM geb.emp;
 count
-------
     3   <------
(1 row)

gebua=> SELECT COUNT(*) FROM geb.dept;
 count
-------
     5 <------
(1 row)

gebua=>

Setup Backup Directory in UAT:

mkdir -p /pgBackup/gebua/backup
chown postgres:postgres /pgBackup/gebua/backup
chmod 755 /pgBackup/gebua/backup

2. SIT Environment Setup (Target)

Set up the target database infrastructure in SIT (GEBTA – 192.168.2.21).

Create Database & Schema in SIT:

-- Connect as superuser on SIT
[postgres@lxceftsgvdb01 ~]$ psql
psql (17.10)
Type "help" for help.

postgres=#


-- Create tablespaces (ensure directories exist)
mkdir -p /pgTBS/tsdata/ts_data_sit

postgres=# CREATE TABLESPACE ts_data_sit LOCATION '/pgTBS/tsdata/ts_data_sit';
CREATE TABLESPACE
postgres=#

-- Create empty database
CREATE DATABASE gebta tablespace ts_data_sit;

-- Create user
CREATE USER gebadm WITH PASSWORD 'gebadm';

-- Connect as superuser
\c gebta postgres

-- Create schema
CREATE SCHEMA geb AUTHORIZATION gebadm;

-- Verify
\dn geb

Setup Backup Directory in SIT:

mkdir -p /pgBackup/gebta/backup
chown postgres:postgres /pgBackup/gebta/backup
chmod 755 /pgBackup/gebta/backup

3. Sample Data in UAT

Insert sample data in UAT that will be backed up and restored to SIT.

postgres=# ALTER ROLE gebadm SET search_path = geb;
ALTER ROLE
postgres=#

[postgres@pgdb02 ~]$ pgbench -i -s 100 -U gebadm gebua
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
vacuuming...
creating primary keys...
done in 41.90 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 28.47 s, vacuum 1.59 s, primary keys 11.81 s).
[postgres@pgdb02 ~]$

gebua=# \dt+ geb.*
                                          List of relations
 Schema |       Name       | Type  | Owner  | Persistence | Access method |    Size    | Description
--------+------------------+-------+--------+-------------+---------------+------------+-------------
 geb    | dept             | table | gebadm | permanent   | heap          | 8192 bytes |
 geb    | emp              | table | gebadm | permanent   | heap          | 16 kB      |
 geb    | pgbench_accounts | table | gebadm | permanent   | heap          | 1281 MB    |
 geb    | pgbench_branches | table | gebadm | permanent   | heap          | 40 kB      |
 geb    | pgbench_history  | table | gebadm | permanent   | heap          | 0 bytes    |
 geb    | pgbench_tellers  | table | gebadm | permanent   | heap          | 80 kB      |
(6 rows)

gebua=#

[postgres@pgdb02 backup]$ psql -d gebua -c "SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM geb.dept UNION ALL
SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL
SELECT 'pgbench_accounts' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_accounts UNION ALL
SELECT 'pgbench_branches' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_branches UNION ALL
SELECT 'pgbench_history' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_history UNION ALL
SELECT 'pgbench_tellers' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_tellers;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@pgdb02 backup]$

4. Backup & Restore (All Formats)

4.1 Plain Text (SQL) Format

BACKUP on UAT:

-- Execute on UAT (192.168.2.23) as postgres user
cd /pgBackup/gebua/backup

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fp -v -f 01_task_gebua_full_backup_sql.sql 2>01_task_gebua_full_backup_sql.log &
[1] 3786
[postgres@pgdb02 backup]$
[postgres@pgdb02 backup]$ jobs -l
[1]+ 3786 Done nohup pg_dump -d gebua -Fp -v -f 01_task_gebua_full_backup_sql.sql 2> 01_task_gebua_full_backup_sql.log
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 01_task_gebua_full_backup_sql.log
nohup: ignoring input and appending output to 'nohup.out'
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 subscription membership of tables
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: creating SCHEMA "geb"
pg_dump: creating TABLE "geb.dept"
pg_dump: creating TABLE "geb.emp"
pg_dump: creating TABLE "geb.pgbench_accounts"
pg_dump: creating TABLE "geb.pgbench_branches"
pg_dump: creating TABLE "geb.pgbench_history"
pg_dump: creating TABLE "geb.pgbench_tellers"
pg_dump: processing data for table "geb.dept"
pg_dump: dumping contents of table "geb.dept"
pg_dump: processing data for table "geb.emp"
pg_dump: dumping contents of table "geb.emp"
pg_dump: processing data for table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: processing data for table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: processing data for table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: processing data for table "geb.pgbench_tellers"
pg_dump: dumping contents of table "geb.pgbench_tellers"
pg_dump: creating CONSTRAINT "geb.dept dept_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

-- Execute on UAT server
scp /pgBackup/gebua/backup/01_task_gebua_full_backup_sql.sql postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT:

-- Execute on SIT (192.168.2.21) as postgres user
cd /pgBackup/gebta/backup

-- 1. Block normal users
SELECT datname, datconnlimit FROM pg_database WHERE datname = 'gebta';
ALTER DATABASE gebta CONNECTION LIMIT 0;

-- 2. Disconnect normal users
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'gebta' AND usename <> 'postgres';

-- 3. Restore from SQL

-- 4. Restore the original connection limit (unlimited) 
ALTER DATABASE gebta CONNECTION LIMIT -1;

postgres=# \c gebta
You are now connected to database "gebta" as user "postgres".
gebta=#
gebta=# drop schema geb cascade;
NOTICE: drop cascades to 7 other objects
DETAIL: drop cascades to table geb.dept
drop cascades to table geb.emp
drop cascades to table geb.hr
drop cascades to table geb.pgbench_accounts
drop cascades to table geb.pgbench_branches
drop cascades to table geb.pgbench_history
drop cascades to table geb.pgbench_tellers
DROP SCHEMA
gebta=#

[postgres@lxceftsgvdb01 backup]$ nohup psql -d gebta -f 01_task_gebua_full_backup_sql.sql > 01_task_gebua_full_restore_sql.log 2>&1 &
[1] 3693
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ jobs -l
[1]+ 3693 Running nohup psql -d gebta -f 01_task_gebua_full_backup_sql.sql > 01_task_gebua_full_restore_sql.log 2>&1 &
[postgres@lxceftsgvdb01 backup]$
[1]+ Done nohup psql -d gebta -f 01_task_gebua_full_backup_sql.sql > 01_task_gebua_full_restore_sql.log 2>&1
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 01_task_gebua_full_restore_sql.log
nohup: ignoring input
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
COPY 5
COPY 3
COPY 10000000
COPY 100
COPY 0
COPY 1000
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
[postgres@lxceftsgvdb01 backup]$

--- Verify 

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM geb.dept UNION ALL
SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL
SELECT 'pgbench_accounts' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_accounts UNION ALL
SELECT 'pgbench_branches' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_branches UNION ALL
SELECT 'pgbench_history' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_history UNION ALL
SELECT 'pgbench_tellers' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_tellers;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

4.2 Custom (DMP) Format – RECOMMENDED

BACKUP on UAT:

-- Execute on UAT (192.168.2.23) as postgres user
cd /pgBackup/gebua/backup

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fc -v -f 02_task_gebua_full_backup_binary.dmp > 02_task_gebua_full_backup_binary.log 2>&1 &
[1] 3866
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 02_task_gebua_full_backup_binary.log
nohup: ignoring input
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 subscription membership of tables
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 "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

-- Execute on UAT server
scp /pgBackup/gebua/backup/02_task_gebua_full_backup_binary.dmp postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT:

-- Execute on SIT (192.168.2.21) as postgres user
cd /pgBackup/gebta/backup

-- 1. Block normal users
SELECT datname, datconnlimit FROM pg_database WHERE datname = 'gebta';
ALTER DATABASE gebta CONNECTION LIMIT 0;

-- 2. Disconnect normal users
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'gebta' AND usename <> 'postgres';

-- 3. Restore from DMP
nohup pg_restore -d gebta -Fc -c -v 02_task_gebua_full_backup_binary.dmp > 02_task_gebua_full_restore_binary.log 2>&1 &

-- 4. Restore the original connection limit (unlimited) 
ALTER DATABASE gebta CONNECTION LIMIT -1

-- Restore from DMP
[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fc -c -v 02_task_gebua_full_backup_binary.dmp > 02_task_gebua_full_restore_binary.log 2>&1 &
[1] 3768
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 02_task_gebua_full_restore_binary.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$

-- Verify
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM geb.dept UNION ALL
SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL
SELECT 'pgbench_accounts' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_accounts UNION ALL
SELECT 'pgbench_branches' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_branches UNION ALL
SELECT 'pgbench_history' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_history UNION ALL
SELECT 'pgbench_tellers' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_tellers;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

4.3 Tar Format

BACKUP on UAT:

-- Execute on SIT (192.168.2.21) as postgres user
cd /pgBackup/gebta/backup

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Ft -v -f 03_task_gebua_full_backup_binary.tar > 03_task_gebua_full_backup_tar.log 2>&1 &
[1] 3896
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 03_task_gebua_full_backup_tar.log
nohup: ignoring input
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 subscription membership of tables
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 "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
pg_dump: dropping DATABASE PROPERTIES gebua
pg_dump: dropping DATABASE gebua
pg_dump: creating DATABASE "gebua"
pg_dump: connecting to new database "gebua"
pg_dump: creating DATABASE PROPERTIES "gebua"
pg_dump: connecting to new database "gebua"
pg_dump: creating SCHEMA "geb"
pg_dump: creating TABLE "geb.dept"
pg_dump: creating TABLE "geb.emp"
pg_dump: creating TABLE "geb.pgbench_accounts"
pg_dump: creating TABLE "geb.pgbench_branches"
pg_dump: creating TABLE "geb.pgbench_history"
pg_dump: creating TABLE "geb.pgbench_tellers"
pg_dump: processing data for table "geb.dept"
pg_dump: processing data for table "geb.emp"
pg_dump: processing data for table "geb.pgbench_accounts"
pg_dump: processing data for table "geb.pgbench_branches"
pg_dump: processing data for table "geb.pgbench_history"
pg_dump: processing data for table "geb.pgbench_tellers"
pg_dump: creating CONSTRAINT "geb.dept dept_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_dump: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@pgdb02 backup]$

TRANSFER to SIT:

-- Execute on UAT server
scp /pgBackup/gebua/backup/03_task_gebua_full_backup_binary.tar postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT:

-- Execute on SIT (192.168.2.21) as postgres user
cd /pgBackup/gebta/backup

-- 1. Block normal users
SELECT datname, datconnlimit FROM pg_database WHERE datname = 'gebta';
ALTER DATABASE gebta CONNECTION LIMIT 0;

-- 2. Disconnect normal users
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'gebta' AND usename <> 'postgres';

-- 3. Restore from TAR
nohup pg_restore -d gebta -Ft -c -v 03_task_gebua_full_backup_binary.tar > 03_task_gebua_full_restore_tar.log 2>&1 &

-- 4. Restore the original connection limit (unlimited) 
ALTER DATABASE gebta CONNECTION LIMIT -1


[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Ft -c -v 03_task_gebua_full_backup_binary.tar > 03_task_gebua_full_restore_tar.log 2>&1 &
[1] 3811
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ cat 03_task_gebua_full_restore_tar.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$

[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM geb.dept UNION ALL
SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL
SELECT 'pgbench_accounts' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_accounts UNION ALL
SELECT 'pgbench_branches' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_branches UNION ALL
SELECT 'pgbench_history' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_history UNION ALL
SELECT 'pgbench_tellers' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_tellers;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

4.4 Directory Format

BACKUP on UAT:

-- Execute on UAT (192.168.2.23) as postgres user
cd /pgBackup/gebua/backup

nohup pg_dump -d gebua -Fd -v -f 04_task_gebua_full_backup_directory > 04_task_gebua_full_backup_directory.log 2>&1 &   <---- Without parallel
nohup pg_dump -d gebua -Fd -v -j2 -f 04_task_gebua_full_backup_directory_01 > 04_task_gebua_full_backup_directory_01.log 2>&1 &  <---- with parallel

**** directory 04_task_gebua_full_backup_directory  will be created automatically by pg_dump -Fd  *********

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fd -v -f 04_task_gebua_full_backup_directory > 04_task_gebua_full_backup_directory.log 2>&1 &
[1] 3927
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ ls -l /pgBackup/gebua/backup/04_task_gebua_full_backup_directory
total 27468
-rw-r--r--. 1 postgres postgres 63 Jul 14 14:53 4363.dat.gz
-rw-r--r--. 1 postgres postgres 145 Jul 14 14:53 4364.dat.gz
-rw-r--r--. 1 postgres postgres 25 Jul 14 14:53 4365.dat.gz
-rw-r--r--. 1 postgres postgres 1774 Jul 14 14:53 4366.dat.gz
-rw-r--r--. 1 postgres postgres 28095689 Jul 14 14:53 4367.dat.gz
-rw-r--r--. 1 postgres postgres 208 Jul 14 14:53 4368.dat.gz
-rw-r--r--. 1 postgres postgres 5660 Jul 14 14:53 toc.dat
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ cat 04_task_gebua_full_backup_directory.log
nohup: ignoring input
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 subscription membership of tables
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 "geb.dept"
pg_dump: dumping contents of table "geb.emp"
pg_dump: dumping contents of table "geb.pgbench_accounts"
pg_dump: dumping contents of table "geb.pgbench_branches"
pg_dump: dumping contents of table "geb.pgbench_history"
pg_dump: dumping contents of table "geb.pgbench_tellers"
[postgres@pgdb02 backup]$


with Parallel -j6

[postgres@pgdb02 backup]$ nohup pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01 > 05_task_gebua_full_backup_directory_01.log 2>&1 &
[1] 3851
[postgres@pgdb02 backup]$
[postgres@pgdb02 backup]$ jobs -l
[1]+ 3851 Running nohup pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01 > 05_task_gebua_full_backup_directory_01.log 2>&1 &
[postgres@pgdb02 backup]$
[postgres@pgdb02 backup]$ ps -ef | grep postgres
root 3276 3198 0 14:57 pts/0 00:00:00 su - postgres
postgres 3277 3276 0 14:57 pts/0 00:00:00 -bash
postgres 3341 1 0 14:57 ? 00:00:00 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data
postgres 3342 3341 0 14:57 ? 00:00:00 postgres: logger
postgres 3343 3341 0 14:57 ? 00:00:00 postgres: checkpointer
postgres 3344 3341 0 14:57 ? 00:00:00 postgres: background writer
postgres 3347 3341 0 14:57 ? 00:00:01 postgres: walwriter
postgres 3348 3341 0 14:57 ? 00:00:00 postgres: autovacuum launcher
postgres 3349 3341 0 14:57 ? 00:00:00 postgres: logical replication launcher
postgres 3851 3277 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3852 3341 1 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3853 3851 58 17:39 pts/0 00:00:04 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3854 3851 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3855 3341 0 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3856 3851 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3857 3851 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3858 3341 32 17:39 ? 00:00:02 postgres: postgres gebua [local] COPY
postgres 3859 3851 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3860 3341 0 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3861 3851 0 17:39 pts/0 00:00:00 pg_dump -d gebua -Fd -v -j6 -f 05_task_gebua_full_backup_directory_01
postgres 3862 3341 0 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3863 3341 0 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3864 3341 0 17:39 ? 00:00:00 postgres: postgres gebua [local] idle in transaction
postgres 3865 3277 0 17:39 pts/0 00:00:00 ps -ef
postgres 3866 3277 0 17:39 pts/0 00:00:00 grep --color=auto postgres
[postgres@pgdb02 backup]$

[postgres@pgdb02 backup]$ pstree -p 3851 
pg_dump(3851)─┬─pg_dump(3853)
              ├─pg_dump(3854)
              ├─pg_dump(3856)
              ├─pg_dump(3857)
              ├─pg_dump(3859)
              └─pg_dump(3861)
[postgres@pgdb02 backup]$


3851 --- Master , Rest all workers

TRANSFER to SIT:

-- Execute on UAT server
scp -r /pgBackup/gebua/backup/04_task_gebua_full_backup_directory postgres@192.168.2.21:/pgBackup/gebta/backup/

RESTORE on SIT:

-- Execute on SIT (192.168.2.21) as postgres user
cd /pgBackup/gebta/backup

-- 1. Block normal users
SELECT datname, datconnlimit FROM pg_database WHERE datname = 'gebta';
ALTER DATABASE gebta CONNECTION LIMIT 0;

-- 2. Disconnect normal users
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'gebta' AND usename <> 'postgres';

-- 3. Restore from directory
nohup pg_restore -d gebta -Fd -c -v 04_task_gebua_full_backup_directory > 04_task_gebua_full_restore_directory.log 2>&1 &

-- 4. Restore the original connection limit (unlimited) 
ALTER DATABASE gebta CONNECTION LIMIT -1


[postgres@lxceftsgvdb01 backup]$ nohup pg_restore -d gebta -Fd -c -v 04_task_gebua_full_backup_directory > 04_task_gebua_full_restore_directory.log 2>&1 &
[1] 3909
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ cat 04_task_gebua_full_restore_directory.log
nohup: ignoring input
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta -c "SELECT 'dept' AS table_name, COUNT(*) AS row_count FROM geb.dept UNION ALL
SELECT 'emp' AS table_name, COUNT(*) AS row_count FROM geb.emp UNION ALL
SELECT 'pgbench_accounts' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_accounts UNION ALL
SELECT 'pgbench_branches' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_branches UNION ALL
SELECT 'pgbench_history' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_history UNION ALL
SELECT 'pgbench_tellers' AS table_name, COUNT(*) AS row_count FROM geb.pgbench_tellers;"
    table_name    | row_count
------------------+-----------
 dept             |         5
 emp              |         3
 pgbench_accounts |  10000000
 pgbench_branches |       100
 pgbench_history  |         0
 pgbench_tellers  |      1000
(6 rows)

[postgres@lxceftsgvdb01 backup]$

5. Restore database with –no-owner and –no-tablespaces

You need to restore a PostgreSQL database when:

The source database owner does not exist on the target PostgreSQL server.
Some tables in the source database are stored in tablespaces other than the database’s default tablespace, and those tablespaces do not exist on the target server.

postgres=# CREATE TABLESPACE ts_data_uat LOCATION ‘/pgTBS/tsdata/ts_data_uat’;
CREATE TABLESPACE
postgres=# CREATE TABLESPACE ts_data_uat2 LOCATION ‘/pgTBS/tsdata/ts_data_uat2’;
CREATE TABLESPACE
postgres=#
postgres=# CREATE DATABASE gebua tablespace ts_data_uat;
CREATE DATABASE
postgres=#

CREATE TABLE geb.hr(emp_id int, emp_name varchar) TABLESPACE ts_data_uat2; <— geb.hr table created in tablespaces other than the database’s default tablespace

For example, the source database contains the GEB schema owned by GEBADM, but the GEBADM role does not exist on the target PostgreSQL instance. In addition, several tables are located in custom tablespaces.

If you perform a normal restore, PostgreSQL report errors because it cannot assign ownership to a role that does not exist or create objects in tablespaces that are unavailable on the target server. However, the database objects and data are still restored successfully. The restored schema is owned by the user performing the restore (for example, postgres), and all objects are created in the target database’s default tablespace.

To avoid these problems, restore the database using the --no-owner and --no-tablespaces options.

--no-owner prevents PostgreSQL from restoring the original object ownership. The objects will be owned by the user performing the restore (for example, postgres).
--no-tablespaces ignores the original tablespace definitions and restores all objects into the target database’s default tablespace.

-- Restore without --no-owner and --no-tablespaces options.

Backup on source:

[postgres@pgdb02 backup]$ psql -d gebua
psql (17.10)
Type "help" for help.

gebua=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 geb    | gebadm            |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

gebua=# \dt geb.*
             List of relations
 Schema |       Name       | Type  | Owner
--------+------------------+-------+--------
 geb    | dept             | table | gebadm
 geb    | emp              | table | gebadm
 geb    | hr               | table | gebadm
 geb    | pgbench_accounts | table | gebadm
 geb    | pgbench_branches | table | gebadm
 geb    | pgbench_history  | table | gebadm
 geb    | pgbench_tellers  | table | gebadm
(7 rows)

gebua=# select count(*) from geb.hr;
 count
-------
     3
(1 row)

gebua=#


[postgres@pgdb02 backup]$ pg_dump -d gebua -Fc -v -f 03_task_gebua_full_backup_binary.dmp 2> 03_task_gebua_full_backup_binary.log


scp to target server

[postgres@pgdb02 backup]$ scp 03_task_gebua_full_backup_binary.dmp lxceftsgvdb01:/pgBackup/gebta/backup
03_task_gebua_full_backup_binary.dmp             100%                 27MB            49.5MB/s               00:00
[postgres@pgdb02 backup]$

Cleanup:
[postgres@lxceftsgvdb01 backup]$ psql -d gebta
psql (17.10)
Type "help" for help.

gebta=# drop schema geb cascade;
NOTICE:  drop cascades to 3 other objects
DETAIL:  drop cascades to table geb.dept
drop cascades to table geb.emp
drop cascades to table geb.hr
DROP SCHEMA
gebta=#
gebta=# \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                   |
(1 row)

gebta=#
gebta=# exit
[postgres@lxceftsgvdb01 backup]$

Restore:
[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -Fc -c -v 03_task_gebua_full_backup_binary.dmp 2> 03_task_gebua_full_backup_binary1.log
[postgres@lxceftsgvdb01 backup]$ cat 03_task_gebua_full_backup_binary1.log
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4217; 2606 41048 CONSTRAINT pgbench_tellers pgbench_tellers_pkey gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: ALTER TABLE ONLY geb.pgbench_tellers DROP CONSTRAINT pgbench_tellers_pkey;
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: from TOC entry 4221; 2606 41046 CONSTRAINT pgbench_branches pgbench_branches_pkey gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: ALTER TABLE ONLY geb.pgbench_branches DROP CONSTRAINT pgbench_branches_pkey;
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: from TOC entry 4219; 2606 41050 CONSTRAINT pgbench_accounts pgbench_accounts_pkey gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: ALTER TABLE ONLY geb.pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey;
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: from TOC entry 4215; 2606 32853 CONSTRAINT dept dept_pkey gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: ALTER TABLE ONLY geb.dept DROP CONSTRAINT dept_pkey;
pg_restore: dropping TABLE pgbench_tellers
pg_restore: from TOC entry 222; 1259 41032 TABLE pgbench_tellers gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.pgbench_tellers;
pg_restore: dropping TABLE pgbench_history
pg_restore: from TOC entry 221; 1259 41029 TABLE pgbench_history gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.pgbench_history;
pg_restore: dropping TABLE pgbench_branches
pg_restore: from TOC entry 224; 1259 41038 TABLE pgbench_branches gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.pgbench_branches;
pg_restore: dropping TABLE pgbench_accounts
pg_restore: from TOC entry 223; 1259 41035 TABLE pgbench_accounts gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.pgbench_accounts;
pg_restore: dropping TABLE hr
pg_restore: from TOC entry 220; 1259 32859 TABLE hr gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.hr;
pg_restore: dropping TABLE emp
pg_restore: from TOC entry 218; 1259 32844 TABLE emp gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.emp;
pg_restore: dropping TABLE dept
pg_restore: from TOC entry 219; 1259 32849 TABLE dept gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP TABLE geb.dept;
pg_restore: dropping SCHEMA geb
pg_restore: from TOC entry 6; 2615 32843 SCHEMA geb gebadm
pg_restore: error: could not execute query: ERROR:  schema "geb" does not exist
Command was: DROP SCHEMA geb;
pg_restore: creating SCHEMA "geb"
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER SCHEMA geb OWNER TO gebadm;

pg_restore: creating TABLE "geb.dept"
pg_restore: from TOC entry 219; 1259 32849 TABLE dept gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.dept OWNER TO gebadm;

pg_restore: creating TABLE "geb.emp"
pg_restore: from TOC entry 218; 1259 32844 TABLE emp gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.emp OWNER TO gebadm;

pg_restore: creating TABLE "geb.hr"
pg_restore: from TOC entry 220; 1259 32859 TABLE hr gebadm
pg_restore: error: could not set "default_tablespace" to ts_data_uat2: ERROR:  invalid value for parameter "default_tablespace": "ts_data_uat2"
DETAIL:  Tablespace "ts_data_uat2" does not exist.
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.hr OWNER TO gebadm;

pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: from TOC entry 223; 1259 41035 TABLE pgbench_accounts gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.pgbench_accounts OWNER TO gebadm;

pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: from TOC entry 224; 1259 41038 TABLE pgbench_branches gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.pgbench_branches OWNER TO gebadm;

pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: from TOC entry 221; 1259 41029 TABLE pgbench_history gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.pgbench_history OWNER TO gebadm;

pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: from TOC entry 222; 1259 41032 TABLE pgbench_tellers gebadm
pg_restore: error: could not execute query: ERROR:  role "gebadm" does not exist
Command was: ALTER TABLE geb.pgbench_tellers OWNER TO gebadm;

pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.hr"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
pg_restore: warning: errors ignored on restore: 21
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$
[postgres@lxceftsgvdb01 backup]$ psql -d gebta
psql (17.10)
Type "help" for help.

gebta=# \dn+
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description
--------+-------------------+----------------------------------------+------------------------
 geb    | postgres          |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   |
(2 rows)

gebta=# \dt geb.*
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 geb    | dept             | table | postgres
 geb    | emp              | table | postgres
 geb    | hr               | table | postgres
 geb    | pgbench_accounts | table | postgres
 geb    | pgbench_branches | table | postgres
 geb    | pgbench_history  | table | postgres
 geb    | pgbench_tellers  | table | postgres
(7 rows)

gebta=# select count(*) from geb.hr;
 count
-------
     3  <-------------
(1 row)

gebta=#

Conclusion
If you perform a normal restore, PostgreSQL report errors because it cannot assign ownership to a role that does not exist or create objects in tablespaces that are unavailable on the target server. However, the database objects and data are still restored successfully. The restored schema is owned by the user performing the restore (for example, postgres), and all objects are created in the target database’s default tablespace.

If you do not want to see these ownership and tablespace-related errors in the restore log, use the --no-owner and --no-tablespaces options.

Restore with –no-owner and –no-tablespaces Options

[postgres@lxceftsgvdb01 backup]$ pg_restore -d gebta -Fc -c -v --no-owner --no-tablespaces 03_task_gebua_full_backup_binary.dmp 2>03_task_gebua_full_backup_binary2.log

Review the restore log:

[postgres@lxceftsgvdb01 backup]$ cat 03_task_gebua_full_backup_binary2.log
pg_restore: connecting to database for restore
pg_restore: dropping CONSTRAINT pgbench_tellers pgbench_tellers_pkey
pg_restore: dropping CONSTRAINT pgbench_branches pgbench_branches_pkey
pg_restore: dropping CONSTRAINT pgbench_accounts pgbench_accounts_pkey
pg_restore: dropping CONSTRAINT dept dept_pkey
pg_restore: dropping TABLE pgbench_tellers
pg_restore: dropping TABLE pgbench_history
pg_restore: dropping TABLE pgbench_branches
pg_restore: dropping TABLE pgbench_accounts
pg_restore: dropping TABLE hr
pg_restore: dropping TABLE emp
pg_restore: dropping TABLE dept
pg_restore: dropping SCHEMA geb
pg_restore: creating SCHEMA "geb"
pg_restore: creating TABLE "geb.dept"
pg_restore: creating TABLE "geb.emp"
pg_restore: creating TABLE "geb.hr"
pg_restore: creating TABLE "geb.pgbench_accounts"
pg_restore: creating TABLE "geb.pgbench_branches"
pg_restore: creating TABLE "geb.pgbench_history"
pg_restore: creating TABLE "geb.pgbench_tellers"
pg_restore: processing data for table "geb.dept"
pg_restore: processing data for table "geb.emp"
pg_restore: processing data for table "geb.hr"
pg_restore: processing data for table "geb.pgbench_accounts"
pg_restore: processing data for table "geb.pgbench_branches"
pg_restore: processing data for table "geb.pgbench_history"
pg_restore: processing data for table "geb.pgbench_tellers"
pg_restore: creating CONSTRAINT "geb.dept dept_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_accounts pgbench_accounts_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_branches pgbench_branches_pkey"
pg_restore: creating CONSTRAINT "geb.pgbench_tellers pgbench_tellers_pkey"
[postgres@lxceftsgvdb01 backup]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

BRIGHT DBA

ORACLE DBA – Tips and Techniques

pg_dump – Database-Level Backup & Restore

pg_dump – Database Backup & Restore (UAT → SIT Migration)

Contents

1. UAT Environment Setup (Source)

2. SIT Environment Setup (Target)

3. Sample Data in UAT

4. Backup & Restore (All Formats)

4.1 Plain Text (SQL) Format

4.2 Custom (DMP) Format – RECOMMENDED

4.3 Tar Format

4.4 Directory Format

5. Restore database with –no-owner and –no-tablespaces

Restore with –no-owner and –no-tablespaces Options