pg_dump 的十六般变化（上篇）

Easy Dump

While you are upgrading from PostgreSQL earlier releases like PostgreSQL 9.1 to later releases like PostgreSQL 11, pg_dump and pg_upgrade provided by PostgreSQL are the most reliable and most powerful tools.

pg_dump is more flexible and can also be used to dump some of databases or tables to another instance.

Easy Dump is a simple shell script to make PostgreSQL pg_dump easier.

With Easy Dump, you are able to dump databases or tables into another PostgreSQL instance easily in one command line.

Apart from the dump functionality, Easy Dump also sends successful notifications and checks errors when dump or restore failed and sends failure notifications with details in cases like invalid password is given or no proper ACL is given, so that you are informed timely with helpful information.

Easy Dump introduction

Easy Dump Overview

Easy Dump is written with library Easy Bash and provides 5 dump modes to support 16 most useful cases.

Why use Easy Bash

Easy Bash is a simple bash library to make scripting easier.

With Easy Bash, you are able to parse command line parameters with only one line(15 characters):

parse_args "$@"

In fact, there is much more Easy Bash can do.

Easy Bash helps you write simple, efficient and colorful scripts easily.

You no longer need to do the following manually:

1. parse all the arguments manually
2. set variables manually
3. write help message manually
4. write config file manually
5. check whether all required parameters are set manually
6. write logs manually

And you are able to do the following easily:

1. add options easily
2. parse options easily
3. do dry run test easily
4. write colorful logs easily
5. write and show different levels of logs easily
6. send messages easily

Easy Bash Overview

Here is an overview of the functionalities of Easy Bash.

Example data

Source PostgreSQL instance

Host: alvindb1.dbadaily.com

Port: 5432

PostgreSQL version: PostgreSQL 9.1

To illustrate the usage of Easy Dump, on the source PostgreSQL instance, create one user and three databases including tables.

Create user

CREATE USER alvin WITH PASSWORD 'alvin';

Create databases and tables

Database alvindb

Create database alvindb and schema alvin.

CREATE DATABASE alvindb OWNER alvin;
\c alvindb
CREATE SCHEMA alvin AUTHORIZATION alvin;

Create tables.

\c alvindb alvin
CREATE TABLE tb_alvindb_vacuum (
    test_id BIGSERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_alvindb_sequence (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_alvindb_test (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

INSERT INTO tb_alvindb_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_alvindb_sequence(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_alvindb_test(test_num) SELECT gid FROM generate_series(1,3,1) gid;

Database dbadaily

Create database dbadaily.

CREATE DATABASE dbadaily OWNER alvin;

Create tables.

\c dbadaily alvin

CREATE TABLE tb_dbadaily_vacuum (
    test_id BIGSERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_dbadaily_sequence (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_dbadaily_test (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

INSERT INTO tb_dbadaily_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_dbadaily_sequence(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_dbadaily_test(test_num) SELECT gid FROM generate_series(1,3,1) gid;

Database testdb

Create database testdb.

CREATE DATABASE testdb OWNER alvin;

Create tables.

\c testdb alvin

CREATE TABLE tb_testdb_vacuum (
    test_id BIGSERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_testdb_sequence (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

CREATE TABLE tb_testdb_test (
    test_id SERIAL PRIMARY KEY,
    test_num BIGINT
);

INSERT INTO tb_testdb_vacuum(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_testdb_sequence(test_num) SELECT gid FROM generate_series(1,3,1) gid;
INSERT INTO tb_testdb_test(test_num) SELECT gid FROM generate_series(1,3,1) gid;

Target PostgreSQL instance

Host: alvindb2.dbadaily.com

Port: 5432

PostgreSQL version: PostgreSQL 11

Dump options

Pure shell library Easy Bash provides an easy way to add and parse options.

Add options as below.

add_main_options() {
  ## custom options
  # source db
  add_options "h:" "host:" "DBHOST" "Y" "source database server host or socket directory"
  add_options "p:" "port:" "DBPORT" "Y" "source database server port number"
  add_options "U:" "username:" "DBUSER" "Y" "source database user name"
  add_options "W:" "password:" "PGPASSWORD" "Y" "source database user password"
  add_options "d:" "dbname:" "DBNAME" "N" "source database name to connect to"

  # target db
  add_options "P:" "target-port:" "DBPORT_TARGET" "Y" "target database server port number"
  add_options "B:" "bin-dir:" "PGBIN" "Y" "PostgreSQL bin directory"

  # dump modes
  add_options "M:" "mode:" "DUMP_MODE" "Y" "dump modes: