반응형
/********************************************************************************************
-- Title : [9.2] backup and restore using pg_dumpall
-- Reference : dbrang.tistory.com
-- Key word : postgresql pg_dumpall backup restore recovery 백업 복구 덤프 dumpall dump
********************************************************************************************/
-- Create Database
create database tttdb;
----------------------------------
-- Title : [9.2] backup and restore using pg_dumpall
-- Reference : dbrang.tistory.com
-- Key word : postgresql pg_dumpall backup restore recovery 백업 복구 덤프 dumpall dump
********************************************************************************************/
/***
**** Similar options with pg_dump/pg_restore
***/
-- References : dbrang.tistory.com/805
/***
**** Before Recovery
***/
----------------------
**** Similar options with pg_dump/pg_restore
***/
-- References : dbrang.tistory.com/805
/***
**** Before Recovery
***/
----------------------
-- Env. Initialization
----------------------
-- Create Backup Folder
# mkdir /home/backup/dumpall_backup
# chown -R postgres.postgres /home/backup/dumpall_backup
-- Create TBS
# mkdir /home/database/tbs_data0
# chown -R postgres.postgres /home/database/tbs_data0
# mkdir /home/database/tbs_data1
# chown -R postgres.postgres /home/database/tbs_data1
create tablespace tbs_data0
owner mapbak
location '/home/database/tbs_data0';
create tablespace tbs_data1
owner mapbak
location '/home/database/tbs_data1';
-- Confirm TBS Creation
select pg_get_userbyid(t.spcowner) "tbs_ownr", t.oid "tbs_id", t.spcname "tbs_nm"
, pg_size_pretty(pg_tablespace_size(t.oid)) "tbs_sz"
, case when pg_tablespace_location(t.oid) = ''
then u.tbs_location
else pg_tablespace_location(t.oid)
end "ts_location"
from pg_tablespace t
left join (select a.setting || '/' || case when b.tbs_nm = 'default'
then 'base' else b.tbs_nm
end "tbs_location"
, 'pg_' || b.tbs_nm "tbs_nm"
from pg_settings a
cross join (select 'default' "tbs_nm" union all
select 'global'
) b
where name = 'data_directory'
) u
on t.spcname = u.tbs_nm
order by t.spcname;
-- Create User
create role mapbak2 login password '***' superuser;
-- Create Database
create database tttdb;
---------------------------------
-- Create Tables
---------------------------------
-- create table on postgres db db by mapbak
-- drop table postgres.public.postgresdb_tbs_data0_by_mapbak;
create table postgres.public.postgresdb_tbs_data0_by_mapbak
( id serial not null primary key
, db_nm varchar(100)
, tbs_nm varchar(100)
, usr_nm varchar(100)
, attname varchar(100)
) tablespace tbs_data0;
alter table postgres.public.postgresdb_tbs_data0_by_mapbak
owner to mapbak;
insert into postgres.public.postgresdb_tbs_data0_by_mapbak
(db_nm, tbs_nm, usr_nm, attname)
select 'postgresdb', 'tbs_data0', 'mapbak', attname
from pg_class, pg_attribute limit 10000;
-- create table on postgres db by mapbak2
-- drop table postgres.public.postgresdb_tbs_data1_by_mapbak2;
create table postgres.public.postgresdb_tbs_data1_by_mapbak2
( id int not null
, db_nm varchar(100)
, tbs_nm varchar(100)
, usr_nm varchar(100)
, attname varchar(100)
, constraint fk_ttttt foreign key (id) references postgresdb_tbs_data0_by_mapbak(id)
) tablespace tbs_data1;
alter table postgres.public.postgresdb_tbs_data1_by_mapbak2
owner to mapbak2;
insert into postgres.public.postgresdb_tbs_data1_by_mapbak2
select id, db_nm, 'tbs_data1', 'mapbak2', attname
from postgres.public.postgresdb_tbs_data0_by_mapbak;
-- create table on tttdb by mapbak
-- drop table tttdb.public.tttdb_tbs_data0_by_mapbak;
create table tttdb.public.tttdb_tbs_data0_by_mapbak
( id serial not null primary key
, db_nm varchar(100)
, tbs_nm varchar(100)
, usr_nm varchar(100)
, attname varchar(100)
) tablespace tbs_data0;
alter table tttdb.public.tttdb_tbs_data0_by_mapbak
owner to mapbak;
insert into tttdb.public.tttdb_tbs_data0_by_mapbak
(db_nm, tbs_nm, usr_nm, attname)
select 'tttdb', 'tbs_data0', 'mapbak', attname
from pg_class, pg_attribute limit 10000;
-- create table on tttdb by mapbak2
-- drop table tttdb.public.tttdb_tbs_data1_by_mapbak2;
create table tttdb.public.tttdb_tbs_data1_by_mapbak2
( id int not null
, db_nm varchar(100)
, tbs_nm varchar(100)
, usr_nm varchar(100)
, attname varchar(100)
, constraint fk_ttttt foreign key (id) references tttdb_tbs_data0_by_mapbak(id)
) tablespace tbs_data1;
alter table tttdb.public.tttdb_tbs_data1_by_mapbak2
owner to mapbak2;
insert into tttdb.public.tttdb_tbs_data1_by_mapbak2
select id, db_nm, 'tbs_data1', 'mapbak2', attname
from tttdb.public.tttdb_tbs_data0_by_mapbak;
-- Select Tables
select * from postgres.public.postgresdb_tbs_data0_by_mapbak limit 1;
select * from postgres.public.postgresdb_tbs_data1_by_mapbak2 limit 1;
select * from tttdb.public.tttdb_tbs_data0_by_mapbak limit 1;
select * from tttdb.public.tttdb_tbs_data1_by_mapbak2 limit 1;
--------------
-- Add Indexes
--------------
-- create index
-- drop index ix_postgresdb_tbs_data0_by_mapbak;
-- drop index ix_postgresdb_tbs_data1_by_mapbak2;
create index ix_postgresdb_tbs_data0_by_mapbak
on postgresdb_tbs_data0_by_mapbak(db_nm, attname)
tablespace tbs_data1;
alter index ix_postgresdb_tbs_data0_by_mapbak
owner to mapbak;
create index ix_postgresdb_tbs_data1_by_mapbak2
on postgresdb_tbs_data1_by_mapbak2(db_nm, attname);
-- drop index ix_tttdb_tbs_data0_by_mapbak;
-- drop index ix_tttdb_tbs_data1_by_mapbak2;
create index ix_tttdb_tbs_data0_by_mapbak
on tttdb_tbs_data0_by_mapbak(db_nm, attname)
tablespace tbs_data1;
alter index ix_tttdb_tbs_data0_by_mapbak
owner to mapbak;
create index ix_tttdb_tbs_data1_by_mapbak2
on tttdb_tbs_data1_by_mapbak2(db_nm, attname);
-- select index
select * from pg_indexes where indexname like '%mapbak%';
/***
**** After Disaster
***/
**** After Disaster
***/
----------------------------------
-- Exec. Backup Using pg_dumpall
----------------------------------
-- Options
ㅇ -a : Dump only the data, not the schema (data definitions). 데이터.
ㅇ -c : Include SQL commands to clean (drop) databases before recreating them.
DROP commands for roles and tablespaces are added as well.
ㅇ -f : Send output to the specified file. If this is omitted, the standard output is used.
ㅇ -r : Dump only roles, no databases or tablespaces.
ㅇ -S : Specify the superuser user name to use when disabling triggers.
This is only relevant if --disable-triggers is used.
ㅇ -s : Dump only the object definitions (schema), not data. 모든 개체 정의.
ㅇ -h : Specifies the host name of the machine on which the database server is running.
ㅇ -l : Specifies the name of the database to connect to to dump global objects and
discover what other databases should be dumped.
ㅇ -p : Specifies the TCP port or local Unix domain socket file extension on which the
server is listening for connections.
ㅇ -U : User name to connect as.
ㅇ -W : Force pg_dumpall to prompt for a password before connecting to a database.
-- Execute pg_dumpall
o Full dump
$ pg_dumpall -f /home/backup/dumpall_backup/dumpall_all_130809.dmp
$ pg_dumpall | gzip -c > /home/backup/dumpall_backup/dumpall_all_130809.gz
o Remote dump
$ pg_dumpall -h 172.20.20.XX -U mapbak -s -f $HOME/dumpall_testsrv.dmp
o Optional dump
$ pg_dumpall -s -f /home/backup/dumpall_backup/dumpall_schema_130809.out
$ pg_dumpall -t -f /home/backup/dumpall_backup/dumpall_tablespace_130809.out
-------------------------
-- Recovery by pg_dumpall
-------------------------
-- Server Stop
$ pg_ctl stop
-- Restore by pg_dumall .dmp file
drop table postgres.public.postgresdb_tbs_data0_by_mapbak;
drop table postgres.public.postgresdb_tbs_data1_by_mapbak2;
drop table tttdb.public.tttdb_tbs_data0_by_mapbak;
drop table tttdb.public.tttdb_tbs_data1_by_mapbak2;
$ psql -f /home/backup/dumpall_backup/dumpall_all_130809.dmp
select * from postgres.public.postgresdb_tbs_data0_by_mapbak;
select * postgres.public.postgresdb_tbs_data1_by_mapbak2;
select * from tttdb.public.tttdb_tbs_data0_by_mapbak;
select * from tttdb.public.tttdb_tbs_data1_by_mapbak2;
-- Restore by pg_dumpall .gz file
drop table postgres.public.postgresdb_tbs_data0_by_mapbak;
drop table postgres.public.postgresdb_tbs_data1_by_mapbak2;
drop table tttdb.public.tttdb_tbs_data0_by_mapbak;
drop table tttdb.public.tttdb_tbs_data1_by_mapbak2;
$ gunzip -c /home/backup/dumpall_backup/dumpall_all_130809.gz | psql
select * from postgres.public.postgresdb_tbs_data0_by_mapbak;
select * from postgres.public.postgresdb_tbs_data1_by_mapbak2;
select * from tttdb.public.tttdb_tbs_data0_by_mapbak;
select * from tttdb.public.tttdb_tbs_data1_by_mapbak2;
반응형