SQL dump
It will generate a text file with SQL commands to recreate the database.
Dump 单独数据库
The basic usage of this command is:
pg_dump dbname > outfile
Restoring the Dump
The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is
psql dbname < infile
where infile is the file output by the pg_dump command. The database dbname will not be created by this command
Dump 所有
pg_dump只能备份单个数据库，而且恢复的时候需要创建空数据库。pg_dumpall可以备份所有数据库，并且备份角色、表空间。
pg_dump dumps only a single database at a time, and it does not dump information about roles or tablespaces (because those are cluster-wide rather than per-database). To support convenient dumping of the entire contents of a database cluster, the pg_dumpall program is provided. pg_dumpall backs up each database in a given cluster, and also preserves cluster-wide data such as role and tablespace definitions. The basic usage of this command is:
pg_dumpall > outfile
The resulting dump can be restored with psql:
psql -f infile postgres
可以指定任何数据库名，如果恢复到一个空的集群中，通常使用postgres数据库
处理大数据（压缩、分割）
Some operating systems have maximum file size limits that cause problems when creating large pg_dump output files. Fortunately, pg_dump can write to the standard output, so you can use standard Unix tools to work around this potential problem. There are several possible methods:
Use compressed dumps. You can use your favorite compression program, for example gzip:
pg_dump dbname | gzip > filename.gz
Reload with:
gunzip -c filename.gz | psql dbname
or:
cat filename.gz | gunzip | psql dbname
Use split. The split command allows you to split the output into smaller files that are acceptable in size to the underlying file system. For example, to make chunks of 1 megabyte:
pg_dump dbname | split -b 1m - filename
Reload with:
cat filename* | psql dbname
File System Level Backup （冷备份）
Directly copy the files that PostgreSQL uses to store the data in the database
for example:
$ tar -cf backup.tar /var/lib/pgsql/14/data/base
限制：需要停掉数据库再操作。
Continuous Archiving and Point-in-Time Recovery (PITR)
基于WAL，这种方式不常用。
At all times, PostgreSQL maintains a write ahead log (WAL) in the pg_xlog/ subdirectory of the cluster's data directory. The log records every change made to the database's data files.
网友评论