반응형
/********************************************************************************************
-- 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
***/

----------------------
-- 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
***/

----------------------------------
-- 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;


반응형

+ Recent posts