PostgreSQL導(dǎo)入導(dǎo)出工具之pg_dump

IT那活兒發(fā)布于2023-01-11 13:19 / 2356人閱讀

PostgreSQL導(dǎo)入導(dǎo)出工具之pg_dump

點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！

一

pg_dump是什么

pg_dump是一個(gè)用于備份PostgreSQL數(shù)據(jù)庫(kù)的實(shí)用工具。

即使當(dāng)前數(shù)據(jù)庫(kù)正在使用，也能夠生成一致性的備份。

這也就意味著轉(zhuǎn)儲(chǔ)的內(nèi)容是pg_dump開(kāi)始運(yùn)行時(shí)刻的數(shù)據(jù)庫(kù)快照，且在pg_dump運(yùn)行過(guò)程中發(fā)生的更新將不會(huì)被轉(zhuǎn)儲(chǔ)。pg_dump備份期間不會(huì)阻塞其他用戶訪問(wèn)數(shù)據(jù)庫(kù)(包括讀、寫(xiě))，但是會(huì)阻塞那些需要排它鎖的操作，比如大部分形式的ALTER TABLE。

pg_dump只能備份一個(gè)數(shù)據(jù)庫(kù)。

如果要備份Cluster中數(shù)據(jù)庫(kù)共有的全局對(duì)象，例如角色和表空間，需要使用pg_dumpall。

轉(zhuǎn)儲(chǔ)可以被輸出到腳本或歸檔文件格式。

轉(zhuǎn)儲(chǔ)的腳本是包含 SQL 命令的純文本文件，它們可以用來(lái)重構(gòu)數(shù)據(jù)庫(kù)到它被轉(zhuǎn)儲(chǔ)時(shí)的狀態(tài)。要從這樣一個(gè)腳本恢復(fù)，直接使用psql執(zhí)行即可。腳本文件還可以通過(guò)修改一部分內(nèi)容在其他架構(gòu)的數(shù)據(jù)庫(kù)上重構(gòu)數(shù)據(jù)庫(kù)，其和mysqldump很類(lèi)似。這也是相對(duì)于其他備份方法的一個(gè)重要優(yōu)勢(shì)。

注意：pg_dump產(chǎn)生的轉(zhuǎn)儲(chǔ)文件不包含優(yōu)化器用來(lái)做出查詢計(jì)劃決定的統(tǒng)計(jì)信息。當(dāng)導(dǎo)入完成后，建議做全庫(kù)的ANALYZE，這對(duì)后續(xù)優(yōu)化器能根據(jù)可用的統(tǒng)計(jì)信息對(duì)SQL執(zhí)行進(jìn)行最優(yōu)化操作。

另一種可選的歸檔文件格式必須與pg_restore配合使用來(lái)重建數(shù)據(jù)庫(kù)。

它們?cè)试Spg_restore能選擇恢復(fù)什么，或者甚至在恢復(fù)之前對(duì)條目重排序。歸檔文件格式被設(shè)計(jì)為在架構(gòu)之間可移植。當(dāng)使用歸檔文件格式之一并與pg_restore組合時(shí)，pg_dump提供了一種靈活的歸檔和傳輸機(jī)制。

pg_dump可以被用來(lái)備份整個(gè)數(shù)據(jù)庫(kù)，然后pg_restore可以被用來(lái)檢查歸檔并/或選擇數(shù)據(jù)庫(kù)的哪些部分要被恢復(fù)。

最靈活的輸出文件格式是“自定義”格式（-Fc）和“目錄”格式（-Fd）。它們?cè)试S選擇和重排序所有已歸檔項(xiàng)、支持并行恢復(fù)并且默認(rèn)是壓縮的。“目錄”格式是唯一一種支持并行轉(zhuǎn)儲(chǔ)的格式。

如果源數(shù)據(jù)庫(kù)的大小很大并且數(shù)據(jù)庫(kù)服務(wù)器之間的連接速度很慢，則可以將源數(shù)據(jù)庫(kù)轉(zhuǎn)儲(chǔ)到文件中，然后將文件復(fù)制到遠(yuǎn)程服務(wù)器上，然后將其還原。

二

以pgtt庫(kù)為例進(jìn)行講解

2.1 將源數(shù)據(jù)庫(kù)轉(zhuǎn)儲(chǔ)到文件中。在之前要查看一下數(shù)據(jù)庫(kù)的大小，選擇合適大小的目錄存放文件。

--源端查看pgtt數(shù)據(jù)庫(kù)大小：

c pgtt
select pg_database_size(pgtt);

--根據(jù)庫(kù)大小將pgtt全庫(kù)導(dǎo)出到/app/pg目錄下，-b包含LOB表：

su - postgres
pg_dump -U root -p 10001 -d pgtt -b -f /app/pg/pgtt_data20211022.sql

2.2 將轉(zhuǎn)儲(chǔ)文件復(fù)制到目標(biāo)端服務(wù)器上。(這里目標(biāo)端接受目錄必須要有其他屬組用戶寫(xiě)權(quán)限)

scp pgtt_data20211022.sql postgres@xxx.xxx.xxx.xxx:/app/pg

2.3 在目標(biāo)端創(chuàng)建新的數(shù)據(jù)庫(kù)，并將轉(zhuǎn)儲(chǔ)文件還原。

--創(chuàng)建用戶和數(shù)據(jù)庫(kù)：

CREATE USER pgtt WITH  ENCRYPTED PASSWORD xxxxxx;
CREATE DATABASE pgtt OWNER pgtt TEMPLATE template1;
REVOKE CONNECT ON DATABASE pgtt FROM PUBLIC;
GRANT CONNECT ON DATABASE pgtt TO pgtt;

--還原轉(zhuǎn)儲(chǔ)文件（如果該庫(kù)有dblink，那么在還原之前要安裝好oracle_fdw插件）：

su - postgres
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_data20211022.sql

如果服務(wù)器之間的連接速度很快并且數(shù)據(jù)庫(kù)的大小不大，則可以使用以下命令：

pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb

如：

pg_dump -C -h $local -U root pgtt | psql -h $remote -U root pgtt

以上方法是將一個(gè)庫(kù)一次性全部導(dǎo)出導(dǎo)入，這樣可能會(huì)報(bào)一些用戶不存在的錯(cuò)誤。

原因是在還原轉(zhuǎn)儲(chǔ)文件的時(shí)候會(huì)將一些表的權(quán)限賦給其他用戶，而該文件中是沒(méi)有該用戶的定義語(yǔ)句的，并且目標(biāo)端服務(wù)器也沒(méi)有該用戶，所以會(huì)報(bào)錯(cuò)。

其實(shí)辦法很簡(jiǎn)單，先僅導(dǎo)角色定義無(wú)需備份表空間、再全導(dǎo)。如果已經(jīng)報(bào)用戶不存在的錯(cuò)誤了，那么就先導(dǎo)角色定義，再導(dǎo)結(jié)構(gòu)（不包含數(shù)據(jù)），最后導(dǎo)數(shù)據(jù)（僅導(dǎo)數(shù)據(jù)）。

--如果僅需備份角色定義而無(wú)需備份表空間，那么可以加上--roles-only選項(xiàng)：

pg_dumpall -h 10.10.xxx.xxx -U root --port=10001 -f /app/pg/onlyroles20211022.sql --roles-only

--只導(dǎo)結(jié)構(gòu)，不導(dǎo)數(shù)據(jù)：

pg_dump -U root -p 10001 -d pgtt -s -b -f /app/pg/pgtt_jg_20211022.sql

--只導(dǎo)數(shù)據(jù)，沒(méi)有創(chuàng)建用戶角色：

pg_dump -U root -p 10001 -d pgtt -a -b -f /app/pg/pgtt_data20211022.sql

--傳輸?shù)侥繕?biāo)端：

scp onlyroles20211022.sql postgres@10.10.xxx.xxx:/app/pg
scp pgtt_jg_20211022.sql postgres@10.10.xxx.xxx:/app/pg
scp pgtt_data20211022.sql postgres@10.10.xxx.xxx:/app/pg

--目標(biāo)端執(zhí)行：

psql -U root -p 10001 -d pgtt -f /app/pg/onlyroles20211022.sql
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_jg_20211022.sql
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_data20211022.sql