pg_dump 的十六般变化（下篇）

已于 2022-05-21 00:08:55 修改
阅读量225 收藏
点赞数
分类专栏： PostgreSQL Linux Shell 文章标签： 数据库 postgresql linux bash
于 2022-05-20 23:57:41 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/dbadaily/article/details/124892542
版权
PostgreSQL 同时被 3 个专栏收录
20 篇文章 3 订阅
订阅专栏
Linux
4 篇文章 0 订阅
订阅专栏
Shell
4 篇文章 0 订阅
订阅专栏

Easy Dump

在这里插入图片描述

DBS

In DBS mode, users will not be dumped by default. You can add option -u or --users to always dump users.

Dump all databases

In some cases you need to dump the users separately and need all the databases.

  1. The instance size is quite small

  2. You have got enough time to wait for the hours long dump

PostgreSQL pg_dump command

Dump all databases one by one manually.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Easy Dump command

bash pg_dump.sh -v -M DBS

[2022-05-15 21:04:16 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416.log
[2022-05-15 21:04:16 TRACE] [parse_args] lv_valid_args: ' -v -M 'DBS' --'
[2022-05-15 21:04:16 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:04:16 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:04:16 DEBUG] [parse_args] DUMP_MODE=DBS
[2022-05-15 21:04:16 INFO] DUMP_MODE=DBS PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 21:04:16 TRACE] lv_case='DBS case 1 - Dump all databases'
[2022-05-15 21:04:16 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_dumpall.log
[2022-05-15 21:04:16 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_restore.log
[2022-05-15 21:04:16 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:04:16 INFO] DATABASES to be dumped: alvindb
dbadaily
postgres
testdb
[2022-05-15 21:04:16 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_create_alvindb.sql
[2022-05-15 21:04:16 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_dump_alvindb.log
[2022-05-15 21:04:16 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_restore_alvindb.log
[2022-05-15 21:04:16 INFO] Database alvindb created
[2022-05-15 21:04:16 INFO] Database alvindb is being dumped. pid = 121991

real	0m0.092s
user	0m0.008s
sys	0m0.005s
[2022-05-15 21:04:16 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 1 - Dump all databases. Database alvindb dump finished.
[2022-05-15 21:04:19 INFO] Database dbadaily CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_create_dbadaily.sql
[2022-05-15 21:04:19 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_dump_dbadaily.log
[2022-05-15 21:04:19 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_restore_dbadaily.log
[2022-05-15 21:04:19 INFO] Database dbadaily created
[2022-05-15 21:04:19 INFO] Database dbadaily is being dumped. pid = 121991

real	0m0.083s
user	0m0.008s
sys	0m0.005s
[2022-05-15 21:04:19 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 1 - Dump all databases. Database dbadaily dump finished.
[2022-05-15 21:04:22 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_dump_postgres.log
[2022-05-15 21:04:22 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_restore_postgres.log
[2022-05-15 21:04:22 INFO] Database postgres created
[2022-05-15 21:04:22 INFO] Database postgres is being dumped. pid = 121991

real	0m0.061s
user	0m0.007s
sys	0m0.002s
[2022-05-15 21:04:22 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 1 - Dump all databases. Database postgres dump finished.
[2022-05-15 21:04:25 INFO] Database testdb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_create_testdb.sql
[2022-05-15 21:04:25 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_dump_testdb.log
[2022-05-15 21:04:25 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210416_restore_testdb.log
[2022-05-15 21:04:25 INFO] Database testdb created
[2022-05-15 21:04:25 INFO] Database testdb is being dumped. pid = 121991

real	0m0.084s
user	0m0.008s
sys	0m0.004s
[2022-05-15 21:04:25 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 1 - Dump all databases. Database testdb dump finished.

Dump specified databases

In some cases you need to dump the users separately and need only some of the databases.

  1. You are just separating one or more databases from a huge instance on which there are multiple databases
  2. You just don’t need other databases

PostgreSQL pg_dump command

Dump specified databases one by one manually.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Easy Dump command

bash pg_dump.sh -v -M DBS -D "alvindb dbadaily"

[2022-05-15 21:05:10 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510.log
[2022-05-15 21:05:10 TRACE] [parse_args] lv_valid_args: ' -v -M 'DBS' -D 'alvindb dbadaily' --'
[2022-05-15 21:05:10 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:05:10 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:05:10 DEBUG] [parse_args] DUMP_MODE=DBS
[2022-05-15 21:05:10 DEBUG] [parse_args] DATABASES=alvindb dbadaily
[2022-05-15 21:05:10 INFO] DUMP_MODE=DBS PARALLEL_IND= DATABASES=alvindb dbadaily DBTABLES=
[2022-05-15 21:05:10 TRACE] lv_case='DBS case 2 - Dump specified databases'
[2022-05-15 21:05:10 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_dumpall.log
[2022-05-15 21:05:10 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_restore.log
[2022-05-15 21:05:10 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:05:10 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_create_alvindb.sql
[2022-05-15 21:05:10 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_dump_alvindb.log
[2022-05-15 21:05:10 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_restore_alvindb.log
[2022-05-15 21:05:10 INFO] Database alvindb created
[2022-05-15 21:05:10 INFO] Database alvindb is being dumped. pid = 122647

real	0m0.081s
user	0m0.008s
sys	0m0.006s
[2022-05-15 21:05:11 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 2 - Dump specified databases. Database alvindb dump finished.
[2022-05-15 21:05:14 INFO] Database dbadaily CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_create_dbadaily.sql
[2022-05-15 21:05:14 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_dump_dbadaily.log
[2022-05-15 21:05:14 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210510_restore_dbadaily.log
[2022-05-15 21:05:14 INFO] Database dbadaily created
[2022-05-15 21:05:14 INFO] Database dbadaily is being dumped. pid = 122647

real	0m0.074s
user	0m0.006s
sys	0m0.006s
[2022-05-15 21:05:14 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 2 - Dump specified databases. Database dbadaily dump finished.

Dump all databases in parallel

In some cases you need to dump the users separately and all the databases, but some databases are quite large.

  1. PostgreSQL instance to be upgraded contains one or more databases with large size
  2. You need to minimize the dump time to reduce the affect on the application

PostgreSQL pg_dump command

Dump all databases one by one in parallel manually.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}" &

Easy Dump command

bash pg_dump.sh -v -M DBS -L -t 3

[2022-05-15 21:05:53 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553.log
[2022-05-15 21:05:53 TRACE] [parse_args] lv_valid_args: ' -v -M 'DBS' -L -t '3' --'
[2022-05-15 21:05:53 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:05:53 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:05:53 DEBUG] [parse_args] DUMP_MODE=DBS
[2022-05-15 21:05:53 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:05:53 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:05:53 INFO] DUMP_MODE=DBS PARALLEL_IND=Y DATABASES= DBTABLES=
[2022-05-15 21:05:53 TRACE] lv_case='DBS case 3 - Dump all databases in parallel'
[2022-05-15 21:05:53 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_dumpall.log
[2022-05-15 21:05:53 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_restore.log
[2022-05-15 21:05:53 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:05:53 INFO] DATABASES to be dumped: alvindb
dbadaily
postgres
testdb
[2022-05-15 21:05:53 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_create_alvindb.sql
[2022-05-15 21:05:53 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_dump_alvindb.log
[2022-05-15 21:05:53 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_restore_alvindb.log
[2022-05-15 21:05:53 INFO] Database alvindb created
[2022-05-15 21:05:53 INFO] Database alvindb is being dumped in parallel. pid = 123557

real	0m0.091s
user	0m0.010s
sys	0m0.003s
[2022-05-15 21:05:53 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 3 - Dump all databases in parallel. Database alvindb dump finished.
[2022-05-15 21:05:56 INFO] Database dbadaily CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_create_dbadaily.sql
[2022-05-15 21:05:56 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_dump_dbadaily.log
[2022-05-15 21:05:56 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_restore_dbadaily.log
[2022-05-15 21:05:56 INFO] Database dbadaily created
[2022-05-15 21:05:56 INFO] Database dbadaily is being dumped in parallel. pid = 123619

real	0m0.081s
user	0m0.010s
sys	0m0.002s
[2022-05-15 21:05:56 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 3 - Dump all databases in parallel. Database dbadaily dump finished.
[2022-05-15 21:05:59 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_dump_postgres.log
[2022-05-15 21:05:59 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_restore_postgres.log
[2022-05-15 21:05:59 INFO] Database postgres created
[2022-05-15 21:05:59 INFO] Database postgres is being dumped in parallel. pid = 123672

real	0m0.060s
user	0m0.004s
sys	0m0.005s
[2022-05-15 21:05:59 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 3 - Dump all databases in parallel. Database postgres dump finished.
[2022-05-15 21:06:02 INFO] Database testdb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_create_testdb.sql
[2022-05-15 21:06:02 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_dump_testdb.log
[2022-05-15 21:06:02 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210553_restore_testdb.log
[2022-05-15 21:06:02 INFO] Database testdb created
[2022-05-15 21:06:02 INFO] Database testdb is being dumped in parallel. pid = 123737

real	0m0.081s
user	0m0.009s
sys	0m0.003s
[2022-05-15 21:06:02 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 3 - Dump all databases in parallel. Database testdb dump finished.

Dump specified databases in parallel

In some cases you need to dump the users separately but not all the databases, and some databases are quite large.

  1. PostgreSQL instance to be upgraded contains one or more databases with large size
  2. You need to minimize the dump time to reduce the affect on the application

PostgreSQL pg_dump command

Dump specified databases one by one in parallel manually.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}" &

Easy Dump command

bash pg_dump.sh -v -M DBS -D "alvindb dbadaily" -L -t 3

[2022-05-15 21:06:51 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651.log
[2022-05-15 21:06:51 TRACE] [parse_args] lv_valid_args: ' -v -M 'DBS' -D 'alvindb dbadaily' -L -t '3' --'
[2022-05-15 21:06:51 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:06:51 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:06:51 DEBUG] [parse_args] DUMP_MODE=DBS
[2022-05-15 21:06:52 DEBUG] [parse_args] DATABASES=alvindb dbadaily
[2022-05-15 21:06:52 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:06:52 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:06:52 INFO] DUMP_MODE=DBS PARALLEL_IND=Y DATABASES=alvindb dbadaily DBTABLES=
[2022-05-15 21:06:52 TRACE] lv_case='DBS case 4 - Dump specified databases in parallel'
[2022-05-15 21:06:52 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_dumpall.log
[2022-05-15 21:06:52 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_restore.log
[2022-05-15 21:06:52 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:06:52 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_create_alvindb.sql
[2022-05-15 21:06:52 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_dump_alvindb.log
[2022-05-15 21:06:52 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_restore_alvindb.log
[2022-05-15 21:06:52 INFO] Database alvindb created
[2022-05-15 21:06:52 INFO] Database alvindb is being dumped in parallel. pid = 124191

real	0m0.111s
user	0m0.005s
sys	0m0.008s
[2022-05-15 21:06:52 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 4 - Dump specified databases in parallel. Database alvindb dump finished.
[2022-05-15 21:06:55 INFO] Database dbadaily CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_create_dbadaily.sql
[2022-05-15 21:06:55 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_dump_dbadaily.log
[2022-05-15 21:06:55 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210651_restore_dbadaily.log
[2022-05-15 21:06:55 INFO] Database dbadaily created
[2022-05-15 21:06:55 INFO] Database dbadaily is being dumped in parallel. pid = 124253

real	0m0.075s
user	0m0.007s
sys	0m0.005s
[2022-05-15 21:06:55 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DBS case 4 - Dump specified databases in parallel. Database dbadaily dump finished.

DB

In DB mode, users will not be dumped by default. You can add option -u or --users to always dump users.

Dump all tables

This is one of the frequently used modes.

In some cases you need to dump the users separately and then dump the database.

  1. The database size is quite small

  2. You’ve got enough time to wait for the hours long dump

  3. You are separating one database from a huge instance on which there are multiple databases or you just don’t need other databases

PostgreSQL pg_dump command

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Easy Dump command

bash pg_dump.sh -v -M DB -d alvindb

[2022-05-15 21:07:46 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746.log
[2022-05-15 21:07:46 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' --'
[2022-05-15 21:07:46 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:07:46 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:07:46 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:07:46 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:07:46 INFO] DUMP_MODE=DB PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 21:07:46 TRACE] lv_case='DB case 1 - Dump all tables'
[2022-05-15 21:07:46 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746_dumpall.log
[2022-05-15 21:07:46 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746_restore.log
[2022-05-15 21:07:46 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:07:46 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746_create_alvindb.sql
[2022-05-15 21:07:46 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746_dump_alvindb.log
[2022-05-15 21:07:46 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_210746_restore_alvindb.log
[2022-05-15 21:07:46 INFO] Database alvindb created
[2022-05-15 21:07:46 INFO] Database alvindb is being dumped. pid = 124384

real	0m0.088s
user	0m0.008s
sys	0m0.006s
[2022-05-15 21:07:47 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 1 - Dump all tables. Database alvindb dump finished.

Dump all tables with exclusion

In some cases you need to dump a database and exclude some tables.

  1. Some tables are not related or belong to other projects
  2. There are some testing tables
  3. Some logging tables are quite large and the data is not needed

PostgreSQL pg_dump command

You can use one -T option to specify table pattern. Please note that the table pattern is not regular expression and in rare cases like same table name exists in in various schema it might not work as expected.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public|alvin.tb_test|tb_alvindb_test" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

You can also use multiple -T options to specify all tables to be excluded.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public.tb_test" -T "alvin.tb_alvindb_test" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Easy Dump command

It uses table pattern. Please note that in rare cases like same table name exists in in various schema it might not work as expected.

Always run in check mode( -C ) first to check matching tables. In rare cases, please use PostgreSQL pg_dump command.

bash pg_dump.sh -v -M DB -d alvindb -E "public.tb_test alvin.tb_alvindb_test"

[2022-05-15 21:12:33 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233.log
[2022-05-15 21:12:33 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -E 'public.tb_test alvin.tb_alvindb_test' --'
[2022-05-15 21:12:33 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:12:33 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:12:33 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:12:33 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:12:33 DEBUG] [parse_args] DBTABLES_EXCLUDE=public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:12:33 INFO] DUMP_MODE=DB PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 21:12:33 TRACE] lv_case='DB case 2 - Dump all tables with exclusion'
[2022-05-15 21:12:33 DEBUG] lv_tables= public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:12:33 DEBUG] lv_tables_exp=public|alvin.tb_test|tb_alvindb_test
[2022-05-15 21:12:33 INFO] Matching tables:
                       List of relations
 Schema |      Name       | Type  | Owner | Size  | Description
--------+-----------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_test | table | alvin | 32 kB |
 public | tb_test         | table | alvin | 32 kB |
(2 rows)

[2022-05-15 21:12:33 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233_dumpall.log
[2022-05-15 21:12:33 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233_restore.log
[2022-05-15 21:12:33 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:12:33 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233_create_alvindb.sql
[2022-05-15 21:12:33 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233_dump_alvindb.log
[2022-05-15 21:12:33 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211233_restore_alvindb.log
[2022-05-15 21:12:33 INFO] Database alvindb created
[2022-05-15 21:12:33 INFO] Database alvindb is being dumped. pid = 125335

real	0m0.112s
user	0m0.009s
sys	0m0.003s
[2022-05-15 21:12:33 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 2 - Dump all tables with exclusion. Database alvindb dump with exclusion finished.

Dump all tables, specified tables are dumped in parallel

This is one of the frequently used modes.

In some cases you need to dump a database and dump some of the tables in parallel.

  1. PostgreSQL database to be dumped contains one or more huge tables or time consuming tables
  2. You need to minimize the dump time to reduce the affect on the application

PostgreSQL pg_dump command

Firstly dump the database with exclusion.

You can use one -T option to specify table pattern. Please note that the table pattern is not regular expression and in rare cases like same table name exists in in various schema it might not work as expected.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public|alvin.tb_vacuum|tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

You can also use multiple -T options to specify all tables to be excluded.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public.tb_vacuum" -T "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Then dump specified tables in parallel.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "public.tb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

Easy Dump command

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -L -t 3

[2022-05-15 21:13:26 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326.log
[2022-05-15 21:13:26 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' -L -t '3' --'
[2022-05-15 21:13:26 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:13:26 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:13:26 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:13:26 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:13:26 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:13:26 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:13:26 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:13:26 INFO] DUMP_MODE=DB PARALLEL_IND=Y DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:13:26 TRACE] lv_case='DB case 7 - Dump all tables, specified tables are dumped in parallel'
[2022-05-15 21:13:26 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:13:26 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum
[2022-05-15 21:13:26 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(2 rows)

[2022-05-15 21:13:26 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_dumpall.log
[2022-05-15 21:13:26 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_restore.log
[2022-05-15 21:13:26 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:13:26 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_create_alvindb.sql
[2022-05-15 21:13:26 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_dump_alvindb.log
[2022-05-15 21:13:26 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_restore_alvindb.log
[2022-05-15 21:13:26 INFO] Database alvindb created
[2022-05-15 21:13:26 INFO] Database alvindb is being dumped in parallel. pid = 126142

real	0m0.089s
user	0m0.006s
sys	0m0.006s
[2022-05-15 21:13:26 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 7 - Dump all tables, specified tables are dumped in parallel. Database alvindb dump with exclusion finished.
[2022-05-15 21:13:29 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_dump_public.tb_vacuum.log
[2022-05-15 21:13:29 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_restore_public.tb_vacuum.log
[2022-05-15 21:13:29 INFO] Table public.tb_vacuum is being dumped in parallel. pid = 126203

real	0m0.074s
user	0m0.008s
sys	0m0.003s
[2022-05-15 21:13:29 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 7 - Dump all tables, specified tables are dumped in parallel. Table public.tb_vacuum dump finished.
[2022-05-15 21:13:32 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_dump_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:13:32 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211326_restore_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:13:32 INFO] Table alvin.tb_alvindb_vacuum is being dumped in parallel. pid = 126246

real	0m0.072s
user	0m0.006s
sys	0m0.004s
[2022-05-15 21:13:32 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 7 - Dump all tables, specified tables are dumped in parallel. Table alvin.tb_alvindb_vacuum dump finished.

Dump all tables with exclusion, specified tables are dumped in parallel

In some cases you need to dump a database and dump some of the tables in parallel and exclude testing or logging or irrelevant tables.

  1. PostgreSQL database to be dumped contains one or more huge tables or time consuming tables
  2. You need to minimize the dump time to reduce the affect on the application

PostgreSQL pg_dump command

Firstly dump the database with exclusion.

You can use one -T option to specify table pattern. Please note that the table pattern is not regular expression and in rare cases like same table name exists in in various schema it might not work as expected.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public|alvin.tb_vacuum|tb_alvindb_vacuum|tb_test|tb_alvindb_test" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

You can also use multiple -T options to specify all tables to be excluded.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public.tb_test" -T "alvin.tb_alvindb_test" -T "public.tb_vacuum" -T "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Then dump specified tables in parallel.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "public.tb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

Easy Dump command

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -E "public.tb_test alvin.tb_alvindb_test" -L -t 3

[2022-05-15 21:13:57 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356.log
[2022-05-15 21:13:57 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' -E 'public.tb_test alvin.tb_alvindb_test' -L -t '3' --'
[2022-05-15 21:13:57 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:13:57 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:13:57 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:13:57 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:13:57 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:13:57 DEBUG] [parse_args] DBTABLES_EXCLUDE=public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:13:57 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:13:57 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:13:57 INFO] DUMP_MODE=DB PARALLEL_IND=Y DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:13:57 TRACE] lv_case='DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel'
[2022-05-15 21:13:57 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:13:57 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum|tb_test|tb_alvindb_test
[2022-05-15 21:13:57 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_test   | table | alvin | 32 kB |
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_test           | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(4 rows)

[2022-05-15 21:13:57 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_dumpall.log
[2022-05-15 21:13:57 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_restore.log
[2022-05-15 21:13:57 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:13:57 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_create_alvindb.sql
[2022-05-15 21:13:57 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_dump_alvindb.log
[2022-05-15 21:13:57 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_restore_alvindb.log
[2022-05-15 21:13:57 INFO] Database alvindb created
[2022-05-15 21:13:57 INFO] Database alvindb is being dumped in parallel. pid = 126694

real	0m0.080s
user	0m0.009s
sys	0m0.003s
[2022-05-15 21:13:57 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Database alvindb dump with exclusion finished.
[2022-05-15 21:14:00 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_dump_public.tb_vacuum.log
[2022-05-15 21:14:00 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_restore_public.tb_vacuum.log
[2022-05-15 21:14:00 INFO] Table public.tb_vacuum is being dumped in parallel. pid = 126737

real	0m0.075s
user	0m0.006s
sys	0m0.004s
[2022-05-15 21:14:00 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Table public.tb_vacuum dump finished.
[2022-05-15 21:14:03 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_dump_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:14:03 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211356_restore_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:14:03 INFO] Table alvin.tb_alvindb_vacuum is being dumped in parallel. pid = 126783

real	0m0.071s
user	0m0.006s
sys	0m0.004s
[2022-05-15 21:14:03 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Table alvin.tb_alvindb_vacuum dump finished.

TABLE

Dump specified tables one by one

In some cases you need to dump only some tables of the database.

  1. Dump of some tables failed and you need to dump them again
  2. You are achiving tables regularly

PostgreSQL pg_dump command

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "public.tb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}"

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}"

Easy Dump command

bash pg_dump.sh -v -M TABLE -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum"

[2022-05-15 21:14:47 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447.log
[2022-05-15 21:14:47 TRACE] [parse_args] lv_valid_args: ' -v -M 'TABLE' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' --'
[2022-05-15 21:14:47 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:14:47 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:14:47 DEBUG] [parse_args] DUMP_MODE=TABLE
[2022-05-15 21:14:47 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:14:47 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:14:47 INFO] DUMP_MODE=TABLE PARALLEL_IND= DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:14:47 TRACE] lv_case='TABLE case 1 - Dump specified tables one by one'
[2022-05-15 21:14:47 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:14:47 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum
[2022-05-15 21:14:47 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(2 rows)

[2022-05-15 21:14:47 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_dumpall.log
[2022-05-15 21:14:47 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_restore.log
[2022-05-15 21:14:47 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:14:47 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_dump_public.tb_vacuum.log
[2022-05-15 21:14:47 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_restore_public.tb_vacuum.log
[2022-05-15 21:14:47 INFO] Table public.tb_vacuum is being dumped. pid = 126909

real	0m0.075s
user	0m0.007s
sys	0m0.005s
[2022-05-15 21:14:48 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: TABLE case 1 - Dump specified tables one by one. Table public.tb_vacuum dump finished.
[2022-05-15 21:14:51 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_dump_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:14:51 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211447_restore_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:14:51 INFO] Table alvin.tb_alvindb_vacuum is being dumped. pid = 126909

real	0m0.066s
user	0m0.008s
sys	0m0.004s
[2022-05-15 21:14:51 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: TABLE case 1 - Dump specified tables one by one. Table alvin.tb_alvindb_vacuum dump finished.

Dump specified tables in parallel

In some cases you need to dump only some tables of the database.

  1. Dump of some tables failed and you need to dump them again in parallel to save time
  2. You are achiving tables regularly, and need to speed up the process

PostgreSQL pg_dump command

Then dump specified tables in parallel.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "public.tb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

Easy Dump command

bash pg_dump.sh -v -M TABLE -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -L -t 3

[2022-05-15 21:15:45 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545.log
[2022-05-15 21:15:45 TRACE] [parse_args] lv_valid_args: ' -v -M 'TABLE' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' -L -t '3' --'
[2022-05-15 21:15:45 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:15:45 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:15:45 DEBUG] [parse_args] DUMP_MODE=TABLE
[2022-05-15 21:15:45 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:15:45 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:15:45 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:15:45 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:15:45 INFO] DUMP_MODE=TABLE PARALLEL_IND=Y DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:15:45 TRACE] lv_case='TABLE case 2 - Dump specified tables in parallel'
[2022-05-15 21:15:45 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:15:45 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum
[2022-05-15 21:15:45 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(2 rows)

[2022-05-15 21:15:45 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_dumpall.log
[2022-05-15 21:15:45 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_restore.log
[2022-05-15 21:15:45 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:15:45 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_dump_public.tb_vacuum.log
[2022-05-15 21:15:45 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_restore_public.tb_vacuum.log
[2022-05-15 21:15:45 INFO] Table public.tb_vacuum is being dumped in parallel. pid = 127742

real	0m0.067s
user	0m0.007s
sys	0m0.004s
[2022-05-15 21:15:45 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: TABLE case 2 - Dump specified tables in parallel. Table public.tb_vacuum dump finished.
[2022-05-15 21:15:48 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_dump_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:15:48 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211545_restore_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:15:48 INFO] Table alvin.tb_alvindb_vacuum is being dumped in parallel. pid = 127785

real	0m0.075s
user	0m0.008s
sys	0m0.003s
[2022-05-15 21:15:48 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: TABLE case 2 - Dump specified tables in parallel. Table alvin.tb_alvindb_vacuum dump finished.

Always dump users

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -E "public.tb_test alvin.tb_alvindb_test" -L -t 3 -u

[2022-05-15 21:22:20 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220.log
[2022-05-15 21:22:20 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' -E 'public.tb_test alvin.tb_alvindb_test' -L -t '3' -u --'
[2022-05-15 21:22:20 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:22:20 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:22:20 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:22:20 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:22:20 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:22:20 DEBUG] [parse_args] DBTABLES_EXCLUDE=public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:22:20 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:22:20 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:22:20 DEBUG] [parse_args] DUMP_ALL_USERS=Y
[2022-05-15 21:22:20 INFO] DUMP_MODE=DB PARALLEL_IND=Y DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:22:20 TRACE] lv_case='DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel'
[2022-05-15 21:22:20 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:22:20 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum|tb_test|tb_alvindb_test
[2022-05-15 21:22:20 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_test   | table | alvin | 32 kB |
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_test           | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(4 rows)

[2022-05-15 21:22:20 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_dumpall.log
[2022-05-15 21:22:20 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_restore.log
[2022-05-15 21:22:20 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:22:20 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Users dumped.
[2022-05-15 21:22:20 INFO] Database alvindb CREATE sql file: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_create_alvindb.sql
[2022-05-15 21:22:20 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_dump_alvindb.log
[2022-05-15 21:22:20 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_restore_alvindb.log
[2022-05-15 21:22:20 INFO] Database alvindb created
[2022-05-15 21:22:20 INFO] Database alvindb is being dumped in parallel. pid = 130090

real	0m0.083s
user	0m0.008s
sys	0m0.004s
[2022-05-15 21:22:20 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Database alvindb dump with exclusion finished.
[2022-05-15 21:22:23 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_dump_public.tb_vacuum.log
[2022-05-15 21:22:23 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_restore_public.tb_vacuum.log
[2022-05-15 21:22:23 INFO] Table public.tb_vacuum is being dumped in parallel. pid = 130135

real	0m0.076s
user	0m0.006s
sys	0m0.005s
[2022-05-15 21:22:23 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Table public.tb_vacuum dump finished.
[2022-05-15 21:22:26 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_dump_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:22:26 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212220_restore_alvin.tb_alvindb_vacuum.log
[2022-05-15 21:22:26 INFO] Table alvin.tb_alvindb_vacuum is being dumped in parallel. pid = 130192

real	0m0.076s
user	0m0.007s
sys	0m0.004s
[2022-05-15 21:22:26 TRACE] Message sent: [SUCCESS] alvindb2.dbadaily.com: DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel. Table alvin.tb_alvindb_vacuum dump finished.

Check mode

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -E "public.tb_test alvin.tb_alvindb_test" -L -t 3 -u -C

[2022-05-15 21:22:29 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212229.log
[2022-05-15 21:22:29 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' -E 'public.tb_test alvin.tb_alvindb_test' -L -t '3' -u -C --'
[2022-05-15 21:22:29 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:22:29 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:22:29 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:22:29 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:22:29 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:22:29 DEBUG] [parse_args] DBTABLES_EXCLUDE=public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:22:29 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:22:29 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:22:29 DEBUG] [parse_args] DUMP_ALL_USERS=Y
[2022-05-15 21:22:29 DEBUG] [parse_args] CHECK_MODE=Y
[2022-05-15 21:22:29 INFO] DUMP_MODE=DB PARALLEL_IND=Y DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:22:29 TRACE] lv_case='DB case 8 - Dump all tables with exclusion, specified tables are dumped in parallel'
[2022-05-15 21:22:29 DEBUG] lv_tables=public.tb_vacuum alvin.tb_alvindb_vacuum public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:22:29 DEBUG] lv_tables_exp=public|alvin.tb_vacuum|tb_alvindb_vacuum|tb_test|tb_alvindb_test
[2022-05-15 21:22:29 INFO] Matching tables:
                        List of relations
 Schema |       Name        | Type  | Owner | Size  | Description
--------+-------------------+-------+-------+-------+-------------
 alvin  | tb_alvindb_test   | table | alvin | 32 kB |
 alvin  | tb_alvindb_vacuum | table | alvin | 32 kB |
 public | tb_test           | table | alvin | 32 kB |
 public | tb_vacuum         | table | alvin | 32 kB |
(4 rows)

[2022-05-15 21:22:29 INFO] lv_dump_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212229_dumpall.log
[2022-05-15 21:22:29 INFO] lv_restore_log: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212229_restore.log
[2022-05-15 21:22:29 INFO] schema file exists: /data/scripts/easybash/easydump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 21:22:29 INFO] CHECK MODE. Skip dumping users.
[2022-05-15 21:22:29 INFO] CHECK MODE. Skip dumping databases.
[2022-05-15 21:22:29 INFO] CHECK MODE. Skip dumping tables.

Failure notificatons

Easy Dump kindly sends detailed messages when failed so that you are informed timely to take actions.

Here list some examples.

password authentication failed

Below show the error messages when given wrong password.

$ bash pg_dump.sh -v -M ALL -s
[2022-05-15 15:46:52 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154652.log
[2022-05-15 15:46:52 TRACE] [parse_args] lv_valid_args: ' -v -M 'ALL' -s --'
[2022-05-15 15:46:52 TRACE] [parse_args] config file found: /data/scripts/easybash/pg_dump/config.sh
[2022-05-15 15:46:52 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 15:46:52 DEBUG] [parse_args] DUMP_MODE=ALL
[2022-05-15 15:46:52 DEBUG] [parse_args] SCHEMA_ONLY=Y
[2022-05-15 15:46:52 INFO] DUMP_MODE=ALL PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 15:46:52 TRACE] lv_case='ALL case 1 - Dump all schema only'
[2022-05-15 15:46:52 INFO] lv_dump_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154652_dumpall.log
[2022-05-15 15:46:52 INFO] lv_restore_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154652_restore.log
[2022-05-15 15:46:52 INFO] schema file exists: /data/scripts/easybash/pg_dump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 15:46:52 INFO] All shema is being dumped. pid = 75553

real	0m0.011s
user	0m0.001s
sys	0m0.005s
[2022-05-15 15:46:52 WARNING] Dump failed. Please check errors in log file: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154652_dumpall.log
pg_dumpall: could not connect to database "template1": FATAL:  password authentication failed for user "postgres"
[2022-05-15 15:46:52 DEBUG] lv_error_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154652_errors.log
[2022-05-15 15:46:52 TRACE] Message sent: [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:pg_dumpall: could not connect to database template1: FATAL:  password authentication failed for user postgres
[2022-05-15 15:46:52 FATAL] [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:pg_dumpall: could not connect to database template1: FATAL:  password authentication failed for user postgres. ln_flag: 1

wrong ports

Below show the error messages when given wrong source port.

$ bash pg_dump.sh -v -M ALL -s
[2022-05-15 15:45:50 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154550.log
[2022-05-15 15:45:50 TRACE] [parse_args] lv_valid_args: ' -v -M 'ALL' -s --'
[2022-05-15 15:45:50 TRACE] [parse_args] config file found: /data/scripts/easybash/pg_dump/config.sh
[2022-05-15 15:45:50 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 15:45:50 DEBUG] [parse_args] DUMP_MODE=ALL
[2022-05-15 15:45:50 DEBUG] [parse_args] SCHEMA_ONLY=Y
[2022-05-15 15:45:50 INFO] DUMP_MODE=ALL PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 15:45:50 TRACE] lv_case='ALL case 1 - Dump all schema only'
[2022-05-15 15:45:50 INFO] lv_dump_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154550_dumpall.log
[2022-05-15 15:45:50 INFO] lv_restore_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154550_restore.log
[2022-05-15 15:45:50 WARNING] Dump failed. Please check errors in log file: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154550_dumpall.log
pg_dumpall: could not connect to database "template1": could not connect to server: Connection refused
	Is the server running on host "alvindb1.dbadaily.com" (10.20.20.1) and accepting
	TCP/IP connections on port 6432?
[2022-05-15 15:45:50 DEBUG] lv_error_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154550_errors.log
# source database server host or socket directory
[2022-05-15 15:45:50 TRACE] Message sent: [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:pg_dumpall: could not connect to database template1: could not connect to server: Connection refused
	Is the server running on host alvindb1.dbadaily.com (10.20.20.1) and accepting
	TCP/IP connections on port 6432?
[2022-05-15 15:45:50 FATAL] [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:pg_dumpall: could not connect to database template1: could not connect to server: Connection refused
	Is the server running on host alvindb1.dbadaily.com (10.20.20.1) and accepting
	TCP/IP connections on port 6432?. ln_flag: 1

Below show the error messages when given wrong target port

$ bash pg_dump.sh -v -M ALL -s
[2022-05-15 15:46:18 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154618.log
[2022-05-15 15:46:18 TRACE] [parse_args] lv_valid_args: ' -v -M 'ALL' -s --'
[2022-05-15 15:46:18 TRACE] [parse_args] config file found: /data/scripts/easybash/pg_dump/config.sh
[2022-05-15 15:46:18 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 15:46:18 DEBUG] [parse_args] DUMP_MODE=ALL
[2022-05-15 15:46:18 DEBUG] [parse_args] SCHEMA_ONLY=Y
[2022-05-15 15:46:18 INFO] DUMP_MODE=ALL PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 15:46:18 TRACE] lv_case='ALL case 1 - Dump all schema only'
[2022-05-15 15:46:18 INFO] lv_dump_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154618_dumpall.log
[2022-05-15 15:46:18 INFO] lv_restore_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154618_restore.log
[2022-05-15 15:46:18 INFO] schema file exists: /data/scripts/easybash/pg_dump/logs/202205/20220515/dumpall_schema_alvindb1.dbadaily.com_5432.sql
[2022-05-15 15:46:18 INFO] All shema is being dumped. pid = 75087

real	0m0.016s
user	0m0.002s
sys	0m0.002s
[2022-05-15 15:46:18 WARNING] Restore failed. Please check errors in log file: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154618_restore.log
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.6432"?
[2022-05-15 15:46:18 DEBUG] lv_error_log: /data/scripts/easybash/pg_dump/logs/202205/20220515/pg_dump_20220515_4_154618_errors.log
[2022-05-15 15:46:18 TRACE] Message sent: [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket /tmp/.s.PGSQL.6432?
[2022-05-15 15:46:18 FATAL] [FAILURE] alvindb2.dbadaily.com: ALL case 1 - Dump all schema only: ALL case 1 - Dump all schema only:psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket /tmp/.s.PGSQL.6432?. ln_flag: 2

Invalid option combination

In DB mode, -T or --tables is only intended to be used together with -L or --parallel.

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum"

[2022-05-15 21:15:51 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_211551.log
[2022-05-15 21:15:51 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -T 'public.tb_vacuum alvin.tb_alvindb_vacuum' --'
[2022-05-15 21:15:51 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:15:51 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:15:51 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:15:51 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:15:51 DEBUG] [parse_args] DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:15:51 INFO] DUMP_MODE=DB PARALLEL_IND= DATABASES= DBTABLES=public.tb_vacuum alvin.tb_alvindb_vacuum
[2022-05-15 21:15:51 FATAL] In DB mode, -T or --tables is only intended to be used together with -L or --parallel.

bash pg_dump.sh -v -M DB -d alvindb -E "public.tb_test alvin.tb_alvindb_test" -L -t 3

[2022-05-15 21:20:05 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212005.log
[2022-05-15 21:20:05 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' -d 'alvindb' -E 'public.tb_test alvin.tb_alvindb_test' -L -t '3' --'
[2022-05-15 21:20:05 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:20:05 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:20:05 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:20:05 DEBUG] [parse_args] DBNAME=alvindb
[2022-05-15 21:20:05 DEBUG] [parse_args] DBTABLES_EXCLUDE=public.tb_test alvin.tb_alvindb_test
[2022-05-15 21:20:05 DEBUG] [parse_args] PARALLEL_IND=Y
[2022-05-15 21:20:05 DEBUG] [parse_args] SLEEP_TIME=3
[2022-05-15 21:20:05 INFO] DUMP_MODE=DB PARALLEL_IND=Y DATABASES= DBTABLES=
[2022-05-15 21:20:05 FATAL] In DB mode, -T or --tables is only intended to be used together with -L or --parallel.

DBNAME should NOT be empty in DB mode.

bash pg_dump.sh -v -M TABLE

[2022-05-15 21:20:58 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212058.log
[2022-05-15 21:20:58 TRACE] [parse_args] lv_valid_args: ' -v -M 'DB' --'
[2022-05-15 21:20:58 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:20:58 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:20:58 DEBUG] [parse_args] DUMP_MODE=DB
[2022-05-15 21:20:58 INFO] DUMP_MODE=DB PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 21:20:58 FATAL] DBNAME should NOT be empty in DB mode.

DBTABLES should NOT be empty in TABLE mode.

[2022-05-15 21:21:51 TRACE] [parse_args] LOG_FILE: /data/scripts/easybash/easydump/logs/202205/20220515/pg_dump_20220515_2_212151.log
[2022-05-15 21:21:51 TRACE] [parse_args] lv_valid_args: ' -v -M 'TABLE' --'
[2022-05-15 21:21:51 TRACE] [parse_args] config file found: /data/scripts/easybash/easydump/config.sh
[2022-05-15 21:21:51 DEBUG] [parse_args] VERBOSE=Y
[2022-05-15 21:21:51 DEBUG] [parse_args] DUMP_MODE=TABLE
[2022-05-15 21:21:51 INFO] DUMP_MODE=TABLE PARALLEL_IND= DATABASES= DBTABLES=
[2022-05-15 21:21:51 FATAL] DBTABLES should NOT be empty in TABLE mode.

Conclusion

Easy Dump employs the advantages of Easy Bash and utilizes powerful and flexible pg_dump to provide an easier and friendly way to dump.

It provides 5 dump modes and 16 most useful cases and is more helpful in following two situations:

  1. PostgreSQL instance to be upgraded contains several databases with large size
  2. PostgreSQL instance to be upgraded has one database in which there are several huge tables or time consuming tables

原文链接：
https://blog.csdn.net/DBADaily/article/details/124892542
您浏览的网址与此链接不一致的话，则为未授权的转载，为了更好的阅读体验，建议阅读原文。

DBADaily
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
pg_dump相关命令参数记录
程序员野蛮成长
11-12 4128
pg_dump是用于备份一种PostgreSQL数据库的工具。即使数据库正在被并发使用，它也能创建一致的备份。pg_dump不阻塞其他用户访问数据库（读取或写入）。 pg_dump只转储单个数据库。要备份一个集簇中 对于所有数据库公共的全局对象（例如角色和表空间），应使用 pg_dumpall。 转储可以被输出到脚本或归档文件格式。脚本转储是包含 SQL 命令的纯文本文件，它们可以用来重构数据库到...
Postgresql在Windows中使用pg_dump实现数据库(指定表)的导出与导入
BADAO_LIUMANG_QIZHI的博客
12-15 2953
上面讲了在mysql中使用mysqldump备份的记录。当然如果两个数据中指定表的同步可以直接使用Navicat等软件进行。但是如果不能同时连接两个库进行数据同步，就需要先把一个库中某个表导出，再到另一个网络环境下执行导入。但是在postgresql中使用navicat直接导出sql文件并导入会有问题。PostgreSQL 提供了备份单个数据库的工具 pg_dump它支持三种文件格式：• plain，文本格式，输出一个纯文本形式的 SQL 脚本，默认值。还原时直接使用 psql 工具导入。
PostgreSql pg_dump用法
脑子进水养啥鱼？的博客
01-29 1万+
pg_dump [connection-option...] [option...] [dbname] pg_dump 连接选项参数如下: -h host 或 --host=host：指定运行服务器的主机名。 -p port 或 --port=port：监听端口号，默认端口为5432。 -U username 或 --username=username：指定要连接的用户名。 -w 或 --no-password：从不提示密码，该选项常用于后台脚本中。 -W 或 --password：强制 pg_dump
PostgreSQL逻辑备份pg_dump使用及其原理解析
Baiyi_destroyer的博客
08-14 3676
PostgreSQL逻辑备份pg_dump使用及其原理解析
线上版本升级 — — pg数据库备份
最新发布
weixin_45565886的博客
01-24 2215
线上版本升级 — — pg数据库备份
Greenplum使用pg_dump备份数据库1
08-04
本文将详细讲解如何使用`pg_dump`工具来备份和恢复Greenplum数据库。 ### 1. `pg_dump`工具介绍 `pg_dump`是PostgreSQL数据库系统提供的一个实用程序，用于创建数据库的转储（或备份）。在Greenplum中，这个工具...
pg_dump_anonymize
05-29
pg_dump_anonymize 匿名 pg_dump 数据并不总是直截了当的。 这个工具有帮助。 除了 ruby​​ 之外，它没有任何依赖项。 另一个好处是敏感数据不需要临时存储，因为这可以与 Unix 管道一起使用，并且数据作为 IO ...
postgresql之pg_dump命令详解 - wling_89的专栏 - 博客频道 - CSDN_NET.mht
05-13
PG导入导出命令详解
postgresql数据库--psql、pg_dump命令带密码执行sql语句
07-26
pg_dump: pg_dump -a -t tbl_test "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=postgres password=123456 dbname=postgres" > /userdir/tbl_data a 参数是表示只导出数据，其他的额外信息不需要，该参数也...
pg_dump-to-s3：自动将PostgreSQL备份转储并存档到Amazon S3
02-05
`pg_dump-to-s3`工具正是结合这两者，为PostgreSQL用户提供了一个自动化的方式来定期备份数据库并将其安全地存储在Amazon S3上。 ### PostgreSQL数据库备份 PostgreSQL提供了多种备份方法，其中`pg_dump`是常用的...
PostgreSQL使用心得与技巧
xunming的专栏
06-24 2486
文章目录一、安装配置1，安装postgresql2，安装postgis3，安装扩展二，实际操作1、关联更新2、pg_dump备份 一、安装配置 1，安装postgresql 绿色版：https://www.enterprisedb.com/download-postgresql-binaries 安装版：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads 建议下载最新安装版pg13，通俗易懂，可配置用户名密码端口等，安装
pg 备份恢复（四）—— 逻辑备份（pg_dump与copy导入导出）
Hehuyi_In的博客
10-23 6279
常用方法 pg_dumpall：适合导出cluster中所有业务库 pg_dump：适合单个库、schema级、表级导出 copy：适合单表或带条件sql结果导出（可导出为csv或txt格式） 一、pg_dumpall pg_dumpall可以转储cluster里的所有数据库（postgres、template0、template1除外）到一个sql文件，通过对cluster里的每个数据...
pg_dump 详解/使用举例
weixin_34007291的博客
03-22 1408
pg_dump是一个用于备份PostgreSQL数据库的实用工具。即使当前数据库正在使用，也能够生成一致性的备份，且不会阻塞其他用户访问数据库(包括读、写) pg_dump只能备份一个数据库。如果要备份Cluster中数据库共有的全局对象，例如角色和表空间，需要使用pg_dumpall。 备份文件以文本或存档文件格式输出。Script dumps是一个普...
PgSQL备份
Csdn129341的博客
05-22 3121
SQL转储。 这里我们用到的工具是pg_dump和pg_dumpall。 这种方式可以在数据库正在使用的时候进行完整一致的备份，并不阻塞其它用户对数据库的访问。它会产生一个脚本文件，里面包含备份开始时，已创建的各种数据库对象的SQL语句和每个表中的数据。可以使用数据库提供的工具pg_dumpall和pg_dump来进行备份。pg_dum...
关于连接PostgreSQL时提示 FATAL: password authentication failed for user "连接用户名" 的解决办法
热门推荐
qq_35433306的博客
08-08 5万+
postgresql密码错误 FATAL: password authentication failed for user
postgresql 口令： psql: 致命错误: 用户 认证失败
weixin_30247781的博客
10-14 3229
在Windows环境下使用psql 命令出现认证失败。 网上找了很多，都没讲到要点 D:\program\PostgreSql\bin>psql口令： psql: 致命错误: 用户 "Lyncent" Password 认证失败 使用psql --help发现 当且仅当输入psql的时候，实际是后面跟着缺省 用户名，就是本地PC的用户名， 实际postgresql数据库里面根...
pg_dump实例详解
xrdlqy的专栏
04-02 3203
本文转自：http://blog.chinaunix.net/uid-354915-id-3504632.html 一、pg_dump的用法： 数据库的导入导出是最常用的功能之一，每种数据库都提供有这方面的工具，例如Oracle的exp/imp，Informix的dbexp/dbimp，MySQL的mysqldump，而PostgreSQL提供的对应工具为pg_dump和pg_restore。
pg_dump命令及参数(postgres数据库)
12-09 2万+
pg_dump 名称 pg_dump— 将一个 Postgres 数据库抽出到一个脚本文件 语法 pg_dump [ dbname ] pg_dump [ -h host ] [ -p port ]     [ -t table ]     [ -a ] [ -c ] [ -d ] [ -D ] [ -i ] [ -n ] [ -N ]     [ -o ] [ -s ] [
pg_dump: server version: 14.2; pg_dump version: 9.2.24 pg_dump: aborting because of server version mismatch
06-10
您看到的错误信息是因为您的 pg_dump 版本过旧，无法与您正在使用的 PostgreSQL 服务器版本兼容。为了解决这个问题，您需要使用与您 PostgreSQL 服务器版本相同或更高版本的 pg_dump。 您可以尝试使用以下步骤来...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值