本文档主要用于展示瀚高数据库并行导入导出步骤和注意事项。
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