Pgsql database backup and restore examples

Backup and restore are most important when you are transporting a database. Today, we tested backup and restore using Pg_dumpall.

Backup Restore method: Pg_dump and Pg_restore, first carefully explain these two commands

Back up the database, instructions are as follows:

Pg_dump-h 164.82.233.54-u postgres databasename > C:\databasename.bak
Start-Run the-cmd pop-up dos console, and then in the console, enter the PostgreSQL installation directory bin:

CD C:\Program Files\postgresql\9.0\bin

Finally execute the backup instruction:

Pg_dump-h 164.82.233.54-u postgres databasename > C:\databasename.bak
Directive explanation: As above command, Pg_dump is the backup database instruction, 164.82.233.54 is the database IP address (must guarantee the database to allow outside access permission Oh ~), of course, the local database IP write localhost;postgres is the database username ; DatabaseName is the name of the database. > meaning is exported to the C:\databasename.bak file, if there is no write path, write only Databasename.bak file name, then the backup file will be saved in C: \program Files\postgresql\9.0\bin Folder.

Restore the database, instructions are as follows:

Psql-h localhost-u postgres-d databasename < C:\databasename.bak (test not successful)

Pg_restore.exe--host localhost--port 5432--username "Postgres"--dbname "Symbolmcnew--no-password"--verbose Ename.backup "(Test successful)
Directive explanation: As above command, Psql is to restore the database command, localhost is to restore to which database address, of course you can write IP address, that is, can be remotely restored (must guarantee the database to allow external access to the right Oh ~); Postgres is the user who wants to restore to which database ; DatabaseName is the database to which to revert. < means to import the C:\databasename.bak file into the specified database.

All of the above is for Windows, if the Linux, will not be effective?

Still works in Linux. One notable thing: if you go directly to the PostgreSQL installation directory bin, execute the command, there may be no pg_dump,psql found, we can:

Backup:

/opt/postgresql/9.5/bin/pg_dump-h 164.82.233.54-u postgres databasename > Databasename.bak
Recovery:

/opt/postgresql/9.5/bin/psql-h localhost-u postgres-d DatabaseName < Databasename.bak

Example

# indicates that under root, $ is the command executed under the Postgres user.

Pgsql Backup

Pg_hba.conf and pg_ident.conf and postgresql.conf are important configuration files for Pgsql, so they need to be backed up.

$ pg_dumpall-p5432-c-u postgres-h localhost | gzip >/tmp/pgsql.gz
$ cd/var/lib/pgsql/9.1/data
$ cp-p pg_hba.conf/tmp
$ cp-p pg_ident.conf/tmp
$ cp-p postgresql.conf/tmp
# Cd/var/lib/pgsql/9.1/data
# RM-FR
# Service postgresql-9.1 Stop
# service postgresql-9.1 Initdb--encoding=utf8
Initializing database:
[OK]
Pgsql Restore

# cp-p/tmp/pg_hba.conf/var/lib/pgsql/9.1/data/
# cp-p/tmp/pg_ident.conf/var/lib/pgsql/9.1/data/
# cp-p/tmp/postgresql.conf/var/lib/pgsql/9.1/data/
# service postgresql-9.1 Start
# gunzip/tmp/pgsql.gz
# Su-postgres
$ psql-f/tmp/pgsql

Pg_dumpall and Pg_dump are referred to as logical backups, Pgsql common commands for version upgrades.

Pg_dump-Extract a PostgreSQL database into a script file or other archive
pg_dump [option ...] [dbname]
Options option ...
The following command-line arguments control the content and format of the output.

dbname
Declares the name of the database that will be dumped. If this argument is not declared, then use the environment variable pgdatabase. If the environment variable is not declared, then use the user name that initiates the connection.

-A
--data-only
Output only data, not output mode (data definition).

This option is only meaningful for plain text formatting. For an archive format, you can declare options when calling Pg_restore.

-B
--blobs
Contains large objects in the dump. You must select a non-text output format.

-C
--clean
The output clears (deletes) the command of the database object before creating the database Creation command.

This option is only meaningful for plain text formatting. For an archive format, you can declare options when calling Pg_restore.

-C
--create
Output is made at the beginning of a command that creates the database itself and joins such a database. (If this is the form of a script, it doesn't matter which database you are connecting to before you run the script.) ）

This option is only meaningful for plain text formatting. For an archive format, you can declare this option when calling Pg_restore.

-D
--inserts
The insert command that outputs the data as (not COPY). This can cause recovery to be very slow. This option is primarily used to make dumps that can be used in other PostgreSQL databases. Note that if you rearrange the order of fields, the recovery may fail completely. -D is safer, but also slower.

-D
--column-inserts
--attribute-inserts
Dumps the data to an INSERT command with an explicit field name. (INSERT into table (column, ...) VALUES ... ）。 This causes recovery to be very slow and is primarily used to make a dump that can be used in other PostgreSQL databases.

-F File
--file=file
Sends the output to the specified file. If these are omitted, standard output is used.

-F format
--format=format
Select the format of the output. Format can be one of the following:

P
Output plain Text SQL script file (default)

T
Outputs the tar archive suitable for input into the pg_restore. Using this archive allows you to reorder and/or exclude database objects when restoring the database. It may also be possible to limit what data is recovered when it is restored.

C
The output is suitable for pg_restore archiving for the customer. This is the most flexible format that allows you to rearrange the loaded data and object definitions. This format is compressed by default.

-I.
--ignore-version
Ignores the version differences between Pg_dump and the database server.

Pg_dump can process databases from previous versions of PostgreSQL, but older versions are not supported (currently support to 7.0). If you need to cross the version check to use this option (and if Pg_dump fails, don't say I didn't warn you).

-N Namespace
--schema=schema
Dumps only the contents of the schema. If this option is not declared, the non-system mode in all target databases will be dumped.

Note: In this mode, Pg_dump does not attempt to dump any database objects that may be dependent on any other selected mode. Therefore, the system does not guarantee a single mode of dump can be successfully restored to a clean database.

-O
--oids
As part of the data, the object ID (OID) is output for each table. If your application is in some way referencing the OID field, (for example, in a foreign key constraint). Then use this option. Otherwise, this option should not be used.

-O
--no-owner
Does not set the object's ownership to the corresponding source database. Typically, Pg_dump emits (psql) ALTER OWNER or the SET session AUTHORIZATION statement to set the ownership of the created database object. See also the-R and-X use-set-session-authorization options. Note that-o does not prevent all reconnect to the database, but prevents exclusive joins that are made to adjust permissions.

This option is only meaningful for plain text formatting. For an archive format, you can declare this option when you call Pg_restore.

-R
--no-reconnect
This option is obsolete, but it is still accepted for backward-compatible reasons.

-S
--schema-only
Outputs only the object definition (pattern) and does not output data.

-S username
--superuser=username
Declares a superuser name to use when closing a trigger. It only has a relationship when it's used--disable-triggers. (Usually, we'd better not enter this parameter, but start the generated script with a superuser.) ）

-T table
--table=table
Output only the data for table tables. It is possible that there are multiple tables with the same name in different modes, and if so, all matching tables will be dumped. Declaring--schema and--table at the same time selects only one table.

Note: In this pattern, Pg_dump does not attempt to dump any database objects that may be dependent on any other selected table. Therefore, the system does not guarantee that a single table dump can be successfully restored to a clean database.

-V
--verbose
Declares redundancy mode. This will allow the Pg_dump to output detailed object comments and a dump file's start and stop time and progress information to standard output.

-X
--no-privileges
--no-acl
Avoids output ACL (give/Undo commands) and table owner relationship information.

-X disable-dollar-quoting
--disable-dollar-quoting
This option turns off the use of dollar characters to surround function bodies. Force them to surround themselves with quotes in the SQL standard string syntax.

-X Disable-triggers
--disable-triggers
This option is only relevant to creating a dump with only data. It tells Pg_dump to include a command to temporarily close the trigger on the target table when recovering data. If you have referential integrity checks or other triggers on the table, and you don't want to overload them when you restore the data, then you should use this option.

Currently, commands issued for--disable-triggers must be done with Superuser. Therefore, you should also declare a super username with-S, or it is best to start the generated script with a superuser identity.

This option is only meaningful for plain text formatting. For an archive format, you can declare this option when calling Pg_restore.

-X Use-set-session-authorization
--use-set-session-authorization
Output the SQL standard SET session AUTHORIZATION command instead of the OWNER to command. Such dump results are more complex, but depending on the history of the objects in the dump, they may not recover correctly.

-Z 0..9
--compress=0..9
Declares the level of compression that is used in those formats that support compression. (Currently, only the customized format supports compression).

The following command-line parameter controls the database as a join parameter.
-H Host
--host=host
Declares the host name of the machine on which the server is running. If the value begins with a slash, it is used as the path to the Unix domain socket. The default is obtained from the PGHOST environment variable, and if you set the environment variable, try a Unix domain socket connection.

-P Port
--port=port
Declares a TCP port or local Unix master socket file handle that the server is listening on and waiting to join. The default is to use the Pgport value of the environment variable (if present), or the compile-time default value.

-u username
To give a user identity join.

-W
Force password prompt. If the server requires password authentication, then this action should occur automatically.

Pg_restore-Restores the PostgreSQL database from a backup file created by Pg_dump.
Pg_restore accepts the following command-line arguments.

FileName
Declares the location of the backup file to be recovered. If no declaration is made, standard input is used.

-A
--data-only
Restore only the data, not the table schema (data definition).

-C
--clean
Clean (remove) The database objects before they are created.

-C
--create
Create the database before restoring it. (If this option appears, the name of the database with-D is used only to emit the original CREATE DATABASE command.) All data is restored to the database in which the name appears in the archive. ）

-D dbname
--dbname=dbname
Joins the database dbname and restores directly to the database.

-E
--exit-on-error
If you encounter an error while sending the SQL command to the database, exit. The default is to continue execution and display an error count at the end of the restore.

-F filename
--file=filename
Declares the output file of the generated script, or the file used for the list when the-l option appears, by default, the standard output.

-F format
--format=format
Declares the format of the backup file. Because Pg_restore automatically determines the format, if declared, it can be one of the following:

T
Backup is a tar archive. Using this format allows you to reorder and/or exclude the table schema elements when restoring the database. It may also limit the data that is loaded at the time of recovery.

C
The backup format is a customized format from the Pg_dump. This is the most flexible format because it allows you to reorder the data, and it also allows you to overload the table schema elements. This format is compressed by default.

-I.
--ignore-version
Ignores database version checking.

-I. Index
--index=index
Only named indexes are restored.

-L
--list
Lists the contents of the backup. The output of this operation can be used to limit and rearrange the recovered items with the-l option.

-L List-file
--use-list=list-file
Restore only the elements within the list-file, in the order in which they appear in the file. You can move individual rows and you can also annotate them by putting '; ' At the beginning of a line. (see below for examples.) ）

-O
--no-owner
Do not output commands that set the object's permissions to match the original database. By default, Pg_restore issues the owner permission for the schema element created by the ALTER OWNER or set session AUTHORIZATION statement. If the initial database connection was not initiated by a superuser (or by the same user who owns all the created objects), the statements will fail. With-O, any user can be used for the initial connection, and the user will have all the created objects.

-P Function-name (Argtype [, ...])
--function=function-name (Argtype [, ...])
Restores only the specified named functions. Be careful to spell the function name and its arguments exactly as they should in the list of contents of the dump.

-R
--no-reconnect
This option has been deprecated, but is still accepted in order to remain backward compatible.

-S
--schema-only
Restore only the table structure (data definition). No data is recovered, and the sequence values are reset.

-S username
--superuser=username
Sets the user name that declares the superuser when the trigger is closed. It is only useful when--disable-triggers is set up.

-T table
--table=table
Restores only the definition and/or data of the table specified by the table.

-T Trigger
--trigger=trigger
Only the specified triggers are recovered.

-V
--verbose
Declares redundancy mode.

-X
--no-privileges
--no-acl
Avoid ACL recovery (grant/revoke command).

-X Use-set-session-authorization
--use-set-session-authorization
Output the SQL standard SET session AUTHORIZATION command, not the OWNER to command. This allows the dump to be better compatible with the standard, but this dump may not be properly recovered based on the history of the objects in the dump.

-X Disable-triggers
--disable-triggers
This option is only relevant when performing data recovery only. It tells Pg_restore to execute some commands to temporarily close a trigger on the target table while loading the data. You can use this option if you have integrity checks or other triggers on the table and you don't want to activate them while loading the data.

Currently, commands issued for--disable-triggers must be issued as Superuser. Therefore, you should also use-s to declare a super username, or better to set up--use-set-session-authorization and run Pg_restore as PostgreSQL Superuser.

Pg_restore also accepts the following command-line arguments as join parameters:

-H Host
--host=host
Declares the host name of the machine on which the server is running. If the value begins with a slash, it is used as a directory for Unix domain sockets. The default is obtained from the PGHOST environment variable (if set), otherwise the Unix domain socket will be attempted.

-P Port
--port=port
Declares a TCP port on which the server listens or a local Unix domain socket file extension. The default is the value of the environment variable Pgport (if set), otherwise the compilation defaults.

-u username
To give a user identity join.