Author Archives: viswamitra

Error: error configuring Terraform AWS Provider: error validating provider credentials: error calling sts:GetCallerIdentity: SignatureDoesNotMatch: Signature expired: is now earlier than (- 15 min.)

When I was configuring the terraform in my server got below error.

Error: error configuring Terraform AWS Provider: error validating provider credentials: error calling sts:GetCallerIdentity: SignatureDoesNotMatch: Signature expired: 20210206T042543Z is now earlier than 20210206T042641Z (20210206T044141Z – 15 min.)

The error was due to the misconfiguration of time in my server, when we do API calls it will compare the timestamps, if it is within 5 mins it will accept else it will fail with above error.

pg_basebackup: error: could not get write-ahead log end position from server

While I was taking physical backup from slave server for configuring HA, I got a strange error.

-bash-4.2$ pg_basebackup -R -h -U replication_user -D /pgdata/data -S ha_290120211601 -C
pg_basebackup: error: could not get write-ahead log end position from server: ERROR:  could not open directory "./lost+found": Permission denied
pg_basebackup: removing contents of data directory "/pgdata/data"

I have all the permissions on the slave but it was due to permission issues at the primary server.

-rw-------  1 postgres postgres 26650 Jun 11  2020 postgresql.conf
drwx------ 11 postgres postgres  4096 Jun 13  2020 base
-rw-------  1 postgres postgres  4389 Jan 28 11:14 pg_hba.conf
drwx------  2 root     root      4096 Jan 28 18:32 lost+found
drwx------  2 postgres postgres  4096 Jan 29 14:03 pg_notify
-rw-------  1 postgres postgres    57 Jan 29 14:03

The lost+found has root ownership which was the issue. After changing the ownership at primary server side, it resolved the issue.

Install MySQL8 on Centos7 using rpm’s

MySQL is one of the most advanced open source database which is widely used. Below is the procedure to install MySQL8 on Centos7 using rpm packages.

  • Download mysql from MySQL :: Download MySQL Community Server
    • select Redhat 7 as operating system
    • select “RPM Bundle” from the list.
  • Copy tar file to server and open command prompt and un-tar the file.
  • You can see below list of files
  • Execute commands in below sequence to install the mysql
rpm -ivh mysql-community-common-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm
yum install openssl-devel openssl
rpm -ivh mysql-community-devel-8.0.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-compat-8.0.22-1.el7.x86_64.rpm
  • By default mysql service is not started, start the service and check the status
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# 
[root@localhost mysql]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Wed 2020-12-30 22:39:29 IST; 1min 32s ago
     Docs: man:mysqld(8)
  Process: 6794 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 6877 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─6877 /usr/sbin/mysqld

Dec 30 22:39:21 localhost.localdomain systemd[1]: Starting MySQL Server...
Dec 30 22:39:29 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost mysql]# 
  • By default the installation creates MySQL user and group. After the service is started it will initialize the data directory. For default data directory and log locations refer here.
  • A superuser account 'root'@'localhost' is created. A password for the superuser is set and stored in the error log file. Get the password to login to mysql
[root@localhost mysql]# sudo grep 'temporary password' /var/log/mysqld.log
2020-12-30T17:09:25.311493Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ************
  • Login to mysql and change the password
[root@localhost mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '************';
Query OK, 0 rows affected (0.01 sec)

NOTE: validate_password is installed by default. The default password policy implemented by validate_password requires that passwords contain at least one uppercase letter, one lowercase letter, one digit, and one special character, and that the total password length is at least 8 characters.

This completes the installation of mysql. Happy learning.

Refer to mysql documentation for more information.

Install pg_cron in Centos 7 using source code

pg_cron is like Linux cron scheduler but this helps the users to schedule the database jobs from database itself. pg_cron follows the same syntax as the Linux cron. I have done the installation for PostgreSQL 12 in Centos7.

Examples from documentation

-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

-- Vacuum every day at 10:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');

-- Change to vacuum at 3:00am (GMT)
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM');

-- Stop scheduling jobs
SELECT cron.unschedule('nightly-vacuum' );
(1 row)

SELECT cron.unschedule(42);
git clone
cd pg_cron
# Ensure pg_config is in your path, e.g.
export PATH=/usr/pgsql-12/bin:$PATH
make && sudo PATH=$PATH make install

Add below configuration parameters to

# add to postgresql.conf:
shared_preload_libraries = 'pg_cron'
cron.database_name = 'postgres'

Restart PostgreSQL cluster

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data restart

Login to postgres database and create pg_cron extension

-bash-4.2$ psql
psql (12.5)
Type "help" for help.

postgres=# CREATE EXTENSION pg_cron;
postgres=# \dn
  List of schemas
  Name  |  Owner   
 cron   | postgres
 public | postgres
(2 rows)

postgres=# \dt cron.job
        List of relations
 Schema | Name | Type  |  Owner   
 cron   | job  | table | postgres
(1 row)

Issues faced

[root@localhost pg_cron]# export PATH=/usr/pgsql-12/bin:$PATH
[root@localhost pg_cron]# make && sudo PATH=$PATH make install
Makefile:23: /usr/pgsql-12/lib/pgxs/src/makefiles/ No such file or directory
make: *** No rule to make target `/usr/pgsql-12/lib/pgxs/src/makefiles/'. Stop.

This requires postgresql12-devel to be installed, so started installing it

yum install postgresql12-devel
--> Finished Dependency Resolution
Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (pgdg12)
           Requires: llvm5.0-devel >= 5.0
Error: Package: postgresql12-devel-12.5-1PGDG.rhel7.x86_64 (pgdg12)
           Requires: llvm-toolset-7-clang >= 4.0.1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

Failed again, this requires epel, centos-release-scl to be installed

yum install epel
yum install centos-release-scl

Not able to connect to PostgreSQL

In this document I am going to discuss about some of the most frequent errors faced by users while connecting to PostgreSQL. This is document is to help the beginners who started working on PostgreSQL. PostgreSQL has a process called postmaster which acts as listener process, by default the process listens on 5432.

There are different tools available to connect to PostgreSQL, here I am going to use the psql command line tool but there is no difference in error messages.


[root@localhost ~]# psql
psql: error: FATAL: role “root” does not exist

By default “psql” command line tool use the operating system user as the database user and localhost the hostname if the user name is not passed as argument, here I logged in as the root user and tried to login to PostgreSQL without username which caused the error.


give the username when logging into the database, by default the psql command line take the username as the database name, so no need to pass the database as I am trying to connect to PostgreSQL.

psql -U postgres -h localhost


[root@localhost ~]# psql -U postgres
psql: error: FATAL: Peer authentication failed for user “postgres”


By default PostgreSQL installation using yum repository configure the pg_hba.conf with peer, ident authentication. So change the peer and ident authentication methods to md5 and reload the configuration.

[root@localhost data]# psql -U postgres
Password for user postgres: 
psql (12.5)
Type "help" for help.



[root@localhost data]# psql -h -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “” and accepting
TCP/IP connections on port 5432?

This is the common error users get when they connect to PostgreSQL. The error represents there is no listener for host and port combination.


Most common issue is listener_address parameter is set to ‘localhost’ or port set to a non default value.

postgres=# show listen_addresses ;
(1 row)

postgres=# show port;
(1 row)

Here the issue is listener_address is localhost, so changed it to ‘*’ where it listens on all ip’s in my host. I have only one ip configured, if your server has multiple ip’s then set the ip address on which the PostgreSQL connections should happen.

Configure the listener_address or change the port and restart the PostgreSQL cluster.

postgres=# alter system set listen_addresses to '*';
postgres=# \q
-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2020-12-20 07:12:20.317 PST [3051] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv4 address "", port 5432
2020-12-20 07:12:20.318 PST [3051] LOG:  listening on IPv6 address "::", port 5432
2020-12-20 07:12:20.319 PST [3051] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-12-20 07:12:20.320 PST [3051] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-12-20 07:12:20.332 PST [3051] LOG:  redirecting log output to logging collector process
2020-12-20 07:12:20.332 PST [3051] HINT:  Future log output will appear in directory "log".
server started


-bash-4.2$ psql -h -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “”, user “postgres”, database “postgres”, SSL off

This is another common error which users will face due to missing entry for the user host address (ip address) in pg_hba.conf file. host based authentication file presents in data directory which has a list of ip addresses or host addresses allowed to connect to PostgreSQL.


Make an entry like below for the user host or subnet in pg_hba.conf file. Any ip address with 192.168.X.X will be able to connect to the host.

host	all		all		md5


-bash-4.2$ psql -h -U postgres -d postgres
psql: error: FATAL: sorry, too many clients already

-bash-4.2$ psql -h -U viswamitra -d postgres
psql: error: FATAL: remaining connection slots are reserved for non-replication superuser connections

-bash-4.2$ psql -U viswamitra -d postgres -h
psql: error: FATAL: too many connections for role “viswamitra”

This is also a common error users face after started using the database in application. This errors will come after the connection limit is reached to the maximum configured value.

Connection limit can be set at different levels

  1. cluster – which is applicable for all users and databases
  2. user – which is applicable for the user
  3. database – which is applicable for the given database


Login to the database as a super user, if there are any idle connections from longer time close them and check the application connection pool settings to close the idle connections after some time interval.

postgres=# select pid,datname,usename,application_name,state,now()-state_change as idle_duration from pg_stat_activity where state = 'idle';
 pid  | datname  |  usename   | application_name | state |  idle_duration  
 3656 | alex     | postgres   | psql             | idle  | 00:14:06.647055
 3652 | alex     | postgres   | psql             | idle  | 00:14:11.718486
 3623 | postgres | viswamitra | psql             | idle  | 00:15:47.530912
(3 rows)

postgres=# select pg_terminate_backend(3656);
(1 row)

If there are no idle sessions to cancel and the error is first and second one in the list increase the “max_connections” in postgresql.conf file. This requires a reboot of the PostgreSQL cluster to make it effect.

postgres=# alter system set max_connections to '1000';

/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/project/data restart

For third error (5.3) , the connection limit is configured at the user level, it can be changed using the alter user statement.

postgres=# alter user viswamitra connection limit 100;

Streaming replication in PostgreSQL 12

PostgreSQL has different in built high availability features for application like streaming replication and logical replication. By using streaming replication we can configure two or more standby servers which receive the transactions from master and apply it on standby server, so that the standby will be used as master during the time of master server crash.

PostgreSQL 12 has interesting changes to replication configuration compare to previous versions.

  1. recovery.conf file is removed on standby server and made all the parameters of recovery.conf as parameters in postgresql.conf
  2. A new file introduced “standby.signal” as a replacement to standby_mode parameter, which indicates the server should start in hot standby mode. If this file is missed PostgreSQL will be started as primary.
  3. trigger_file parameter is renamed to promote_triger_file.

Before we dive into replication configuration we will see some of the important configuration parameters used for configuration.

Parameters on master

max_wal_senders – This parameter specifies the maximum number of concurrent connections allowed on the sender from standby(s) or tools like pg_basebackup clients for streaming. The default value is 10. Set this parameter based on number standby(s) and pg_basebackup like tool going to stream the data from a sender.

max_replication_slots – This parameter specifies the maximum number of replication slots that a sender can support, and the number of standby(s)/tools can be allowed to use replication slots to stream data. The default value is 10. Set this parameter based on the number of standby(s)/tools is going to use for streaming.

wal_sender_timeout – With this parameter, a sender/master decides on terminating replication connections that are inactive for longer than the amount of time mentioned for the parameter. This parameter is useful to detect a standby crash or network outage. The default value is 60 seconds. A value of zero disables the timeout mechanism.

wal_level = replica – With this parameter settings, the master server decides on how much information is written to the WAL. The default value of this parameter is replica. With this setting, the sender writes enough data to enable streaming replication.

wal_log_hints = on – This parameter makes a master server write each disk page to WAL with the first modification with hint bits. With this setting, you can use pg_rewind to make a master be standby of a new master after the failover.

Parameters on standby

primary_conninfo – ‘connection string of master/sender’

primary_slot_name = ‘name of the replication slot on sender/master

promote_trigger_file = /trigger/file/location – when this file is created on standby server it make it self as master.

hot_standby = on – This parameter configures a standby(s) to allow connections and execute READ queries during recovery. The default value of this parameter is on.

recovery_target_timeline = latest – during the time of failover it makes the standby to follow the new master server.

Test environment – Master server – Standby server

Replication configuration

On Master server

  1. Configure listen_address to appropriate value, create a user with replication permission
 postgres=# alter system set listen_addresses to '*';
 postgres=# create user viswamitra password 'postgres' replication;

2. Make a replication entry for the user in pg_hba.conf with standby server ip address and reboot the instance

-bash-4.2$ grep viswamitra pg_hba.conf
 host    replication     viswamitra      md5
/usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/data restart

On Slave Server

3. Take a base backup of the master server using pg_basebackup tool

-bash-4.2$ pg_basebackup --pgdata=standby --write-recovery-conf --create-slot --slot=standby1 --verbose --host= --port=5432 --username=viswamitra
 pg_basebackup: initiating base backup, waiting for checkpoint to complete
 pg_basebackup: checkpoint completed
 pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
 pg_basebackup: starting background WAL receiver
 pg_basebackup: created replication slot "standby1"
 pg_basebackup: write-ahead log end point: 0/2000138
 pg_basebackup: waiting for background process to finish streaming …
 pg_basebackup: syncing data to disk …
 pg_basebackup: base backup completed

The command options I used here it self pretty informative, if you want to know more about this options click here.

4. Below is the content of the data directory (I named it as standby) created by previous step.

standby.signal file is created as described previously, below is the content of file.

-bash-4.2$ cat
 Do not edit this file manually!
 It will be overwritten by the ALTER SYSTEM command.
 listen_addresses = '*'
 primary_conninfo = 'user=viswamitra passfile=''/var/lib/pgsql/.pgpass'' host= port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
 primary_slot_name = 'standby1'

-bash-4.2$ /usr/pgsql-12/bin/pg_ctl -D standby start
 waiting for server to start….2020-12-09 10:54:56.497 PST [3955] LOG:  starting PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
 2020-12-09 10:54:56.499 PST [3955] LOG:  listening on IPv4 address "", port 5432
 2020-12-09 10:54:56.499 PST [3955] LOG:  listening on IPv6 address "::", port 5432
 2020-12-09 10:54:56.501 PST [3955] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
 2020-12-09 10:54:56.512 PST [3955] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
 2020-12-09 10:54:56.524 PST [3955] LOG:  redirecting log output to logging collector process
 2020-12-09 10:54:56.524 PST [3955] HINT:  Future log output will appear in directory "log".
 server started

5. Verify the replication configuration

On standby

postgres=# select * from pg_stat_wal_receiver ;
 -[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 pid                   | 3961
 status                | streaming
 receive_start_lsn     | 0/3000000
 receive_start_tli     | 1
 received_lsn          | 0/3000148
 received_tli          | 1
 last_msg_send_time    | 2020-12-09 10:55:26.599891-08
 last_msg_receipt_time | 2020-12-09 10:55:26.608928-08
 latest_end_lsn        | 0/3000148
 latest_end_time       | 2020-12-09 10:54:56.536338-08
 slot_name             | standby1
 sender_host           |
 sender_port           | 5432
 conninfo              | user=viswamitra passfile=/var/lib/pgsql/.pgpass dbname=replication host= port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

On master

-bash-4.2$ psql
 psql (12.4)
 Type "help" for help.
 postgres=# \x
 Expanded display is on.
 postgres=# select * from pg_stat_replication ;
 -[ RECORD 1 ]----+------------------------------
 pid              | 5234
 usesysid         | 16384
 usename          | viswamitra
 application_name | walreceiver
 client_addr      |
 client_hostname  |
 client_port      | 50846
 backend_start    | 2020-12-09 10:54:56.533403-08
 backend_xmin     |
 state            | streaming
 sent_lsn         | 0/3000148
 write_lsn        | 0/3000148
 flush_lsn        | 0/3000148
 replay_lsn       | 0/3000148
 write_lag        |
 flush_lag        |
 replay_lag       |
 sync_priority    | 0
 sync_state       | async
 reply_time       | 2020-12-09 10:56:56.65843-08

Load AWS metrics to PostgreSQL

AWS provide lot of metrics about resource utilization of ec2/rds instance which is useful to analyze the system utilization. There are different ways to read the metrics like aws command line or using python boto3 library etc.

To know the resource utilization trend of the instance we need to maintain this metrics for longer term. As it is costly to store this metrics in Cloudwatch for longer term it would be good to copy the data to some database which will help to find the trend of resource utilization and useful for capacity planning.

Below is a simple way to load aws metrics of rds instance to PostgreSQL. PostgreSQL has bulk loading command COPY which can be used to load metrics directly from other programs. Below is the snippet of code useful to load metrics from aws to PostgreSQL.

Here I am creating three tables for read iops, write iops and cpu utilization and loading the respective metrics into the tables.

 export PGPASSWORD='**********'
 psql -h -U viswamitra -d postgres -c "create table if not exists write_iops(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"
 psql -h -U viswamitra -d postgres -c "create table if not exists read_iops(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"
 psql -h -U viswamitra -d postgres -c "create table if not exists cpu_utilization(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"

PostgreSQL idle sessions filled my disk space

We have an internal application using PostgreSQL as backend with application connection pooling. After few days we started seeing a peculiar issue where the disk allocated for database is getting full and application is running too slow. After restarting the application the space got released and application is working as expected.

After checking the tables which are consuming more space we found that temporary tables are consuming more space(to know more about temporary tables visit this). After checking it further, temporary tables will be dropped once the session is closed but as we are using the application connection pooling which is not managed properly causing this issue.

Before closing or establishing the connection it is recommended to execute the “DISCARD ALL” which will drop all the temporary tables or resources allocated in that session.

test_temp=# create temporary table test_emp(id int, name varchar);
 test_temp=# insert into test_emp values (1,'Viswamitra');
 test_temp=# select * from test_emp ;
  id |    name
   1 | Viswamitra
 (1 row)
 test_temp=# discard all;
 test_temp=# select * from test_emp ;
 ERROR:  relation "test_emp" does not exist
 LINE 1: select * from test_emp ;

In the tomcat connection pooling configuration (validationquery) set the command “discard all” which will release the resources occupied by the session before returning the connection back to the pool.


PostgreSQL: Documentation: 11: DISCARD

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.

Snowflake monitoring queries

This document describes some of the useful queries which helps in monitoring Snowflake account. There are two monitoring schemas in a Snowflake account which has the functions and views to monitor the Snowflake account


Which provides a set of system defined views and metadata with real time statistics about queries. This data is typically retained for up to 7/14 days and is most useful to monitor ongoing queries.


Provides a rich set of historical metadata which is held for up to a year and will be the primary source of usage analysis over time.

check current running queries

select * from table(information_schema.query_history())  where execution_status  in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');

queries running by a user

select * from table(information_schema.QUERY_HISTORY_BY_USER(USER_NAME => '<usr name>'))  where execution_status  in ('RESUMING_WAREHOUSE','RUNNING','QUEUED','BLOCKED');

Time consuming queries

Find top 10 time consuming queries

--- Queries taking more than 10 sec
SET long_running_threshold_seconds = 10;

SELECT * FROM TABLE(information_schema.query_history()) WHERE (execution_status = 'RUNNING' AND datediff('seconds',start_time,current_timestamp()) > $long_running_threshold_seconds);

--- To get the history , list top 10 queries
use schema snowflake.account_usage;

from query_history
order by sum(TOTAL_ELAPSED_TIME) desc
limit 10;

Queries consuming more credits for cloud services

use schema snowflake.account_usage;

from query_history
limit 10;

Queries doing more full partition scans

use schema snowflake.account_usage;

from query_history
order by PARTITIONS_TOTAL desc;

Lock Monitoring in Snowflake

select * from table(information_schema.query_history())  where execution_status  in ('BLOCKED');

To see more information about the blocked sessions execute “show locks in account” command. It will show the sessions who are blocking each other with status as “HOLDING” and “WAITING”.

“show transactions in account” command will show the current running transactions account wise with status.

To cancel any of the transaction (either blocking and blocked)

select system$abort_transaction(<transaction id>);
select system$abort_transaction(1594638696404);

Check query execution plan

If you find there is a long running query and you want to know the execution plan for the query, you can get that in two ways

  • using explain command
  • using query profile


The explain command will give the logical plan of a query.  EXPLAIN compiles the SQL statement, but does not execute it, so EXPLAIN does not require a running warehouse. Although EXPLAIN does not consume any compute credits, the compilation of the query does consume Cloud Service credits, just as other metadata operations do.

use schema snowflake_sample_data.tpch_sf1;

--- Check the query execution plan in text format
explain using text select
       sum(l_quantity) as sum_qty,
       sum(l_extendedprice) as sum_base_price,
       sum(l_extendedprice * (1-l_discount)) as sum_disc_price,
       sum(l_extendedprice * (1-l_discount) * (1+l_tax)) as sum_charge,
       avg(l_quantity) as avg_qty,
       avg(l_extendedprice) as avg_price,
       avg(l_discount) as avg_disc,
       count(*) as count_order
       l_shipdate <= dateadd(day, -90, to_date('1998-12-01'))
 group by
 order by

1:3                    ->Filter  LINEITEM.L_SHIPDATE <= '1998-09-02'  
1:4                         ->TableScan  SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM  L_QUANTITY, L_EXTENDEDPRICE, L_DISCOUNT, L_TAX, L_RETURNFLAG, L_LINESTATUS, L_SHIPDATE  {partitionsTotal=10, partitionsAssigned=10, bytesAssigned=165278208}

NOTE: The EXPLAIN plan is the “logical” explain plan. It shows the operations that will be performed, and their logical relationship to each other. The actual execution order of the operations in the plan does not necessarily match the logical order shown by the plan.

Query profile

Query Profile, available through the Snowflake web interface, provides execution details for a query.

Go to history => Click on Query ID => This will show the query details and execution plan along with time.

Get query plan using query_id

You can get the query id from query history view or history from GUI

--- To get the plan in text format
select SYSTEM$EXPLAIN_JSON_TO_TEXT(system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041')) as explain_plan;

1:0     ->Result  TEST_123.ID  
1:1          ->TableScan  LOCK_TEST.PUBLIC.TEST_123  ID  {partitionsTotal=0, partitionsAssigned=0, bytesAssigned=0}

--- To get the plan in json format
select system$explain_plan_json('01958e99-0187-c08e-0000-00214103b041') as explain_plan;{"GlobalStats":{"partitionsTotal":0,"partitionsAssigned":0,"bytesAssigned":0},"Operations":[[{"id":0,"operation":"Result","expressions":["TEST_123.ID"]},{"id":1,"parent":0,"operation":"TableScan","objects":["LOCK_TEST.PUBLIC.TEST_123"],"expressions":["ID"],"partitionsAssigned":0,"partitionsTotal":0,"bytesAssigned":0}]]}

Login history

Snowflake maintain the user login history for 1 year and it also contain the failed login attempts by the users. you have two methods to read the login history

  • login_history function in information_schema
  • login_history in account_usage schema

information_schema maintain the data for last 7 days

--- login history for last 24 hours
select * from table(information_schema.login_history(dateadd('hours',-24,current_timestamp()),current_timestamp())) order by event_timestamp;

--- login history of a user in last 24 hours
select * from table(information_schema.login_history_by_user('<USERNAME>',dateadd('hours',-1,current_timestamp()),current_timestamp())) order by event_timestamp;

If the requirement to get the login history is beyond 7 days use the snowflake.account_usage.login_history view. There is a delay of 2 hrs to get the data into this view.

select * from snowflake.account_usage.login_history order by event_timestamp;

NOTE: Query_id in query_history is unique for each run. Same query can have different query_id’s.

« Older Entries Recent Entries »