| |
For backup PostgreSQL database in one of the official manual of the method is to use "SQL Dump" way (Another way is to back up files directly in the file system, you can refer to the official manual).
The basic usage is as follows:
pg_dump dbname> outfile
First, as demonstrated by the command line, pg_dump command is output to a standard output.
Secondly, pg_dump does not affect the course of their work to other database operations (mainly concerned pg_dump will produce read-write lock (read lock, write lock)), but there are exceptions, and that is what needs to use mutex (exclusive lock) operations such as ALTER TABLE.
Due to the operation and maintenance, it usually requires the use of scripts to perform database backup, instead of manually every day execute the command line and enter the password backup, specifically checked the documents, according to the documentation "31.15. The Password File" section, you can build a profile in the user directory, the password is written in advance in the configuration file, the format of the configuration file is as follows:
hostname: port: database: username: password
Under this document on the need to perform pg_dump command user directory, save as .pgpass file and that the permissions of 600, otherwise PostgreSQL will report
WARNING: password file "/root/.pgpass" has group or world access; permissions should be u = rw (0600) or less
Note: If the database table structure PostgreSQL database (database schema) dependent on OIDs (such as foreign keys), then pg_dump need to add the -o option.
Export PostgreSQL database speed is quite fast, exporting more than 30,000 lines of less than 1s.
Export PostgreSQL database command line examples:
pg_dump -U confluence -d confluence -h 127.0.0.1 -o> /tmp/tmp_confluence_postgresql.sql
among them,
-U That the implementation of user
-d represents the database
-h host indicates
-o expressed support OLDs
NOTE: If you do not use .pgpass files, you can add -W option on the command line, which means a password before performing.
Appendix: PostgreSQL database for some of the basic operation and maintenance operations
Login PostgreSQL database:
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
If you do not want to enter a password, you can do:
psql "host = 127.0.0.1 hostaddr = 127.0.0.1 port = 5432 user = yourloginname password = yoursecret"
List Database:
\ L
Exit database console:
\ Q
tag: PostgreSQL database backup, PostgreSQL command line does not enter a password, the basic operation of the export PostgreSQL database, PostgreSQL database, PostgreSQL operation and maintenance tutorial |
|
|
|