postgres備份

| | 備份速度|備份范圍|恢復(fù)范圍|操作影響|備份原理|恢復(fù)成本|
| ------- |:------:|:-----:|:------:|:------:|:-----:|:-----:|:-------|
| 流復(fù)制 | 實(shí)時(shí) | 全備 | 當(dāng)前時(shí)刻|備庫(kù)只讀 | 預(yù)寫日志 | 高可用切換|
| pg_dump(all) | 慢 |全備(庫(kù)表) | 備份時(shí)刻|無(wú),互斥鎖,DDL|SQL訪問(wèn)|停庫(kù)恢復(fù)|
| pg_basebackup|快| 全備 | 備份時(shí)刻|無(wú)|文件復(fù)制|停庫(kù)恢復(fù)|
|連續(xù)歸檔(PITR)|快 | 增量 |上一次全備到當(dāng)前任意時(shí)刻|無(wú)|歸檔日志|停庫(kù)恢復(fù)

postgres安裝

  • centos自帶postgres，通過(guò)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ù)庫(kù), 在數(shù)據(jù)文件目錄/var/lib/pgsql/9.6/data/ 下執(zhí)行

    • su - postgres
    • initdb ./

  • 支持密碼登錄修改相關(guān)文件：

    • vi /var/lib/pgsql/9.6/data/pg_hba.conf添加host all all 0.0.0.0 0.0.0.0 md5
    • 開(kāi)啟遠(yuǎn)程訪問(wèn)，vi postgresql.conf修改 listen_addresses='*'
    • 重啟postgres，su postgres -c 'pg_ctl restart'，如果沒(méi)有設(shè)置$PGDATA環(huán)境變量，執(zhí)行該命令需要指定數(shù)據(jù)庫(kù)目錄-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

postgres備份

  • SQL轉(zhuǎn)儲(chǔ)
  • 文件系統(tǒng)級(jí)別備份
  • 流復(fù)制
  • 在線增量備份與任意時(shí)間點(diǎn)恢復(fù)

一、SQL轉(zhuǎn)儲(chǔ)

SQL 轉(zhuǎn)儲(chǔ)方法的思想是創(chuàng)建一個(gè)由SQL命令組成的文件，當(dāng)把這個(gè)文件回饋給服務(wù)器時(shí)，服務(wù)器將利用其中的SQL命令重建與轉(zhuǎn)儲(chǔ)時(shí)狀態(tài)一樣的數(shù)據(jù)庫(kù)。pg_dump創(chuàng)建的備份在內(nèi)部是一致的， 也就是說(shuō)，轉(zhuǎn)儲(chǔ)表現(xiàn)了pg_dump開(kāi)始運(yùn)行時(shí)刻的數(shù)據(jù)庫(kù)快照，且在pg_dump運(yùn)行過(guò)程中發(fā)生的更新將不會(huì)被轉(zhuǎn)儲(chǔ)。pg_dump工作的時(shí)候并不阻塞其他的對(duì)數(shù)據(jù)庫(kù)的操作。 （但是會(huì)阻塞那些需要排它鎖的操作，比如大部分形式的ALTER TABLE）

  • 導(dǎo)出數(shù)據(jù)庫(kù)數(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ù)庫(kù)及角色 pg_dumpall > /home/pg_all.dmp， 恢復(fù) psql –f /home/pg_all.dmp postgres
  • 備份壓縮(處理大型數(shù)據(jù)庫(kù))
    • 備份 pg_dump dbname | gzip > filename.gz
    • 恢復(fù) gunzip -c filename.gz | psql dbname

二、文件系統(tǒng)級(jí)別備份

  • 連續(xù)歸檔基礎(chǔ)備份pg_basebackup，被用于獲得一個(gè)正在運(yùn)行的PostgreSQL數(shù)據(jù)庫(kù)集簇的基礎(chǔ)備份，備份通過(guò)一個(gè)常規(guī)PostgreSQL連接制作，并且使用復(fù)制協(xié)議。獲得這些備份不會(huì)影響連接到該數(shù)據(jù)庫(kù)的其他客戶端,

  • 直接復(fù)制PostgreSQL用于存儲(chǔ)數(shù)據(jù)庫(kù)中數(shù)據(jù)的文件,要求主庫(kù)關(guān)閉。
    tar -cf backup.tar /usr/local/pgsql/data

  • 創(chuàng)建一個(gè)數(shù)據(jù)目錄的"一致快照"，創(chuàng)建一個(gè)包含數(shù)據(jù)庫(kù)的卷的"凍結(jié)快照"，然后從該快照復(fù)制整個(gè)數(shù)據(jù)目錄（如上，不能是部分復(fù)制）到備份設(shè)備，最后釋放凍結(jié)快照。即使在數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行時(shí)，這種方式也有效。但是，以這種方式創(chuàng)建的備份保存的文件看起來(lái)就像數(shù)據(jù)庫(kù)沒(méi)有被正確關(guān)閉時(shí)的狀態(tài)。因此，當(dāng)你從備份數(shù)據(jù)上啟動(dòng)數(shù)據(jù)庫(kù)服務(wù)器時(shí)，它會(huì)認(rèn)為上一次的服務(wù)器實(shí)例崩潰了并嘗試重放WAL日志。這不是問(wèn)題，只是需要注意（當(dāng)然WAL文件必須要包括在備份中）。你可以在拍攝快照之前執(zhí)行一次CHECKPOINT以便節(jié)省恢復(fù)時(shí)間。

  • 使用rsync來(lái)執(zhí)行一次文件系統(tǒng)備份。其做法是先在數(shù)據(jù)庫(kù)服務(wù)器運(yùn)行時(shí)執(zhí)行rsync，然后關(guān)閉數(shù)據(jù)庫(kù)服務(wù)器足夠長(zhǎng)時(shí)間來(lái)做一次rsync --checksum （--checksum是必需的，因?yàn)閞sync的文件修改 時(shí)間粒度只能精確到秒）。

三、流復(fù)制

流復(fù)制允許一臺(tái)后備服務(wù)器比使用基于文件的日志傳送更能保持為最新的狀態(tài)。 后備服務(wù)器連接到主服務(wù)器， 主服務(wù)器則在 WAL(write ahead log) 記錄產(chǎn)生時(shí)即將它們以流式傳送給后備服務(wù)器而不必等到 WAL文件被填充。在這種情況下主服務(wù)器上提交一個(gè)事務(wù)與該變化在后備服務(wù)器上變得可見(jiàn)之間存在短暫的延遲。 不過(guò)這種延遲比基于文件的日志傳送方式中要小得多， 在后備服務(wù)器的能力足以跟得上負(fù)載的前提下延遲通常低于一秒。

默認(rèn)情況下流復(fù)制是異步的

主庫(kù)配置

  • 創(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段的大小

從庫(kù)配置

    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'
  • 啟動(dòng)postgres： pg_ctl start

測(cè)試 流復(fù)制

查看相關(guān)進(jìn)程

  • 主庫(kù)查找進(jìn)程ps -ef |grep postgres，其中包含 postgres: wal sender process...
  • 從庫(kù)查找進(jìn)程ps -ef |grep postgres，其中包含 postgres: wal receiver process...

查看記錄點(diǎn)

  • 返回主庫(kù)記錄點(diǎn)、備庫(kù)記錄點(diǎn)：postgres=# select txid_current_snapshot(); 主庫(kù)每增加一條寫入，記錄點(diǎn)的值就會(huì)加1

查看主備庫(kù)同步狀態(tài)

  • postgres=# select * from pg_stat_replication; 字段state顯示的同步狀態(tài)有：startup（連接中）、catchup（同步中）、streaming（同步）；字段sync_state顯示的模式有：async（異步）、sync（同步）、potential（雖然現(xiàn)在是異步模式，但是有可能升級(jí)到同步模式）

  • 此時(shí)在主庫(kù)對(duì)數(shù)據(jù)的操作將會(huì)同步至備庫(kù)，備庫(kù)處于只讀模式。參考主從切換，將備庫(kù)升級(jí)為主庫(kù)。

四、在線增量備份與任意時(shí)間點(diǎn)恢復(fù)

在任何時(shí)間，PostgreSQL在數(shù)據(jù)集簇目錄的pg_xlog/子目錄下都保持有一個(gè)預(yù)寫式日志（WAL）。這個(gè)日志存在的目的是為了保證崩潰后的安全：如果系統(tǒng)崩潰，可以"重放"從最后一次檢查點(diǎn)以來(lái)的日志項(xiàng)來(lái)恢復(fù)數(shù)據(jù)庫(kù)的一致性。該日志的存在也使得第三種備份數(shù)據(jù)庫(kù)的策略變得可能：我們可以把一個(gè)文件系統(tǒng)級(jí)別的備份和WAL文件的備份結(jié)合起來(lái)。當(dāng)需要恢復(fù)時(shí)，我們先恢復(fù)文件系統(tǒng)備份，然后從備份的WAL文件中重放來(lái)把系統(tǒng)帶到一個(gè)當(dāng)前狀態(tài)。這種方法比之前的方法管理起來(lái)要更復(fù)雜，但是可以實(shí)現(xiàn)數(shù)據(jù)任意時(shí)間點(diǎn)恢復(fù)功能。

主庫(kù)設(shè)置(基于流復(fù)制中主庫(kù)的配置)

  • 配置postgresql.conf：
#開(kāi)啟歸檔模式
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í)行該命令前要保證 主備庫(kù)服務(wù)器的免密登錄, postgres用戶目錄在/var/lib/pgsql，切換postgres用戶，執(zhí)行ssh-keygen -t rsa，將生成的公鑰/var/lib/pgsql/.ssh/id_rsa.pub添加到免密登錄主機(jī)對(duì)應(yīng)的/var/lib/pgsql/.ssh/authorized_keys文件中，注意公鑰中的主機(jī)名需要在免密登錄的主機(jī)上配置dns或直接改為ip地址。

重啟主庫(kù)pg_ctl restart

從庫(kù)設(shè)置

  • 創(chuàng)建存儲(chǔ)歸檔文件的目錄： mkdir -p /data/postgres/pg_archive，設(shè)置文件權(quán)限chown postgres:postgres /data/postgres/pg_archive

  • 創(chuàng)建主庫(kù)的基礎(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將主庫(kù)基礎(chǔ)備份至該目錄，然后需要修改基礎(chǔ)備份中的postgresql.conf，關(guān)閉歸檔模式，注釋掉archive_mode和archive_command

  • 配置恢復(fù)文件，restore_command將歸檔日志復(fù)制過(guò)來(lái)，recovery_target_time是恢復(fù)到的時(shí)間點(diǎn),將$PGDATA即/var/lib/pgsql/9.6/data鏈接到基礎(chǔ)備份目錄/data/postgres/pg_basebackup，設(shè)置權(quán)限chmod 0700 $PGDATA，啟動(dòng)postgres，pg_ctl start，即可恢復(fù)到指定時(shí)間點(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#

http://blog.csdn.net/yanggd1987/article/details/51209344

https://www.2cto.com/database/201708/670383.html

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容