4 Utilities

Source chapter 3.2 for PostgreSQL 9.6.

Beta 1Source pages: PDF 18-22Original PDF on GitHub

PDF page 18

The following sections describe the major enhancements of the utility commands.

3.2.1 psql

The following features in psql command has been added.

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

postgres=> \set PROMPT1 '%/(%p)=> '
postgres(2619)=>

Backslash commands

The following enhancements have been made.

\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

Table 17 Added and modified backslash commands

Command Modification Description

PDF page 19

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

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.

\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

postgres=> SELECT 'CREATE TABLE data2(c1 NUMERIC)' ;
            ?column?
--------------------------------
 CREATE TABLE data4(c1 NUMERIC)
(1 row)
postgres=> \gexec
CREATE TABLE

\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

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

-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

–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

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

The slot is not created at the backup destination for the database cluster.

PDF page 22

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/

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

Some of the new features in pgbench command has been added, but it does not have to verify.