New in PostgreSQL 9.3: Client Application improvements

Long long time. I haven't blogged at all. So, I have decided whenever I get chance I will blog about the cool things going in Database Technology market. I have lot of topics to share. Some got lost in air. Some I retained in mind. Before I lost more. I decided to continue from PostgreSQL 9.3 features and accordingly will go towards about new things as it starts to popup. PostgreSQL users must have heard about release of PostgreSQL 9.3 and they must have started testing PostgreSQL 9.3.

In today series, I am going to blog about client application improvements done in PostgreSQL 9.3.

Lets take it one by one.

1. New binary/command pg_isready. 

PostgreSQL 9.3 is coming with new binary called pg_isready. this command helps user to know the status of server/PostgreSQL cluster.

Before 9.3, user used to have their own tool/script to check the status of PostgreSQL, for that they used execute some random SQL like "SELECT 1" and if the status is successful, PostgreSQL is running or if status is unsuccessful, then PostgreSQL 9.3.
However, pg_isready utility/command is much better than old methods in the sense it covers following scenario for checking the status of PostgreSQL 9.3:

   a. Check if PostgreSQL is ready and accepting connections.
   b. Check if PostgreSQL is ready and connection is acception/rejecting.
   c. Check if PostgreSQL is non-responsive.
   d. it provide exit code and also has option being quiet about message, which be useful for users using exit code which create their own health check and messaging in application.

Different exit codes and Message use by pg_isready to tell the status of PostgreSQL is given below:

1. exit code 0, 
   Message: "host:port - accepting connections"
2. exit code: 1
   Message: "host:port - rejecting connections"
3. exit code: 2
   Message: "host:port - no response"

usage example is given below:[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_isready -h localhost

localhost:5445 - accepting connections 

2. --table (multiple table option) in pg_restore, clusterdb, reindexdb and vacuumdb 
--table (multiple table option) was missing in pre-9.3 PostgreSQL. I personally missed an option for specifying multiple tables in single command. Now user can use multiple table option with following commands in PostgreSQL 9.3 onwards. Example is given below:

Till 9.2, when user uses command like

pg_restore -t emp -t dept test.dmp

user will get information of table which is last table mention in command, as given below:

[root@ip-10-159-51-181 ~]# pg_restore --version
pg_restore (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_restore -Fc -t emp -t dept test.dmp 
--
-- EnterpriseDB database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog, sys;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace: 
--

CREATE TABLE dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13)
);


ALTER TABLE public.dept OWNER TO enterprisedb;

--
-- EnterpriseDB database dump complete
--

You can see in pre-9.3, pg_restore has shown the result of last table in list in pg_restore command.

Now in 9.3, user can mention multiple table names and will be able to listed all tables in command:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_restore  -Fc -t emp -t dept test.dmp 
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace: 
--

CREATE TABLE dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13)
);


ALTER TABLE public.dept OWNER TO enterprisedb;

--
-- Name: emp; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace: 
--

CREATE TABLE emp (
    empno numeric(4,0) NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric(4,0),
    hiredate timestamp without time zone,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0),
    CONSTRAINT emp_sal_ck CHECK ((sal > (0)::numeric))
);


ALTER TABLE public.emp OWNER TO enterprisedb;

--
-- PostgreSQL database dump complete
--

similarly this option is enable for reindexdb. Example is given below:

Before 9.3:

[root@ip-10-159-51-181 ~]# reindexdb --version 
reindexdb (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# reindexdb -t emp -t dept -e
REINDEX TABLE dept;

In 9.3:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/reindexdb  -t emp -t dept -e
REINDEX TABLE emp;

REINDEX TABLE dept;

User can observer usage of this switch for clusterdb and vacuumdb too.
Addition of this, it has reduced lot of effort for defining script which can recursively call the reindexdb/vacuumdb/clusterdb for each tables.

 9.3 gives option to user for using these binaries more efficiently in their environment and tools.

3. --dbname/-d option for binaries: pg_dumpall, pg_basebackup and pg_receivexlog. 

Before 9.3, --dbname and -d option was available in psql, clusterdb, reindexdb command etc.
However this option wasn't available for command like pg_dumpall, pg_basebackup and pg_receivexlog, which made users to define connection string for this binaries differently from other binaries. For example:

Before 9.3:

[root@ip-10-159-51-181 ~]# pg_basebackup --version
pg_basebackup (PostgreSQL) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_basebackup --help|grep dbname

In 9.3, user can see this option available:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_basebackup --help|grep -i dbname
  -d, --dbname=CONNSTR   connection string

4. remove warning message for psql, when psql uses to connect to old server. 

Example is given below:
Before 9.3:

User used to get warning message something like given below:

[root@ip-10-159-51-181 ~]# psql -p 5444
psql (9.2.4.10, server 9.1.9.17)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
Type "help" for help.

Now 9.3 onwards:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql  -p5444
psql (9.3beta2, server 9.1.9.17)
Type "help" for help.

worktest=#  

Note:: Warning message is kept if user uses psql to connect to higher major version of postgresql.

5. psql --single-transaction mode for STDIN: 

Before 9.3, --single-transaction mode doesn't work for STDIN.
For example if you have a set of commands which you want to pass to psql through STDIN, then it doesn't used to work.
To make it work, people used to put the commands in a file and then they used to use the --single-transaction mode.
However 9.3 onwards, it won't require any more. Example is given below:

Before 9.3:

[root@ip-10-159-51-181 ~]# psql --version
psql (EnterpriseDB) 9.2.4.10

[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|psql --single-transaction
ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
                     ^
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE a;
                      ^
CREATE DATABASE

Above example shows that we there was error on 1st and 2nd command, however psql executed third command successfully.

worktest=# select datname from pg_database where datname='test';
 datname 
---------
 test
(1 row)

9.3 on wards:

[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|/usr/pgsql-9.3/bin/psql --single-transaction
ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
                     ^
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE a;
                      ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
[root@ip-10-159-51-181 ~]# 

You can see transaction was aborted when user uses the STDIN with psql --single-transaction.

6. Other improvements in psql.

In 9.3, psql has further improved for tab completion and patter searching. This has been done by tuning of functions cost settings.

7. New addition in Backslash commands:

9.3 also coming with some new backslash commands. Those are going to help in lot ways for user to monitor and using psql in efficient way in there script/monitoring.

Following are new in backslash commands:

i. \watch. 
Similar to Linux system watch command, psql is also coming with \watch command. Using this command user can execute current buffer query repeatedly.
For example, if user wants to monitor/watch the sessions made to postgresql database, then he/she can use command something like given below in psql to do that:

a. Start a psql session as given below:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -p 5445 -U enterprisedb -d worktest
psql (9.3beta2, server 9.2.4.10)
Type "help" for help.

worktest=#

b. Now execute a query which you want to use repeatedly as given below

worktest=# select datname, usename, application_name, query from pg_stat_activity ;
 datname  |   usename    | application_name |                                  query                
                   
----------+--------------+------------------+-------------------------------------------------------
-------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from 
pg_stat_activity ;
(1 row)

c. Now use command "\watch [seconds]"

worktest=# \watch 1
                                       Watch every 1s Sat Jul 27 04:15:21 2013

 datname  |   usename    | application_name |                                  query                                   
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

                                       Watch every 1s Sat Jul 27 04:15:22 2013

 datname  |   usename    | application_name |                                  query                                   
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

Or user combine above too in one command to use it something like given below:

worktest=# select datname, usename, application_name, query from pg_stat_activity \watch 1
 datname  |   usename    | application_name |                                  query                
                   
----------+--------------+------------------+-------------------------------------------------------
-------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from 
pg_stat_activity ;
(1 row)

                                       Watch every 1s Sat Jul 27 04:36:00 2013

 datname  |   usename    | application_name |                                  query                                   
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

In above, I wanted to use query to monitor the pg_stat_statement every seconds.

 \watch commands interrupted/stop in following condition:

if user has interrupted the running watch.
if query fails.

ii. \gset command to store query results in psql variables.

New command is added in backslash. This is very interesting command and user can use it in multiple purpose. This backslash command allows users to set value of a variable based on single row/output of SQL.
If SQL returns multiple output, then \gset will send proper message and will not set variable.

There are multiple example of using this backslash, for example you can store the output of now query in a variable and can use in psql session as given below:

worktest=# select now() as timestamp
worktest-# \gset
worktest=# \echo :timestamp
27-JUL-13 04:53:13.214203 -04:00
worktest=# 

Some, user like me, can also use this command to prepare dynamic commands and can execute in session.
For example I have users like test which I want to drop from PG instance, so, I can do something like given below:

worktest=# SELECT replace(array_to_string(ARRAY(SELECT 'DROP USER '||usename||';' FROM pg_user where usename ~ 'test'),', '),',','') as drop_test_user
worktest-# \gset
worktest=# \echo "Verify the Drop test user command"
"Verify the Drop test user command"
worktest=# \echo :drop_test_user
DROP USER test; DROP USER test_su;
worktest=# :drop_test_user
DROP ROLE
DROP ROLE

iii. improvement in \conninfo to show ssl information 

Before 9.3, backslash command conninfo used to show information about user,database, port and host. Even if user is using ssl connection. However psql in 9.3 will give ssl information too. Example is given below:
Before 9.3

edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".

In 9.3:

edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

iv. database name patter support in \l 

Backslash \l command people use to list the database in psql.
However, before 9.3, this command didn't have the pattern search support.
Now, in 9.3, this command has been improved in terms of displaying/listing the database based on pattern provided by user. Snapshot is given below:

Before 9.3:

edb=> \l pe*
                                        List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges       
-----------+--------------+----------+-------------+-------------+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 pem       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
           |              |          |             |             | pem_user=Tc/enterprisedb     +
           |              |          |             |             | pem_agent=Tc/enterprisedb
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb              +
           |              |          |             |             | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb              +
           |              |          |             |             | enterprisedb=CTc/enterprisedb
(5 rows)

\l: extra argument "pe*" ignored

As you can see it has ignored the pattern based listing and listed all database.

Now in 9.3:

edb=> \l pe*
                                     List of databases
 Name |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges       
------+--------------+----------+-------------+-------------+-------------------------------
 pem  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
      |              |          |             |             | pem_user=Tc/enterprisedb     +
      |              |          |             |             | pem_agent=Tc/enterprisedb
(1 row)

Thats really a good enhancement in backslash command.
 iv. Fix in "\g filename", which was affecting subsequent commands after an error. 

Before 9.3, if "\g filename" commands fails, then it was also affecting the execution of query after this backslash command.
For example, if user executes query something like given below, then subsequent query which output user doesn't want to store in a file, will go in the filename mentioned in first command:

worktest=# select 'a'/1 \g /tmp/test
ERROR:  invalid input syntax for integer: "a"
LINE 1: select 'a'/1 
               ^
worktest=# select 1/2;
worktest=# 

As you can see 1st SQL failed. However second SQL output not displayed on screen and it went in /tmp/test file. which is inconsistent.

Now in 9.3 this has been fixed:

worktest=# select 'a'/1 \g /tmp/test
ERROR:  invalid input syntax for integer: "a"
LINE 1: select 'a'/1 
               ^
worktest=# select 1/2;
        ?column?        
------------------------
 0.50000000000000000000
(1 row)

v. Improvement in \df+ command to show Security label. 
If user creates following function with security definer

CREATE FUNCTION test_func() 
RETURNS integer
SECURITY DEFINER
AS
$$ SELECT 1;
$$ language sql;

before 9.3, \df+ command never used to give information on SECURITY label. Like in above case, SECURITY DEFINER. Below is output

before 9.3:

-[ RECORD 1 ]-------+-------------
Schema              | oma
Name                | test_func
Result data type    | integer
Argument data types | 
Type                | normal
Volatility          | volatile
Owner               | enterprisedb
Language            | sql
Source code         |  SELECT 1;
                    | 
Description         | 

In 9.3:

Schema              | oma
Name                | test_func
Result data type    | integer
Argument data types | 
Type                | normal
Security            | definer
Volatility          | volatile
Owner               | enterprisedb
Language            | sql
Source code         |  SELECT 1;
                    | 
Description         |

In above you can see column name Security.

8. New improvement psql Output: 

9.3 is also coming with some new improvements in output psql commands below are details of those:

i. latex-longtable support and border=3 style for latex output. 

Those who are not familiar with LaTex, I would recommend following two links:

        http://www.latex-project.org
        http://en.wikipedia.org/wiki/LaTeX
      

So,those, who are familiar with Latex, for them this new feature will be helpful.
latex-longtable allows tabular format output to in multiple pages. With this there border=3 defined for latex output in psql 9.3

ii. --tuples-only (-t) with --expanded (-x) not to show "(No rows)" 

Before 9.3, if someone uses --tuples-only with --expanded switch in psql and query executed doesn't return any record,then combination two switches used to print "(No rows)". As given below:

[root@ip-10-159-51-181 ~]# psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
(No rows)
     

which used to give some inconvenience in scripting psql.
However now, 9.3 onwards, if query doesn't return any record/row, then psql will return nothing. As given below:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
[root@ip-10-159-51-181 ~]# 

which helps in better scripting.

ii. --no-align (-A) with --expanded (-x) not to print empty lines. 

Before 9.3, combination of --no-align (-A) with --expanded (-x) used to print empty line, if query doesn't return any value. However this is fixed. In 9.3 it will return nothing, as shown below:

Before 9.3

[root@ip-10-159-51-181 ~]# psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A

[root@ip-10-159-51-181 ~]# 

In 9.3:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A
[root@ip-10-159-51-181 ~]#

From scripting perspective, this is good improvement.


9. Features/imporvement in pg_dump i. parallel dump option using --jobs in pg_dump 

9.3 has added new performance improvement in pg_dump.

pg_dump can be faster in unloading data from PG database. It has been improved to do with parallel jobs.

There are some notes, which user should know:

  1. parallel dump is supported if user uses dump format directory. i.e -Fd.
  2. parallel dump can increase load on server, so user has to be cautious about choosing number of jobs for pg_dump.
  3. --jobs will open a n+1 connections to database, so, user would like to set the max_connections appropriately. Out of n+1 connections, one connection will be used by master process of pg_dump and n connections will be used by master workers.
  4. Worker process in pg_dump takes SHARED LOCK with NOWAIT option. so, if there is any exclusive lock on table then worker process will not be able to acquire SHARED LOCK will exit and accordingly inform to master process,which will abort the pg_dump.
  5. for consistent backup, database needs to support synchronized snapshots which was introduced in 9.2. Therefore if user is planning to use pg_dump of 9.3 to take backup of pre-9.2 postgresql, then they have to make sure that database content doesn't change.
 

Example of parallel dump is given below:

pg_dump -U username -j4 -Fd -f  databasename

ii. --dbname switch in pg_dump.

Before 9.3, pg_dump didn't have the --dbname option. Since it was assummed that user will use database name at the end of pg_dump, as given below:

   
pg_dump -U username -p port -h host [database name]

To keep the consistency of connection option with other utilities/binaries, in 9.3 --dbname option has been introduced. Now, user can also execute command like given below:

     pg_dump -U username -p port -h host -d [database name]
   

10. New in initdb

9.3 has also added new functionality and feature in initdb command too.

i. fsync the newly created data directory. 

In 9.3, initdb now make sure data directly is safely written to disk. which makes data directory created by initdb is more durable. There will be slight performance impact of using default fysnc. However user can disable this option by choosing --nosync (-N) option, which is not recommended.

Below is some stats:

With default:
real 0m4.304s
user 0m2.736s
sys 0m1.604s

with --nosync
real 0m4.129s
user 0m2.667s
sys 0m1.493s

you can see there is not much difference as per performance.

ii. --sync-only option in initdb. 

9.3 has also added new switch called sync-only. Using this option user can make sure existing data directory to be written safely to disk. Example of usage is given below:

-bash-4.1$ /usr/pgsql-9.3/bin/initdb --sync-only -D /var/lib/pgsql/test
syncing data to disk ... ok
-bash-4.1$ 

iii. warning message if initdb is used to place data directory in top of file filesystem mount point.

In 9.3, initdb has been made more sensible on warning user,if user is trying to create data directory top of filesystem mount point. Following are some warning which user will get:

It contains a dot-prefixed/invisible file, perhaps due to it being a mount point.
It contains a lost+found directory, perhaps due to it being a mount point.
Using a mount point directly as the data directory is not recommended.                          Create a subdirectory under the mount point

Enjoy Learning more about 9.3!!!