Periodic pg_dump backup in linux

Pg_dump periodic backup OS in linux: CentOSDB: PostgreSQL93, OSuser: S 1. Compile the pg_dump backup script: 01getthedayofweek1-7startingmon = 102DOW = 'date + & quot; % u & quot; '0304de periodic backup of pg_dump in linux OS: CentOS DB: PostgreSQL9.3, OS user: s I. Compile the pg_dump backup script: 01 # get the day of week 1-7 starting mon = 102DOW = 'date + "% u" '03 04 # define variables05DB_INSTALL_DIR = '/opt/PostgreSQL/9.3' 06 # DB_TYPE = 'postgresql '07 # DB_SERVER = '127. 0.0.1 '08 # DB_PORT = '000000' 09DB _ INSTANCE = 'mydb' 10DB _ USER = 'codecs' 11DB _ PASSWORD = 'codecs' 12DB _ STORAGE = '/home/S/backup' 13 14 # specify the postgres password in the PGPASSWORD var15 # for pg_dump not to prompt for a password16export PGPASSWORD = $ DB_PASSWORD17 18 # change to the bin dir19cd $ DB_INSTALL_DIR/bin20 21 # run logs. /pg_dump-f $ DB_STORAGE/$ {DOW}-mydb. SQL-U $ DB_USER-F p-a $ DB_INSTANCE Save to/home/postgres/backup. sh. 2. set a script execution plan (scheduled or periodic): each user has a list of different Scheduled Tasks. after logging on to the account su username, run the crontab-l command to view the scheduled tasks of each task. run the crontab-e command to enter the 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. 1su postgres2crontab-l3crontab-e enters vi mode and adds the corresponding script execution plan, for example, */2 ***** root run-parts/home/S/backup. sh indicates that backup is executed every 2 minutes. sh, that is, backup once every 2 minutes; or 0 2 * root run-parts/home/S/backup. sh indicates that backup is performed at every day. sh, that is, backup once every day at 2 o'clock. 1su root2service crond restart note: 1. set shell scripts to regularly execute the content added through crontab-e. In fact, a file named OS username (postgres here) is generated under the/var/spool/cron directory, the content is added by crontab-e (this is */2 * root run-parts/home/S/backup. sh ). 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 syntax 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 of 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 environmentvariable value is used.