티스토리 뷰

⑤ 개발, 데이터베이스/ⓓ Database

[PostgreSQL] 운영자 가이드

GodNR 2018. 9. 6. 15:07
728x90
반응형

1. DB 생성 및 관리

본 장에서는 PostgreSQL powered by S-Core DB생성 및 관리, Schema 관리에 대해 설명합니다.

1.1  데이터베이스 생성

CREATE DATABASE를 활용하여 데이터베이스를 생성합니다.

PostgreSQL powered by S-Core 설치 후 기본으로 생성되는 Database & User 정보를 확인합니다.

1.1.1  데이터베이스 정보 확인

postgres=# \l

                              List of databases

   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges

-----------+-------+----------+-------------+-------------+-------------------

 postgres  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |

 template0 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

           |       |          |             |             | pgsql=CTc/pgsql

 template1 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

           |       |          |             |             | pgsql=CTc/pgsql

(3 rows)

postgres=#

1.1.2  데이터베이스 유저 정보 확인

postgres=# \du;

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 pgsql     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

1.1.3  데이터베이스 유저(ROLE) 추가

postgres=# CREATE ROLE nrson;

CREATE ROLE

postgres=# \du;

                                   List of roles

 Role name |                         Attributes                         | Member of

-----------+------------------------------------------------------------+-----------

 nrson     | Cannot login                                               | {}

 pgsql     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#

1.1.4  새로운 데이터베이스 생성

postgres=# CREATE DATABASE scoreDB WITH ENCODING='UTF8' OWNER=nrson CONNECTION LIMIT=25;

CREATE DATABASE

postgres=# \l

                              List of databases

   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges

-----------+-------+----------+-------------+-------------+-------------------

 postgres  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |

 scoredb   | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |

 template0 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

           |       |          |             |             | pgsql=CTc/pgsql

 template1 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

           |       |          |             |             | pgsql=CTc/pgsql

(4 rows)

postgres=#

신규 데이터베이스는 scoredb라는 이름을 갖고 있으며 신규로 생성한 nrson이 소유자이다.

 

1.2  데이터베이스 수정

ALTER DATABASE를 활용하여 기존 데이터베이스를 수정합니다.

1.2.1  데이터베이스 이름 변경

postgres=#  ALTER DATABASE scoredb RENAME TO newScoredb;

ALTER DATABASE

postgres=# \l

                               List of databases

    Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges

------------+-------+----------+-------------+-------------+-------------------

 newscoredb | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/nrson        +

            |       |          |             |             | nrson=CTc/nrson

 postgres   | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +

            |       |          |             |             | pgsql=CTc/pgsql

 template0  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

 template1  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

(4 rows)

postgres=#

1.2.2  데이터베이스 소유자 변경

postgres=# \l

                               List of databases

    Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges

------------+-------+----------+-------------+-------------+-------------------

 newscoredb | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +

            |       |          |             |             | pgsql=CTc/pgsql

 postgres   | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +

            |       |          |             |             | pgsql=CTc/pgsql

 template0  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

 template1  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

(4 rows)

postgres=# ALTER DATABASE newscoredb OWNER TO nrson;

ALTER DATABASE

postgres=# \l

                               List of databases

    Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges

------------+-------+----------+-------------+-------------+-------------------

 newscoredb | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/nrson        +

            |       |          |             |             | nrson=CTc/nrson

 postgres   | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +

            |       |          |             |             | pgsql=CTc/pgsql

 template0  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

 template1  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +

            |       |          |             |             | pgsql=CTc/pgsql

(4 rows)

postgres=#

1.2.3  데이터베이스가 사용하는 테이블스페이스 변경

scoretbs tablespace 생성

postgres=# CREATE TABLESPACE scoretbs location '/home/pgsql/TBS/pg_ScoreTBS';

CREATE TABLESPACE

postgres=# \db

               List of tablespaces

    Name    | Owner |          Location

------------+-------+-----------------------------

 pg_default | pgsql |

 pg_global  | pgsql |

 scoretbs   | pgsql | /home/pgsql/TBS/pg_ScoreTBS

(3 rows)

postgres=#

tablespace 변경

postgres=# \l+

                                                                 List of databases

    Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description

------------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------

 newscoredb | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/nrson        +| 7621 kB | pg_default |

            |       |          |             |             | nrson=CTc/nrson   |         |            |

 postgres   | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +| 7621 kB | pg_default | default administrative connection database

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template0  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | unmodifiable empty database

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template1  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | default template for new databases

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

(4 rows)

postgres=# ALTER DATABASE newscoredb SET TABLESPACE scoretbs;

ALTER DATABASE

postgres=# \l+

                                                                 List of databases

    Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description

------------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------

 newscoredb | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/nrson        +| 7621 kB | scoretbs   |

            |       |          |             |             | nrson=CTc/nrson   |         |            |

 postgres   | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +| 7621 kB | pg_default | default administrative connection database

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template0  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | unmodifiable empty database

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template1  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | default template for new databases

            |       |          |             |             | pgsql=CTc/pgsql   |         |            |

(4 rows)

postgres=#

1.2.4  데이터베이스 동적 설정 변경

postgres=# ALTER DATABASE newscoredb SET escape_string_warning TO OFF;

ALTER DATABASE

postgres=#

 

1.3  데이터베이스 삭제

DROP DATABASE를 활용하여 데이터베이스를 제거합니다.

1.3.1  데이터베이스 삭제

postgres=# DROP DATABASE IF EXISTS newscoredb;

DROP DATABASE

postgres=# DROP DATABASE IF EXISTS nrson;

NOTICE:  database "nrson" does not exist, skipping

DROP DATABASE

postgres=#

1.4  데이터베이스 복사

데이터베이스를 다른 데이터베이스로 복사합니다.

1.4.1  동일한 노드에서 데이터베이스를 복사

postgres=# \l+

                                                                List of databases

   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description

-----------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------

 postgres  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +| 7621 kB | pg_default | default administrative connection database

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 scoredb   | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |                   | 7489 kB | scoretbs   |

 template0 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | unmodifiable empty database

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template1 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | default template for new databases

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

(4 rows)

postgres=# CREATE DATABASE latestdb WITH TEMPLATE scoredb;

CREATE DATABASE

postgres=# \l+

                                                                List of databases

   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace |                Description

-----------+-------+----------+-------------+-------------+-------------------+---------+------------+--------------------------------------------

 latestdb  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |                   | 7489 kB | scoretbs   |

 postgres  | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =Tc/pgsql        +| 7621 kB | pg_default | default administrative connection database

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 scoredb   | nrson | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 |                   | 7489 kB | scoretbs   |

 template0 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | unmodifiable empty database

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

 template1 | pgsql | UTF8     | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/pgsql         +| 7489 kB | pg_default | default template for new databases

           |       |          |             |             | pgsql=CTc/pgsql   |         |            |

(5 rows)

postgres=#

1.4.2  다른 노드에서 데이터베이스를 복사

scored backup file 생성합니다.

[pgsql@score ~]$ pg_dump -d scoredb -U pgsql -F p -b -v -f scoredb.backup.txt

Password:

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: flagging inherited columns in subtables

pg_dump: reading indexes

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 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: creating SCHEMA "public"

pg_dump: creating COMMENT "SCHEMA public"

pg_dump: creating EXTENSION "plpgsql"

pg_dump: creating COMMENT "EXTENSION plpgsql"

[pgsql@score ~]$

score.backup.txt 파일 remote 서버로 복사합니다.

target 서버에 database 생성합니다.

postgres=# CREATE DATABASE targetdb;

CREATE DATABASE

postgres=#

schema 반영합니다.

[pgsql@nrson ~]$ psql -U pgsql -d targetdb -f scoredb.backup.txt

Password for user pgsql:

SET

SET

SET

SET

SET

 set_config

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

 

(1 row)

 

SET

SET

SET

CREATE EXTENSION

COMMENT

[pgsql@nrson ~]$

 

1.5  데이터베이스 주요요소들의 사이즈 확인

데이터베이스, 테이블, 인덱스 등을 얻기 위한 다양한 편리한 기능을 소개합니다.

1.5.1  데이터베이스 테이블 사이즈

데이터베이스를 생성합니다.

postgres=# CREATE TABLE PGT (name varchar);

CREATE TABLE

postgres=#

데이터베이스 테이블 사이즈를 확인합니다.

postgres=# SELECT PG_RELATION_SIZE('PGT');

 pg_relation_size

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

                0

(1 row)

postgres=#

테이블에 데이터 입력합니다.

postgres=# INSERT INTO PGT VALUES ('PostgreSQL');

INSERT 0 1

postgres=#

데이터베이스 테이블 사이즈를 재확인합니다.

postgres=# SELECT PG_RELATION_SIZE('PGT');

 pg_relation_size

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

             8192

(1 row)

postgres=#

kb, mb, gb, tb등 적절한 포맷으로 변경하여 보여줍니다.

postgres=# SELECT pg_size_pretty (pg_relation_size('pgt'));

 pg_size_pretty

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

 8192 bytes

(1 row)

postgres=# SELECT pg_size_pretty (pg_total_relation_size('pgt'));

 pg_size_pretty

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

 16 kB

(1 row)

postgres=

마지막으로 모든 테이블의 사이즈를 조회

SELECT

    relname AS "relation",

    pg_size_pretty (

        pg_total_relation_size (C .oid)

    ) AS "total_size"

FROM

    pg_class C

LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)

WHERE

    nspname NOT IN (

        'pg_catalog',

        'information_schema'

    )

AND C .relkind <> 'i'

AND nspname !~ '^pg_toast'

ORDER BY

    pg_total_relation_size (C .oid) DESC;

postgres-#     pg_total_relation_size (C .oid) DESC;

 relation | total_size

----------+------------

 pgt      | 16 kB

 hcs      | 8192 bytes

 kkm      | 8192 bytes

 aa       | 8192 bytes

 bb       | 8192 bytes

 cc       | 8192 bytes

(6 rows)

postgres=#

1.5.2  데이터베이스 사이즈

a.     Database scoredb의 size

postgres=# SELECT

postgres-#     pg_size_pretty (

postgres(#         pg_database_size ('scoredb')

postgres(#     );

 pg_size_pretty

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

 7621 kB

(1 row)

postgres=#

b.     모든 database의 size

postgres=# SELECT

postgres-#     pg_database.datname,

postgres-#     pg_size_pretty(pg_database_size(pg_database.datname)) AS size

postgres-#     FROM pg_database;

  datname  |  size

-----------+---------

 template1 | 7489 kB

 template0 | 7489 kB

 postgres  | 7717 kB

 scoredb   | 7621 kB

 latestdb  | 7489 kB

 targetdb  | 7621 kB

(6 rows)

postgres=#

1.5.3  데이터베이스 인덱스 사이즈

postgres=# SELECT

postgres-#     pg_size_pretty (pg_indexes_size('pgt'));

 pg_size_pretty

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

0 bytes

(1 row)

1.5.4  데이터베이스 테이블스페이스 사이즈

postgres=# SELECT

postgres-#     pg_size_pretty (

postgres(#         pg_tablespace_size ('scoretbs')

postgres(#     );

pg_size_pretty

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

15    MB

(1 row)

 

2. 백업과 복구

본 장에서는 pg_dump, pg_dumpall, psql, pg_restore, pgAdmin을 포함하여 다양한

PostgreSQL 백업 및 복원 도구를 사용하여 데이터베이스를 복원하는 방법에 대해

알아보도록 하겠습니다.

2.1  PostgreSQL 백업

pg_dump 및 pg_dumall과 같은 PostgreSQL 백업 도구를 사용하여 데이터베이스를

백업하는 방법에 대해 알아보겠습니다.

2.1.1  하나의 데이터베이스 백업

[pgsql@score ~]$ pg_dump -d scoredb -U pgsql -F p -b -v -f scoredb.backup.txt

Password:

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: flagging inherited columns in subtables

pg_dump: reading indexes

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 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: creating SCHEMA "public"

pg_dump: creating COMMENT "SCHEMA public"

pg_dump: creating EXTENSION "plpgsql"

pg_dump: creating COMMENT "EXTENSION plpgsql"

[pgsql@score ~]$

# 옵션 설명

pg_dump -d scoredb -U pgsql -F p -b -v -f scoredb.backup.txt

-d : Database

-U : User

-F : Format

c : custom-format archive file format

d : directory-format archive

t : tar

p : plain text SQL script file).

-b : include large objects in the dump.

-v : verbose

-f : Output Filename

2.1.2  모든 데이터베이스 백업

[pgsql@score ~]$ pg_dumpall -U pgsql -v -f all.backup.txt

Password:

pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false)

pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2

pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '16384'

pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '10'

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'nrson')

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'pgsql')

pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')ORDER BY 1,2,3

pg_dumpall: executing SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, pg_catalog.pg_tablespace_location(oid), (SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(coalesce(spcacl,pg_catalog.acldefault('t',spcowner))) AS acl EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('t',spcowner))) as foo)AS spcacl,(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(pg_catalog.acldefault('t',spcowner)) AS acl EXCEPT SELECT pg_catalog.unnest(coalesce(spcacl,pg_catalog.acldefault('t',spcowner)))) as foo)AS rspcacl,array_to_string(spcoptions, ', '),pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1

pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_tablespace'::pg_catalog.regclass AND objoid = '16393'

pg_dumpall: executing SELECT pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname = 'template0'

pg_dumpall: executing SELECT datname, coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), pg_encoding_to_char(d.encoding), datcollate, datctype, datfrozenxid, datminmxid, datistemplate, (SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE "C") FROM (   SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl   EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as datacls)AS datacl, (SELECT pg_catalog.array_agg(acl ORDER BY acl::text COLLATE "C") FROM (   SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl   EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as rdatacls)AS rdatacl, datconnlimit, (SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) WHERE datallowconn ORDER BY 1

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = 'latestdb')

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = 'postgres')

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = 'scoredb')

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = 'targetdb')

pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setrole = 0 AND setdatabase = (SELECT oid FROM pg_database WHERE datname = 'template1')

pg_dumpall: executing SELECT rolname, datname, unnest(setconfig) FROM pg_db_role_setting, pg_authid u, pg_database WHERE setrole = u.oid AND setdatabase = pg_database.oid

pg_dumpall: executing SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1

pg_dumpall: dumping database "latestdb"...

pg_dumpall: running ""/home/pgsql/SEPAS/bin/pg_dump"  -v -f all.backup.txt -Fa 'user=pgsql dbname=latestdb'"

….

….

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: creating COMMENT "DATABASE template1"

pg_dump: creating SCHEMA "public"

pg_dump: creating COMMENT "SCHEMA public"

pg_dump: creating EXTENSION "plpgsql"

pg_dump: creating COMMENT "EXTENSION plpgsql"

[pgsql@score ~]$

2.1.3  특정 구성요소만 백업

- schema

[pgsql@score ~]$ pg_dumpall --schema-only -W -f allschema.backup.txt

- role

[pgsql@score ~]$ pg_dumpall --roles-only -W -f allrole.backup.txt

- tablespace

[pgsql@score ~]$ pg_dumpall --tablespaces-only -f alltbs.backup.txt

 

2.2  PostgreSQL 복구

psql 및 pg_restore 도구를 사용하여 PostgreSQL 데이터베이스를 복원하는 다양한 방법에 대해 알아보겠습니다.

2.2.1  포멧이 Text인 경우 psql를 활용한 복구

[pgsql@score ~]$ psql -U pgsql --set ON_ERROR_STOP=on -f scoredb.backup.txt

Password for user pgsql:

SET

SET

SET

SET

SET

 set_config

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

 

(1 row)

 

SET

SET

SET

CREATE EXTENSION

COMMENT

[pgsql@score ~]$

# 참조

ON_ERROR_STOP=on : error 발생시 중지하는 옵션

2.2.2  포멧이 tar인 경우 pg_restore를 활용한 복구

- database 생성

postgres=# CREATE DATABASE newscoredb;

CREATE DATABASE

postgres=#

 

- pg_restore 사용

[pgsql@score ~]$ pg_restore --dbname=newscoredb --verbose scoredb.backup.tar

pg_restore: connecting to database for restore

Password:

pg_restore: creating SCHEMA "public"

pg_restore: creating COMMENT "SCHEMA public"

pg_restore: creating EXTENSION "plpgsql"

pg_restore: creating COMMENT "EXTENSION plpgsql"

[pgsql@score ~]$

2.2.3  특정 요소만 복구

- database 생성

postgres-# CREATE DATABASE newscoredb2;

CREATE DATABASE

postgres=#

 

- pg_restore --section 사용 (각각 pre-data, data, post-data 영역 restore)

[pgsql@score~]$ pg_restore --dbname=newscoredb2 --section=pre-data --verbose scoredb.backup.tar

[pgsql@score~]$ pg_restore --dbname=newscoredb2 --section=data --verbose scoredb.backup.tar

[pgsql@score~]$ pg_restore --dbname=newscoredb2 --section=post-data --verbose scoredb.backup.tar

 


728x90
반응형
저작자표시 비영리

'⑤ 개발, 데이터베이스 > ⓓ Database' 카테고리의 다른 글

[PostgreSQL] 설치 및 운영자 가이드  (2) 2018.11.05
[Oracle] getConnection 지연 조치 방법  (2) 2018.08.04
Oracle CTF vs TAF  (0) 2018.07.09
[SQL 6일차] 데이터베이스 보안  (0) 2018.07.03
[SQL 5일차] 데이터베이스 서브쿼리와 제약조건, 뷰, 트랜잭션  (0) 2018.07.03