Tag Archives: PostgreSQL

How to export query result from pgadmin IV to excel sheet

To export the query result to excel in pgadmin-IV

1) Execute the query
2) press F8, it will export the result to XL(csv format) sheet.

pg_archivecleanup to clean old archive logs in PostgreSQL

pg_archivecleanup is designed to be used as an archive_cleanup_command to clean up WAL file archives when running as a standby server. pg_archivecleanup can also be used as a standalone program to clean WAL file archives.

If you are using it in the stadnby server (where stadnby server configured with log-shipping) then put below option in recovery.conf in stadnby server.

archive_cleanup_command = ‘pg_archivecleanup archivelocation %r’

If you want to use the pg_archivecleanup command to clean the archive logs in a standlone node use the below command. When used as a standalone program all WAL files logically preceding the restartwalfile will be removed from archivelocation.

pg_archivecleanup [ option … ] archivelocation restartwalfile


pg_archivecleanup -d /home/postgres/archive 000000010000003700000010.00000020.backup

This will remove all the logfiles which are older than “000000010000003700000010”

So After completion of the base backup we can use this command to clean the old archivelogs.

connection pooling for postgresql using pgbouncer

Creating a database connection and closing it is a resource intensive task. If we are using any web application server which frequently connects and disconnects from database server will take lot of resources and time. To improve the performance and reduce the connection establishment time we use the connection pooling mechanism where there are bunch of existing connections to the database and application can use one of the connection, after completion of the work the connection is given back to the pool.

Pgbouncer is a light weight connection pooler where it caches the database connections. Applications will connect to pgbouncer on 6432 port (default), pgbouncer will create a new connection to the database server or it will use an existing connection.
Installation of PGBouncer:
Download the latest version from(https://pgbouncer.github.io/downloads).

PgBouncer depends on few things to get compiled:

Install all the above packages.
# Create directory to install pgbouncer
mkdir /home/postgres/pgbouncer 

./configure –prefix=/home/postgres/pgbouncer


make install
Configuration of PGBouncer:
Login as admin user to database, execute below command and create the users list file which contain the user name and password.
SELECT ‘”‘ || rolname || ‘” “‘ ||
CASE WHEN rolpassword IS null THEN ” ELSE rolpassword END || ‘”‘
FROM pg_authid
TO ‘/home/postgres/pgbouncer/userlist.txt ‘;
PGBouncer have pgbouncer.ini file which contain the all the configuration parameters. Below are some of important configuration parameters to start the Pgbouncer.
Open the file 
“/home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini” and set the below parameters. The file contain different headers like [databases], [authentication] etc.
Below “[databases]” we need to configure the databases for which we need to create the connection pools. These are the key value pairs to connect to the database server.

postgres=host= port=5432 user=postgres password=postgres dbname=postgres

This is like a pool of connections are created for the postgres database with postgres user.
Under “Administrative settings” we have logfile location and listener addresses etc.
logfile = /home/postgres/pgbouncer/pgbouncer.log
pidfile = /home/postgres/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
Under “Authentication settings”
auth_type = md5
auth_file = /home/postgres/pgbouncer/userlist.txt
auth_hba_file = /opt/PostgreSQL/9.6/data/pg_hba.conf
auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1
admin_users = postgres
pool_mode = session
Under “Connection limits” (This depends on client requirement)
max_client_conn = 1000
default_pool_size = 200
By using the above configuration there are connection pool for postgres database with postgres user.
auth_file : This parameter is the list of users in the database with their passwords.
auth_hba_file : This is same as pg_hba.conf of PostgreSQL database.
auth_query : This is the query used to get the password of the user if there is no entry for it in “userlist.txt”.
admin_users : This is the user name who can administrate the pgbouncer.
If you want to create a connection pool for database for each user existing in the database use the parameter “auth_user” in the [databases]. If the user entered by the client is not in userslist.txt then the username and password can get by pgbouncer from database cluster using the query mentioned in “auth_query”.
postgres = host= port=5432 dbname=postgres auth_user=postgres
Authentication in pgbouncer:-
Pgbouncer has “auth_file”(user list) which contain the list of users and passwords which can be used by the pgbouncer to verify the user.
If you have set the auth_user parameter in the databses part, then it will get the password of the user from database cluster by executing the query.
Starting and Stopping pgbouncer:-
./pgbouncer -d /home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini &
./pgbouncer -R -d /home/postgres/pgbouncer/share/doc/pgbouncer/pgbouncer.ini &
Connecting to pgbouncer to administer:-
psql -p 6432 pgbouncer
pgbouncer=# show help;
NOTICE:  Console usage
        SET key = arg
        PAUSE []
        RESUME []

Convert Bytea to text in PostgreSQL

One of my friend asked me a question that , He has stored XML data in a table with bytea column and he want to get the data back as XML. We can use the “encode function” to get the same.

postgres=# create table test(
name bytea);

postgres=# insert into test values (”);

postgres=# insert into test values (‘Srinivas’);

postgres=# select * from test;
(2 rows)

postgres=# select encode(name,’escape’) from test;
(2 rows)

Query to get all user defined objects in PostgreSQL

To get all the user defined objects in a database in PostgreSQL use the below query. This will list tables , views , indexes, constraints etc

SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,  c.relname AS “OBJECT_NAME”,relkind as “OBJECT_TYPE”,
CASE c.relkind 
    WHEN ‘v’ THEN ‘VIEW’
    WHEN ‘m’ THEN ‘VIEW’
    WHEN ‘i’ THEN ‘INDEX’ 
FROM pg_catalog.pg_class c 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.conname as “OBJECT_NAME”, ‘c’ as “OBJECT_TYPE”,’CONSTRAINT’ as “DESCRIPTION”
from pg_catalog.pg_constraint c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.connamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
select ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,c.proname as “OBJECT_NAME”, ‘f’ as “OBJECT_TYPE”,’FUNCTION’ as “DESCRIPTION” 
from pg_catalog.pg_proc c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.pronamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’)
SELECT ‘processsuitedb’as “DBNAME”,n.nspname as “SCHEMA_NAME”,t.tgname as “OBJECT_NAME”, ‘t’ as “OBJECT_TYPE”,’TRIGGER’ as “DESCRIPTION”   
FROM pg_catalog.pg_trigger t
join pg_catalog.pg_class c on t.tgrelid = c.oid
JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname=’public’ AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’, ‘information_schema’);

zip archive log files using archive_command postgres

In PostgreSQL WAL files are created as 16 MB chunks. Even though there is no transactions written into the WAL files still it occupy 16 MB space. To reduce the space usage, zip those files with archive_command parameter.

Enable archiving with compression of archivelogs:

wal_level=archive # you can set this value to any value after archive
archive_mode = on
archive_command = ‘cp %p /home/postgres/archivedir/%f && gzip /home/postgres/archivedir/%f’

NOTE: replace “/home/postgres/archivedir” with the archive location where you want to store your archive logs.

ERROR: could not load library "/opt/PostgreSQL/9.6/lib/postgresql/plperl.so": /opt/PostgreSQL/9.6/lib/postgresql/plperl.so: undefined symbol: Perl_save_strlen

I am using PostgreSQL 9.6 version and tried to create plperl language. I was getting the below error.

[postgres@localhost ~]$ export LD_LIBRARY_PATH=/usr/lib64/perl5/CORE:/opt/PostgreSQL/9.6/lib
[postgres@localhost ~]$ export PATH=/opt/PostgreSQL/9.6/bin:$PATH
[postgres@localhost ~]$ psql
psql.bin (9.6.0)
Type “help” for help.

postgres=# create language plperl;
ERROR:  could not load library “/opt/PostgreSQL/9.6/lib/postgresql/plperl.so”: libperl.so: cannot open shared object file: No such file or directory
postgres=# \q
[postgres@localhost ~]$
[postgres@localhost ~]$ ldd /opt/PostgreSQL/9.6/lib/postgresql/plperl.so
        linux-vdso.so.1 =>  (0x00007ffd06fcc000)
        libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x00007fda1ad1c000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fda1aaed000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fda1a72c000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fda1a512000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fda1a2f8000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fda1a0f4000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fda19df2000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fda19bba000)
        libutil.so.1 => /lib64/libutil.so.1 (0x00007fda199b7000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fda1b2c3000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007fda197b4000)
[postgres@localhost ~]$

[postgres@localhost ~]$ pg_ctl -D /opt/PostgreSQL/9.6/data restart
waiting for server to shut down…. done

[postgres@localhost ~]$ psql
psql.bin (9.6.0)
Type “help” for help.

postgres=# create language plperl;
ERROR:  could not load library “/opt/PostgreSQL/9.6/lib/postgresql/plperl.so”: /opt/PostgreSQL/9.6/lib/postgresql/plperl.so: undefined symbol: Perl_save_strlen

I have searched a lot for this error but did not find anything related to this. Finally I tried the same steps in PostgreSQL 9.4 and it was successful with same steps as above.

[postgres@localhost ~]$ export PATH=/opt/PostgreSQL/9.4/bin:$PATH
[postgres@localhost ~]$ export LD_LIBRARY_PATH=/usr/lib64/perl5/CORE:/opt/PostgreSQL/9.4/lib
[postgres@localhost ~]$ ldd /opt/PostgreSQL/9.4/lib/postgresql/plperl.so
        linux-vdso.so.1 =>  (0x00007ffdfa973000)
        libperl.so => /usr/lib64/perl5/CORE/libperl.so (0x00007ffb8e2d6000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007ffb8e0a7000)
        libc.so.6 => /lib64/libc.so.6 (0x00007ffb8dce6000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007ffb8dacc000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007ffb8d8b2000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007ffb8d6ae000)
        libm.so.6 => /lib64/libm.so.6 (0x00007ffb8d3ac000)
        libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007ffb8d174000)
        libutil.so.1 => /lib64/libutil.so.1 (0x00007ffb8cf71000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ffb8e87c000)
        libfreebl3.so => /lib64/libfreebl3.so (0x00007ffb8cd6e000)
[postgres@localhost ~]$
[postgres@localhost ~]$ pg_ctl -D /opt/PostgreSQL/9.4/data restart
waiting for server to shut down…. done
server stopped
server starting
[postgres@localhost ~]$ 2017-12-06 10:01:31 IST LOG:  redirecting log output to logging collector process
2017-12-06 10:01:31 IST HINT:  Future log output will appear in directory “pg_log”.

[postgres@localhost ~]$
[postgres@localhost ~]$ psql -p 5433
psql.bin (9.4.10)
Type “help” for help.

postgres=# create language plperl;

This is happening only in PostgreSQL 9.6.

PostgreSQL session and object level auditing

Auditing in PostgreSQL using pgaudit

The PostgreSQL Audit Extension (or pgaudit) provides detailed session and/or object audit logging via the standard logging facility provided by PostgreSQL. The goal of PostgreSQL Audit to provide the tools needed to produce audit logs required to pass certain government, financial, or ISO certification audits.

We can perform the audit using the standard logging facility provided by PostgreSQL but it logs all the statements executed on the database cluster.If we want to log some specific statements like select or write operations or on specific object we can not do it using standard logging.

Advantages of Pgaudit:

1) Log specific statements like select , insert , update , delete.
2) Log statements of object level (table).
3) Log statements related to roles or privileges.

How to install pgaudit:

Download pgaudit from “https://github.com/pgaudit/pgaudit/releases” based on your PostgreSQL version.

Follow below steps to perform the installation.

unzip or untar the downloaded file. Go to contrib location in the PostgreSQL software you have downloaded.

Rename the downloaded file to “pgaudit”.

[postgres@localhost contrib]$ mv pgaudit-1.1.1 pgaudit
[postgres@localhost contrib]$
[postgres@localhost contrib]$ cd pgaudit/
[postgres@localhost pgaudit]$
[postgres@localhost pgaudit]$ make -s check
============== creating temporary instance            ==============
============== initializing database system           ==============
============== starting postmaster                    ==============
running on port 57838 with PID 20941
============== creating database “contrib_regression” ==============
============== running regression test queries        ==============
test pgaudit                  … ok
============== shutting down postmaster               ==============
============== removing temporary instance            ==============

 All 1 tests passed.

[postgres@localhost pgaudit]$ make install
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/lib’
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/share/extension’
/bin/mkdir -p ‘/home/postgres/9.6_SOFTWARE/share/extension’
/bin/install -c -m 755  pgaudit.so ‘/home/postgres/9.6_SOFTWARE/lib/pgaudit.so’
/bin/install -c -m 644 ./pgaudit.control ‘/home/postgres/9.6_SOFTWARE/share/extension/’
/bin/install -c -m 644 ./pgaudit–1.1.1.sql ./pgaudit–1.0–1.1.1.sql  ‘/home/postgres/9.6_SOFTWARE/share/extension/’

Configuration of pgaudit:

To use pgaudit. set the shared_preload_libraries to pgaudit.


restart postgreSQL cluster.

create extension pgaudit;

Configuration parameters for pgaudit:

pgaudit.log :

This is used to specify the statements to log. 

READ : SELECT and COPY on a query or relation.
FUNCTION : DO blocks
DDL : DDL that is not included with ROLE

pgaudit.log_catalog  : log the queries on catalog tables(pg_class, pg_tables etc).

pgaudit.log_level : Level of information need to be written in logfile. Refer to for logging level 

pgaudit.log_parameter : Specifies that audit logging should include the parameters that were passed with the statement. 

pgaudit.log_relation : audit log will create a separate log entry for each relation (TABLE, VIEW, etc.) referenced in a SELECT or DML statement.

pgaudit.log_statement_once : log the statement once, if any statement executed previously it will not be logged.

pgaudit.role : Specifies the master role to use for object audit logging.

Session Audit Logging :-

Session audit logging provides detailed logs of all statements executed by a user in the backend. It will log all the sessions.

Configuration :

Session logging is enabled with the pgaudit.log setting.


alter system set pgaudit.log = ‘read’;

execute some select queries.

Example logging output :

LOG:  AUDIT: SESSION,5,1,READ,SELECT,TABLE,public.test,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG:  AUDIT: SESSION,5,1,READ,SELECT,TABLE,public.test_cp,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG:  AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.test,select count(*) from test join test_cp on (test.id=test_cp.id);,
LOG:  AUDIT: SESSION,6,1,READ,SELECT,TABLE,public.test_cp,select count(*) from test join test_cp on (test.id=test_cp.id);,

Object Audit Logging :

Object audit logging logs statements that affect a particular relation. Only SELECT, INSERT, UPDATE and DELETE commands are supported. TRUNCATE is not included in object audit logging. Object-level audit logging is implemented via the roles system. The pgaudit.role setting defines the role that will be used for audit logging. A relation (TABLE, VIEW, etc.) will be audit logged when the audit role has permissions for the command executed or inherits the permissions from another role.

Set pgaudit.role to auditor and grant SELECT and DELETE privileges on the account table. Any SELECT or DELETE statements on the account table will now be logged:

alter system set pgaudit.role=’auditor’;
grant SELECT , insert , update , delete on test to auditor;

postgres=# select pg_reload_conf();
(1 row)

postgres=# select count(*) from test;
(1 row)

postgres=# insert into test_cp values (1,’sd’);


\2017-12-03 19:46:03.998 IST \postgres \postgres: LOG:  AUDIT: OBJECT,25,1,READ,SELECT,TABLE,public.test,select count(*) from test;,

Enable auto start of PostgreSQL cluster on Linux machine

When we do manual installation of PostgreSQL, after rebooting the server, postgreSQL will not start automatically. To start the PostgreSQL cluster automatically after server reboot follow the below procedure.

Login as a root user.

1) Create a file /etc/init.d/pg_auto using the script.

2) Set permissions on the script

chmod 777 /etc/init.d/pg_auto

3) Tell Linux to autostart/stop the service using below command

# chkconfig –add pg_auto
# chkconfig pg_auto on

4) Starting / Stopping the DB

Now on wards PostgreSQL cluster will start and stop at machine boot and shutdown.

Or it can be manually controlled with below commands :

# service pg_auto start
# service pg_auto stop
# service pg_auto restart
# service pg_auto status

PostgreSQL auto start script

#! /bin/bash
# chkconfig: 2345 80 05
# description: start and stop PostgreSQL Database Enterprise Edition

# Note: Change the value of POSTGRES to the login name of the PostgreSQL owner

export PGDATA=/home/postgres/DATA
export PGPORT=5432
export PGSOFTWARE=/home/postgres/9.6_SOFTWARE #location where we installed the software

export PATH

case $1 in
        echo $”Starting PostgreSQL: “
        if [[ `whoami` == ‘postgres’ ]]
        $PGSOFTWARE/bin/pg_ctl start
        su $POSTGRES -c “$PGSOFTWARE/bin/pg_ctl start”
        echo $”Shutting down PostgreSQL: “
        if [[ `whoami` == ‘postgres’ ]]
        $PGSOFTWARE/bin/pg_ctl start
        su $POSTGRES -c “$PGSOFTWARE/bin/pg_ctl stop”
        echo $”Status of PostgreSQL: “
        if [[ `whoami` == ‘postgres’ ]]
        $PGSOFTWARE/bin/pg_ctl status
        su $POSTGRES -c “$PGSOFTWARE/bin/pg_ctl status”
        echo $”Restarting PostgreSQL: “
        if [[ `whoami` == ‘postgres’ ]]
        $PGSOFTWARE/bin/pg_ctl restart
        su $POSTGRES -c “$PGSOFTWARE/bin/pg_ctl restart”
        echo “usage: $0 {start|stop|restart|status}”

Recent Entries »