Automatic backup of the Postgres database

Postgres is a very good open-source database. Good performance.

When it comes to databases, there's the topic of backup and import.

Backups are also broken down in a number of ways.

1. Full backup

This is the way most people use it to back up the entire database, including all database objects, such as user tables, system tables, indexes, views, and stored procedures. But it takes more time and space, so it's generally recommended to do a full backup once a week.
2. Transaction log backup
The transaction log is a separate file that records changes to the database and only requires a small amount of time to replicate the changes that have been made to the database since the last backup.
3. Differential backup
Also called an incremental backup. It is another way to back up only part of the database, it does not use the transaction log, instead it uses a new image of the entire database. It is smaller than the initial full backup because it contains only the databases that have changed since the last full backup. It has the advantage of faster storage and recovery. It is recommended to make a differential backup every day.
4. File backup

For the first full backup, we can take two more ways. Automatic backup and manual backup .

The manual backup will not be said. Simply say the automatic backup.

Under Linux, you can write a shell execution file and then log the task into cron to perform the automatic backup task on a regular basis.

The password is required for the Postgres user to execute the backup command. In order to achieve automatic backup, we have to prepare an authentication file.

Certification files. Pgpass under the/home/postgres directory.

. Pgpass Content:

Hostname:port:database:username:password

Example: localhost:5432:*:p ostgres:postgres

* is the meaning of all databases, if we just back up a database, can be replaced with the database name.

One thing to be aware of is to execute chmod. Pgpass. Ensure that only Postgres can use this file.

Then, you execute the backup command in your own backup file.

Full backup: $ pg_dumpall-h localhost-u postgres- c > Pg_dumpall.dump

When a particular database is backed up: pg_dump dbname-h localhost-u postgres-c > Dbnamebak.dump

When the shell file is ready, log in to Cron.

Edit Append in/etc/crontab.

4 * * * postgres/home/postgres/pg_dumpall.sh

Cron settings Online A lot of information, to Mr. Google there is a question to know.