postgresql備份與恢復資料庫

一、pg_dump含義

pg_dump 是一個用於備份 Postgresql 資料庫的實用程式。由 pg_dump 備份的轉儲檔案是完全一致的，因為轉儲是 pg_dump 開始執行時資料的快照。 pg_dump 不會阻止其他使用者訪問資料庫。如果普通使用者已被授予資料庫許可權，則可以執行 pg_dump 。 pg_dump 只能備份一個資料庫。 Pg_dumpall 用於備份整個叢集或備份叢集中所有資料庫共有的公共物件。

二、pg_dump檔案型別

轉儲檔案可以指令碼或存檔檔案格式匯出。

指令碼轉儲檔案是純文字檔案，其中包含將資料庫恢復到儲存時所處狀態所需的 SQL 命令。使用以此方式建立的指令碼；使用 psql 或其他工具執行生成的 sql 檔案將資料庫恢復到當前狀態就足夠了。必須先建立相關的資料庫，然後才能執行此命令，否則預設情況下將在 postgres 資料庫中建立物件。

如果不想手動建立資料庫，則必須使用 pg_dump 以存檔檔案格式，以自定義，目錄， tar方式來 建立備份。然而，像這種情況下，需要使用 pg_restore 工具來恢復資料。

三、pg_dump語法

pg_ dump [connection-option …] [option …] [dbname]

-h 引數指定要備份的資料庫的主機

-p 引數指定埠

-U 引數指定資料庫使用者

四、pg_dump備份與恢復

4.1 使用pg_dump備份與恢復資料庫

1、 備份資料庫

可以使用幾種不同的語法生成名為 zabbix 的資料庫的 SQL 轉儲檔案。

pg_dump zabbix > db.sql

pg_dump -h localhost -p 5432 -U postgres zabbix > /home/postgres/dump/db_data.sql

pg_dump -h localhost -p 5432 -U postgres zabbix -f /home/postgres/dump/db_data.sql

2、恢復 資料庫

可以將建立的轉儲檔案還原為另一個名為 zabbix 的資料庫。

psql -d zabbix -f /home/postgres/dump/db_zabbix_data.sql

psql -f /home/postgres/dump/db_zabbix_data.sql -d newzabbix -p 5432 -U postgres

4.2 、 pg_dump 自定義格式

使用以下命令以自定義檔案格式建立 zabbix 資料庫的轉儲。透過這種方式生成的備份檔案將比 sql 檔案小得多，因為在後臺使用 zlib 壓縮了該檔案。

pg_dump -Fc zabbix > /home/postgres/dump/zabbix_db.dump

從自定義檔案格式轉儲恢復資料庫

pg_restore -d test_zabbix /home/postgres/dump/zabbix_db.dump

4.3 pg_dump目錄格式

使用以下命令以目錄格式建立 zabbix 資料庫的轉儲。

pg_dump -Fd zabbix -f dumpdirectory

或使用並行方式備份

pg_dump -Fd zabbix -j 5 -f dumpdir

從目錄檔案恢復資料庫

pg_restore -Fd -l dumpdirectory

4.4、pg_dump tar格式備份

下面的命令以 tar 檔案格式建立 mydb 資料庫的轉儲檔案。

pg_dump -Ft zabbix> /home/postgres/dump/zabbix_db.tar

從tar檔案還原Postgres資料庫

pg_restore -Ft -d zabbix  /home/postgres/dump/zabbix_db.tar

4.5、pg_dump指定表備份

1、備份指定的表

pg_dump -t  table1 -d zabbix > /home/postgres/dump/table1.sql

2、備份名稱以tbl開頭的表

pg_dump -t 'tbl*' -d zabbix > /home/postgres/dump/tbl.sql

3、備份名稱以tbl開頭但不是tbl_mustafa的表

pg_dump -t 'tbl*' -T tbl_mustafa -d zabbix > /home/postgres/dump/tbl1.sql

4、備份名稱以East或West開頭並以gsm結尾的所有模式，並排除名稱包含test的所有模式

pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' zabbix >/home/postgres/dump/tbl2.sql

pg_dump -n '(east|west)*gsm' -N '*test*' zabbix > db.sql

5、匯出資料、不導表結構

pg_dump -a zabbix > /home/postgres/dump/tbl1.sql

pg_dump --data-only zabbix > /home/postgres/dump/tbl1.sql

4.6、pg_dump壓縮

以下命令用於透過 pg_dump 進行壓縮備份。

pg_dump zabbix | gzip -9 > zabbix.gz

4.7、pg_dump拆分

將轉儲輸出拆分為一定大小的多個檔案

pg_dump zabbix | split -b 1m – files

恢復拆分轉儲檔案

cat files* | psql zabbix

4.8、pg_dump ON_ERROR_STOP

預設 ;  如果還原時發生錯誤，則 psql 將忽略該錯誤並繼續執行。我們可以透過設定 ON_ERROR_STOP 變數來自定義這種情況。執行此命令後，程式碼將一直執行到發生錯誤的行，但後續行將不被處理。

psql --set zabbix < zabbix.sql

4.9、pg_dumpall備份

pg_dumpall 備份叢集的所有內容。 pg_dumpall 為給定叢集中的每個資料庫建立一個備份，併為叢集範圍的角色和表空間定義之類的資料建立備份。 使用 pg_dumpall 進行恢復時，始終需要超級使用者訪問許可權才能恢復角色和表空間資訊。 pg_dumpall 傳送命令來建立角色，表和空資料庫，然後為每個資料庫執行 pg_dump 。

備份全庫：

pg_dumpall > all_dbdata.sql

pg_dumpall -f all_dbdata.sql

恢復全庫：

psql postgres < all_dbdata.sql 

psql -f all_dbdata.sql postgres