Periodic pg_dump backup in Linux

OS: CentOSDB: PostgreSQL9.3, OSuser: postgres A, write pg_dump backup script: # getthedayofweek1-7startingmon1DOW'

OS: CentOSDB: PostgreSQL9.3, OS user: postgres 1. Compile the pg_dump backup script: # get the day of week 1-7 starting mon = 1 DOW ='

OS: CentOS

DB: PostgreSQL9.3, OS user: postgres

I. Compile the pg_dump backup script:

# Get the day of week 1-7 starting mon = 1DOW = 'date + "% u" '# define variablesDB_INSTALL_DIR ='/opt/PostgreSQL/100' # DB_TYPE = 'postgresql' # DB_SERVER = '2017. 0.0.1 '# DB_PORT = '000000' DB _ INSTANCE = 'mydb' DB _ USER = 'dones' DB _ PASSWORD = 'dones' DB _ STORAGE ='/home/S/backup '# specify the login s password in the PGPASSWORD var # for pg_dump not to prompt for a passwordexport PGPASSWORD = $ DB_PASSWORD # change to the bin dircd $ DB_INSTALL_DIR/bin # run pg_dump. /pg_dump-f $ DB_STORAGE/$ {DOW}-mydb. SQL-U $ DB_USER-F p-a $ DB_INSTANCE

Save the preceding script to/home/postgres/backup. sh.

2. Set the Script Execution Plan (timed or periodic ):

Each user has a different list of scheduled tasks. After logging on to the account su username,

Run the crontab-l command to view the scheduled tasks of each task,

Run crontab-e to enter vi Mode. You can modify your scheduled tasks,

After each task is added, make sure the service crond restart restarts the crond service. Otherwise, the task will not take effect. If the current user does not have the permission to restart the task, switch to the root user and restart the service.

Then, the system executes the script regularly or periodically.

Su Hei scrontab-l crontab-e

In vi Mode, add the corresponding Script Execution Plan, such:

*/2 * root run-parts/home/S/backup. sh indicates that backup. sh is executed every two minutes, that is, backup is performed every two minutes;
Or 0 2 * root run-parts/home/postgres/backup. sh indicates that backup. sh is executed at every day, that is, backup at every day.

Su rootservice crond restart

Note:

1. Set regular shell script execution

The content added through crontab-e is actually in the/var/spool/cron directory, a file named operating system username (postgres here) is generated, the content is added by crontab-e (this is */2 * root run-parts/home/S/backup. sh ).

References:

2. crontab usage: crontab [-u user] file

Crontab [-u user] [-e |-l |-r]
(Default operation is replace, per 1003.2)
-E (edit user's crontab)
-L (list user's crontab)
-R (delete user's crontab)
-I (prompt before deleting user's crontab)
-S (selinux context)

3. pg_dump Usage:

Pg_dump [OPTION]… [DBNAME]

General options:
-F,-file = FILENAME output file or directory name
-F,-format = c | d | t | p output file format (custom, directory, tar,
Plain text (default ))
-J,-jobs = NUM use this role parallel jobs to dump
-V,-verbose mode
-V,-version output version information, then exit
-Z,-compress = 0-9 compression level for compressed formats
-Lock-wait-timeout = TIMEOUT fail after waiting TIMEOUT for a table lock
-?, -Help show this help, then exit

Options controlling the output content:
-A,-data-only dump only the data, not the schema
-B,-blobs include large objects in dump
-C,-clean (drop) database objects before recreating
-C,-create include commands to create database in dump
-E,-encoding = ENCODING dump the data in encoding ENCODING
-N,-schema = SCHEMA dump the named schema (s) only
-N,-exclude-schema = SCHEMA do NOT dump the named schema (s)
-O,-oids include OIDs in dump
-O,-no-owner skip restoration of object ownership in
Plain-text format
-S,-schema-only dump only the schema, no data
-S,-superuser = NAME superuser user name to use in plain-text format
-T,-table = TABLE dump the named table (s) only
-T,-exclude-table = TABLE do NOT dump the named table (s)
-X,-no-privileges do not dump privileges (grant/revoke)
-Binary-upgrade for use by upgrade utilities only
-Column-inserts dump data as INSERT commands with column names
-Disable-dollar-quoting disable dollar quoting, use SQL standard quoting
-Disable-triggers disable triggers during data-only restore
-Exclude-table-data = TABLE do NOT dump data for the named table (s)
-Inserts dump data as INSERT commands, rather than COPY
-No-security-labels do not dump security label assignments
-No-synchronized-snapshots do not use synchronized snapshots in parallel jobs
-No-tablespaces do not dump tablespace assignments
-No-unlogged-table-data do not dump unlogged table data
-Quote-all-identifiers quote all identifiers, even if not key words
-Section = SECTION dump named section (pre-data, data, or post-data)
-Serializable-deferrable wait until the dump can run without anomalies
-Use-set-session-authorization
Use set session authorization commands instead
Alter owner commands to set ownership

Connection options:
-D,-dbname = DBNAME database to dump
-H,-host = HOSTNAME database server host or socket directory
-P,-port = PORT database server port number
-U,-username = NAME connect as specified database user
-W,-no-password never prompt for password
-W,-password force password prompt (shocould happen automatically)
-Role = ROLENAME do set role before dump

If no database name is supplied, then the PGDATABASE environment
Variable value is used.

Recommended reading:

PostgreSQL deletes duplicate data rows in a table

Establishment of PostgreSQL database connection pool PgBouncer

Compiling PostgreSQL on Windows

Notes on PostgreSQL backup

PostgreSQL details: click here
PostgreSQL: click here