Postgresql 백업 및 복구

1. recovery  설정

  checkpoint_segments = 10000               -- 16M base

  checkpoint_timeout = 3600

 

  pg_xlog space 계산 : 3 X 16MB X cheack_segments

2. 백업 종류 및 방식

	SQL dump to an archive file: pg_dump -F c	SQL dump to a script file: pg_dump -F p orpg_dumpall	Filesystem backup using pg_start_backup
Backup type	Logical	Logical	Physical
Recover to point in time?	No	No	Yes
Back up all databases?	One at a time	Yes (pg_dumpall)	Yes
All databases backed up at same time?	No	No	Yes
Selective backup	Yes	Yes	No (see note 3)
Incremental backup	No	No	Possible (see note 4)
Selective restore	Yes	Possible (see note 1)	No (see note 5)
DROP TABLErecovery	Yes	Yes	Possible (see note 6)
DROP TABLESPACErecovery	Possible (see note 2)	Possible (see note 6)	Possible (see note 6)
Compressed backup files	Yes	Yes	Yes
Backup in multiple files	No	No	Yes
Parallel backup possible?	No	No	Yes
Parallel restore possible?	Yes	No	Yes
Restore to later release?	Yes	Yes	No
Standalone backup	Yes	Yes	Yes (see note 7)
Allows DDL during backup	No	No	Yes

3. Hot logical backup of one database

  pg_dump는 one databases에 대해 parallel로 백업 가능 (snapshot export 특징 사용)

  pg_dump -F c > dumpfile

  pg_dump -F c -f dumpfile

  pg_dump -j 8 -F c     -- parallel 8로 백업수행

  pg_restore --schema-only -v dumpfile | head | grep Started ( start time 확인)

  pg_restore -j 8  -- parallel 8로 복구 수행

4. Hot logical backup of all databases

  전체를 받는 script를 작성해주나, database 1개씩 백업을 수해하며, 복구시 순차적으로 

  복구해야 해서, pg_dump를 사용하여 여러개 db를 동시에 백업 받는 방식이 나음

  pg_dumpall -g

5. Hot logical backup of all tables in db

  1) object가 포함된 db 확인

     select datname from pg_database where oid in (select 

     pg_tablespace_database(ts.oid) from pg_tablespace ts where spcname = 'test')

  2) DB와 테이블스페이스 이름으로 export table 추출

  SELECT 'pg_dump '

|| array_to_string(

     array_agg('-t ' || n.nspname || '.' || t.relname)

   , ' ')

|| ' -F c ' || :'DBNAME'

|| ' > dumpfile_' || :'DBNAME' -- name of the output file

FROM pg_class t

JOIN pg_tablespace ts

  ON ts.oid = t.reltablespace

JOIN pg_namespace n

  ON n.oid = t.relnamespace

WHERE ts.spcname = :'TSNAME'

  AND t.relkind = 'r';   

  3) 위에 sql로 pg_dump script 생성

     psql -Aqt -v TSNAME=myt -f myt.sql mytdb > mytdb.sql

     ./mytdb.sql

6. object meta 정보 백업

   pg_dumpall  --schema-only > alldbdump.sql  -- 전체 schema with all objects

  pg_dumpall --roles-only > role.sql              -- role

  pg_dumpall --tablespaces-only > tabsp.sql    -- tablespace

  pg_dumpall --global-only > global.sql           -- role and tablespaces

7. hot physical database backup

  1) archive directory 생성

    mkdir  pgdata/archive_dest

    mkdir  pgdata/archive_dest/active

  2) postgresql.conf에 파라미터 설정

   archive_mode = on

   archive_command = 'test ! ../pgdata/archive_dest/active || 

   cp -i %p ../pgdata/archive_dest/%f'

   (active 디렉토리가 없으면 명령 실행 안됨)

   wal_level = archive

   => server restart 

 3)  backup

    psql -c "select pg_start_backup('test')"

 4)  base backup store

    tar -cv --exclude="pg_xlog/*"  \-f  ../pgdata/backup.tar  ../pgdata

 5) stop backup

    psql -c "select pg_stop_backup(), current_timestamp"

 6) stop archiving...

    rm ../pgdata/archive_dest/active   

 7) achive 파일 backup.tar 포함

    tar -rf  ../pgdata/backup.tar ../pgdata/archive_dest 

 8)  복구를 위해 recovery.conf 파일 생성 및 tar 포함

     restore_command = 'cp archive/%f %p'

     recovery_end_command = 'rm -R archive_dest'

    tar -rf ../pgdata/backup.tar  recovery.conf