| | 備份速度|備份范圍|恢復(fù)范圍|操作影響|備份原理|恢復(fù)成本|
| ------- |:------:|:-----:|:------:|:------:|:-----:|:-----:|:-------|
| 流復(fù)制 | 實(shí)時 | 全備 | 當(dāng)前時刻|備庫只讀 | 預(yù)寫日志 | 高可用切換|
| pg_dump(all) | 慢 |全備(庫表) | 備份時刻|無,互斥鎖,DDL|SQL訪問|停庫恢復(fù)|
| pg_basebackup|快| 全備 | 備份時刻|無|文件復(fù)制|停庫恢復(fù)|
|連續(xù)歸檔(PITR)|快 | 增量 |上一次全備到當(dāng)前任意時刻|無|歸檔日志|停庫恢復(fù)
postgres安裝
centos自帶postgres丘损，通過
yum list | grep postgresql查看，安裝最新版的postgres,安裝yum源:yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm安裝PostgreSQL：
yum install postgresql96-server postgresql96-contrib可執(zhí)行文件在
/usr/pgsql-9.6/bin/添加到環(huán)境變量， 數(shù)據(jù)和配置文件在/var/lib/pgsql/9.6/data/
export POSTGRES_HOME=/usr/pgsql-9.6
export PGDATA=/var/lib/pgsql/9.6/data/
export PATH=$PATH:$POSTGRES_HOME/bin
TZ='Asia/Shanghai'; export TZ
-
初始化數(shù)據(jù)庫, 在數(shù)據(jù)文件目錄/var/lib/pgsql/9.6/data/ 下執(zhí)行
su - postgresinitdb ./
-
支持密碼登錄修改相關(guān)文件：
-
vi /var/lib/pgsql/9.6/data/pg_hba.conf添加host all all 0.0.0.0 0.0.0.0 md5 - 開啟遠(yuǎn)程訪問舆驶，
vi postgresql.conf修改listen_addresses='*' - 重啟postgres，
su postgres -c 'pg_ctl restart'拘荡，如果沒有設(shè)置$PGDATA環(huán)境變量珊皿，執(zhí)行該命令需要指定數(shù)據(jù)庫目錄-D /data
-
-
登錄袱结，創(chuàng)建用戶
- 執(zhí)行
su postgres垢夹，輸入psql進(jìn)入postgres命令行 - 執(zhí)行
ALTER USER postgres WITH PASSWORD '123456'設(shè)置postgres用戶的密碼 - 查看角色
\du果元，創(chuàng)建用戶并分配權(quán)限create user test_user password '123456' Superuser CreateDB;刪除用戶drop user test_user; - 退出
\q
- 執(zhí)行
postgres備份
- SQL轉(zhuǎn)儲
- 文件系統(tǒng)級別備份
- 流復(fù)制
- 在線增量備份與任意時間點(diǎn)恢復(fù)
一而晒、SQL轉(zhuǎn)儲
SQL 轉(zhuǎn)儲方法的思想是創(chuàng)建一個由SQL命令組成的文件，當(dāng)把這個文件回饋給服務(wù)器時迅耘，服務(wù)器將利用其中的SQL命令重建與轉(zhuǎn)儲時狀態(tài)一樣的數(shù)據(jù)庫监署。pg_dump創(chuàng)建的備份在內(nèi)部是一致的钠乏， 也就是說晓避，轉(zhuǎn)儲表現(xiàn)了pg_dump開始運(yùn)行時刻的數(shù)據(jù)庫快照，且在pg_dump運(yùn)行過程中發(fā)生的更新將不會被轉(zhuǎn)儲暑塑。pg_dump工作的時候并不阻塞其他的對數(shù)據(jù)庫的操作梯投。 （但是會阻塞那些需要排它鎖的操作，比如大部分形式的ALTER TABLE）
- 導(dǎo)出數(shù)據(jù)庫數(shù)據(jù)
pg_dump -U postgres -f /home/pg1.sql pg - 恢復(fù)數(shù)據(jù)
psql -U postgres -f /home/pg1.sql pg - pg_dump 表備份
pg_dump databasename –t tablename1 –t tablename2 >filename - pg_dumpall備份所有數(shù)據(jù)庫及角色
pg_dumpall > /home/pg_all.dmp， 恢復(fù)psql –f /home/pg_all.dmp postgres - 備份壓縮(處理大型數(shù)據(jù)庫)
- 備份
pg_dump dbname | gzip > filename.gz - 恢復(fù)
gunzip -c filename.gz | psql dbname
- 備份
二么鹤、文件系統(tǒng)級別備份
連續(xù)歸檔基礎(chǔ)備份pg_basebackup蒸甜，被用于獲得一個正在運(yùn)行的PostgreSQL數(shù)據(jù)庫集簇的基礎(chǔ)備份柠新，備份通過一個常規(guī)PostgreSQL連接制作辉巡，并且使用復(fù)制協(xié)議郊楣。獲得這些備份不會影響連接到該數(shù)據(jù)庫的其他客戶端,
直接復(fù)制PostgreSQL用于存儲數(shù)據(jù)庫中數(shù)據(jù)的文件,要求主庫關(guān)閉。
tar -cf backup.tar /usr/local/pgsql/data創(chuàng)建一個數(shù)據(jù)目錄的"一致快照"钥组，創(chuàng)建一個包含數(shù)據(jù)庫的卷的"凍結(jié)快照"程梦，然后從該快照復(fù)制整個數(shù)據(jù)目錄（如上橘荠，不能是部分復(fù)制）到備份設(shè)備砾医，最后釋放凍結(jié)快照如蚜。即使在數(shù)據(jù)庫服務(wù)器運(yùn)行時，這種方式也有效探赫。但是伦吠，以這種方式創(chuàng)建的備份保存的文件看起來就像數(shù)據(jù)庫沒有被正確關(guān)閉時的狀態(tài)。因此毛仪，當(dāng)你從備份數(shù)據(jù)上啟動數(shù)據(jù)庫服務(wù)器時搁嗓，它會認(rèn)為上一次的服務(wù)器實(shí)例崩潰了并嘗試重放WAL日志。這不是問題箱靴，只是需要注意（當(dāng)然WAL文件必須要包括在備份中）腺逛。你可以在拍攝快照之前執(zhí)行一次CHECKPOINT以便節(jié)省恢復(fù)時間。
- 使用rsync來執(zhí)行一次文件系統(tǒng)備份衡怀。其做法是先在數(shù)據(jù)庫服務(wù)器運(yùn)行時執(zhí)行rsync棍矛，然后關(guān)閉數(shù)據(jù)庫服務(wù)器足夠長時間來做一次rsync --checksum （--checksum是必需的，因?yàn)閞sync的文件修改 時間粒度只能精確到秒）抛杨。
三够委、流復(fù)制
流復(fù)制允許一臺后備服務(wù)器比使用基于文件的日志傳送更能保持為最新的狀態(tài)怖现。 后備服務(wù)器連接到主服務(wù)器茁帽， 主服務(wù)器則在 WAL(write ahead log) 記錄產(chǎn)生時即將它們以流式傳送給后備服務(wù)器而不必等到 WAL文件被填充。在這種情況下主服務(wù)器上提交一個事務(wù)與該變化在后備服務(wù)器上變得可見之間存在短暫的延遲真竖。 不過這種延遲比基于文件的日志傳送方式中要小得多脐雪， 在后備服務(wù)器的能力足以跟得上負(fù)載的前提下延遲通常低于一秒。
默認(rèn)情況下流復(fù)制是異步的
主庫配置
創(chuàng)建復(fù)制用戶：
create user rep replication password '123456';配置pg_hba.conf：
host replication rep 10.0.12.5/8 md5配置postgresql.conf：
wal_level = replica
hot_standby = on
max_wal_senders = 2 #流復(fù)制最大連接數(shù)
wal_keep_segments = 16 #xlog段的大小
從庫配置
pg_basebackup基礎(chǔ)備份：
pg_basebackup -D $PGDATA -Fp -Xs -v -P -h 10.0.12.56 -p 5432 -U rep配置recovery.conf
cp /usr/pgsql-9.6/share/recovery.conf.sample $PGDATA/recovery.conf
vim $PGDATA/recovery.conf
standby_mode = on
primary_conninfo = 'host=10.0.12.56 port=5432 user=rep password=123456'
- 啟動postgres：
pg_ctl start
測試 流復(fù)制
查看相關(guān)進(jìn)程
- 主庫查找進(jìn)程
ps -ef |grep postgres恢共，其中包含postgres: wal sender process... - 從庫查找進(jìn)程
ps -ef |grep postgres战秋，其中包含postgres: wal receiver process...
查看記錄點(diǎn)
- 返回主庫記錄點(diǎn)、備庫記錄點(diǎn)：
postgres=# select txid_current_snapshot();主庫每增加一條寫入讨韭，記錄點(diǎn)的值就會加1
查看主備庫同步狀態(tài)
postgres=# select * from pg_stat_replication;字段state顯示的同步狀態(tài)有：startup（連接中）脂信、catchup（同步中）、streaming（同步）透硝；字段sync_state顯示的模式有：async（異步）狰闪、sync（同步）、potential（雖然現(xiàn)在是異步模式濒生，但是有可能升級到同步模式）此時在主庫對數(shù)據(jù)的操作將會同步至備庫埋泵，備庫處于只讀模式。參考主從切換罪治，將備庫升級為主庫丽声。
四、在線增量備份與任意時間點(diǎn)恢復(fù)
在任何時間觉义，PostgreSQL在數(shù)據(jù)集簇目錄的pg_xlog/子目錄下都保持有一個預(yù)寫式日志（WAL）雁社。這個日志存在的目的是為了保證崩潰后的安全：如果系統(tǒng)崩潰，可以"重放"從最后一次檢查點(diǎn)以來的日志項(xiàng)來恢復(fù)數(shù)據(jù)庫的一致性晒骇。該日志的存在也使得第三種備份數(shù)據(jù)庫的策略變得可能：我們可以把一個文件系統(tǒng)級別的備份和WAL文件的備份結(jié)合起來霉撵。當(dāng)需要恢復(fù)時磺浙，我們先恢復(fù)文件系統(tǒng)備份，然后從備份的WAL文件中重放來把系統(tǒng)帶到一個當(dāng)前狀態(tài)徒坡。這種方法比之前的方法管理起來要更復(fù)雜撕氧，但是可以實(shí)現(xiàn)數(shù)據(jù)任意時間點(diǎn)恢復(fù)功能。
主庫設(shè)置(基于流復(fù)制中主庫的配置)
- 配置postgresql.conf：
#開啟歸檔模式
archive_mode = on
archive_command = 'ssh 10.0.12.5 test ! -f /data/postgres/pg_archive/%f && scp %p 10.0.12.5:/data/postgres/pg_archive/%f'
其中archive_command是歸檔命令崭参，將歸檔文件傳送至 10.0.12.5備份服務(wù)器的/data/postgres/pg_archive目錄下
執(zhí)行該命令前要保證 主備庫服務(wù)器的免密登錄, postgres用戶目錄在/var/lib/pgsql呵曹，切換postgres用戶，執(zhí)行ssh-keygen -t rsa何暮，將生成的公鑰/var/lib/pgsql/.ssh/id_rsa.pub添加到免密登錄主機(jī)對應(yīng)的/var/lib/pgsql/.ssh/authorized_keys文件中，注意公鑰中的主機(jī)名需要在免密登錄的主機(jī)上配置dns或直接改為ip地址铐殃。
重啟主庫pg_ctl restart
從庫設(shè)置
創(chuàng)建存儲歸檔文件的目錄：
mkdir -p /data/postgres/pg_archive海洼，設(shè)置文件權(quán)限chown postgres:postgres /data/postgres/pg_archive創(chuàng)建主庫的基礎(chǔ)備份目錄
mkdir -p /data/postgres/pg_basebackup，執(zhí)行pg_basebackup -D /data/postgres/pg_basebackup -Fp -Xs -v -P -h 10.0.12.56 -p 5432 -U rep將主庫基礎(chǔ)備份至該目錄富腊，然后需要修改基礎(chǔ)備份中的postgresql.conf坏逢，關(guān)閉歸檔模式，注釋掉archive_mode和archive_command配置恢復(fù)文件赘被，
restore_command將歸檔日志復(fù)制過來是整，recovery_target_time是恢復(fù)到的時間點(diǎn),將$PGDATA即/var/lib/pgsql/9.6/data鏈接到基礎(chǔ)備份目錄/data/postgres/pg_basebackup，設(shè)置權(quán)限chmod 0700 $PGDATA民假，啟動postgres浮入，pg_ctl start，即可恢復(fù)到指定時間點(diǎn)羊异。
cp /usr/pgsql-9.6/share/recovery.conf.sample /data/postgres/pg_basebackuprecovery.conf
vim recovery.conf
restore_command = 'cp /data/postgres/pg_basebackup/%f %p'
recovery_target_time = '2017-11-14 18:31:00'
參考資料：
http://www.postgres.cn/docs/9.6/backup.html
https://yq.aliyun.com/articles/59355#
