目錄
文檔用途
詳細信息

文檔用途
本文檔主要用于展示瀚高數據庫并行導入導出步驟和注意事項。

詳細信息
1 創建測試環境

1.1 創建測試庫

create database htest ;
1.2 創建測試表

htest=# create table bxtest (a integer,b integer,c integer ,d integer);

CREATE TABLE

1.3創建虛擬數據

htest=# insert into bxtest select (random()(10^{5))::integer,(random()*(10}5))::integer,(random()(10^5))::integer from generate_series(1,1000000) ;

INSERT 0 1000000

注意：該出可以根據實際情況多執行幾次，小批量數據在固態硬盤環境并行導出效果可能并不明顯，每1000000條數據約42.5MB。

2 并行導出

2.1導出命令

mkdir /home/highgo/baktest

pg_dump -j 5 -f /home/highgo/baktest/test1 -d htest -F d -h localhost -p 5866 -U htest --導出htest庫

pg_dump -j 5 -f /home/highgo/baktest/test2 -d htest -t bxtest -F d -h localhost -p 5866 -U htest --導出htest庫中的bxtest表

2.1 進程展示

并行導出前：

[highgo@tqz baktest]$ ps -ef | grep postgres

highgo 1876 1 0 15:28 pts/1 00:00:00 /data/highgo/4.7.7/bin/postgres

highgo 1877 1876 0 15:28 ? 00:00:00 postgres: logger process

highgo 1879 1876 0 15:28 ? 00:00:00 postgres: checkpointer process

highgo 1880 1876 0 15:28 ? 00:00:00 postgres: writer process

highgo 1881 1876 0 15:28 ? 00:00:00 postgres: wal writer process

highgo 1882 1876 0 15:28 ? 00:00:00 postgres: autovacuum launcher process

highgo 1883 1876 0 15:28 ? 00:00:00 postgres: archiver process last was 000000010000000000000077

highgo 1884 1876 0 15:28 ? 00:00:00 postgres: stats collector process

highgo 4347 1876 1 18:19 ? 00:00:11 postgres: htest htest [local] idle

2.2 并行導出數據庫狀態

[highgo@tqz baktest]$ ps -ef | grep postgres

highgo 1876 1 0 15:28 pts/1 00:00:00 /data/highgo/4.7.7/bin/postgres

highgo 1877 1876 0 15:28 ? 00:00:00 postgres: logger process

highgo 1879 1876 0 15:28 ? 00:00:00 postgres: checkpointer process

highgo 1880 1876 0 15:28 ? 00:00:00 postgres: writer process

highgo 1881 1876 0 15:28 ? 00:00:00 postgres: wal writer process

highgo 1882 1876 0 15:28 ? 00:00:00 postgres: autovacuum launcher process

highgo 1883 1876 0 15:28 ? 00:00:00 postgres: archiver process last was 000000010000000000000077

highgo 1884 1876 0 15:28 ? 00:00:00 postgres: stats collector process

highgo 4347 1876 1 18:19 ? 00:00:11 postgres: htest htest [local] idle

highgo 4746 1876 7 18:32 ? 00:00:00 postgres: htest htest localhost(43298) idle in transaction

highgo 4752 1876 0 18:32 ? 00:00:00 postgres: htest htest localhost(43300) idle in transaction

highgo 4753 1876 0 18:32 ? 00:00:00 postgres: htest htest localhost(43302) idle in transaction

highgo 4754 1876 39 18:32 ? 00:00:00 postgres: htest htest localhost(43304) COPY

highgo 4755 1876 0 18:32 ? 00:00:00 postgres: htest htest localhost(43306) idle in transaction

highgo 4756 1876 0 18:32 ? 00:00:00 postgres: htest htest localhost(43308) idle in transaction

2.3 并行導出數據庫中表狀態

[highgo@tqz baktest]$ ps -ef | grep postgres

highgo 1876 1 0 15:28 pts/1 00:00:00 /data/highgo/4.7.7/bin/postgres

highgo 1877 1876 0 15:28 ? 00:00:00 postgres: logger process

highgo 1879 1876 0 15:28 ? 00:00:00 postgres: checkpointer process

highgo 1880 1876 0 15:28 ? 00:00:00 postgres: writer process

highgo 1881 1876 0 15:28 ? 00:00:00 postgres: wal writer process

highgo 1882 1876 0 15:28 ? 00:00:00 postgres: autovacuum launcher process

highgo 1883 1876 0 15:28 ? 00:00:00 postgres: archiver process last was 000000010000000000000077

highgo 1884 1876 0 15:28 ? 00:00:00 postgres: stats collector process

highgo 4347 1876 1 18:19 ? 00:00:11 postgres: htest htest [local] idle

highgo 4794 1876 1 18:34 ? 00:00:00 postgres: htest htest localhost(43316) idle in transaction

highgo 4800 1876 0 18:34 ? 00:00:00 postgres: htest htest localhost(43318) idle in transaction

highgo 4801 1876 0 18:34 ? 00:00:00 postgres: htest htest localhost(43320) idle in transaction

highgo 4802 1876 0 18:34 ? 00:00:00 postgres: htest htest localhost(43322) idle in transaction

highgo 4803 1876 26 18:34 ? 00:00:02 postgres: htest htest localhost(43324) COPY

highgo 4804 1876 0 18:34 ? 00:00:00 postgres: htest htest localhost(43326) idle in transaction

2.4 并行導出后狀態

[highgo@tqz baktest]$ ps -ef | grep postgres

highgo 1876 1 0 15:28 pts/1 00:00:00 /data/highgo/4.7.7/bin/postgres

highgo 1877 1876 0 15:28 ? 00:00:00 postgres: logger process

highgo 1879 1876 0 15:28 ? 00:00:00 postgres: checkpointer process

highgo 1880 1876 0 15:28 ? 00:00:00 postgres: writer process

highgo 1881 1876 0 15:28 ? 00:00:00 postgres: wal writer process

highgo 1882 1876 0 15:28 ? 00:00:00 postgres: autovacuum launcher process

highgo 1883 1876 0 15:28 ? 00:00:00 postgres: archiver process last was 000000010000000000000077

highgo 1884 1876 0 15:28 ? 00:00:00 postgres: stats collector process

highgo 4347 1876 1 18:19 ? 00:00:11 postgres: htest htest [local] idle

2.5 導出后的文件

[highgo@tqz baktest]$ ll test1

total 26660

-rw-rw-r–. 1 highgo highgo 72 Jun 29 18:32 3584.dat.gz

-rw-rw-r–. 1 highgo highgo 78 Jun 29 18:32 3585.dat.gz

-rw-rw-r–. 1 highgo highgo 31 Jun 29 18:32 3586.dat.gz

-rw-rw-r–. 1 highgo highgo 32 Jun 29 18:32 3587.dat.gz

-rw-rw-r–. 1 highgo highgo 32 Jun 29 18:32 3588.dat.gz

-rw-rw-r–. 1 highgo highgo 27269433 Jun 29 18:32 3589.dat.gz

-rw-rw-r–. 1 highgo highgo 4953 Jun 29 18:32 toc.dat

[highgo@tqz baktest]$ ll test2

total 26636

-rw-rw-r–. 1 highgo highgo 27269433 Jun 29 18:34 3578.dat.gz

-rw-rw-r–. 1 highgo highgo 973 Jun 29 18:34 toc.dat

3 并行導入

3.1 創建數據庫

create database htest_c;

3.2 使用超級用戶關閉autovacuum，加速導入(可選)

alter system set autovacuum=off;

select pg_reload_conf();

3.3 導入數據

pg_restore -d htest_c -F d -j 5 -h localhost -p 5866 -U htest /home/highgo/baktest/test1

[highgo@tqz baktest]$ ps -ef | grep postgres

highgo 1876 1 0 15:28 pts/1 00:00:00 /data/highgo/4.7.7/bin/postgres

highgo 1877 1876 0 15:28 ? 00:00:00 postgres: logger process

highgo 1879 1876 0 15:28 ? 00:00:00 postgres: checkpointer process

highgo 1880 1876 0 15:28 ? 00:00:00 postgres: writer process

highgo 1881 1876 0 15:28 ? 00:00:00 postgres: wal writer process

highgo 1882 1876 0 15:28 ? 00:00:00 postgres: autovacuum launcher process

highgo 1883 1876 0 15:28 ? 00:00:00 postgres: archiver process last was 00000001000000000000007A

highgo 1884 1876 0 15:28 ? 00:00:00 postgres: stats collector process

highgo 4347 1876 0 18:19 ? 00:00:11 postgres: htest htest [local] idle

highgo 5029 1876 0 18:43 ? 00:00:00 postgres: htest htest_c localhost(43332) idle

highgo 5030 1876 0 18:43 ? 00:00:00 postgres: htest htest_c localhost(43334) idle

highgo 5031 1876 0 18:43 ? 00:00:00 postgres: htest htest_c localhost(43336) idle

highgo 5032 1876 0 18:43 ? 00:00:00 postgres: htest htest_c localhost(43338) idle

highgo 5033 1876 88 18:43 ? 00:00:03 postgres: htest htest_c localhost(43340) COPY

3.4 驗證數據

htest:

htest=# select count(*) from bxtest where a = ‘16097’;

count

---

24

(1 row)

htest_c:

htest=# \c htest_c htest

PSQL: Release 4.7.7

Connected to:

HighGo Database V4.7 Enterprise Edition Release 4.7.7 - 64-bit Production

You are now connected to database “htest_c” as user “htest”.

htest_c=# select count(*) from bxtest where a = ‘16097’;

count

---

24

(1 row)

3.5 使用超級用戶開啟autovacuum（如果做了3.2)

alter system set autovacuum=on;

select pg_reload_conf();

4 注意事項

1、 通過max_connections參數和查詢當前數據庫連接數占用數確保有足夠的連接數可供并行進程導出。

2、 并行導出參數必須和 -F d 參數結合使用，因為這是唯一一種讓多個進程能在同一時間寫其數據的輸出格式。

3、 pg_dump將打開njobs + 1 個到該數據庫的連接。

更多詳細信息請登錄【瀚高技術支持平臺】查看https://support.highgo.com/#/index/docContent/f11a7650005a21ba

總結

以上是生活随笔為你收集整理的瀚高数据库并行导入导出的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。