Backup of PostgreSQL Database

Backup of PostgreSQL Database

First, establish a database connection

Command: PSQL-H IP address-P Port-U database user name-D database name

Psql-h 127.0.0.1-p 5432-u postgres-d postgres

Psql Command connection options

Connection options:

-H,--host=hostname host default Local

-P,--port=port port default 5432

-U,--username=username user name default Postgres

-W,--no-password never prompt password

-W,--password force psql prompt for password, even if no password is indicated.

-d Specifies the name of the library to connect to

=============================================

Ii. Data Backup and restore

Pg_restore can recover files backed up by Pg_dump, and it regenerates all the commands that are needed for all user-defined types, functions, tables, and indexes, including data

Executes the name of the database using the-D option, and-c specifies the path to the backup file

pg_restore-d testdb-u postgres-c/home/postgres/testdb.sql

Psql is a PostgreSQL terminal, it can run user input statements, input statements can also come from a file,

So for a backup of the text file containing the Create and INSERT statements, you can use Psql to recover to the data.

psql-d testdb-u postgres-f/home/postgres/testdb.sql

1.pg_dump backing up the database

Command: PG_DUMP-H IP address-P Port-U database user name-f Target Store file and path target database name

Backing up the TestDB database to the/home/postgres/testdb.sql file

Pg_dump-u postgres-f/home/postgres/testdb.sql TestDB

Recovery

Psql-u postgres-d testdb-f/home/postgres/testdb.sql

Back up the Pmp_login_log table in the TestDB library

Pg_dump-u postgres-t pmp_login_log-f/home/postgres/login_log.sql TestDB

Recovery

Psql-u postgres-d testdb-f/home/postgres/login_log.sql

2.pg_dumpall backing up the database

Backing up the entire server's database using Pg_dumpall

Backup

Pg_dumpall-u postgres-f/home/postgres/postgres.sql

Recovery

Psql-u postgres-f/home/postgres/postgres.sql

Third, PostgreSQL does not need to manually enter the password

PostgreSQL does not include the password option, the general Backup command needs to enter the password manually, so it will bring some inconvenience to the automatic backup.

View official documents, (English is not good, the whole translation/(ㄒoㄒ)/~~)

Pgpassword behaves the same as the password connection parameter. Use of this environment variable are not recommended to security reasons, as some operating systems allow non-root users t o See process environment variables via PS; Instead consider using the ~/.pgpass file (see section 32.15).

PGPASSFILE Specifies the name of the password file to use for lookups. If not set, it defaults to ~/.pgpass (see section 32.15).

On Unix systems, the permissions On.pgpassmust disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions is less strict than this, the file would be ignored.

Two methods are mentioned in the document;

The first method: Use the environment variable parameter of PostgreSQL to implement saving password.

Export pgpassword= "123456"

Second method: Create a ~/.pgpass file to save the password

Format of password file: Hostname:port:database:username:password

Cat ~/.pgpass

localhost:5432:testdb:postgres:123456

Note: According to the official documentation, for security reasons, the environment variables are not recommended in a way that

It is recommended to use ~/.pgpass to save the password, this file must be set to 0600 permissions, if the permissions are not so strict, the file will be ignored.

chmod 0600 ~/.pgpass

This article is from "Linux operations-Small Ink" blog, please be sure to keep this source http://xmomo.blog.51cto.com/5994484/1977529

Backup of PostgreSQL Database