[9.2] backup and recovery using pg_dump and pg_restore

/********************************************************************************************
-- Title : [9.2] backup and recovery using pg_dump and pg_restore
-- Reference : dbrang.tistory.com
-- Key word : postgresql pg_dump pg_restore backup restore recovery 백업 복구
********************************************************************************************/

/***
**** Feature and Syntax
***/

-- Feature of pg_dump
    o Able to read, write on running.
    o No special privileges, but encourage or recommend  superuser.
    o No commit insert, update after pg_dump as pg_dump is snapshot.
    o Needs bigger memory and big disk space.
    o 압축 옵션(compress) 사용시 용량 정략됨(60~70%?)

-- Syntax of pg_dump
    o pg_dump [options] [dbname]
    o pg_dump dbname > dbname.dmp
    o pg_dump -h host -p port -U userid -W password

-- Syntax of pg_dump with gzip, split
    o  pg_dump dbname | gzip > filename.gz
        reload) gunzip filename.gz |gunzip | psql dbname
    o  pg_dump dbname | split -b 1m -filename
        reload) cat filename* | psql dbname
 
-- pg_dump/pg_restore Options : dbrang.tistory.com/805

-- Sample Database : dbrang.tistory.com/804


 

/***

**** Using Plain SQL Text

***/

---------

-- Backup 

---------

-- database level

$ pg_dump tttdb -U pgsys -Fp > /home/backup/dump_backup/101.dump_tttdb.dmp   

  or   

$ pg_dump tttdb -U pgsys -f /home/backup/dump_backup/102.dump_tttdb.dmp   

  or   

$ pg_dump tttdb -U pgsys -Fp -f /home/backup/dump_backup/103.dump_tttdb.dmp  

  or

$ pg_dump tttdb > /home/backup/dump_backup/104.dump_tttdb.dmp

-- schema only(-n : namespace schema)

$ pg_dump tttdb -b -n sch_shop -f /home/backup/dump_backup/201.dump_tttdb_sch_shop.dmp

  or

$ pg_dump tttdb -b -n sch_shop > /home/backup/dump_backup/202.dump_tttdb_sch_shop.dmp

-- object definition only(-s)

$ pg_dump tttdb -b -s -f /home/backup/dump_backup/301.dump_tttdb_def.dmp

-- table only(-t)

$ pg_dump tttdb -t sch_shop.store -f "/home/backup/dump_backup/401.dump_tttdb_sch_shop_store.dmp"

-- data only without definition(-a)

$ pg_dump tttdb -a -t sch_shop.store > /home/backup/dump_backup/501.dump_tttdb_sch_shop_store_data.dmp

----------

-- Restore

----------

-- database level

drop database tttdb;

CREATE DATABASE tttdb

WITH OWNER = postgres

     TABLESPACE = tbs_data0;

$ psql tttdb -U pgsys -f /home/backup/dump_backup/103.dump_tttdb.dmp 

  or

$ psql tttdb -U pgsys -Fp -f /home/backup/dump_backup/102.dump_tttdb.dmp 

  or 

$ psql tttdb -f "/home/backup/dump_backup/104.dump_tttdb.dmp"

  or 

=# \i /home/backup/dump_backup/104.dump_tttdb.dmp

select * from sch_shop.store;

-- schema only

drop table sch_shop.staff cascade;

drop table sch_shop.store cascade;

drop table sch_shop.payment cascade;

drop table sch_shop.rental cascade;

$ psql tttdb -f /home/backup/dump_backup/201.dump_tttdb_sch_shop.dmp

  or

$ psql tttdb -U pgsys -Fp -f /home/backup/dump_backup/202.dump_tttdb_sch_shop.dmp

select * from sch_shop.store;

-- definition only

drop table sch_shop.store cascade;

$ psql tttdb -f /home/backup/dump_backup/301.dump_tttdb_def.dmp

select * from sch_shop.store; /*no result*/

-- table only(-t)

drop table sch_shop.store cascade;

$ psql tttdb -f /home/backup/dump_backup/401.dump_tttdb_sch_shop_store.dmp

select * from sch_shop.store; 

-- Dump Data Only without definition(-a)

delete from sch_shop.store;

$ psql tttdb -f /home/backup/dump_backup/501.dump_tttdb_sch_shop_store_data.dmp

select * from sch_shop.store; 

/***

**** Using Custom format

***/

---------

-- Backup

---------

-- database level

$ pg_dump tttdb -U pgsys -Fc > /home/backup/dump_backup/1001.dump_tttdb.dmpc   

  or   

$ pg_dump tttdb -U pgsys -Fc -f /home/backup/dump_backup/1002.dump_tttdb.dmpc   

  or  

$ pg_dump tttdb -Fc > /home/backup/dump_backup/1003.dump_tttdb.dmpc

-- schema only(-n : namespace schema)

$ pg_dump tttdb -b -n sch_shop -Fc -f /home/backup/dump_backup/2001.dump_tttdb_sch_shop.dmpc

  or

$ pg_dump tttdb -b -n sch_shop -Fc > /home/backup/dump_backup/2002.dump_tttdb_sch_shop.dmpc

-- object definition only(-s)

$ pg_dump tttdb -b -s -Fc -f /home/backup/dump_backup/3001.dump_tttdb_def.dmpc

-- table only(-t)

$ pg_dump tttdb -t sch_shop.store -Fc -f /home/backup/dump_backup/4001.dump_tttdb_sch_shop_store.dmpc

-- data only without definition(-a)

$ pg_dump tttdb -a -t sch_shop.store -Fc > /home/backup/dump_backup/5001.dump_tttdb_sch_shop_store_data.dmpc

----------

-- Restore

----------

-- database level

drop database tttdb;

CREATE DATABASE tttdb

WITH OWNER = postgres

     TABLESPACE = tbs_data0;

$ pg_restore -d tttdb -U pgsys -Fc /home/backup/dump_backup/1001.dump_tttdb.dmpc

select * from sch_shop.store;

-- schema only

drop table sch_shop.staff cascade;

drop table sch_shop.store cascade;

drop table sch_shop.payment cascade;

drop table sch_shop.rental cascade;

$ pg_restore -d tttdb -U pgsys -Fc /home/backup/dump_backup/2002.dump_tttdb_sch_shop.dmpc

select * from sch_shop.store;

-- definition only

drop table sch_shop.store cascade;

$ pg_restore -d tttdb -Fc /home/backup/dump_backup/3001.dump_tttdb_def.dmpc

select * from sch_shop.store; /*no result*/

-- table only(-t)

drop table sch_shop.store cascade;

$ pg_restore -d tttdb -Fc /home/backup/dump_backup/4001.dump_tttdb_sch_shop_store.dmpc

select * from sch_shop.store; 

-- Dump Data Only without definition(-a)

delete from sch_shop.store;

$ pg_restore -d tttdb -Fc /home/backup/dump_backup/5001.dump_tttdb_sch_shop_store_data.dmpc

select * from sch_shop.store; 

/***

**** Using Tar format

***/

---------

-- Backup

---------

-- table only(-t)

$ pg_dump tttdb -t sch_shop.store -Ft -f /home/backup/dump_backup/8001.dump_tttdb_sch_shop_store.dmpt

  or

$ pg_dump tttdb -t sch_shop.store -Ft > /home/backup/dump_backup/8002.dump_tttdb_sch_shop_store.dmpt

----------

-- Restore

----------

-- table only(-t)

drop table sch_shop.store cascade;

$ pg_restore -d tttdb -Ft /home/backup/dump_backup/8001.dump_tttdb_sch_shop_store.dmpt

select * from sch_shop.store; 

/***

**** Using gzip and split

***/

-------------------

-- Backup with gzip

-------------------

-- pg_dump with gzip

$ pg_dump tttdb | gzip > /home/backup/dump_backup/10001.dump_tttdb.gz

$ pg_dump tttdb -b -n sch_shop | gzip > /home/backup/dump_backup/10002.dump_tttdb.gz

-- restore with gunzip

$ gunxip -c /home/backup/dump_backup/10001.dump_tttdb.gz | psql tttdb

drop table sch_shop.staff cascade;

drop table sch_shop.store cascade;

drop table sch_shop.payment cascade;

drop table sch_shop.rental cascade;

$ cat /home/backup/dump_backup/10002.dump_tttdb.gz | gunzip | psql tttdb

select * from sch_shop.store;

--------------------

-- Backup with split

--------------------

-- Dump Database with split

$ pg_dump tttdb | split -b 1m - /home/backup/dump_backup/20001.dump_tttdb_split

-- resotre with split

drop table sch_shop.store cascade;

$ cat /home/backup/dump_backup/20001.dump_tttdb_split* | psql tttdb

select * from sch_shop.store;


 /***
**** Compare File Size 
***/