Shulou(Shulou.com)06/01 Report--

There are three ways to back up the database:

SQL dump

File system level backup.

Keep archiving.

SQL dump

Use the SQL command to generate a file.

Backup usage: pg_dump dbname > dumpfile

Pg_dump is a regular PostgreSQL client application.

Pg_dump cannot run with special permissions and must have read access to all tables to be backed up.

Pg_dump connections are constrained by a normal client authentication mechanism.

Pg_dump does not dump information about roles or tablespaces.

Recovery usage: psql dbname

< dumpfile 在执行psql恢复之前,需要先创建数据库。 在还原SQL转储之前，所有拥有对象或被授予对转储数据库中对象的权限的用户必须已存在。 恢复时遇到SQL错误后, 退出恢复: psql --set ON_ERROR_STOP=on dbname dumpfile 恢复: psql -f dumpfilepostgres 在恢复pg_dumpall转储时始终需要具有数据库超级用户访问权限，因为这需要恢复角色和表空间信息。 使用pg_dumpall --globals-only选项单独转储群集范围的数据. 1.3 处理大型数据库： 使用压缩转储: pg_dump dbname| gzip>

Filename.gz

Restore: gunzip-c filename.gz | PSQLdbname

Or: cat filename.gz | gunzip | PSQLdbname

Use split: make a 1-megabyte block: pg_dump dbname | split-b 1m-filename

Use pg_dump 's custom dump format: pg_dump-Fc dbname > filename

The custom dump format compresses the data when it is written to the output file.

Custom format dump, which must be restored using pg_restore: pg_restore-d dbname filename

Use the parallel dump feature of pg_dump:

Use the-j parameter to control the degree of parallelism. Parallel dumps only support the Catalog archive format.

Pg_dump-j num-F d-fout.dir dbname

Use pg_restore-j to restore dumps in parallel. This applies to any archive in Custom or Catalog archiving mode, whether or not pg_dump-j.

File system level backup:

Directly copy the files that PostgreSQL uses to store data in the database

Tar-cf backup.tar / services/install/postgresql10/data/

You must shut down the database server to get available backups and shut down the server before restoring the data.

File system backups apply only to full backups and restores of the entire database cluster.

Use rsync to perform file system backups. The database service needs to be shut down.

File system backups are usually larger than SQL dumps, and file system backups may be faster.

Continuous archiving and point-in-time recovery (PITR):

Postgresql maintains a prewritten log PG _ wal in the data directory.

The log records every change made to the data file of the database. This log is mainly used for crash security purposes: if the system crashes, you can "replay" the database back to the log entries that have been made since the last checkpoint.

Combine file system level backups with backups of WAL files.

"when restoring, restore the file system backup and then replay it from the backed up WAL file to bring the system into its current state."

Advantages:

A fully consistent file system backup is not required. Inconsistencies in the backup are corrected by log replay.

Continuous backups can be achieved simply by continuing to archive WAL files.

You can stop playback at any time and have a consistent snapshot of the database at that time, supporting point-in-time recovery.

Note:

Only the entire database cluster can be restored. Need a lot of archive storage, basic backup is very large, high reliability of the preferred backup technology.

Continuous archiving (online backup) requires continuous archiving of WAL files.

3.1 set up WAL archiving:

The system physically divides this sequence into WAL segment files, usually 16MB.

The numerical names of segment files reflect their position in the abstract WAL sequence.

Enable WAL archiving:

Set the wal_level configuration parameter to replica

Archive_mode is set to on

And specify the shell command to use in the archive_command configuration parameters

These settings are in the postgresql.conf file.

The archive command is called only on completed WAL segments. Limit the time it takes to use unarchived data: set archive_timeout to force the server to switch to new WAL segment files at least often. An archive_timeout setting of about a minute is usually reasonable.

Note:

WAL archiving restores any changes made to the data in the PostgreSQL database, but does not restore changes made to the configuration file.

Wal_level can only be changed when the server starts. Archive_command can be changed using a configuration file reload.

3.2 make a basic backup:

Use the pg_basebackup tool. The backup can be a file or an tar archive.

When full_page_writes is disabled, backup runtime performance degrades. Full_page_writes effectively enforces backups during backup mode.

Use backup:

All WAL segment files generated during and after file system backups need to be retained

The basic backup process creates a backup history file that is immediately stored in the WAL archive area. This file is named after the first WAL segment file required for the file system backup.

3.3 use low-level API for basic backups

Low-level basic backups can be done in a non-exclusive or exclusive manner. A non-exclusive method is recommended.

Non-exclusive low-level backups are backups that allow other concurrent backups to run:

1. Make sure WAL archiving is enabled and working properly.

two。 Connect to the server as a user with permission to run pg_start_backup:

SELECT pg_start_backup ('label',false,false)

Any string that label uses to uniquely identify this backup operation.

False by default, the pg_start_backup time is longer because the checkpoint is performed, and the I / O required by the checkpoint will be unfolded over a long period of time, which is half the checkpoint interval by default. The impact on query processing can be minimized.

False: tell pg_start_backup to initiate a non-exclusive basic backup.

3. SELECT * FROM pg_stop_backup (false,true); terminate backup mode

3.4 back up the data directory:

Pg_dynshmem/,pg_notify/,pg_serial/,pg_snapshots/,pg_stat_tmp/, and pg_subtrans/ are initialized when postmaster starts.

Omit directories such as pg_wal/,postmaster.pid and postmaster.opts,pg_replslot,pg_dynshmem/,pg_notify/,pg_serial/,pg_snapshots/,pg_stat_tmp/, and pg_subtrans from the backup.

Pgsql_tmp can omit any file or directory that begins with a file or directory from the backup.

Pg_start_backup or pg_stop_backup cannot be used when the server is stopped.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.