port 5432 failed: FATAL: PAM authentication failed for user
I was facing this issue in RDS PostgreSQL after I grant all roles to postgres user. To resolve this issue run below command
revoke rds_iam from postgres;
node “node2” (ID: 2) is not attached to its upstream node “node1” (ID: 1)
Login to the standby server and run the below command to attach it to repmgr cluster
repmgr -f /etc/repmgr.conf standby follow
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>');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;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 ;
GRANTTo 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=> Reading package lists… DoneN: Skipping acquire of configured file ‘main/binary-i386/Packages’ as repository ‘http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease’ doesn’t support architecture ‘i386’
Replace the line in the file /etc/apt/sources.list.d/pgdg.list with
deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ focal-pgdg mainInconsistent 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)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)
ERROR: You must not be logged in as root to run this tool.
Symptoms
During the installation of Oracle database software getting this error even when running through root user.
Cause
Oracle installation need to be run as a sudo user not with root user.
Solution
Create user with sudo permissions and install Oracle using that user.
In CentOS, use below commands to create a user with sudo permissions
adduser oginstall
passwd oginstall
usermod -aG wheel oginstallorg.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.
- Socket timeout set at the application (driver) level
- 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
Viswamitra's blog 