When restoring logical backup, data between logical backup and failure time is difficult to recover, so the logical backup method is generally not used for database backup, but logic is suitable for cross-platform and cross-version data migration;
There are three main types of logical backup and recovery:
pg_dump
pg_dumpall
copy
This section mainly explains pg_dump
pg_dump backup
Only a single database can be backed up, and no information related to roles and tablespaces will be exported.
-Fc Backup is in binary format, compressed and stored. It can be used for fine restore by pg_restore
-F p Backup as text, large library is not recommended
pg_dump recovery
psql dbname -U username < bakfile
or
pg_restore – pg_restore [option] ... [filename] – pg_restore -d dbname bakfile
Backups in binary format can only be restored using pg_restore. You can specify the restored table, edit the TOC file, customize the restore order, table, index, etc.
Backup and restore in text format, directly use the user to connect to the corresponding database to perform backup text, such as psql dbname -f
pg_dump backup recovery example
1) Create a database
createdb testdb
2) Connect to the database testdb
psql testdb
3) Create a test table and insert data
testdb=# create table tt(a int) tablespace tbls_t; testdb=# insert into tt(a) values(1); testdb=# insert into tt(a) values(2);
4) View data
testdb=# select * from tt;
5) Backup
pg_dump testdb>/dbbak/ #Simple syntax, can be flexibly backed up with options
6) Delete the database testdb
dropdb testdb
7) Create a new database (you need to create a database before restoring)
createdb testdb
8) Recover data
psql testdb </dbbak/
9) Check whether the data is replying
psql testdb
testdb=# select * from tt;
At this point, the data has been successfully restored!
pg_restore -d postgres /dbbak/pgdumpbak/
pg_dump backup recovery command extension exercise
pg_dump -F c -f /dbbak/pgdumpbak/ -C -E UTF8 -h 127.0.0.1 -U postgres testdb #Binary format backup filepg_dump -F p -f /dbbak/pgdumpbak/ -C -E UTF8 -h 127.0.0.1 -U postgres testdb # Backup file in text format, "-C" means to contain the creation statementpg_restore /dbbak/|less Can parse backup files in binary format pg_restore -l /dbbak/ pg_restore -d testdb /dbbak/pgdumpbak/ #You need to create a target library firstpg_restore -d postgres /dbbak/pgdumpbak/ #The file contains commands to create a database, and there is no need to create a target library
Toc file selective backup and recovery
1) Generate toc file based on binary backup file
pg_restore -l -f /dbbak/pgdumpbak/toc /dbbak/pgdumpbak/
2) Modify the toc file and comment out the content that does not need to be restored by adding a semicolon to the first line.
3) Recover with toc file list
pg_restore -F c -L /dbbak/pgdumpbak/toc -d testdb /dbbak/pgdumpbak/
Supplement: Postgresql backup and restore command pg_dump
Postgresql database backup and restore command pg_dump
Commonly used commands:
Backup:
pg_dump -U postgres -d myDBname -f
in
postgres is the username
myDBname is the database name
It's the file name
reduction:
createdb newDBname
psql -d newDBname -U postgres -f
in
postgres is the username
newDBname is the database name
It's the file name
refer to:
pg_dump Dump a database into a plain text file or other format.
usage:
pg_dump [Options]... [Database name]
一般Options:
-f, --file=FILENAME Output file or directory name
-F, --format=c|d|t|p Output file format (custom made, Table of contents, tar)
Plain text (default value))
-j, --jobs=NUM Perform multiple parallel tasks for backup and dump work
-v, --verbose Detailed mode
-V, --version Output version information，Then exit
-Z, --compress=0-9 Compression level of compressed format
--lock-wait-timeout=TIMEOUT Operation failed after waiting for table lock to timeout
-?, --help Show this help, Then exit
控制输出内容Options:
-a, --data-only Dump data only,Not included
-b, --blobs Include large objects in the dump
-c, --clean Before recreating，Clear first（delete）Database Objects
-C, --create Include commands in the dump,In order to create a database
-E, --encoding=ENCODING Dump toENCODINGFormal coded data
-n, --schema=SCHEMA Dump only the pattern of the specified name
-N, --exclude-schema=SCHEMA Not dumping named patterns
-o, --oids Include in the dump OID
-O, --no-owner 在Plain text格式中, Ignore the restore object's belonging
-s, --schema-only Dump mode only, Excluding data
-S, --superuser=NAME 在Plain text格式中use指定的超级用户名
-t, --table=TABLE Dump only tables with specified names
-T, --exclude-table=TABLE Don't dump tables with specified names
-x, --no-privileges Don't dump permissions (grant/revoke)
--binary-upgrade Only used by upgrade tools
--column-inserts with column namesINSERTDump data in command form
--disable-dollar-quoting Cancel USD (symbol) quotation marks, use SQL 标准quotation marks
--disable-triggers Disable triggers while recovering data only
--enable-row-security Enable row security（Dump only content that users can access）
--exclude-table-data=TABLE Don't dump tables with specified names中的data
--if-exists 当delete对象时useIF EXISTS
--inserts byINSERTOrder，InsteadCOPYOrder的形式转储data
--no-security-labels No dumping of security label allocation
--no-synchronized-snapshots 在并行工作集中不use同步快照
--no-tablespaces Do not dump tablespace allocation information
--no-unlogged-table-data No dumping of table data without logs
--quote-all-identifiers 所有标识符加quotation marks，Even if it's not a keyword
--section=SECTION Backup named section (Before the data, data, and data后)
--serializable-deferrable 等到Backup可by无异常运行
--snapshot=SNAPSHOT 为转储use给定的快照
--strict-names Requires each table and/orschema包括模式by匹配至少一个实体
--use-set-session-authorization
use SESSION AUTHORIZATION Order代替
ALTER OWNER Order来设置所有权
联接Options:
-d, --dbname=DBNAME 对data库 DBNAMEBackup
-h, --host=Host Name data库服务器的Host Nameor套接字Table of contents
-p, --port=Port number data库服务器的Port number
-U, --username=name by指定的data库用户联接
-w, --no-password Never prompt for password
-W, --password Force password prompt (automatic)
--role=ROLENAME Run before dumpSET ROLE
如果没有提供Database name, 那么use PGDATABASE Environment variables
The value of.
Report an error to <pgsql-bugs@>.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.