설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


Powered by EnterpriseDB
총 게시물 11건, 최근 0 건
   

데이터베이스 유저/TABLESPACE 구문 백업받기

글쓴이 : PostgresDBA 날짜 : 2012-12-01 (토) 16:55 조회 : 2667
테이블스페이스를 하나 생성해보겠습니다.

postgres@[local]:5432 postgres#SQL> create tablespace kss_tbs location '/var/lib/pgsql/9.2/data/pg_ksstbs';
CREATE TABLESPACE
Time: 57.330 ms
postgres@[local]:5432 postgres#SQL> \q
[postgres@olmaster:~]$ find /var/lib/pgsql/9.2/data/pg_ksstbs
/var/lib/pgsql/9.2/data/pg_ksstbs
/var/lib/pgsql/9.2/data/pg_ksstbs/PG_9.2_201204301

pg_dumpall 명령어로 --globals-only 옵션과 함께 백업을 수행해봤습니다.
[postgres@olmaster:~]$ pg_dumpall -U postgres -f globals.sql --globals-only
Password: 
[postgres@olmaster:~]$ cat globals.sql 
--
-- PostgreSQL database cluster dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET escape_string_warning = off;

--
-- Roles
--

CREATE ROLE hadoop;
ALTER ROLE hadoop WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5943ccedc972b223494a26119dd8bc073';
CREATE ROLE lion;
ALTER ROLE lion WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md55b5435c6a34a0ce4ba3d06a46bd52fe1';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md5b9760e32366af29f63bd23fbaf1e8bf5';
CREATE ROLE scott;
ALTER ROLE scott WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5f5b3b8231a686f681fdee8f66bf91290';

--
-- Tablespaces
--

CREATE TABLESPACE kss_tbs OWNER postgres LOCATION '/var/lib/pgsql/9.2/data/pg_ksstbs';

--
-- PostgreSQL database cluster dump complete
--
위에서 볼수 있듯이 유저 생성구문과 user-created-tablespace 구문이 보이네요.

이번에는 --roles-only 옵션과 함께 백업을 수행하였습니다.

[postgres@olmaster:~]$ pg_dumpall -U postgres -f roles.sql --roles-only
Password: 
[postgres@olmaster:~]$ cat roles.sql 
--
-- PostgreSQL database cluster dump
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET escape_string_warning = off;

--
-- Roles
--

CREATE ROLE hadoop;
ALTER ROLE hadoop WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5943ccedc972b223494a26119dd8bc073';
CREATE ROLE lion;
ALTER ROLE lion WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md55b5435c6a34a0ce4ba3d06a46bd52fe1';
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION PASSWORD 'md5b9760e32366af29f63bd23fbaf1e8bf5';
CREATE ROLE scott;
ALTER ROLE scott WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5f5b3b8231a686f681fdee8f66bf91290';

--
-- PostgreSQL database cluster dump complete
--

-- 유저 생성 구문만 보이네요.
[postgres@olmaster:~]$ 


PostgreSQL DBA 라면 위 명령으로 최소 한달에 한번 정도는 메타정보를 백업 받아놓는게 좋겠죠 ^^

   

postgresdba.com