概述
備份重於一切，今天主要介紹PG的五種備份方式，僅供參考。
ps:前四種重點掌握
一、pg_dump備份及pg_restore恢復1、語法
可以在本地及遠端進行備份，只需要表的讀許可權即可備份。pg_dump建立的備份是一致的，在pg_dump執行時資料庫產生快照，不阻塞資料庫的DML操作，但是會阻塞需要排他鎖的操作，如alter table等。特別注意的是，pg_dump一次只能備份一個單獨的資料庫，且不能備份角色和表空間資訊（因為這些資訊是cluster-wide，而不是在某個資料庫中（per-database））。
使用pg_dump的自定義轉儲格式。. 如果PostgreSQL所在的系統上安裝了zlib壓縮庫，自定義轉儲格式將在寫出資料到輸出檔案時對其壓縮。這將產生和使用gzip時差不多大小的轉儲檔案，但是這種方式的一個優勢是其中的表可以被有選擇地恢復。
下面的命令使用自定義轉儲格式來轉儲一個數據庫：pg_dump -Fc dbname > filename自定義格式的轉儲不是psql的指令碼，只能透過pg_restore恢復，例如：pg_restore -d dbname filename
pg_dump [OPTION]... [DBNAME]pg_restore [OPTION]... [FILE]2、常見用法
--匯出指定庫（不含create database語句）pg_dump -h xx.xx..142 -U hwb -p 55432 -d pas_db > /data/pgbackup/pas_db_bak202012.sql#匯出指定庫（包含create database語句）pg_dump -h xx.xx..142 -U hwb -p 55432 -C pas_db > /data/pgbackup/pas_db_bak202012.sql--匯出指定庫，結果以自定義壓縮格式輸出pg_dump -Fc -h xx.xx..142 -U hwb -p 55432 -d pas_db > /data/pgbackup/pas_db_bak202012.dump--備份表pg_dump -h xx.xx..142 -U hwb -p 55432 -d pas_db -t t1 -t t2 > /data/pgbackup/t.sql--備份某個模式所有表(schema名為hwb)pg_dump -h xx.xx.142 -U hwb -p 55432 -d pas_db -t 'hwb.*' > /data/pgbackup/schema_202012.sql--備份某個模式所有表，排除一張表pg_dump -h xx.xx.142 -U hwb -p 55432 -d pas_db -t 'hwb.*' -T hwb.t1 > /data/pgbackup/schema_t1_202012.sql--還原（匯入postgres庫，自動建立schema和表）pg_restore -h xx.xx.142 -U postgres -p 55432 -d postgres -v /data/pgbackup/pas_db_bak202012.dump--single-transaction表示整個恢復過程是一個事務，要麼成功要麼回滾--恢復後需執行ANALYZE收集統計資訊psql -h xx.xx.142 -U postgres -p 55432 -d postgres --single-transaction < /data/pgbackup/pas_db_bak202012.sqlpg_dump只備份資料庫叢集中的某個資料庫的資料，它不會匯出角色和表空間相關的資訊。pg_dumpall則可以匯出整個資料庫叢集中所有的資料庫中的資料，同時也會匯出角色、使用者和表空間的定義資訊。
執行pg_dumpall需要超級使用者許可權。
1、語法
pg_dumpall [OPTION]...2、常用用法
--匯出所有database(當應用需要OID欄位的話(比如在外來鍵約束中用到)，需新增-o選項)pg_dumpall -v > /data/pgbackup/db_all.dmp--只轉儲全域性物件(角色和表空間)，而不轉儲資料庫pg_dumpall -g -v > /data/pgbackup/role_tbs.sql---r（roles-only）只轉儲角色，不轉儲資料庫或表空間pg_dumpall -r -v > /data/pgbackup/role.sql-s(schema-only)只輸出物件定義(模式)，不輸出資料pg_dumpall -s -v > /data/pgbackup/schema.sql--恢復（執行這個命令的時候連線到哪個資料庫無關緊要，因為pg_dumpall 建立的指令碼會包含建立和連線資料庫的命令）--恢復時需刪對應資料庫，否則如果資料庫存在對應的表會自動插入新的資料psql postgres -f db_all.dmp COPY在PostgreSQL表和檔案之間交換資料。 COPY TO把一個表的所有內容都複製到一個檔案，而COPY FROM從一個檔案裡複製資料到一個表裡(把資料附加到表中已經存在的內容裡)。 COPY TO還能複製SELECT查詢的結果。
如果聲明瞭一個欄位列表，COPY將只在檔案和表之間複製已宣告欄位的資料。 如果表中有任何不在欄位列表裡的欄位，那麼COPY FROM將為那些欄位插入預設值。
帶檔名的COPY指示PostgreSQL伺服器直接從檔案中讀寫資料。 如果聲明瞭檔名，那麼伺服器必須可以訪問該檔案，而且檔名必須從伺服器的角度宣告。 如果使用了PROGRAM選項，則伺服器會從指定的這個程式進行輸入或是寫入該程式作為輸出。 如果使用了STDIN 或STDOUT選項，那麼資料將透過客戶端和伺服器之間的連線來傳輸。
注意：copy命令必須在plsql命令列執行，執行使用者必須為superuser，普通使用者進行執行，需要在copy前面加入 “\”，即 \copy。
COPY只能用於表，不能用於檢視，不過可以用於COPY (SELECT * FROM viewname) TO ...
1、語法
--匯出COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ]--匯入（如果匯出的時候，指定了header屬性，那麼在匯入的時候，也需要指定）COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ]copy to的匯出速度非常之快，經測試10W的資料量只需要3秒左右的時間。
COPY FROM能夠識別下列特殊反斜槓字元：
2、常見用法
--服務端匯出，匯出到資料庫所在伺服器copy t2 to '/data/pgbackup/t2.csv' with csv;--匯出指定屬性copy t2(name) to '/data/pgbackup/t2_name.csv' with csv;copy (select * from t2) to '/data/pgbackup/t2_sel.csv' with csv;--客戶端匯出，匯出到psql命令所在伺服器\copy t2 to '/tmp/t2.dmp' 或者psql -c "copy t2 to stdout" > /tmp/t2.dmp--如果匯出的欄位，有integer[]型別，直接匯出，再匯入的話，會有問題，解決辦法是需要在匯出的時候，進行處理\COPY ( select coalesce(integer_array, '{}')::integer[] as integer_array from table ) TO '/tmp/data.csv' with csv header;--使用escape或unicode模式輸入特殊字元(例如TAB做分隔符)\copy aa from '/tmp/aa.csv' with (delimiter U&'\0009')\copy aa from '/tmp/aa.csv' with (delimiter E'\t')1、基礎備份
--postgresql.conf# - Archiving -wal_level = replicaarchive_mode = on # enables archiving; off, on, or alwaysarchive_command = 'test ! -f /data/pgarch/%f && cp %p /data/pgarch/%f;find /data/pgarch/ -type f -mtime +30 -exec rm -f {} \;'--建立REPLICATION角色CREATE ROLE replica login replication encrypted password 'replica@1234';--配置pg_hba.conf，允許遠端流式備份echo "host replication replica 0.0.0.0/0 md5" >> pg_hba.conf--模擬資料create database pas_db with owner=hwb ENCODING='UTF8' TABLESPACE=pas_data connection limit=-1;\c pas_db postgresselect pg_switch_wal();create table t4(id int);select pg_switch_wal();insert into t4 values(1),(2),(3),(4);select pg_switch_wal();select current_timestamp;-- 2020-12-14 16:13:23.10133+08select pg_switch_wal();insert into t4 values(5),(6),(7),(8);--遠端使用pg_basebackup備份systemctl stop postgresqlrm -rf /data/pgdata/*#-Fp表示以plain格式資料，-Xs表示以stream方式包含所需的WAL檔案，-P表示顯示進度，-R表示為replication寫配置資訊。#備份完成，使用-R選項，在data目錄下自動生成standby.signal“訊號”檔案（可手工使用touch命令生成）以及更新了postgresql.auto.conf檔案#postgresql.auto.conf中寫入了主庫的連線資訊（可手工新增primary_conninfo資訊）。pg_basebackup --progress -D /data/pgdata -h xx.142 -p 55432 -U replica --password -Fp -Xs -P -R2、恢復
--在data目錄下建立一個空檔案：touch recovery.signal--修改 postgresq.conf 檔案把archive相關引數全部註釋掉並增加兩行：restore_command = 'cp /data/pgarch/%f %p'recovery_target_time = '2020-12-14 16:13:23.10133+08'a.恢復到最新：restore_command = 'cp /data/pgarch/%f %p'recovery_target_timeline = 'latest'b.恢復到指定的時間點：restore_command = 'cp /data/pgarch/%f %p'recovery_target_time = '2020-12-14 16:13:23.10133+08'c.建立還原點：SELECT pg_create_restore_point('restore_point1');d.恢復到還原點：restore_command = 'cp /data/pgarch/%f %p'recovery_target_name ='restore_point1'--啟動資料庫進行恢復systemctl restart postgresqlPostgreSQL有一個匯出和匯入事務快照的功能，這個功能在9.2版本開始支援，允許事務共享它當時的snapshot給其他的事務使用。SET TRANSACTION SNAPSHOT命令允許新的事務使用與一個現有事務相同的快照執行。已經存 在的事務必須已經把它的快照用pg_export_snapshot函式匯出。該函式會返回一個快照識別符號，SET TRANSACTION SNAPSHOT需要被給定一個快照識別符號來指定要匯入的快照。需要注意的是：只有事務是SERIALIZABLE以及 repeatable read時，DEFERRABLE 事務屬性才會有效。
PostGreSQL採用“快照”方式來實現MVCC。具體地說，這意味著每一個事務中的查詢僅能看到：
1)該事務啟動之前已經提交的事務所作出的資料更改。
2)當前事務中該查詢之前的查詢所作出的更改。
下面基於事務隔離級別repeatable read進行測試
1、建表
create table test (id int); insert into test values (1),(2);--開啟五個會話進行測試2、session1:
begin TRANSACTION ISOLATION LEVEL repeatable read; SELECT pg_export_snapshot(); --00000004-0000047B-1insert into test values (3);SELECT pg_export_snapshot(); --00000004-0000047B-2select * from txid_current();select * from txid_current_snapshot(); 3、session2(插入一條新資料並提交)：
insert into test values (4);4、session3(能檢視到會話2插入的資料):
select * from test;5、session4 (匯入s1的第一個snapshot, 因此看不到s2提交的資料) :
begin TRANSACTION ISOLATION LEVEL repeatable read; SET TRANSACTION SNAPSHOT '00000004-0000047B-1';select * from test; select * from txid_current(); select * from txid_current_snapshot();6、session5 (匯入s1的第二個snapshot, 因此看不到s2提交的資料, 同時驗證了看不到s1修改過的資料):
begin TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION SNAPSHOT '00000004-0000047B-2';select * from test; select * from txid_current(); select * from txid_current_snapshot(); 7、session1(提交)：
8、session4 (s1提交後, 這個snapshot還存在, 只要還有匯入了這個snapshot的事務存在著) :
select * from test; 9、session5 (s1提交後, 這個snapshot還存在, 只要還有匯入了這個snapshot的事務存在著)
篇幅有限，基於時間點恢復的內容後面單獨介紹吧，感興趣的朋友可以關注下！