PostgreSQL 10 pg_dump 사용 테스트(40GB)

OS환경 : Oracle Linux 7.6 (64bit)

DB 환경 : PostgreSQL 10

방법 : PostgreSQL 10 pg_dump 백업 방법

샘플 테이블 및 데이터를 생성한 뒤 pg_dump를 이용해 백업하는 방법에 대해 설명함

유저 생성

SQL> create user 유저명 password '패스워드' createdb(db생성 권한부여);
SQL> create user imsi password 'imsi12345' createdb;

psql 나가기

SQL> \q

imsi 유저로 로그인 후 imsi 데이터베이스 생성

$ psql -U imsi
SQL> create database imsi;
SQL> \q

imsi 유저로 imsi database로그인

$ psql -U imsi -d imsi

샘플 테이블 및 데이터 생성

SQL> 
CREATE TABLE t AS 
    SELECT
        gs as idx,
        'imsidata' || gs AS test_string,
        md5(random()::text) AS random_string
    FROM
        generate_series(1, 100000000) AS gs;

테이블 용량 확인

SQL> select pg_size_pretty(pg_total_relation_size('t')) as size;
  size   
---------
 8054 MB
(1 row)

용량 더늘리기(반복)

SQL> insert into t select * from t;

최종용량

SQL> select pg_size_pretty(pg_total_relation_size('t')) as size;
 size  
-------
 39 GB
(1 row)

남은 용량 확인

$ df -h
Filesystem                Size  Used Avail Use% Mounted on
devtmpfs                  1.8G     0  1.8G   0% /dev
tmpfs                     1.8G  8.0K  1.8G   1% /dev/shm
tmpfs                     1.8G  9.3M  1.8G   1% /run
tmpfs                     1.8G     0  1.8G   0% /sys/fs/cgroup
/dev/mapper/ol_oel7-root   36G   28G  7.8G  78% /
/dev/sda1                1014M  226M  789M  23% /boot
tmpfs                     368M   12K  368M   1% /run/user/42
tmpfs                     368M     0  368M   0% /run/user/0
/dev/sdb1                  79G   41G   35G  55% /dump

pg_dump 명령으로 export

$ pg_dump -d imsi > imsidump.dmp

export 테스트 결과

용량 : 40GB

시작시각 : 06:20:13

끝난시각 : 06:40:14

export 총 시간 : 20분

단순계산 40GB -> 20분

             400GB -> 200분(3시간 20분)

                          

dump 파일 용량

$ ls -lh
total 28G
drwx------. 20 postgres postgres 4.0K Oct 31 05:43 data
drwx------.  2 postgres postgres  16K Oct 31 05:40 lost+found
-rw-r--r--.  1 postgres postgres  28G Oct 31 06:40 imsidump.dmp

40GB를 뽑았지만 실제로는 28GB만 나옴

서버 남은 용량 확인

# df -h
Filesystem                Size  Used Avail Use% Mounted on
devtmpfs                  1.8G     0  1.8G   0% /dev
tmpfs                     1.8G  8.0K  1.8G   1% /dev/shm
tmpfs                     1.8G  9.3M  1.8G   1% /run
tmpfs                     1.8G     0  1.8G   0% /sys/fs/cgroup
/dev/mapper/ol_oel7-root   36G   28G  7.8G  78% /
/dev/sda1                1014M  226M  789M  23% /boot
tmpfs                     368M   12K  368M   1% /run/user/42
tmpfs                     368M     0  368M   0% /run/user/0
/dev/sdb1                  79G   68G  6.9G  91% /dump

head 명령으로 dump 파일 내용 확인

# head -70 imsidump.dmp 
--
-- PostgreSQL database dump
--
 
-- Dumped from database version 10.10
-- Dumped by pg_dump version 10.10
 
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
 
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--
 
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
 
 
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--
 
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
 
 
SET default_tablespace = '';
 
SET default_with_oids = false;
 
--
-- Name: t; Type: TABLE; Schema: public; Owner: imsi
--
 
CREATE TABLE public.t (
    idx integer,
    test_string text,
    random_string text
);
 
 
ALTER TABLE public.t OWNER TO imsi;
 
--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: imsi
--
 
COPY public.t (idx, test_string, random_string) FROM stdin;
1    imsidata1    0c3f96a7a2bb64aad6491208305fb7e9
2    imsidata2    c0a5cc8cf9f713b837fcc6517d5ccad5
3    imsidata3    5769436fc020de09dcadea3f4c289007
4    imsidata4    1c56ab749b09cac371befd889aabd9e1
5    imsidata5    50e8b4b1735db315bf570ac4fcef47ca
6    imsidata6    46a9f662459869937b7572bac219961d
7    imsidata7    93d4effeeeb8027b03bb0453cd9396ad
8    imsidata8    ffc9d223f7512e6ad578111f12b2b949
9    imsidata9    cda614efa83c3a897a45f1b4c6836d81
10    imsidata10    ff3556c4c503d827635fa9e672fb2a61
11    imsidata11    98b467aee99b13164625f8ba2a6963ef
12    imsidata12    af27840869da1077efd7b397d1990b81
13    imsidata13    bfb1594b4587d82be95b6513be154157
14    imsidata14    b64ec49a8b0bc9dc8d1b1c1aacacce9b
15    imsidata15    e494f6d71541a6ca1d2c5dd3985ffcbe
16    imsidata16    58fb6d3d0d9bd9cda9ef8bd076f6df5e

테이블 생성 구문과 insert 구문은 아니지만 COPY 구문이 들어있음 

imsi 데이터베이스 삭제

SQL> drop database imsi;
ERROR:  cannot drop the currently open database
SQL> \q

지금 접속해 있는 db가 imsi db여서 삭제가 안됨

다시 -d 옵션 없이 접속 후 imsi 데이터베이스 삭제

$ psql -U imsi
SQL> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 imsi      | imsi     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
 
SQL> drop database imsi;
DROP DATABASE

dump 파일을 다시 postgreSQL로 넣기 위해서는 먼저 데이터베이스를 생성해줘야함

$ psql -U imsi
SQL> create database imsi;
CREATE DATABASE
SQL> \q

아래 형식으로 dump파일 import 작업 진행

$ psql -f (덤프파일명) (db 명)
$ psql -f imsidump.dmp imsi

import 테스트 결과

용량 : 40GB

시작시각 : 20:25:48

끝난시각 : 20:41:38

import 총 시간 : 16분

단순계산 40GB -> 16분

             400GB -> 160분

인덱스가 없어서 빠른듯 함 인덱스도 같은 넣고 다시 테스트해봐야할듯함

참조 : https://blog.gaerae.com/2015/09/postgresql-generate-series.html

https://positivemh.tistory.com/259

https://positivemh.tistory.com/464