반응형
/****************************************************************************************************************
-- Title : [PGS9.2] Online hot backup and Recovery - ver.dBRang
-- Reference : dbrang.tistory.com
-- Key word : 백업 복구 온라인 핫 백업 online hot backup recovery restore
****************************************************************************************************************/

-- XLog and Archive file status every step

Backup & Recovery.xlsx



/************************************
-- Initiate Env.
************************************/

-- Sett Arhive Mode
    o Reference : dbrang.tistory.com/756
    $ vi /home/postgres/pgsql/data/postgresql.conf
    wal_level = archive
    archive_mode = on
    archive_commane = 'cp %p /Databases/ARCHIVE/%f'
    archive_timeout = 0
    
-- Confirm Databases
-- Monitoring View : dbrang.tistory.com/866
    =# select * from sp_server0 where name like '%archive%' or name like '%file%';
    =# select * from sp_tablespace0;

    $ echo $POSTGRES_HOME
    /home/postgres/pgsql

    $ echo $PGDATA
    /home/postgres/pgsql/data

-- Clean Archive files
    $ find /Databases/ARCHIVE -cmin +30 -exec rm -rf {} \;   -- remove archive files(if archive file is too many)
    
-- Create Table  
    =# drop table ttt;
    =# create table ttt
       ( seq serial not null primary key
       , text varchar(100) not null
       , date timestamp null
       ) tablespace ts_data1;

-- Insert initiate data
    =# insert into ttt(text, date)
       select a.text, a.date
       from 
       (select '1. 전체 백업 전 데이터' || 'DUMMY DUMMY DUMMY DUMMY DUMMY' "text", now() "date") a
       cross join 
       (select generate_series(1,100) "pivot") b;

    =# select pg_switch_xlog();    -- execute switching to drop archive file, not mandatory

-- Select table
    =# select * from ttt order by 1 desc;

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:33 0000000100000008000000D7.done



/************************************
-- Online Hot Backup
************************************/
-- You have to choose one, TAR or BASEBackup.
-- In this case, we chose TAR.

/*
-- Online Hot Backup via TAR
*/
-- Start backup
    =# select pg_start_backup('backup_20140313_tar');  -- "8/D9000020"
    =# select pg_xlogfile_name('8/D9000020');          -- "0000000100000008000000D9"

    $ cat /home/postgres/pgsql/data/backup_label
    START WAL LOCATION: 8/D9000020 (file 0000000100000008000000D9)
    CHECKPOINT LOCATION: 8/D9000020
    BACKUP METHOD: pg_start_backup
    BACKUP FROM: master
    START TIME: 2014-03-18 13:34:54 KST
    LABEL: backup_20140313_tar

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:34 0000000100000008000000D6.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:34 0000000100000008000000D8.done

-- Insert data
    =# insert into ttt(text, date)
       select a.text, a.date
       from 
       (select '2. pg_start_backup 직후, TAR 직전 데이터' || 'DUMMY DUMMY DUMMY DUMMY DUMMY' "text", now() "date") a
       cross join 
       (select generate_series(1,100) "pivot") b;

    =# select * from ttt order by 1 desc;

    =# select pg_switch_xlog();    -- execute switching to drop archive file, not mandatory

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:42 0000000100000008000000D9

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:34 0000000100000008000000D6.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:34 0000000100000008000000D8.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:42 0000000100000008000000D9.done

-- Execute tar
-- You have to move designated directory using "cd" command.
    $ cd /home/postgres/pgsql
    $ tar zcvf /Backup/TAR_Backup/pgdata_backup_20140313.tar.gz ./data

    $ cd /Databases
    $ tar zcvf /Backup/TAR_Backup/ts_data1_16391_backup_20140313.tar.gz ./ts_data1

-- Insert data
    =# insert into ttt(text, date)
       select a.text, a.date
       from 
       (select '3. TAR 직후, pg_stop_backup 직전 데이터' || 'DUMMY DUMMY DUMMY DUMMY DUMMY' "text", now() "date") a
       cross join 
       (select generate_series(1,100) "pivot") b;

    =# select pg_switch_xlog();  -- if need be.

    =# select * from ttt order by 1 desc;

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:42 0000000100000008000000D9
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:45 0000000100000008000000DA

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:42 0000000100000008000000D9.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:45 0000000100000008000000DA.done

-- Stop backup
    =# select pg_stop_backup();                        -- "8/DB00280C"
    =# select pg_xlogfile_name('8/DB00280C');          -- "0000000100000008000000DB"

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:42 0000000100000008000000D9
    -rw-------. 1 postgres postgres      306 2014-03-18 13:50 0000000100000008000000D9.00000020.backup
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:45 0000000100000008000000DA
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:50 0000000100000008000000DB

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:50 0000000100000008000000D9.00000020.backup.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:45 0000000100000008000000DA.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:50 0000000100000008000000DB.done

-- Confirm Backup Files
    $ ll /Backup/TAR_Backup
    -rw-rw-r--. 1 postgres postgres 16145631 2014-03-18 13:43 pgdata_backup_20140313.tar.gz
    -rw-rw-r--. 1 postgres postgres     1238 2014-03-18 13:43 ts_data1_16391_backup_20140313.tar.gz


    /**
    -- Online Hot Backup via Basebackup
    -- Just refer to script as follows

    -- Edit configure file
        $ vi /home/postgres/pgsql/data/postgresql.conf
        max_wal_senders = 1 
    
        $ vi /home/postgres/pgsql/data/pg_hba.conf
        host    replication    postgres    172.20.20.62/32    md5
    
    -- Start backup
        =# select pg_start_backup('backup_20140313_base');  -- "8/71000020"
        =# select pg_xlogfile_name('8/71000020');           -- "000000010000000800000071"
    
    -- Execute basebackup
        $ pg_basebackup -h 172.20.20.62 -U postgres -D /Backup/BASE_Backup -xlog -c fast -P -Ft
    
    -- Stop backup
        =# select pg_stop_backup();                        -- "8/71000098"
        =# select pg_xlogfile_name('8/71000098');          -- "000000010000000800000071"
    
    -- Confirm Backup Files
        $ ll /Backup/BASE_Backup
        -rw-rw-r--. 1 postgres postgres    19456 2014-03-13 14:48 16391.tar
        -rw-rw-r--. 1 postgres postgres 36995584 2014-03-13 14:48 base.tar
    **/
    

/*
-- Add Data after full backup
*/
-- Add Data
    =# insert into ttt(text, date)
       select a.text, a.date
       from 
       (select '4. pg_stop_backup 직후 데이터' || 'DUMMY DUMMY DUMMY DUMMY DUMMY' "text", now() "date") a
       cross join 
       (select generate_series(1,100) "pivot") b

    =# select * from ttt order by 1 desc;
    =# select pg_switch_xlog();    -- if need be.

-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:42 0000000100000008000000D9
    -rw-------. 1 postgres postgres      306 2014-03-18 13:50 0000000100000008000000D9.00000020.backup
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:45 0000000100000008000000DA
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:50 0000000100000008000000DB
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:07 0000000100000008000000DC

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:50 0000000100000008000000D9.00000020.backup.done
    -rw-------. 1 postgres postgres    0 2014-03-18 13:50 0000000100000008000000DB.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:07 0000000100000008000000DC.done


/*
-- 데이터 추가/xlog 교체....
*/
-- Add Data(10회 반복 수행)
    =# insert into ttt(text, date)
       select a.text, a.date
       from 
       (select '5. pg_xlog 교체용 데이터' || 'DUMMY DUMMY DUMMY DUMMY DUMMY' "text", now() "date") a
       cross join 
       (select generate_series(1,100) "pivot") b;

       select pg_switch_xlog();    -- if need be.


    =# select * from ttt order by 1 desc;


-- Confirm Archiving
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D5
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D6
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:33 0000000100000008000000D7
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:34 0000000100000008000000D8
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:42 0000000100000008000000D9
    -rw-------. 1 postgres postgres      306 2014-03-18 13:50 0000000100000008000000D9.00000020.backup
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:45 0000000100000008000000DA
    -rw-------. 1 postgres postgres 16777216 2014-03-18 13:50 0000000100000008000000DB
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:07 0000000100000008000000DC
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000DD
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000DE
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000DF
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E0
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E1
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E2
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E3
    -rw-------. 1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E4

-- Confirm Archive_status
    -rw-------. 1 postgres postgres    0 2014-03-18 13:50 0000000100000008000000D9.00000020.backup.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:10 0000000100000008000000E0.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:10 0000000100000008000000E1.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:11 0000000100000008000000E2.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:11 0000000100000008000000E3.done
    -rw-------. 1 postgres postgres    0 2014-03-18 14:11 0000000100000008000000E4.done

-- Confirm pg_xlog now
    -rw-------.  1 postgres postgres      306 2014-03-18 13:50 0000000100000008000000D9.00000020.backup
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E0
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E1
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E2
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E3
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E4
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:11 0000000100000008000000E5
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E6
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E7
    -rw-------.  1 postgres postgres 16777216 2014-03-18 14:10 0000000100000008000000E8



/*********************************************
-- Generate Disaster & Emergency Xlog Backup
*********************************************/
-- Generating Disaster
   $ pg_ctl stop -m immediate

-- Emergency XLog Backup
   $ cp -r /home/postgres/pgsql/data/pg_xlog/* /Backup/XLOG_Backup/



/************************************
-- Recovery Using TAR Backup Set
************************************/
/*
-- Recovery from Full Backup set.
*/
-- Rename Existing Dreictory
    $ mv /home/postgres/pgsql/data /home/postgres/pgsql/data_old
    $ mv /Databases/ts_data1 /Databases/ts_data1_old   -- if this folder is here..
    
-- Install PostgreSQL on NEW Machine(Supposing).

-- Copy basckuped file
    $ cp /Backup/TAR_Backup/pgdata_backup_20140313.tar.gz /home/postgres/pgsql/
    $ cp /Backup/TAR_Backup/ts_data1_16391_backup_20140313.tar.gz /Databases/

-- Decompression
-- You have to move to designated directory using "cd" command to decompress tar file at correct directory.
    $ cd /home/postgres/pgsql
    $ tar zxvf pgdata_backup_20140313.tar.gz

    $ cd /Databases
    $ tar zxvf ts_data1_16391_backup_20140313.tar.gz

-- Start up
    $ pg_ctl start
    
    =# select * from ttt order by 1 desc

/*
-- Recovery
*/
-- Copy ARCHIVE Files
    $ cp -f /Databases/ARCHIVE/* /home/postgres/pgsql/data/pg_xlog/

-- Copy Emergency xlog files
    $ cp -f /Backup/XLOG_Backup/* /home/postgres/pgsql/data/pg_xlog/

-- Start up
    $ pg_ctl start
    
    =# select * from ttt order by 1 desc;


※ You can see how to PITR(Point In Time Recovery) or recovery using BASE Backup set as follows:
반응형
저작자표시 비영리 변경금지

+ Recent posts

티스토리툴바