How to schedule postgresql auto backup in linux

This article describes How to schedule postgresql auto backup in linux.

Step 1: Create a .pgpass file under your home directory.


                   [root@centos6 ~]# vim .pgpass

                                localhost:5432:DB-Name:Username:Password

Step 2: Install Crond packages.  http://www.admincool.info/2016/09/how-to-scheduling-crontab-for-     backup.html


Step3: Edit crontab


                  [root@centos6 ~]# crontab -e

                  30      10      *      *      *      /opt/PostgreSQL/9.4/bin/pg_dump -U username --format=c --file=/postgres-backup/dbname.backup dbname


The backup of database will scheduled every day at 10.30 AM under 'postgres-backup' directory.


Step 4: Restart crontab


                 # service crond restart


Step 5: Add crontab on startup


                # chkconfig crond on

Synopsis

pg_dump [connection-option...] [option...] [dbname]

Description

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

Dumps can be output in script or archive file formats. Script dumps are plain-text files containing the SQL commands required to reconstruct the database to the state it was in at the time it was saved. To restore from such a script, feed it to psql. Script files can be used to reconstruct the database even on other machines and other architectures; with some modifications, even on other SQL database products.

The alternative archive file formats must be used with pg_restore to rebuild the database. They allow pg_restore to be selective about what is restored, or even to reorder the items prior to being restored. The archive file formats are designed to be portable across architectures.

When used with one of the archive file formats and combined with pg_restore, pg_dump provides a flexible archival and transfer mechanism. pg_dump can be used to backup an entire database, then pg_restore can be used to examine the archive and/or select which parts of the database are to be restored. The most flexible output file format is the "custom" format (-Fc). It allows for selection and reordering of all archived items, and is compressed by default.

Options

-U username

--username=username

User name to connect as.

-F format

--format=format

Selects the format of the output. format can be one of the following:

p

plain

Output a plain-text SQL script file (the default).

c

custom

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

d

directory

Output a directory-format archive suitable for input into pg_restore. This will create a directory with one file for each table and blob being dumped, plus a so-called Table of Contents file describing the dumped objects in a machine-readable format that pg_restore can read. A directory format archive can be manipulated with standard Unix tools; for example, files in an uncompressed archive can be compressed with the gzip tool. This format is compressed by default.

t

tar

Output a tar-format archive suitable for input into pg_restore. The tar format is compatible with the directory format: extracting a tar-format archive produces a valid directory-format archive. However, the tar format does not support compression. Also, when using tar format the relative order of table data items cannot be changed during restore.

-i

--ignore-version

A deprecated option that is now ignored.