Postgresql線上備份和恢複

標籤：

1.實驗環境

OS：  RedHat Linux Enterprisedb 6.3DB： postgresql 9.3PGHOME: /opt/PostgreSQL/9.3PGDATA: /opt/PostgreSQL/9.3/data歸檔目錄：/opt/pg_archive基礎備份目錄：/opt/base_archive

--生產環境中歸檔和資料等目錄應該放在不同的分區，或者儲存上。

2.修改歸檔配置，添加測試資料

• 修改pg設定檔postgresql.conf中以下條目

wal_level = archivearchive_mode = on archive_command = ‘cp -i %p /opt/pg_archive/%f </dev/null‘

• 建立歸檔目錄，並重啟資料庫，使歸檔生效

[[email protected] /]# mkdir -p /opt/pg_archive/[[email protected] /]# chown -R postgres:postgres /opt/pg_archive/[[email protected] /]# chmod -R 700 /opt/pg_archive/[[email protected] /]# mkdir -p /opt/base_archive[[email protected] /]# chown -R postgres:postgres /opt/base_archive[[email protected] /]# chmod -R 700 /opt/base_archive[[email protected] opt]# su - postgres-bash-4.1$  pg_ctl restart

• 建立一個測試表

-bash-4.1$ psql -d testpsql.bin (9.3.6)Type "help" for help.test=# create table time (ctime time);CREATE TABLEtest=# insert into time values(current_time);INSERT 0 1test=# select * from time;      ctime      ----------------- 11:40:26.229787(1 row)
test=# \q

3.執行基礎備份，並添加測試資料

• 備份data目錄，並壓縮

-bash-4.1$ psql postgres -c "select pg_start_backup(‘hot_backup‘);" pg_start_backup ----------------- 0/A000028(1 row)-bash-4.1$  tar --exclude $PGDATA/pg_xlog -cvjpf /opt/base_archive/pgbackup.tar.bz2 $PGDATAtar: Removing leading `/‘ from member names/opt/PostgreSQL/9.3/data//opt/PostgreSQL/9.3/data/pg_clo......-bash-4.1$ psql postgres -c "select pg_stop_backup();"NOTICE:  pg_stop_backup complete, all required WAL segments have been archived pg_stop_backup ---------------- 0/A0000B8(1 row)

--pg_start_backup會執行一個checkpoint的檢查點，可能需要消耗一段時間

--pg_stop_backup會終止備份模式，並自動切換到下一個WAL檔案，直到最後的WAL段檔案被歸檔後pg_stop_backup命令才會返回。

• 繼續添加測試資料

-bash-4.1$ psql -d testpsql.bin (9.3.6)Type "help" for help.test=# insert into time values (current_time);INSERT 0 1test=# insert into time values (current_time);INSERT 0 1test=# select * from time;      ctime      ----------------- 11:40:26.229787 11:49:56.836129 11:52:04.235879(3 rows)test=# \q

• 執行一次手動歸檔

-bash-4.1$ psqlpsql.bin (9.3.6)Type "help" for help.postgres=# select pg_switch_xlog(); pg_switch_xlog ---------------- 0/B000368(1 row)postgres=# \q-bash-4.1$ ls -alt /opt/pg_archive/ total 196628-rw-------. 1 postgres postgres 16777216 May  5 11:54 00000002000000000000000Bdrwx------. 2 postgres postgres     4096 May  5 11:54 .-rw-------. 1 postgres postgres      294 May  5 11:48 00000002000000000000000A.00000028.backup-rw-------. 1 postgres postgres 16777216 May  5 11:48 00000002000000000000000A-rw-------. 1 postgres postgres 16777216 May  5 11:45 000000020000000000000009-rw-------. 1 postgres postgres 16777216 May  5 10:30 000000020000000000000008drwxr-xr-x. 7 root     root         4096 May  4 12:01 ..

4.類比資料庫崩潰

[[email protected] pg_archive]# ps -ef |grep postgrespostgres  8112     1  0 10:30 pts/4    00:00:00 /opt/PostgreSQL/9.3/bin/postgres -D /opt/PostgreSQL/9.3/datapostgres  8113  8112  0 10:30 ?        00:00:00 postgres: logger process                                    postgres  8115  8112  0 10:30 ?        00:00:00 postgres: checkpointer process                              postgres  8116  8112  0 10:30 ?        00:00:00 postgres: writer process                                    postgres  8117  8112  0 10:30 ?        00:00:00 postgres: wal writer process                                postgres  8118  8112  0 10:30 ?        00:00:00 postgres: autovacuum launcher process                       postgres  8119  8112  0 10:30 ?        00:00:00 postgres: archiver process   last was 00000002000000000000000Bpostgres  8120  8112  0 10:30 ?        00:00:00 postgres: stats collector process                           root      9161  9034  0 14:02 pts/3    00:00:00 grep postgres[[email protected] pg_archive]# kill -9 8112[[email protected] pg_archive]# ps -ef |grep postgresroot      9174  9034  0 14:06 pts/3    00:00:00 grep postgres

5.資料庫恢複到指定時間 11:50:00

• 移除資料目錄

[[email protected] pg_archive]# cd /opt/PostgreSQL/9.3/[[email protected] 9.3]# mv data data_bak

--一般為了安全考慮，儲存空間足夠時，會把事故發生時的檔案做一個備份，而不是直接刪除掉。具體到這裡，整個data目錄和歸檔目錄最好不要刪除。

• 複製基礎備份到指定的位置，並檢查屬主和許可權

[[email protected] 9.3]# cd /opt/base_archive/[[email protected] base_archive]# lspgbackup.tar.bz2[[email protected] base_archive]# tar -xvf pgbackup.tar.bz2......[[email protected] base_archive]# cd opt/PostgreSQL/9.3/[[email protected] 9.3]# lsdata[[email protected] 9.3]# cp -r data/ /opt/PostgreSQL/9.3/[[email protected] 9.3]# cd /opt/PostgreSQL/9.3/[[email protected] 9.3]# ls -al |grep datadrwx------. 15 root     root        4096 May  5 14:15 datadrwx------. 16 postgres postgres    4096 May  5 11:48 data_bak[[email protected] 9.3]# chown -R postgres:postgres data[[email protected] 9.3]# chmod -R 700 data

• 在data目錄中建立pg_xlog目錄

[[email protected] data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog[[email protected] data]# chown -R postgres:postgres  /opt/PostgreSQL/9.3/data/pg_xlog[[email protected] data]# chmod -R 700  /opt/PostgreSQL/9.3/data/pg_xlog

• 在pg_xlog目錄中建立子目錄

[[email protected] data]# mkdir -p /opt/PostgreSQL/9.3/data/pg_xlog/archive_status[[email protected] data]# chown -R postgres:postgres  /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/[[email protected] data]# chmod -R 700  /opt/PostgreSQL/9.3/data/pg_xlog/archive_status/

• 在$PGDATA目錄中，建立恢複設定檔recovery.conf

[[email protected] data]# cd /opt/PostgreSQL/9.3/data[[email protected] data]# touch recovery.conf[[email protected] data]# echo "restore_command=‘cp -i /opt/pg_archive/%f %p‘" >>recovery.conf[[email protected] data]# echo "recovery_target_time = ‘2015-05-05 11:50:00‘" >>recovery.conf[[email protected] data]# cat recovery.conf restore_command=‘cp -i /opt/pg_archive/%f %p‘recovery_target_time = ‘2015-05-05 11:50:00‘[[email protected] data]# chown postgres:postgres recovery.conf [[email protected] data]# chmod 700 recovery.conf 

--恢複完成後，recovery.conf會被自動重新命名為recovery.done

6.檢查資料恢複情況

• 啟動資料庫服務

[[email protected] data]# service postgresql-9.3 startStarting PostgreSQL 9.3: pg_ctl: another server might be running; trying to start server anywaywaiting for server to start.... doneserver startedPostgreSQL 9.3 started successfully

• 串連進入資料庫查看是否恢複到對應的時間點

[[email protected] archive_status]# su - postgres-bash-4.1$ psql -d testpsql.bin (9.3.6)Type "help" for help.test=# select * from time;      ctime      ----------------- 11:40:26.229787 11:49:56.836129(2 rows)test=# \q-bash-4.1$ 

　　可以看到該表已經成功恢複到指定的時間點。

參考文檔：https://wiki.postgresql.org/wiki/9.1%E7%AC%AC%E4%BA%8C%E5%8D%81%E5%9B%9B%E7%AB%A0

Postgresql線上備份和恢複