Backup of PostgreSQL Database

Source: Internet
Author: User
Tags chmod postgresql psql postgresql terminal

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 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


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


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


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


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


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

Backup of PostgreSQL Database

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.