PostgreSQL導入導出工具之pg_dump

IT那活兒發布于2023-01-11 13:19 / 2364人閱讀

點擊上方“IT那活兒”公眾號，關注后了解更多內容，不管IT什么活兒，干就完了！！！

一

pg_dump是什么

pg_dump是一個用于備份PostgreSQL數據庫的實用工具。

即使當前數據庫正在使用，也能夠生成一致性的備份。

這也就意味著轉儲的內容是pg_dump開始運行時刻的數據庫快照，且在pg_dump運行過程中發生的更新將不會被轉儲。pg_dump備份期間不會阻塞其他用戶訪問數據庫(包括讀、寫)，但是會阻塞那些需要排它鎖的操作，比如大部分形式的ALTER TABLE。

pg_dump只能備份一個數據庫。

如果要備份Cluster中數據庫共有的全局對象，例如角色和表空間，需要使用pg_dumpall。

轉儲可以被輸出到腳本或歸檔文件格式。

轉儲的腳本是包含 SQL 命令的純文本文件，它們可以用來重構數據庫到它被轉儲時的狀態。要從這樣一個腳本恢復，直接使用psql執行即可。腳本文件還可以通過修改一部分內容在其他架構的數據庫上重構數據庫，其和mysqldump很類似。這也是相對于其他備份方法的一個重要優勢。

注意：pg_dump產生的轉儲文件不包含優化器用來做出查詢計劃決定的統計信息。當導入完成后，建議做全庫的ANALYZE，這對后續優化器能根據可用的統計信息對SQL執行進行最優化操作。

另一種可選的歸檔文件格式必須與pg_restore配合使用來重建數據庫。

它們允許pg_restore能選擇恢復什么，或者甚至在恢復之前對條目重排序。歸檔文件格式被設計為在架構之間可移植。當使用歸檔文件格式之一并與pg_restore組合時，pg_dump提供了一種靈活的歸檔和傳輸機制。

pg_dump可以被用來備份整個數據庫，然后pg_restore可以被用來檢查歸檔并/或選擇數據庫的哪些部分要被恢復。

最靈活的輸出文件格式是“自定義”格式（-Fc）和“目錄”格式（-Fd）。它們允許選擇和重排序所有已歸檔項、支持并行恢復并且默認是壓縮的。“目錄”格式是唯一一種支持并行轉儲的格式。

如果源數據庫的大小很大并且數據庫服務器之間的連接速度很慢，則可以將源數據庫轉儲到文件中，然后將文件復制到遠程服務器上，然后將其還原。

二

以pgtt庫為例進行講解

2.1 將源數據庫轉儲到文件中。在之前要查看一下數據庫的大小，選擇合適大小的目錄存放文件。

--源端查看pgtt數據庫大小：

c pgtt
select pg_database_size(pgtt);

--根據庫大小將pgtt全庫導出到/app/pg目錄下，-b包含LOB表：

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

2.2 將轉儲文件復制到目標端服務器上。(這里目標端接受目錄必須要有其他屬組用戶寫權限)

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

2.3 在目標端創建新的數據庫，并將轉儲文件還原。

--創建用戶和數據庫：

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;

--還原轉儲文件（如果該庫有dblink，那么在還原之前要安裝好oracle_fdw插件）：

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

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

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

以上方法是將一個庫一次性全部導出導入，這樣可能會報一些用戶不存在的錯誤。

原因是在還原轉儲文件的時候會將一些表的權限賦給其他用戶，而該文件中是沒有該用戶的定義語句的，并且目標端服務器也沒有該用戶，所以會報錯。

其實辦法很簡單，先僅導角色定義無需備份表空間、再全導。如果已經報用戶不存在的錯誤了，那么就先導角色定義，再導結構（不包含數據），最后導數據（僅導數據）。

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

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

--只導結構，不導數據：

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

--只導數據，沒有創建用戶角色：

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

--傳輸到目標端：

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

--目標端執行：

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

本文作者：魏斌（上海新炬王翦團隊）

本文來源：“IT那活兒”公眾號

云服務器 GPU云服務器 mysql數據庫導入導出工具導出導入導入導出導入?導出

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://m.specialneedsforspecialkids.com/yun/129397.html

Postgresql 備份與恢復

摘要：指定要用于查找的口令文件的名稱。前四個字段可以是確定的字面值，也可以使用通配符匹配所有。利用環境變量引用的文件權限也要滿足這個要求，否則同樣會被忽略。在上，該文件被假定存儲在一個安全的目錄中，因此不會進行特別的權限檢查。 pg_dump pg_dump 把一個數據庫轉儲為純文本文件或者是其它格式. 用法: pg_dump [選項]... [數據庫名字] 一般選項: -f, --fi...

阿羅 2019-07-25 12:34 評論0 收藏0
用 PostgreSQL 的 COPY 導入導出 CSV

摘要：無意中看到了一篇講數據批量導入的文章，才注意到的命令。的直接可以干這個事情，而且導出速度是非常快的。總結還有一些其他配置，比如把輸入輸出源指定成和命令，或者指定的等等。合理使用能大大提高效率。 TL;DR 無意中看到了一篇講數據批量導入的文章，才注意到 PostgreSQL 的 COPY 命令。簡而言之，它用來在文件和數據庫之間復制數據，效率非常高，并且支持 CSV 。導出 CS...

ethernet 2019-07-25 12:34 評論0 收藏0
用 PostgreSQL 的 COPY 導入導出 CSV

摘要：無意中看到了一篇講數據批量導入的文章，才注意到的命令。的直接可以干這個事情，而且導出速度是非常快的。總結還有一些其他配置，比如把輸入輸出源指定成和命令，或者指定的等等。合理使用能大大提高效率。 TL;DR 無意中看到了一篇講數據批量導入的文章，才注意到 PostgreSQL 的 COPY 命令。簡而言之，它用來在文件和數據庫之間復制數據，效率非常高，并且支持 CSV 。導出 CS...

FleyX 2019-06-25 18:24 評論0 收藏0
Rails 3 升級 Rails 4 中遇到的問題及解決方法

摘要：的問題當出現不能的時候，我也嘗試過這種提示里面的命令，可是還是不能解決這個問題。解決方法一般自己機器上面的都是測試數據，所以可以直接刪除掉舊的數據庫文件。解決方法然后就可以該干嘛干嘛了。有些出現的問題其實是不懂正確的流程，都是在試錯，可是還是學到了很多東西，寫下了，希望對我和大家都有幫助。 Homebrew 的問題當我去運行brew update的時候出現錯誤untracked...

史占廣 2019-07-25 12:31 評論0 收藏0
Oracle SQL Developer 個人使用記錄

摘要：打開多個窗口一個數據庫連接打開多個窗口用于查看數據表及數據進入工具首選項數據庫對象查看器勾選自動凍結對象查看器窗口即可。顯示行號進入工具首選項代碼編輯器行裝訂線勾選顯示行數即可。 Oracle SQL Developer 個人使用記錄以下簡稱 SQL Developer 對我Java開發來說，這個工具已經足夠使用了，雖然還有很多缺點，但夠用就行，相對于我來說的優點：整體UI還算舒...

leanxi 2019-06-25 18:59 評論0 收藏0