Backup and Restore of partitioned tables

While taking backup using pg_dump utility of partitioned table, need to specify parent table as well as its child tables with the option -t.

• If we specify only the parent table with the option "-t" in that case data will be not restored on the target database, only the table will be created.
• If we provide a specific parent table as well as it’s child tables with the option "-t" then the data and table both will restored into the target database.

This article aims to show how to take a backup of the Partition Table and its Child Tables without data loss. Normally, we take a dump with the -t table name, however, if the table is partitioned table in that case we have to mention the parent Table and all child tables with the -t option to avoid data loss.

Create partitioned table "sales":

-bash-4.2$ ./psql -p 5444 edb
psql (14.6.0, server 14.6.0)
Type "help" for help.
edb=# CREATE TABLE sales
edb-# (
edb(# dept_no number, 
edb(# part_no varchar2,
edb(# country varchar2(20),
edb(# date date,
edb(# amount number
edb(# )
edb-# PARTITION BY LIST(country)
edb-# (
edb(# PARTITION europe VALUES('FRANCE', 'ITALY'),
edb(# PARTITION asia VALUES('INDIA', 'PAKISTAN'),
edb(# PARTITION americas VALUES('US', 'CANADA'),
edb(# PARTITION others VALUES (DEFAULT)
edb(# );
CREATE TABLE

edb=# \dt
List of relations
Schema | Name | Type | Owner 
public | sales | partitioned table | enterprisedb
public | sales_americas | table | enterprisedb
public | sales_asia | table | enterprisedb
public | sales_europe | table | enterprisedb
public | sales_others | table | enterprisedb
(5 rows)

edb=# INSERT INTO sales VALUES(40, '3000x', 'IRELAND', '01-Mar-2012', '45000');
INSERT 0 1

edb=# INSERT INTO sales VALUES(30, '9000x', 'I', '01-APRIL-2012', '49000');
INSERT 0 1

edb=# select * from sales;
dept_no | part_no | country | date | amount 
40 | 3000x | IRELAND | 01-MAR-12 00:00:00 | 45000
30 | 9000x | I | 01-APR-12 00:00:00 | 49000
(2 rows)

1. Take backup:

/usr/edb/as14/bin/pg_dump -Fc -p 5444 -d edb -t sales -f /opt/backup/dump.txt

2. Restore taken backup into target database db1:

/usr/edb/as14/bin/pg_restore -Fc -p 5444 -d db1 /opt/backup/dump.txt

3. Connect to the target database to check the restored table:

-bash-4.2$ ./psql -p 5444 edb
psql (14.6.0, server 14.6.0)
Type "help" for help.

edb=# \c db1
You are now connected to database "db1" as user "enterprisedb".
db1=# \dt
List of relations
Schema | Name | Type | Owner 
public | sales | partitioned table | enterprisedb
public | sales_americas | table | enterprisedb
public | sales_asia | table | enterprisedb
public | sales_europe | table | enterprisedb
public | sales_others | table | enterprisedb
(5 rows)

db1=# select * from sales;
dept_no | part_no | country | date | amount 
(0 rows)

Only table "sales" and its child tables are created on target, however, data is not restored.

1. Take a backup by providing a list of partition tables and their child tables with option -t:

/usr/edb/as14/bin/pg_dump -Fc -p 5444-d edb -t sales -t sales_americas -t sales_asia -t sales_europe -t sales_others -f /opt/backup/dump1.txt

2. Restore backup into target database db2:

/usr/edb/as14/bin/pg_restore -Fc -p 5444 -d db2 /opt/backup/dump1.txt

3. Connect to the target database to check the restored table:

-bash-4.2$ ./psql -p 5444 edb
psql (14.6.0, server 14.6.0)
Type "help" for help.

edb=# \c db2
You are now connected to database "db2" as user "enterprisedb".

db2=# \dt
List of relations
Schema | Name | Type | Owner 
public | sales | partitioned table | enterprisedb
public | sales_americas | table | enterprisedb
public | sales_asia | table | enterprisedb
public | sales_europe | table | enterprisedb
public | sales_others | table | enterprisedb
(5 rows)

db2=# select * from sales;
dept_no | part_no | country | date | amount 
40 | 3000x | IRELAND | 01-MAR-12 00:00:00 | 45000
30 | 9000x | I | 01-APR-12 00:00:00 | 49000
(2 rows)

In this case, both the partitioned table as well as data are restored on the target database.