📚 Using PG_Dump to backup specific objects¶

### 1. For example backup Table in DB（schema + data）

• 1.1 Doing the dump aalc tables for postgres DB

pg_dump -h localhost -U postgres -p 5435 -Fc --table=aalc postgres > potstgres.dump

ll

• 1.2 Before restoring, we check the table status

Check the table

Drop the table

• 1.3 Do the restore table from our dump file

pg_restore -h localhost -U postgres -p 5455 -d postgres --table=aalc potstgres.dump

use -Fc use pgrestor to recovery,use psql Fp(Default) use psql to recover

• 1.4 Go to check table again. You will find the deleted tableback

### 2. For example backup a DB in cluster（schema + data）

pg_dump -h 127.0.0.1 -p port -d dbname -C -f /tmp/dbname.sql

Note: use “-C” include Create Dabase in the dump file

### 3. backup Schema in DB（schema + data）

pg_dump -h 127.0.0.1 -p port -d dbname -n schemaname -f /tmp/schemaname.sql

Note: Use “--inserts” Will cause the backup files to increase in size, Advantage is quickly execute a single resotre command

### 4. backup schema in DB and Restore a part（schema + data）

• 4.1 Doing the dump MMS_PRD Schema for postgres DB

pg_dump -h localhost -U postgres -p 5436 -Fc -d ZSMOHP -n \"MMS_PRD\" > potstgres.dump

• 4.2 Selective backup and recovery of files

• 4.2.1 Generate files based on binary backup files

pg_restore -l -f potstgres.toc potstgres.dump

• 4.2.2 Modify the TOC file using '; ’Annotate the content that does not need to be restored（Example mark refrence table mms_introduce_main）

vim potstgres.toc

Ago：

After Restore a part：

• 4.2.3 Restore with TOC file list

pg_restore -p 5436 -Fc -L potstgres.toc -e -v -d postgres potstgres.dump

Ago：

After：

### 5. backup schema in DB and Restore to new Schema（schema + data）

• 5.1 Doing the dump MMS_PRD Schema for postgres DB

pg_dump -h localhost -U postgres -p 5436 -d ZSMOHP -n \"MMS_PRD\" > potstgres.sql

• 5.2 Use Sed command to replace(schema and function/trigger/producure content)

sed -i 's/MMS_PRD/MMS_RENAME/g' potstgres.sql

• 5.3 Create refrence objects（maybe you shou create tablespace and role）

create role "MMS_RENAME";

• 5.4 restore

psql -p 5436 -d postgres -f potstgres.sql

\dn