Category Archives: PostgreSQL

List all child tables of a partitioned table in PostgreSQL 14

To find the child tables of a partitioned table in PostgreSQL 14 use the below query.

select relname as table_name
from pg_class join pg_inherits on (pg_class.oid=pg_inherits.inhrelid and relkind='r')
where inhparent in (select oid from pg_class where relname='<parent_table_name>');

Identify a role has execute permission on function in PostgreSQL

To identify a role has execute permission on function, PostgreSQL has a catalog function(has_function_privilege) which makes it easy. Below is the simple example.

I have created a simple schema test with a function row_count() which return the row count of employee table. I have grated execut permission on the function to reader role.

postgres=# create schema test;
CREATE SCHEMA
postgres=# revoke USAGE on SCHEMA test from PUBLIC ;
REVOKE
postgres=# revoke execute on all functions in schema test from public;
REVOKE
postgres=# set search_path to test, public;
SET
postgres=# CREATE OR REPLACE FUNCTION row_count()
postgres-#  RETURNS integer AS $total$
postgres$#  declare
postgres$#  total integer;
postgres$#  BEGIN
postgres$#  SELECT count(1) into total FROM public.employee;
postgres$#  RETURN total;
postgres$#  END;
postgres$#  $total$
postgres-#  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# create role reader;
CREATE ROLE
postgres=# create role temp;
CREATE ROLE
postgres=# grant usage on schema test to reader ;
GRANT
postgres=# grant USAGE on SCHEMA test to temp;
GRANT
postgres=# grant SELECT on TABLE public.employee to reader ;
GRANT
postgres=# grant EXECUTE on FUNCTION test.row_count to reader ;
GRANT

To know the reader and temp roles has permissions on the row_count() function

postgres=# SELECT has_function_privilege('reader', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 t
(1 row)

postgres=# SELECT has_function_privilege('temp', 'test.row_count()', 'execute');
 has_function_privilege 
------------------------
 f
(1 row)

The reader role has permissions to execute but not the temp.

postgres=# set role reader ;
SET
postgres=> 
postgres=> select test.row_count();
 row_count 
-----------
         1
(1 row)

postgres=> set role temp;
SET
postgres=> 
postgres=> select test.row_count();
ERROR:  permission denied for function row_count
postgres=> 

Inconsistent behavior of json_to_recordset/jsonb_to_recordset in PostgreSQL >=10 version

Recently we have upgraded from PostgreSQL 9.6 to 11 version. Post upgradation we started observing inconsistent behavior of json/jsonb_to_recordset functions.

JSON embedded inside the JSON as a string can be converted to records using json/jsonb_to_recordset functions.

In PostgreSQL 9.6, it is able to parse the json inside json and parse the escape characters as special symbols.

postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
-----------------------------------------------------+---
{"test": "test", "type_id": "9", "currency": "INR"} | 1
2 | 2
(2 rows)

In PostgreSQL 10/11/12, It is parsing the escape symbols as a part of json string giving them in output.

postgres=# select * from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a jsonb, b int);
a | b
--------------------------------------------------------------+---
"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}" | 1
"2" | 2
(2 rows)

To achieve the same behavior as PostgreSQL 9.6 in PostgreSQL 10/11/12, convert the json string to text and then convert to jsonb/json.

postgres=# select x.a::json,b from json_to_recordset('[{"a":"{\"type_id\":\"9\",\"test\":\"test\",\"currency\":\"INR\"}","b":1},{"a":"2","b": 2}]') as x(a text, b int);
a | b
------------------------------------------------+---
{"type_id":"9","test":"test","currency":"INR"} | 1
2 | 2
(2 rows)

https://www.postgresql.org/message-id/flat/6c07d61e-be88-a2d2-f158-023175b9e03f%40dunslane.net#a3518a193da8c7b87547a4694608d062

Get FIRST DAY AND LAST DAY OF A MONTH IN Postgresql

Below is the functions to get first day and last day of month in PostgreSQL.

To get first day of month, below is the plpgsql function, this function take the date value as input and return the first day of that month.

CREATE OR REPLACE FUNCTION first_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1))::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

part=# select first_day('2020-07-03');
 first_day
------------
 2020-07-01
(1 row)

To get the last of month, below is the plpgsql function, this function take the date value as input and return the last day of month.

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql'
IMMUTABLE STRICT;

part=# select last_day('2020-07-03');
  last_day
------------
 2020-07-31
(1 row)

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

Symptoms
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:350)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
	at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:236)
	at PostgreSqlExample.main(PostgreSqlExample.java:15)
........................
........................
Cause

Client connection got dropped before the query execution completed by PostgreSQL Server. Below are some of the major reasons for this.

  1. Socket timeout set at the application (driver) level
  2. Connection dropped due to issues at network/firewall
Solution
  • Check the timeout parameters configured in the application, below is an example where the socket timeout is set for 10 seconds.
jdbc:postgresql://192.168.2.3:5432/postgres?socketTimeout=10&ssl=true&sslmode=prefer
  • Check the firewall/network related settings

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 10.10.12.13 -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 postmaster.pid

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

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'$$);
 schedule
----------
       42

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

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

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

SELECT cron.unschedule(42);
 unschedule
------------
          t
git clone https://github.com/citusdata/pg_cron.git
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 postgresql.auto.conf

# 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;
CREATE EXTENSION
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/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/pgsql-12/lib/pgxs/src/makefiles/pgxs.mk'. 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.

ERROR – 1

[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.

Solution

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

ERROR – 2

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

Solution

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.

postgres=# 

ERROR – 3

[root@localhost data]# psql -h 192.168.159.151 -U postgres -d postgres
psql: error: could not connect to server: Connection refused
Is the server running on host “192.168.159.151” 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.

Solution

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

postgres=# show listen_addresses ;
 listen_addresses 
------------------
 localhost
(1 row)

postgres=# show port;
 port 
------
 5432
(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 '*';
ALTER SYSTEM
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 "0.0.0.0", 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".
 done
server started

ERROR – 4

-bash-4.2$ psql -h 192.168.159.151 -U postgres -d postgres
psql: error: FATAL: no pg_hba.conf entry for host “192.168.159.151”, 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.

Solution

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		192.168.0.0/16		md5

ERROR – 5

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

-bash-4.2$ psql -h 192.168.159.151 -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 192.168.159.151
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

Solution

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);
 pg_terminate_backend 
----------------------
 t
(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';
ALTER SYSTEM
postgres=# 

/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;
ALTER ROLE

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

192.168.159.147 – Master server

192.168.159.151 – 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 '*';
 ALTER SYSTEM
 postgres=# create user viswamitra password 'postgres' replication;
 CREATE ROLE
 postgres=#

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      192.168.159.151/32      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=192.168.159.147 --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
 -bash-4.2$

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 postgresql.auto.conf file.

-bash-4.2$ cat postgresql.auto.conf
 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=192.168.159.147 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
 primary_slot_name = 'standby1'
 -bash-4.2$

-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 "0.0.0.0", 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".
  done
 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           | 192.168.159.147
 sender_port           | 5432
 conninfo              | user=viswamitra passfile=/var/lib/pgsql/.pgpass dbname=replication host=192.168.159.147 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      | 192.168.159.151
 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

« Older Entries