Comparison of PostgreSQL database backup and recovery technologies

Comparison of PostgreSQL database backup and recovery technologies

There are three completely different methods to back up PostgreSQL data:

• SQL dump
• File System-level backup
• Online backup

I. SQL dump

The pg_dump application tool is used to export the current data block as an SQL file. When the database needs to be rebuilt, the SQL file is returned to the server to restore the database status when the database is re-built.

When backing up the entire database cluster, you can use the pg_dumpall program to back up each database in the cluster provided by pg_dumpall, and ensure that the global data status such as users and groups is retained.

When the table size in the database is greater than the maximum file size allowed by the system, you need to use a standard UNIX tool to solve this problem.

Disadvantages:

To obtain the Super User Permissions of the database, the existence of these permissions is still required when the database is restored.

Database updates during pg_dump operation will not be dumped.

Ii. File System-level backup

Directly copy the files used to store database data in the PostgreSQL database.

Method:

"Consistent snapshot method"

Use rsync to perform File System Backup

Disadvantages:

The database must be closed during Backup.

When a database is distributed across multiple file systems, the database can be completely backed up only after it is closed for a long time.

The dumped file is large.

Iii. Online backup and Instant recovery

This method combines system backup and WAL file backup. When recovery is required, the file backup is restored and then the WAL file is replayed.

Advantages:

In the initial stage, a perfect consistent backup is not required. Therefore, you do not need to use the snapshot function. tar or similar archiving tools can be used for backup;

Continuous database backup can be achieved through continuous WAL file archiving;

The database can be restored to any point in time since the backup was started;

Requirements:

Only the whole database cluster can be restored;

A large amount of archive storage is required;

Operation:

1. Set WAL Archive

1) create a WAL Backup directory, for example, d: \ pg_xlog_archive.

2) modify the archive_command settings in postgresql. conf, for example:

Archive_command = 'Copy "% p" d: \ pg_xlog_archive \ % F'

Note: % p should be enclosed in double quotation marks to process the installation directory with spaces.

Archive_mode = on

Wal_level = 'archive'

In postgresql. in the conf file, use archive_command to declare the shell command to copy a complete segment file to a specified location. In this shell command, use % p to indicate the absolute path of the file to be archived, % f indicates the file name.

Eg: archive_command = 'cp-I % p/mnt/server/archivedir/% f </dev/null'

The archive command must be executed under the permission of the same user running the PostgreSQL server. In addition, it returns 0 only when the archive command runs successfully.

Note: although the WAL archive allows you to recover any changes made to the data in the PostgreSQL database, it does not recover modifications to the configuration file after the initial basic backup (that is, postgresql. conf, pg_cmd.conf, and pg_ident.conf), because these files are manually edited, rather than being edited through SQL operations. Therefore, you may need to put your configuration file in a place that can be processed in a routine file system backup process.

2. Perform basic backup

1) Make sure that the WAL archive is enabled and can run.

2) connect to the database as a database superuser and issue the command SELECT pg_start_backup ('label ');

The label here is the unique identifier of any backup operation you want to use. (A good habit is to use the full path of the destination where you want to place the backup dump file .) Pg_start_backup uses your backup information. In your cluster directory, create a backup label file called backup_label.

3) perform backup and use any convenient file system tool, such as tar or cpio. During these operations, you do not need to shut down the database or the database operations.

4) connect to the database as a database superuser again, and then issue the command SELECT pg_stop_backup ();

5) as long as the WAL file used during the backup process is backed up as part of the normal database activity, your backup will be completed.

Note: Make sure that your backup dump includes files (such as/usr/local/pgsql/data) and tablespaces in the directory of all database clusters.

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address: