[PostgreSQL - PGDUMP 백업&복구]

▶PG_DUMP 유틸리티 

장점 : 특정 데이터베이스를 선택하여 백업 및 복구 할 수 있음, 테이블의 읽기 엑세스 권한 필요

           ※ 데이터베이스 전체를 백업하는 경우 슈퍼유저(postgre)로 해야합니다.

Dump는 SQL, File 형태로 백업 받을 수 있으며, PG_RESTORE 유틸리티를 이용하여 복원 가능.

 

 

▶ test data insert (kim사용자로 test 데이터베이스)

postgres@jh-post002:/home/postgres# psql -h 192.168.1.11 -U kim -d test;

test=> CREATE TABLE t AS SELECT gs as idx, '테스트 문자열' || gs AS test_string, md5(random()::text) AS random_string FROM generate_series(1, 1000000) AS gs;
test=> CREATE TABLE t2 AS SELECT (ROW_NUMBER() OVER()) as idx, md5(random()::text) AS random_string, gs AS test_date FROM generate_series('2015-01-01 00:00'::timestamp, '2015-01-01 23:00'::timestamp, '1 hours') AS gs;



test=# select count(*) from t;
  count  
---------
1000000
(1 row)

test=# select count(*) from t2;
count
-------
    24
(1 row)

 

 

AS-IS

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description         
        
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+------------------------------------
--------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7985 kB | pg_default | default administrative connection d
atabase
repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 11 MB   | pg_default |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 97 MB   | pg_default |
test_new  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7961 kB | pg_default |



database 대상 선정 -> test 데이터베이스

 

 

▶ export

postgres@jh-post002:/home/postgres# pg_dump -h 192.168.1.11 -U kim -d test -w -Fc -v -f test.dump
n   h : host , p : port, U : user name, d : database name, w : password 생략, Fc : Custem 형식(Dump),  v : 상세정보 출력 f : 파일 이름

pg_dump: last built-in OID is 16383
pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: finding the columns and types of table "public.t"
pg_dump: finding the columns and types of table "public.t2"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "public.t"
pg_dump: reading policies for table "public.t"
pg_dump: reading row security enabled for table "public.t2"
pg_dump: reading policies for table "public.t2"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "public.t"
pg_dump: dumping contents of table "public.t2"

 

 

▶dump파일 생성 확인

postgres@jh-post002:/home/postgres# ll
total 24388
drwxr-xr-x 4 postgres postgres       74 Nov  3 17:20 cli_linux
-rw-rw-r-- 1 postgres postgres 24971628 Nov  9 10:55 test.dump

 

▶ Database drop

postgres@jh-post002:/home/postgres# psql              => super user로 접속
psql (12.8)
Type "help" for help.


postgres=# drop database test;
DROP DATABASE



postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description         
        
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+------------------------------------
--------
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7985 kB | pg_default | default administrative connection d
atabase
repmgr    | repmgr   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 11 MB   | pg_default |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7809 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
test_new  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7961 kB | pg_default |
(5 rows)

 

 

▶ PG_RESTORE 

postgres=# create database test;               //깡통 test database 생성
CREATE DATABASE

## Restore 실행
postgres@jh-post002:/home/postgres# pg_restore -h 192.168.1.11 -U kim -d test -w test.dump

 

 

TO-BE

▶ 데이터 확인

postgres@jh-post002:/home/postgres# psql -h 192.168.1.11 -U kim -d test
psql (12.8)
Type "help" for help.

test=> \dt
       List of relations
Schema | Name | Type  | Owner
--------+------+-------+-------
public | t    | table | kim
public | t2   | table | kim
(2 rows)



test=> select count(*) from t;
  count  
---------
1000000
(1 row)

test=> select count(*) from t2;
count
-------
    24
(1 row)