PostgreSQL Tutorial (18): Client command (2) _postgresql

Seven, pg_dump:

    pg_dump is a tool for backing up PostgreSQL databases. It can even make a complete and consistent backup while the database is being used concurrently without blocking other users' access to the database. The dump format generated by the tool can be divided into two types, scripts and archive files. The script format is a plain text format containing many SQL commands. These SQL commands can be used to rebuild the database and restore it to the state when the script was generated. This operation needs to be completed using psql. As for the archive format, if you need to rebuild the database, you must use it with the pg_restore tool. During the reconstruction process, you can select the objects to be restored, and you can even reorder the items that need to be restored before the restoration. The command is used as follows:
 

Copy the code:

    pg_dump [option ...] [dbname]
 

    1. List of command line options:
Option Description
-a (-data-only) Output data only, not output mode (data object definition). This option is only meaningful for plain text format. For archive formats, you can specify options when calling pg_restore.
-b (-blobs) Include large objects in the dump.
-c (-clean) Before outputting the SQL command to create a database object, output the SQL command to delete the database object. This option is only meaningful for plain text format. For archive formats, you can specify options when calling pg_restore.
-C (-create) Outputs the command to create a database before reconnecting to the newly created database. For scripts in this format, it doesn't matter which database you connect to before running it. This option is only meaningful for plain text format. For archive formats, you can specify options when calling pg_restore.
-Eencoding creates the dump file with the specified character set.
-ffile Output to the specified file, or to standard output if this option is not present.
-Fformat
p (plain): SQL script file in plain text format (default). c (custom): output a custom archive format suitable for pg_restore. This is the most flexible format, and it allows rearrangement of loaded data and object definitions. This format is compressed by default. t (tar): Output a tar archive suitable for pg_restore. Using this archive allows reordering and / or excluding database objects when restoring the database. At the same time, it may also be possible to limit which data is restored during recovery.

-n schema Dump only the contents of the schema. If this option is not specified, all non-system schemas in the target database are dumped. This option can also be specified multiple times to specify different patterns.
-Nschema does not dump the contents of the matching schema, other rules are consistent with -n.
-o (-oids) Outputs object identifiers (OIDs) for each table as part of the data.
-O (-no-owner) Do not output SQL commands that set object ownership.
-s (-schema-only) Outputs only the object definition (schema), not data.
-Susername Specifies the superuser name to use when closing the trigger. It only matters when using --disable-triggers.
-ttable Outputs data for the table only. It is possible to have multiple tables with the same name in different schemas. If so, all matching tables will be dumped. By specifying this parameter multiple times, you can dump multiple tables at once. You can also specify the same pattern as psql to match more tables. (With regard to pattern, the basic usage is that it can be regarded as a wildcard for unix, that is, * means any character,? Means any single character,. (Dot) means a separator between the schema and the object, such as a * .b * , Indicates a schema starting with a and a database object beginning with b. If there is no. (Dot), it will only represent the database object. Here you can also use basic regular expressions, such as [0-9] for numbers.)
-Ttable Excludes the specified table. Other rules are consistent with the -t option.
-x (-no-privileges) Do not export access information (grant / revoke commands).
-Z0..9 Specifies the compression level used in those formats that support compression. (Currently only custom formats support compression)
--column-inserts The exported data is indicated by the insert into table_name (columns_list) values (values_list) command. Such operations are slower than other operations, but in special cases, such as the position of data table fields may change Or a new field is inserted in the middle of the original field list, etc. Because the columns_list is explicitly specified, the problem of data being imported into the wrong field does not occur during import.
--inserts The exported data is represented by the insert command, not the copy command. Even if insert is slower than copy, it is more meaningful for importing into other non-PostgreSQL databases in the future.
--no-tablespaces Do not output the command to set the tablespace. If this option is specified, all objects will be restored to the default tablespace when pg_restore is executed.
--no-unlogged-table-data For data tables that are not included in the log (unlogged), its data will not be exported. Whether to export its schema information depends on other options.
-h (-host = host) Specify the host name of the PostgreSQL server.
-p (-port = port) Specifies the listening port of the server. If not specified, the default is 5432.
-U (-username = username) The login user name for this operation. If the -O option is not specified, the Owner of this database will be the login user.
-w (-no-password) If the currently logged in user does not have a password, you can specify this option to log in directly.
2. Application examples:
Copy the code:

    # -h: The host of the PostgreSQL server is 192.168.149.137.
    # -U: The login user is postgres.
    # -t: Export the data table whose table name starts with test, such as testtable.
    # -a: Only export the data, not the schema information of the object.
    # -f: The output file is my_dump.sql in the current directory
    # mydatabase is the target database for this operation.
    /> pg_dump -h 192.168.149.137 -U postgres -t test * -a -f ./my_dump.sql mydatabase
    # -c: First output the SQL command to delete the database object, and output the SQL command to create the database object, which is very convenient for deploying a clean initial system or setting up a test environment.
    /> pg_dump -h 192.168.220.136 -U postgres -c -f ./my_dump.sql mydatabase
    #Export the information of mydatabase database. You can re-specify the database when importing through the psql command, such as: /> psql -d newdb -f my_dump.sql
    /> pg_dump -h 192.168.220.136 -U postgres -f ./my_dump.sql mydatabase
    #Export mode is my_schema and database object names starting with test, but not including my_schema.employee_log object.
    /> pg_dump -t 'my_schema.test *' -T my_schema.employee_log mydatabase> my_dump.sql
    #Export all database objects in east and west modes. The following two commands are equivalent, except that the latter uses regularity.
    /> pg_dump -n 'east' -n 'west' mydatabase -f my_dump.sql
    /> pg_dump -n '(east | west)' mydatabase -f my_dump.sql
Eight, pg_restore:


    pg_restore is used to restore any non-plain text format files exported by pg_dump. It rebuilds the database to the state it was in when it was saved. For archived files, pg_restore can perform selective recovery, and even rearrange the order of data before recovery.

    pg_restore can operate in two modes. If a database is specified, the archive is restored directly to that database. Otherwise, you must manually create the database, and then restore the data to the newly created database through pg_restore. The command is used as follows:
 

Copy the code:

    pg_restore [option ...] [filename]
 

    1. List of command line options:
 

Option Description
filename specifies the backup file to restore, if not specified, standard input is used.
-a (-data-only) Restores only the data, not the table schema (data object definition).
-c (-clean) Clean (delete) database objects before creating them.
-C (-create) Creates the database before restoring it. (When using this option, the database name needs to be specified by the -d option. This option only executes the most basic CREATE DATABASE command. It should be noted that all data in the archive will be restored to the database specified in the archive. .
-ddbname Establishes a connection with the database dbname and restores the data directly to the database.
-e (-exit-on-error)
If you encounter an error while sending a SQL command to the database, exit. The default is to continue execution and display an error count at the end of the restore.

-Fformat Specifies the format of the backup file. Since pg_restore automatically determines the format, specifying the format is not necessary. If specified, it can be in one of the following formats: t (tar): Use this format to allow reordering and / or exclude table schema information when restoring the database, and possibly limit the data loaded during restore. c (custom): This format is a custom format from pg_dump. This is the most flexible format because it allows data to be reordered and table schema information to be reloaded. This format is compressed by default.
-I index Restore only the specified index.
-l (-list) List the contents of the backup. The output of this operation can be used as input for the -L option. Note that if the filtering options -n or -t are used with the -l option, they will also limit the entries listed.
-L list-file restores only the entries listed in list-file, the order of restoration is the order in which each entry appears in the file, you can also manually edit the file, and rearrange the positions of these entries, and then proceed Redo operation, in which the comment lines starting with a semicolon (;) are not imported.
-n namespace Restores only database objects of the specified schema. This option can be used in conjunction with the -t option to restore the specified data object.
-O (-no-owner) Do not output SQL commands that set object ownership.
-Pfunction-name (argtype [, ...])
Only the specified named functions are restored. The name should be exactly the same as in the list of contents of the dump.

-s (-schema-only) Restores only the table structure (data definition). The data is not restored and the sequence values are reset.
-Susername Specifies the superuser name to use when closing the trigger. It only matters when using --disable-triggers.
-t table Restores only the schema and / or data of the specified table. This option can also be used with the -n option to specify the schema.
-x (-no-privileges) Do not restore access information (grant / revoke commands).
-1 (-single-transaction) executes a resume command in a single transaction. This option implicitly includes the --exit-on-error option.
--no-tablespaces Do not output the command to set the tablespace. If this option is specified, all objects will be restored to the default tablespace when pg_restore is executed.
--no-data-for-failed-tables By default, even if the table creation fails, if the table already exists, the data loading operation will not stop. As a result, it is easy to cause a lot of duplicate data to be inserted Into the table. With this option, the load of the data table is ignored if any errors occur for the table.
--role = rolename Perform a restore operation with the specified role name. Generally speaking, if the connection role does not have sufficient permissions for this recovery operation, you can use this option to switch to a role with sufficient permissions after the connection is established.
-h (-host = host) specifies the host of the PostgreSQL server Machine name.
-p (-port = port) Specifies the listening port of the server. If not specified, the default is 5432.
-U (-username = username) The login user name for this operation. If the -O option is not specified, the Owner of this database will be the login user.
-w (-no-password) If the currently logged in user does not have a password, you can specify this option to log in directly.
 2. Application examples:
 

Copy the code:

    #First use the createdb command to log in as the myuser user and create newdb with recovered data
    /> createdb -U myuser newdb
    #Use the -l option of the pg_restore command to export a detailed list of exported database objects in the my_dump.dat backup file.
    /> pg_restore -l my_dump.dat> db.list
    /> cat db.list
    2; 145344 TABLE species postgres
    4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    8; 145416 TABLE ss_old postgres
    10; 145433 TABLE map_resolutions postgres
    #Modify the contents of the above list file into the following form.
    #The main modification is to comment out the three database objects numbered 2, 4, and 8, while placing the number 10 object at the head of the file, so that based on the list
    #File import, the three objects 2, 4, and 8 will not be imported, during the restoration process, the data of the object number 10 is imported, and then the data of object 6 is imported.
    /> cat new_db.list
    10; 145433 TABLE map_resolutions postgres
    ; 2; 145344 TABLE species postgres
    ; 4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    ; 8; 145416 TABLE ss_old postgres
    #The database specified during recovery is newdb, which database objects are imported and the import order will be imported according to the rules suggested in the new_db.list file.
    /> pg_restore -d newdb -L new_db.list my_dump.dat
Nine, psql:

    The interactive terminal of PostgreSQL is equivalent to sqlplus in Oracle.
    1. List of common command line options:

 

Option Description
-c command Specify psql to execute a SQL command (enclosed in double quotes), and exit after execution.
-d dbname The name of the database to be connected.
-E Echo the actual query generated by \ d and other backslash commands.
-f filename Use the data in the filename file as the command input source instead of reading the query interactively. After processing the file, psql ends and exits.
-h hostname declares the host name of the running server
-l List all available databases and exit.
-L filename Output all query records to the file filename except the normal output source.
-o filename Redirect all queries to file filename.
-p port Specify the listening port of the PostgreSQL server.
-q --quiet Causes psql to perform its tasks quietly. By default psql will print a welcome message and many other messages.
-t --tuples-only Turn off printing of column name and result row count footnotes.
-U username Use the user username instead of the default user to establish a connection to the database.
 2. Application examples:
 

Copy the code:

    #First use the createdb command to log in as the myuser user and create newdb with recovered data
    /> createdb -U myuser newdb
    #Use the -l option of the pg_restore command to export a detailed list of exported database objects in the my_dump.dat backup file.
    /> pg_restore -l my_dump.dat> db.list
    /> cat db.list
    2; 145344 TABLE species postgres
    4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    8; 145416 TABLE ss_old postgres
    10; 145433 TABLE map_resolutions postgres
    #Modify the contents of the above list file into the following form.
    #The main modification is to comment out the three database objects numbered 2, 4, and 8, while placing the number 10 object at the head of the file, so that based on the list
    #File import, the three objects 2, 4, and 8 will not be imported, during the restoration process will first import the data of the object number 10, and then import the data of object 6.
    /> cat new_db.list
    10; 145433 TABLE map_resolutions postgres
    ; 2; 145344 TABLE species postgres
    ; 4; 145359 TABLE nt_header postgres
    6; 145402 TABLE species_records postgres
    ; 8; 145416 TABLE ss_old postgres
    #The database specified during recovery is newdb, which database objects are imported and the import order will be imported according to the rules suggested in the new_db.list file.
    /> pg_restore -d newdb -L new_db.list my_dump.dat

    3. List of built-in commands:
    The format of the psql built-in command is a backslash followed by a command verb, followed by any arguments. Parameters can be separated from command verbs and other parameters by white space. If the parameter contains white space, the parameter must be enclosed in single quotes. If the parameter contains single quotes, it must be escaped with a backslash. The parameters in single quotes also support escape keywords similar to the C language printf function, such as \ t, \ n, etc.

Command Description
\ a If the current table output format is misaligned, switch to aligned. If it is aligned, switch to misaligned.
\ cd [directory] Changes the current working directory to directory. Without parameters, it changes to the current user's home directory.
\ C [title] Add a title to the query result. If there is no parameter, cancel the current title.
\ c [dbname [username]] Connects to the new database and disconnects the current connection. If the dbname parameter is-, it means that the current database is still connected. If username is omitted, it means that the current username will continue to be used.
\ copy The parameters are similar to SQL copy, and the function is almost equivalent to SQL copy. One important difference is that the built-in command can export the contents of the table to the local, or import from the local to the table specified by the database, while the SQL copy is Export the data in the table to a file on the server, or import the file from the server into a data table. It can be seen that the efficiency of SQL copy is better than this built-in command.
\ d [pattern] Displays database objects that match the pattern, such as tables, views, indexes, or sequences. Show all columns, their type, tablespace (if not the default), and any special attributes.
\ db [pattern] List all available tablespaces. If pattern is specified, only those table spaces that match the pattern are displayed.
\ db + [pattern] Compared with the previous command, it also adds permission information for each tablespace.
\ df [pattern] Lists all available functions, along with their parameters and the data types returned. If pattern is specified, only matching (regular expression) functions are displayed.
\ df + [pattern] Compared to the previous command, additional information is displayed showing each function, including language and description.
\ distvS [pattern] This is not a separate command name: the letters i, s, t, v, S stand for index, sequence, table, view, and system table . You can declare some or all of these letters in any order to get a list of these objects.
\ dn [pattern] List all available patterns. If pattern is specified, only the pattern names that match the pattern are listed.
\ dn + [pattern] Compared with the previous command, it also adds the permissions and comments for each object.
\ dp [pattern] Generate a list of available tables and their associated permissions. If pattern is specified, only tables whose names match the pattern are listed.
\ dT [pattern] List all data types or only those that match pattern.
\ du [pattern] List all configured users or only those users who match pattern.
\ echotext [...] Print parameters to standard output, separated by a space and followed by a new line. For example: \ echo `date`
\ g [{filename || command}] sends the contents of the current query result buffer to the server and stores the output of the query to an optional filename or directs the output to a separate Unix shell executing the command
\ ifilename reads from file filename and executes the query as if it were entered from the keyboard.
\ l Lists the names of all databases on the server, their owners, and character set encoding.
\ o [{filename || command}] saves the subsequent query results to the file filename or directs the subsequent query results to a separate shell command.
\ p Print the current query buffer to standard output.
\ q Quit the psql program.
\ r Resets (clears) the query buffer.
\ s [filename] Print or save the command line history to filename. If filename is omitted, history is output to standard output.
\ t Toggles whether to output column / field name headers and row count footnotes.
\ w {filename || command} Output the current query buffer to the file filename or direct it to the Unix command command.
\ z [pattern] Generate a list of all tables, views, and sequences in the database with a list of access rights. If any pattern is given, it is treated as a regular expression, showing only matching tables, views, and sequences.
\! [command] Returns to a separate Unix shell or executes a Unix command command. The parameters are not interpreted further, the shell will see all the parameters.
 4. Example of built-in command application:

    In psql, most of the built-in commands are relatively easy to understand, so here are just a few commands that I personally think are relatively easy to confuse.
 

Copy the code:

    # \ c: The horizontal line (-) indicates that the current database is still connected, and myuser is the new username.
    postgres = # \ c-myuser
    Password for user myuser:
    postgres => SELECT user;
     current_user
    --------------
     myuser
    (1 row)
    # Execute any SQL statement.
    postgres = # SELECT * FROM testtable WHERE i = 2;
     i
    ---
     2
    (1 row)
    # \ g command will output the result of the previous SQL command to the specified file.
    postgres = # \ g my_file_for_command_g
    postgres = # \! cat my_file_for_command_g
     i
    ---
     2
    (1 row)
    # \ g command will end the last SQL command
Output from the pipeline to the specified shell command, such as cat.
     postgres = # \ g | cat
      i
     ---
      2
     (1 row)
     # \ p Print the last SQL command.
     postgres = # \ p
     SELECT * FROM testtable WHERE i = 2;
     # \ w Output the last SQL command to the specified file.
     postgres = # \ w my_file_for_option_w
     postgres = # \! cat my_file_for_option_w
     SELECT * FROM testtable WHERE i = 2;
     # \ o Contrary to \ g, this command will output the output of the following psql command to the specified file until the next independent \ o
     # Subsequent command results will no longer be output to this file.
     postgres = # \ o my_file_for_option_o
     postgres = # SELECT * FROM testtable WHERE i = 1;
     # The results of the commands following the termination are also output to the my_file_for_option_o file.
     postgres = # \ o
     postgres = # \! cat my_file_for_option_o
      i
     ---
      1
     (1 row)