PDF page 18
The following sections describe the major enhancements of the utility commands. #### 3.2.1 psql {#pg9-6-3-2-1-psql} The following features in psql command has been added. ##### Prompt setting {#pg9-6-p18-prompt-setting} The Process ID of the backend process can be set now in the prompt of the psql command. To set the Process ID , Specify the %p to the variable PROMPT1, PROMPT2, PROMPT3. **Example 10.** Specifying the prompt ```text postgres=> \set PROMPT1 '%/(%p)=> ' postgres(2619)=> ``` ##### Backslash commands {#pg9-6-p18-backslash-commands} The following enhancements have been made. **Table 17.** Added and modified backslash commands | \\ev view_name | Added | Change the View definition by an external editor | | --- | --- | --- | | \\svview_name | Added | Show the View definition | | \\sv+view_name | Added | Show the View definition with line numbers | | \\gexec | Added | Run the SQL statements in the output result | | \\errverbose | Added | Show the error information that occurred just before | | \\crosstabviewcolumn_name | Added | Show the cross tabulation | | \\x auto | Changed | Does not use extended table format with EXPLAIN ANALYZE statement | | \\watch and \\pset title | Changed | Show the value of the \\pset title when executing the \\watch command | Command Modification DescriptionPDF page 19
##### Show and edit the VIEW definition. {#pg9-6-p19-show-and-edit-the-view-definition} \ev and \sv command has been added to the psql command. \ev command edits the specified view definition in the editor. \sv command shows the specified view definition. The \sv+ command displays the line number in the view definition. **Example 11.** Reference of the VIEW definition ```text postgres=> CREATE VIEW view1 AS SELECT COUNT(*) cnt FROM data1 ; CREATE VIEW postgres=> \sv+ view1 1 CREATE OR REPLACE VIEW public.view1 AS 2 SELECT count(*) AS cnt 3 FROM data1 postgres=> ``` ##### Execute result(s) of previous query as new queries. {#pg9-6-p19-execute-result-s-of-previous-query-as-new-queries} \gexec command executes the result(s) of the SQL Statement just before as new SQL Statements. This is useful when creating the CREATE statement to generate the object using the SELECT statement. **Example 12.** Executes the output result ```text postgres=> SELECT 'CREATE TABLE data2(c1 NUMERIC)' ; ?column? -------------------------------- CREATE TABLE data4(c1 NUMERIC) (1 row) postgres=> \gexec CREATE TABLE ``` ##### \watch and \pset title {#pg9-6-p19-watch-and-pset-title} At the time of execution in the \watch command, the string specified in the \pset title command is now displayed.PDF page 20
**Example 13.** \\watch and \\pset title ```text postgres=> \pset title 'Demo Title' Title is "Demo Title". postgres=> SELECT COUNT(*) FROM data1 ; Demo Title count --------- 3000000 (1 row) postgres=> \watch 1 Demo Title Wed May 13 12:07:41 2016 (every 1s) count ------- 10000 (1 row) ``` ##### Multiple -c option and -f option {#pg9-6-p20-multiple-c-option-and-f-option} -c option and -f option that specify the SQL statement to be executed at the time of connection, can now be specified multiply. #### 3.2.2 pg_basebackup {#pg9-6-3-2-2-pg-basebackup} --slot (or -S) option that specify the slot name to be used for the backup has been added to pg_basebackup command. If non-existent slot name is specified, a warning will be shown, but the backup process will be executed. When this option is specified along with the --write-recovery-conf (-R) option, primary_slot_name option will be added to the recovery.conf file in the backup destination. In order to use the --slot option, it is required to use --xlog-method = setting of the stream (-Xs) option at the same time.PDF page 21
**Example 14.** Specify the \-\-slot option ```bash $ pg_basebackup -D back -x -v -R --slot=slot_1 ← no -Xs option pg_basebackup: replication slots can only be used with WAL streaming Try "pg_basebackup --help" for more information. $ $ pg_basebackup -D back -v -R --slot=slot_X -Xs ← not exists slot name transaction log start point: 0/4000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: could not send replication command "START_REPLICATION": ERROR: replication slot "slot_X" does not exist transaction log end point: 0/4000130 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: child process exited with error 1 $ $ pg_basebackup -D back -v -R --slot=slot_1 -Xs ← normal execution transaction log start point: 0/6000028 on timeline 1 pg_basebackup: starting background WAL receiver transaction log end point: 0/60000F8 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: base backup completed $ $ cat back/recovery.conf ← Check the recovery.conf file standby_mode = 'on' primary_conninfo = 'user=postgres port=5432 sslmode=disable sslcompression=1' primary_slot_name = 'slot_1' $ $ ls -l back/pg_replslot/ ← check the slot directory total 0 $ ``` ```text The slot is not created at the backup destination for the database cluster. ```PDF page 22
#### 3.2.3 pg_rewind {#pg9-6-3-2-3-pg-rewind} Pg_rewind command can work when the target timeline changes. This feature is not verified. Please refer to the following URL. [http://michael.otacoo.com/postgresql-2/postgres-9-6-feature-highlight-pg-rewind-timeline/](http://michael.otacoo.com/postgresql-2/postgres-9-6-feature-highlight-pg-rewind-timeline/) #### 3.2.4 pg_dump / pg_restore {#pg9-6-3-2-4-pg-dump-pg-restore} The --strict-names option has been added to the pg_dump command and pg_restore command. In addition, -t option of pg_restore command also match relation other than the normal table. This feature is not verified. #### 3.2.5 pgbench {#pg9-6-3-2-5-pgbench} Some of the new features in pgbench command has been added, but it does not have to verify.