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.
-
The instance size is quite small
-
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.
- You are just separating one or more databases from a huge instance on which there are multiple databases
- 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.
- PostgreSQL instance to be upgraded contains one or more databases with large size
- 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] D