Tag Archives: PostgreSQL

Query to kill idle connections in PostgreSQL

PostgreSQL is process oriented architecture, for evey user connection one process will be created in background. If the user forget to close the connection the process will still consume some resources. Below is the query to close the idle connections

--- Close the connections which are idle for more than 15 mins

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'databasename'
   AND pid <> pg_backend_pid()
   AND state = 'idle'
   and extract(epoch from (current_timestamp - query_start)) > 15*60;

Temporary tables in PostgreSQL

Temporary tables are short lived tables which will be dropped at the end of session or transaction. These are used to store intermediate data in a transaction. Temporary tables can be created in the same way as the permanent tables where we just add TEMP or TEMPORARY to create table statement. Below is the syntax to create temporary tables

create [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } table
col1 datatype,
col2 datatype,
[ TABLESPACE tablespace_name ]

Databases like Oracle or SQL server support global temporary tables where a temporary table can be created once and used in all sessions, but in PostgreSQL it is not yet supported, the GLOBAL keyword is there just for SQL standard but it actually represents the LOCAL.

Temporary tables are visible only in a session, sessions can’t see temporary tables of each other. Temporary tables can be created with same name as the permanent tables but it is not recommended. When you try to access the table without schema, temporary tables will be given priority than permanent tables.

In the below example temporary table with name emp has more priority than permanent emp table. To access the permanent tables use fully qualified name (schema.table).

 postgres=# create database test_temp;
 postgres=# \c test_temp
 psql (12.4, server 10.14)
 You are now connected to database "test_temp" as user "postgres".
 test_temp=# create table emp(id int, name varchar);
 test_temp=# insert into emp values (1,'Viswamitra');
 test_temp=# create temporary table emp (id int, name varchar);
 test_temp=# \dt
           List of relations
   Schema   | Name | Type  |  Owner   
  pg_temp_4 | emp  | table | postgres
 (1 row)
 test_temp=# show search_path ;
 "$user", public
 (1 row)
 test_temp=# \dt public.*
         List of relations
  Schema | Name | Type  |  Owner   
  public | emp  | table | postgres
 (1 row)
 test_temp=# select * from emp;
  id | name 
 (0 rows)
 test_temp=# select * from public.emp;
  id |    name    
   1 | Viswamitra
 (1 row)

Temporary tables will be created in a special schema with naming convention “pg_temp_nnn”, temporary tables can not be created in non-temporary schemas. When a query is executed , PostgreSQL will search for matching tables in pg_temp_nnn schema and then it will search in schemas specified in search_path variable.

postgres=# create temporary table public.test(id int);
 ERROR:  cannot create temporary relation in non-temporary schema
 LINE 1: create temporary table public.test(id int);

By default temporary tables(files store the data) will be created in the default tablespace pg_default, if there is any tablespace created and set in the parameter temp_tablespaces, tables will use that tablespace.

 test_temp=# create temporary table test_size(id int, name varchar);
 test_temp=# select pg_relation_filepath('test_size');
 (1 row)
 test_temp=# ! du -sh /pgdata/10/data/base/90113/t4_90140
 0       /pgdata/10/data/base/90113/t4_90140
 test_temp=# insert into test_size select generate_series(1,10000),md5(generate_series(1,10000)::text);
 INSERT 0 10000
 test_temp=# ! du -sh /pgdata/10/data/base/90113/t4_90140
 960K    /pgdata/10/data/base/90113/t4_90140
 test_temp=# \dt+ test_size
                         List of relations
   Schema   |   Name    | Type  |  Owner   |  Size  | Description
  pg_temp_4 | test_size | table | postgres | 704 kB |
 (1 row)
 test_temp=# ! mkdir /tmp/test
 test_temp=# create tablespace test location '/tmp/test';
 test_temp=# create temporary table test_size_1(id int, name varchar) tablespace test;
 test_temp=# select pg_relation_filepath('test_size_1');
 (1 row)

Indexes can be created on temporary tables which are by default temporary and dropped after that. autovacuum process can not handle the temporary tables so it is highly recommended to analyze the temporary tables before using in complex queries.

temp_buffers is a session level memory component which is allocated each session to access the temporary tables, by default the size of temp_buffers is 8 MB. if the temporary tables are too large than temp_buffers query will start using disk (merge disk algorithm) to process the data.

Aurora read replica option is not available for RDS PostgreSQL

We tried to migrate our existing RDS PostgreSQL instance of 9.5 version to 11.8 version. Below are the steps we followed after checking the documentation. The maximum Aurora PostgreSQL version supported is 11.8.

  1. upgrade existing RDS PostgreSQL to 11.8 version
  2. create read replica for existing RDS PostgreSQL instance
  3. Once both instances are in sync, promote read replica

When we were performing this on dev machine we found that RDS do not support read replica creation for 11.8. After discussing with AWS team they confirmed that RDS do not support Aurora read replica for all PostgreSQL versions. Below are details which can help others who want to migrate to Aurora.

RDS PostgreSQL VersionAurora Read Replica Version Supported, 9.6.9, 9.6.9, 9.6.9, 9.6.9, 9.6.9, 9.6.9, 9.6.12
9.6.14No Aurora Read Replica
9.6.15No Aurora Read Replica
9.6.16No Aurora Read Replica
9.6.17No Aurora Read Replica
9.6.18No Aurora Read Replica
10.110.4, 10.5, 10.6, 10.7, 10.12
10.310.4, 10.5, 10.6, 10.7, 10.12
10.410.4, 10.5, 10.6, 10.7, 10.12
10.510.5, 10.6, 10.7, 10.12
10.610.6, 10.7, 10.12
10.710.7, 10.12
10.910.11, 10.12
10.1O10.11, 10.12
10.1110.11, 10.12
10.13No Aurora Read Replica
11.111.4, 11.7
11.211.4, 11.7
11.411.4, 11.7
11.511.6, 11.7
11.611.6, 11.7
11.8No Aurora Read Replica
12.2No Aurora Read Replica
12.3No Aurora Read Replica

VACUUM FULL or CLUSTER in online using pg_repack

pg_repack is a PostgreSQL extension which lets you remove bloat from tables and indexes, and optionally restore the physical order of clustered indexes. Unlike CLUSTER and VACUUM FULL it works online, without holding an exclusive lock on the processed tables during processing. pg_repack is efficient to boot, with performance comparable to using CLUSTER directly.
One caveat is though that it requires about double the size of the target table and its indexes, and the target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column.
PostgreSQL versions
PostgreSQL 9.1, 9.2, 9.3, 9.4, 9.5, 9.6, 10
Disk space
Performing a full-table repack requires free disk space about twice as large as the target table(s) and its indexes. For example, if the total size of the tables and indexes to be reorganized is 1GB, an additional 2GB of disk space is required.

[postgres@localhost ~]$ unzip pg_repack-1.4.2.zip
[postgres@localhost ~]$ cd pg_repack-1.4.2/
[postgres@localhost pg_repack-1.4.2]$
[postgres@localhost pg_repack-1.4.2]$ make
make[1]: Entering directory `/home/postgres/pg_repack-1.4.2/bin’
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/home/postgres/SOFTWARE_9.6/include -DREPACK_VERSION=1.4.2 -I. -I./ -I/home/postgres/SOFTWARE_9.6/include/server -I/home/postgres/SOFTWARE_9.6/include/internal -D_GNU_SOURCE   -c -o pg_repack.o pg_repack.c
make[1]: Entering directory `/home/postgres/pg_repack-1.4.2/regress’
make[1]: Nothing to be done for `all’.
make[1]: Leaving directory `/home/postgres/pg_repack-1.4.2/regress’
[postgres@localhost pg_repack-1.4.2]$ make install
make[1]: Entering directory `/home/postgres/pg_repack-1.4.2/bin’
/bin/mkdir -p ‘/home/postgres/SOFTWARE_9.6/bin’
/bin/install -c -m 644 .//pg_repack.control ‘/home/postgres/SOFTWARE_9.6/share/extension/’
/bin/install -c -m 644  pg_repack–1.4.2.sql pg_repack.control ‘/home/postgres/SOFTWARE_9.6/share/extension/’
make[1]: Leaving directory `/home/postgres/pg_repack-1.4.2/lib’
make[1]: Entering directory `/home/postgres/pg_repack-1.4.2/regress’
make[1]: Nothing to be done for `install’.
make[1]: Leaving directory `/home/postgres/pg_repack-1.4.2/regress’
[postgres@localhost pg_repack-1.4.2]$
[postgres@localhost pg_repack-1.4.2]$ psql
psql (9.6.6)
Type “help” for help.
postgres=# create extension pg_repack ;
pg_repack command options
–all repack all databases
–table=TABLE repack specific table only
–parent-table=TABLE repack specific parent table and its inheritors
–schema=SCHEMA repack tables in specific schema only
–tablespace=TBLSPC move repacked tables to a new tablespace
–moveidx move repacked indexes to TBLSPC too
–order-by=COLUMNS order by columns instead of cluster keys
–no-order do vacuum full instead of cluster
–dry-run print what would have been repacked and exit
–jobs=NUM Use this many parallel jobs for each table
–index=INDEX move only the specified index
–only-indexes move only indexes of the specified table
–wait-timeout=SECS timeout to cancel other backends on conflict
Example :
Repack a table(non-clustered) :
pg_repack -t test_rpk postgres
Reoack a cluster table without clustering(ordering) :
pg_repack –no-order -t test_rpk postgres
Like this we can do for schema , indexes , move to different tablespaces without taking locks for much time compare to remaining methods like VACUUM FULL and CLUSTER.
How repack works internally :

Full Table Repacks:
To perform a full-table repack, pg_repack will:
*) create a log table to record changes made to the original table
*) add a trigger onto the original table, logging INSERTs, UPDATEs and DELETEs into our log table
*) create a new table containing all the rows in the old table
*) build indexes on this new table
*) apply all changes which have accrued in the log table to the new table
*) swap the tables, including indexes and toast tables, using the system catalogs
*) drop the original table
*) pg_repack will only hold an ACCESS EXCLUSIVE lock for a short period during initial setup (steps 1 and 2 above) and during the final swap-and-drop phase (steps 6 and 7). For the rest of its time, pg_repack only needs to hold an ACCESS SHARE lock on the original table, meaning INSERTs, UPDATEs, and DELETEs may proceed as usual.
Index Only Repacks:
To perform an index-only repack, pg_repack will:
*) create new indexes on the table using CONCURRENTLY matching the definitions of the old indexes
*) swap out the old for the new indexes in the catalogs
*) drop the old indexes

script to take the dump backup in postgresql

Before using the script create the below directory structure

Create directory : /home/postgres/BACKUP

create below folders inside BACKUP


inside dumps create logs , PGDUMP folders.
inside scripts put the below script with name you like and give execution permissions. I gave name dump_backup.sh

chmod +x dump_backup.sh

DUMP_FILE=$BACKUP_LOC”/”$DATABASE”_”$(date +%d%m%Y%H%M).custom
LOG_FILE=$LOG_LOC”/”$DATABASE”_”$(date +%d%m%Y%H%M).log

pg_dump –file=$DUMP_FILE –format=c –verbose –blobs –create $DATABASE >> $LOG_FILE 2>&1
if [ $? -eq 0 ]
echo “Backup is successful” >> $LOG_FILE
echo “Backup failed” >> $LOG_FILE

Execute script using below command:

sh dump_backup.sh postgres ==> This will take the dump of postgres database in custom format.

pg_stat_statements extension in PostgreSQL

This is used for tracking execution statistics of all SQL statements executed by a server. It will track all the SQL statements executed by the users on all the databases. But we created this extension in a single database and we can access the stats only by login into that database.

This is a contrib module (extension). For this we need to install the contrib modules, refer to http://oguridbtech.blogspot.in/2018/01/install-all-contrib-modules-in.html
create extension pg_stat_statements; — Execute this statement by login to the database where you want the views to be created.

Put the pg_stat_statements in shared_preload_libraries and restart the server to take the value effect.

This will create a view called “pg_stat_statements” in the database. Now on wards what ever the queries are getting executed they are logged into the pg_stat_statements view. There will be an entry for “userid”,”dbid”,”queryid”. 

pg_stat_statements_reset() — function is used to reset the table or delete the old stats.
pg_stat_statements(showtext boolean) returns setof record — This function return all the columns except the query column.
Configuration parameters:

pg_stat_statements.max:pg_stat_statements.max is the maximum number of statements tracked by the module (i.e., the maximum number of rows in the pg_stat_statements view).
pg_stat_statements.track:pg_stat_statements.track controls which statements are counted by the module. Specify top to track top-level statements (those issued directly by clients), all too also track nested statements (such as statements invoked within functions), or none to disable statement statistics collection. The default value is top.
pg_stat_statements.track_utility:pg_stat_statements.track_utility controls whether utility commands are tracked by the module. Utility commands are all those other than SELECT, INSERT, UPDATEand DELETE. 
pg_stat_statements.save:pg_stat_statements.save specifies whether to save statement statistics across server shutdowns. If it is off then statistics are not saved at shutdown nor reloaded at server start. The default value is on.
To delete all the old stats execute the below function :
select pg_stat_statements_reset();

Installation of pgagent in Linux

. pgagent is used as an alternative for crontab scheduler or windows scheduler.
Installation of pgagent:

Create a repository in Linux using the disk or online.
yum -y install gtk*
yum -y install automake*
yum -y install autoconf*
yum -y install gcc*
yum -y install libxml2*
yum -y install libxslt*
yum -y install python*
install wxGTK 2.8.x from http://www.wxwidgets.org/ 


Unpack the wxGTK tarball to a convenient location, and build and install it as follows:
     cd /path/to/wxGTK/source/
     ./configure –with-gtk –enable-gtk2 –enable-unicode
     sudo make install
     # Install wxWidgets contrib modules.
     cd contrib/
     sudo make install
Download the pgagent source from (https://www.pgadmin.org/download/pgagent-source-code/) and follow the below procedure.
export LD_LIBRARY_PATH=/usr/local/lib:/home/postgres/SOFTWARE_9.6/lib:/usr/lib
export PATH=/home/postgres/SOFTWARE_9.6/bin:$PATH
untar the downloaded pgagent source and follow below steps.
[root@localhost Desktop]# cd pgAgent-3.4.0-Source/
 [root@localhost pgAgent-3.4.0-Source]# cmake -D PG_CONFIG_PATH:FILEPATH=/home/postgres/SOFTWARE_9.6/bin/pg_config -D STATIC_BUILD:BOOL=OFF .
— ================================================================================
— Configuration summary:
—   Project                     : pgagent
—   Description                 : pgAgent is a job scheduling engine for PostgreSQL
—   Version                     : 3.4.0
—   PostgreSQL version string   : PostgreSQL 9.6.6
—   PostgreSQL version parts    : 9.6.6
—   PostgreSQL path             : /home/postgres/SOFTWARE_9.6
—   PostgreSQL config binary    : /home/postgres/SOFTWARE_9.6/bin/pg_config
—   PostgreSQL include path     : /home/postgres/SOFTWARE_9.6/include
—   PostgreSQL library path     : /home/postgres/SOFTWARE_9.6/lib
—   PostgreSQL share path       : /home/postgres/SOFTWARE_9.6/share
—   wxWidgets version           : 2.8.12
—   wxWidgets path              : /usr/local
—   wxWidgets config binary     : /usr/local/bin/wx-config
—   wxWidgets Static linking    : OFF
—   wxWidgets Debug?            : NO
— ================================================================================
— Configuring done
— Generating done
— Build files have been written to: /root/Desktop/pgAgent-3.4.0-Source
[root@localhost pgAgent-3.4.0-Source]# make
Scanning dependencies of target pgagent
[ 12%] Building CXX object CMakeFiles/pgagent.dir/connection.cpp.o
[ 25%] Building CXX object CMakeFiles/pgagent.dir/job.cpp.o
[ 37%] Building CXX object CMakeFiles/pgagent.dir/misc.cpp.o
[ 50%] Building CXX object CMakeFiles/pgagent.dir/pgAgent.cpp.o
[ 62%] Building CXX object CMakeFiles/pgagent.dir/precomp.cpp.o
[ 75%] Building CXX object CMakeFiles/pgagent.dir/unix.cpp.o
[ 87%] Building CXX object CMakeFiles/pgagent.dir/win32.cpp.o
Linking CXX executable pgagent
[ 87%] Built target pgagent
Scanning dependencies of target run
[100%] Generating pgagent–3.4.sql, pgagent.control
[100%] Built target run
 [root@localhost pgAgent-3.4.0-Source]# make install
[ 87%] Built target pgagent
[100%] Built target run
Install the project…
— Install configuration: “release”
— Installing: /usr/local/bin/pgagent
— Removed runtime path from “/usr/local/bin/pgagent”
— Installing: /usr/local/share/pgagent.sql
— Installing: /usr/local/share/pgagent_upgrade.sql
— Installing: /usr/local/./README
— Installing: /usr/local/./LICENSE
— Installing: /home/postgres/SOFTWARE_9.6/share/extension/pgagent–3.4.sql
— Installing: /home/postgres/SOFTWARE_9.6/share/extension/pgagent.control
— Installing: /home/postgres/SOFTWARE_9.6/share/extension/pgagent–unpackaged–3.4.sql
[root@localhost pgAgent-3.4.0-Source]#
 chown -R postgres:postgres /home/postgres/SOFTWARE_9.6
Now create pgagent extension.
postgres=# create extension pgagent ;
Starting the pgagent in background:
Create below script in “/etc/init.d” location.
[root@localhost postgres]# cat /etc/init.d/pgagent
# /etc/rc.d/init.d/pgagent
# Manages the pgagent daemon
# chkconfig: 2345 80 05
# description: PgAgent PostgreSQL Job Service
# processname: pgagent
export LD_LIBRARY_PATH=/usr/local/lib:/home/postgres/SOFTWARE_9.6/lib:/usr/lib
export PATH=/home/postgres/SOFTWARE_9.6/bin:$PATH
start() {
    echo -n “Starting $prog: “
    nohup /usr/local/bin/pgagent hostaddr= dbname=postgres user=postgres & > /tmp/t.out
stop() {
    echo -n “Stopping $prog: “
    killall /usr/local/bin/pgagent
case “$1” in
    echo $”Usage: $0 {start|stop|restart|reload|status}”
    exit 1
exit $RETVAL
# Execute below command to add pgagent in autostart script of server, whenever server reboots automatically the pgagent starts.
chkconfig –add pgagent
Execute below command to start the pgagent service

service pgagent start

Install all contrib modules in PostgreSQL using souce code

Contrib modules are extensions for PostgreSQL. They provide additional functionality which is not part of PostgreSQL by default. To install the contrib modules follow the below procedure.

1) Install PostgreSQL manually
2) Set the environment variables (include the postgres locations in PATH and LD_LIBRARY_PATH variables).
3) When you untar the source code you can see a folder contrib in that

4) enter into contrib directory
cd contrib

5) execute “make” , “make install” commands
make install

This will complete the installation of all the extensions. You can see the “.control” , “.sql” files created in “/home/postgres/SOFTWARE_9.6/share/extension” location.

pglogical installation and configuration of logical streaming replication using pglogical

pglogical is a logical replication system implemented entirely as a PostgreSQL extension. Fully integrated, it requires no triggers or external programs. This alternative to physical replication is a highly efficient method of replicating data using a publish/subscribe model for selective replication.

Migrate and upgrade PostgreSQL with almost zero downtime
Accumulate changes from sharded database servers into a Data Warehouse
Copy all or a selection of database tables to other nodes in a cluster
Feed database changes in real-time to other systems

What benefits does pglogical provide?

Here I am listing important one

Synchronous replication
Delayed replication
Configurable conflict resolution
Ability to convert physical standby to logical replication
Sequences can be replicated
Subscriber isn’t in hot standby recovery, so you can use temp, unlogged, or normal tables
Subscriber can have different users & security, different indexes, different parameter settings
Replicate just one database, or a subset of tables, known as Replication Sets
Replicate across PostgreSQL versions or architectures, allowing low- or zero-downtime upgrades
Refer document for more features.

pglogical works on a per-database level, not whole server level like physical streaming replication. To use pglogical the provider and subscriber must be running PostgreSQL 9.4 or newer.

Installation of pglogical:

We can install pglogical using two methods

1) Using rpms
2) Using source code

Here I am explain using the source code.

Untar the archive

export the PATH variable such that it includes the command pg_config.
export LD_LIBRARY_PATH variable which point to libraries of postgresql.

Install the PostgreSQL using source code
export the path variable which contain pg_config command
make USE_PGXS=1 clean all
make USE_PGXS=1 install

This will complete installation of pglogical.

pglogical terminology:

Nodes and Roles

A node simply refers to a PostgreSQL server. It can play the role of:
Provider (upstream):  changes to data on this node are tracked and replicated to subscribing nodes.
Subscriber (downstream): subscribes to changes on a provider node and replicates changes.
These roles are not mutually exclusive. A node can be both a provider and a subscriber.


Make below changes to postgresql.conf file in both the nodes.

On primary node:

wal_level = ‘logical’
max_worker_processes = 10
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = ‘pglogical’
track_commit_timestamp = on # 9.5+ only

On slave node:

max_worker_processes = 10
shared_preload_libraries = ‘pglogical’

Replication User

On both the nodes create the user for pglogical.

Changes to pg_hba.conf:

Allow replication connections from localhost
host replication replicationuser md5
host  dbname replicationuser md5

Allow connection to Primary from standby and standby to primary by replication user by adding below line in pg_hba.conf of all nodes according to your environment. 

host replication_db_name replication_user_name host_or_ipaddress md5
host replication replication_user_name host_or_ipaddress md5

Restart both the instances (Primary and slave).


Create extension in both the nodes.


We now need to create the node in pglogical. You should do this only once per database in each node.

SELECT pglogical.create_node(
    node_name := ‘provider’,
    dsn := ‘host= port=5432 dbname=pglogical user=replication_user password=password’);
SELECT pglogical.create_node(
    node_name := ‘subscriber’,
    dsn := ‘host= port=5433 dbname=pglogical user=replication_user password=password’);

Replication Sets

Replication sets provide a mechanism to control which tables in the database will be replicated and which actions on those tables will be replicated. The tables are not replicated until they are added into a replication set.

There are three pre-existing replication sets named “default”, “default_insert_only” and “ddl_sql”. The “default” replication set is defined to replicate all changes to tables in. The “default_insert_only” only replicates INSERTs and is meant for tables that don’t have primary key (see Limitations section for details). The “ddl_sql” replication set is defined to replicate schema changes specified by pglogical.replicate_ddl_command
pglogical.create_replication_set(set_name name, replicate_insert bool, replicate_update bool, replicate_delete bool, replicate_truncate bool)

This function creates a new replication set.

set_name – name of the set, must be unique
replicate_insert – specifies if INSERT is replicated, default true
replicate_update – specifies if UPDATE is replicated, default true
replicate_delete – specifies if DELETE is replicated, default true
replicate_truncate – specifies if TRUNCATE is replicated, default true

select pglogical.create_replication_set(set_name := ‘APP_RSET’, replicate_insert := true, replicate_update := true, replicate_delete := true, replicate_truncate := true);

select pglogical.replication_set_add_all_tables(set_name := ‘APP_RSET’, schema_names := ARRAY[‘app_schema’], synchronize_data :=true);

select pglogical.replication_set_add_all_sequences(set_name := ‘APP_RSET’, schema_names :=ARRAY[‘app_schema’], synchronize_data :=true)


Slave nodes will subscribe to the primary nodes to get the changes from the primary node. Replace the para

SELECT pglogical.create_subscription(
    subscription_name := ‘subscription1’,
    provider_dsn := ‘host= port=5432 dbname=pglogical user=replicati  on_user password=password’,
    replication_sets := ARRAY[‘APP_RSET’],
    synchronize_structure := true,
    synchronize_data := true);


subscription_name – name of the subscription, must be unique
provider_dsn – connection string to a provider
replication_sets – array of replication sets to subscribe to, these must already exist, default is “{default,default_insert_only,ddl_sql}”
synchronize_structure – specifies if to synchronize structure from provider to the subscriber, default false
synchronize_data – specifies if to synchronize data from provider to the subscriber, default true
forward_origins – array of origin names to forward, currently only supported values are empty array meaning don’t forward any changes that didn’t originate on provider node, or “{all}” which means replicate all changes no matter what is their origin, default is “{all}”
apply_delay – how much to delay replication, default is 0 seconds

Functions to perform DDL:

pglogical do not replicate DDL commands, So we need to manually execute the commands on both the nodes(primary and slave) manually. We can use the below function to execute DDL commands on the both the nodes at a time.

select pglogical.replicate_ddl_command(command := ‘alter table app_schema.employee add address varchar(200)’, replication_sets := ARRAY[‘APP_RSET’]);

pglogical=# select pglogical.replicate_ddl_command(command := ‘create table app_schema.test_rep (id int primary key)’, replication_sets := ARRAY[‘APP_RSET’]);
(1 row)

pglogical=# select pglogical.replication_set_add_table(set_name := ‘APP_RSET’ , relation := ‘app_schema.test_rep’ , synchronize_data := true);
(1 row)
Row Filtering

On the provider the row filtering can be done by specifying row_filter parameter for the pglogical.replication_set_add_table function. The row_filter is normal PostgreSQL expression which has the same limitations on what’s allowed as the CHECK constraint.


In case the node is subscribed to multiple providers, or when local writes happen on a subscriber, conflicts can arise for the incoming changes. These are automatically detected and can be acted on depending on the configuration.

The configuration of the conflicts resolver is done via the pglogical.conflict_resolution setting. The supported values for the pglogical.conflict_resolution are:
error – the replication will stop on error if conflict is detected and manual action is needed for resolving
apply_remote – always apply the change that’s conflicting with local data, this is the default
keep_local – keep the local version of the data and ignore the conflicting change that is coming from the remote node
last_update_wins – the version of data with newest commit timestamp will be be kept (this can be either local or remote version)
first_update_wins – the version of the data with oldest timestamp will be kept (this can be either local or remote version)

Synchronous Replication

Synchronous replication is supported using same standard mechanism provided by PostgreSQL for physical replication.
The synchronous_commit and synchronous_standby_names settings will affect when COMMIT command reports success to client if pglogical subscription name is used in synchronous_standby_names. Refer to PostgreSQL documentation for more info about how to configure these two variables.

Limitations and Restrictions

Currently pglogical replication and administration requires superuser privileges. 
UNLOGGED and TEMPORARY tables will not and cannot be replicated
To replicate multiple databases you must set up individual provider/subscriber relationships for each. We can do only one database at a time.
UPDATEs and DELETEs cannot be replicated for tables that lack a PRIMARY KEY or other valid replica identity such as aUNIQUE constraint.
Automatic DDL replication is not supported. Managing DDL so that the provider and subscriber database(s) remain compatible is the responsibility of the user.
pglogical provides the pglogical.replicate_ddl_command function to allow DDL to be run on the provider and subscriber at a consistent point.
PGLogical does not support replication between databases with different encoding. We recommend using UTF-8 encoding in all replicated databases.

Check the status of the replication:-

To check the status of the replication use the pg_stat_replication view which is present in the master node. Where pglogical internally create a logical replication slot to perform the replication.

Overview on how to do up gradation with less down time using pglogical:- (9.4 to 9.6)

1) Install 9.6 software on same machine.
2) Initiate new cluster using 9.6 software.
3) Copy the global objects along with database (only db creation command) of 9.4 to 9.6.
4) Install pglogical as explain above. (install two times for two software versions by setting the path variables)
5) Set the required parameters in both the servers and restart the server to take effect of variable values.
6) Create provider and subscriber nodes in both 9.4 and 9.6
7) Create replication set in primary and add all the tables and sequences to replication set in 9.4.
8) Create subscription for the replication set created in primary in 9.6.
9) Once the primary and standby are in sync. Stop the application.
10) Verify the data and sequences are in sync
11) Stop the application
12) Remove pglogical configuration in both the nodes
13) start the 9.6 server with the port number used in 9.4.

Kill idle sessions in PostgreSQL

To find out the sessions which are in idle state we use the pg_stat_activity view by using the below query.

select datname,usename,application_name,state,current_timestamp-state_change as idle_since from pg_stat_activity where state=’idle’;

To find the queries which are idle since “100” seconds use the below query. If you want to get sessions in idle state more than 100 seconds then replace the 100 in below query with different time.

  (SELECT pid,
          (EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
   FROM pg_stat_activity
   WHERE state=’idle’)
SELECT datname,
WHERE idle_since>100;

 datname  | usename  | application_name | state | idle_since
 postgres | postgres | psql             | idle  | 245.355612
(1 row)

To kill the sessions which are in idle state for more than 100 seconds use the below query.

  (SELECT pid,
          (EXTRACT(HOUR FROM (CURRENT_TIMESTAMP-state_change)) * 60*60) + (EXTRACT (MINUTES FROM (CURRENT_TIMESTAMP-state_change)) * 60) + (EXTRACT (SECONDS FROM (CURRENT_TIMESTAMP-state_change))) AS idle_since
   FROM pg_stat_activity
   WHERE state=’idle’)
SELECT pg_terminate_backend(pid)
WHERE idle_since>100;

(1 row)

NOTE : The above query will kill all sessions which are idle for more than 100 seconds , if you want to kill all idle sessions then use the below query.

postgres=# select pg_terminate_backend(pid) from pg_stat_activity where state=’idle’;
(1 row)
« Older Entries