Objective: Perform schema copy in postgresql from one db to other
Method: pg_dump/pg_restore
Backup dump destination: /pgwal/logicalbkp (4GB space left)
pg version: 14.7 (source) | 15.2 (target)
PGDATA: /pgdata/14/data (14.7) | /pgdata/15/data (15.2)
Schema: test
DB Name: pgbenchdb
option to use: -n, --schema=PATTERN dump the specified schema(s) only
Steps:
1) Perform a backup of the schema (5432 port)
Command:
\dn+
\dt test.*
select * from test.table1;
output:
postgres=# \c pgbenchdb
psql (15.2, server 14.7)
You are now connected to database "pgbenchdb" as user "postgres".
pgbenchdb=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | postgres | |
(2 rows)
pgbenchdb=# \dt test.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
test | tbl1 | table | postgres
(1 row)
pgbenchdb=#
Backup Command:
pg_dump \
-d pgbenchdb \
-U postgres \
-p 5432 \
-n test \
-v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log
Backup Command Output:
-bash-4.2$ pg_dump \
> -d pgbenchdb \
> -U postgres \
> -p 5432 \
> -n test \
> -v > /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql 2>/pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log
-bash-4.2$
2) Verify the backup for any errors
ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql
grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log
Output:
-bash-4.2$ ls -altr /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql
-rw-r--r--. 1 postgres postgres 1222 Aug 16 22:31 /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql
-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log
-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.log
-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql
-bash-4.2$
3) Perform a restore of the schema in target (same server - 5433 port)
Before restore:
postgres=# \c pgbenchdb
You are now connected to database "pgbenchdb" as user "postgres".
pgbenchdb=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(1 row)
Restore Command:
psql -p 5433 \
-d pgbenchdb \
-U postgres \
-f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \
-L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log
Restore Command Output:
-bash-4.2$ psql -p 5433 \
> -d pgbenchdb \
> -U postgres \
> -f /pgwal/logicalbkp/16aug2023_pgbehnchdb_test_pgdump.sql \
> -L /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log
SET
SET
SET
SET
SET
set_config
------------
(1 row)
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
SET
CREATE TABLE
ALTER TABLE
COPY 4
4) Validate for any error
grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log
Output:
-bash-4.2$ view /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log
-bash-4.2$ grep -Ei "err|warn|caution|fatal|failure" /pgwal/logicalbkp/16aug2023_pgbehnchdb_psql.log
SET client_min_messages = warning;
-bash-4.2$
5) Verify the table accessibility in target:
Command:
\dn+
\dt test.*
select * from test.table1;
Output:
postgres=# \c pgbenchdb
You are now connected to database "pgbenchdb" as user "postgres".
pgbenchdb=# \dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
test | postgres | |
(2 rows)
pgbenchdb=# \dt test.*
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
test | tbl1 | table | postgres
(1 row)
pgbenchdb=# select * from test.tbl1;
id | name
----+------
1 | R
1 | R
1 | R
1 | R
(4 rows)
pgbenchdb=#
YouTube Video of the same:
No comments:
Post a Comment