PostgreSQL Error - User was holding shared buffer pin for too long

This is little uncommon error which you observe in Replica/Slave

Error -
======
ERROR: canceling statement due to conflict with recovery
DETAIL: User was holding shared buffer pin for too long.

Cause -
=====
The parameter hot_standby_feedback" is set to '1' and it encountered the cancelling statement. This can happen when the VACUUM will have generated a WAL record that needs super exclusive access to the block. Since feedback was enabled that record would not have removed data visible by the query, but still needs to edit the block. The query was pinning that block, so this situation led to a delay on the standby, which then led to cancellation of the query. Looking at the scenario it seems like the contention was at block level which happens rarely.

AWS - CFN - Launch RDS PostgreSQL

This is a small template which can be tuned based on your environment.

{
    "AWSTemplateFormatVersion": "2010-09-09",
    "Description": "AWS CloudFormation Sample Template for creating an Amazon RDS DB instance: Sample template showing
how to create a DB instance with Enhanced Monitoring enabled. **WARNING** This template creates an RDS DB instance. You will
be billed for the AWS resources used if you create a stack from this template.",

    "Parameters": {
        "DBInstanceID": {
            "Default": "pgcfn",
            "Description": "My database instance",
            "Type": "String",
            "MinLength": "1",
            "MaxLength": "63",
            "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",

            "ConstraintDescription": "Must begin with a letter and must not end with a hyphen or contain two consecutive hyphens."
        },
        "DBName": {
            "Default": "rssaha",
            "Description": "My database",
            "Type": "String",
            "MinLength": "1",
            "MaxLength": "64",
            "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
            "ConstraintDescription": "Must begin with a letter and contain only alphanumeric characters."
        },
        "DBInstanceClass": {
            "Default": "db.m5.large",

            "Description": "DB instance class",
            "Type": "String",
            "ConstraintDescription": "Must select a valid DB instance type."
        },
        "DBAllocatedStorage": {
            "Default": "100",
            "Description": "The size of the database (GiB)",
            "Type": "Number",
            "MinValue": "5",
            "MaxValue": "1024",

            "ConstraintDescription": "must be between 20 and 65536 GiB."
        },
        "DBUsername": {
            "NoEcho": "true",
            "Description": "Username for PG database access",
            "Type": "String",
            "MinLength": "1",
            "MaxLength": "16",
            "AllowedPattern": "[a-zA-Z][a-zA-Z0-9]*",
            "ConstraintDescription": "must begin with a letter and contain only alphanumeric characters."
        },

        "DBPassword": {
            "NoEcho": "true",
            "Description": "Password PG database access",
            "Type": "String",
            "MinLength": "8",
            "MaxLength": "41",
            "AllowedPattern": "[a-zA-Z0-9]*",
            "ConstraintDescription": "must contain only alphanumeric characters."
        }
    },
    "Resources": {
        "MyDB": {
            "Type": "AWS::RDS::DBInstance",
            "Properties": {
                "DBInstanceIdentifier": {
                    "Ref": "DBInstanceID"
                },

                "DBName": {
                    "Ref": "DBName"
                },
                "DBInstanceClass": {
                    "Ref": "DBInstanceClass"
                },
                "AllocatedStorage": {
                    "Ref": "DBAllocatedStorage"
                },
                "Engine": "postgres",
                "EngineVersion": "10.6",
                "MasterUsername": {

                    "Ref": "DBUsername"
                },
                "MasterUserPassword": {
                    "Ref": "DBPassword"

                }
            }
        }
    }
}

Install PostgreSQL 12 in Linux Script

As you know PostgreSQL 12 have become GA, the below script can install PostgreSQL in just few minutes. Please find the script below -

#!/bin/bash

set -x

sudo su - <<EOF

/usr/bin/yum -y install gcc

/usr/bin/yum -y install readline-devel

/usr/bin/yum -y install zlib-devel

echo "Adding User for Postgres server"

/usr/sbin/useradd -d /home/postgres -m postgres

read -s -p "Enter password for postgres: " password

echo $password |passwd --stdin postgres

chage postgres -M -1

#/usr/sbin/useradd -d /home/postgres -m postgres

#passwd postgres

#read -p "New password:" postgres

#read -p "Retype new password " postgres

/usr/bin/wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.gz

/bin/tar -xzf postgresql-12.2.tar.gz

cd postgresql-12.2

/root/postgresql-12.2/configure --prefix=/usr/local/pgsql12/

make

make install

/bin/mkdir /usr/local/pgsql12/data

/bin/chown postgres:postgres -R /usr/local/pgsql12

EOF

sudo su - postgres <<EOF

cd /usr/local/pgsql12/bin

/usr/local/pgsql12/bin/initdb -D /usr/local/pgsql12/data

/usr/local/pgsql12/bin/pg_ctl -D /usr/local/pgsql12/data start

EOF

sudo su - <<EOF

/bin/ln -s /usr/local/pgsql12/bin/psql /usr/bin

EOF

Backup Script PostgreSQL

Please use the below script to backup all the DB using pg_dump if pg_dumpall is not an option. Please exclude the DB which you do not want to backup. For example, rdsadmin in case of RDS.And template1 and template0 which are not user related DB

#!/bin/bash
## Backup Script of Postgres
## Script will take backup of individual database and global objects. Script is good if DB size is small and do not need backup of WAL Files
## Author - rahulsaha0309@gmail.com

set -x

DATE=`(date +"%Y-%m-%d")`
export PGPASSWORD=postgres
SIMPLE_DATE=`(date +"%Y%m%d")`
BACKUP_DIRECTORY=/opt/postgresql/postgres_backup/$SIMPLE_DATE
LOG_FILE_DIRECTORY=/opt/postgresql/postgres_backup/backup_log_file
BACKUP_USER=postgres
PG_DUMP=/opt/postgresql/pg96/bin/pg_dump
PORT=5432
PSQL=/opt/postgresql/pg96/bin/psql
DUMPALL=/opt/postgresql/pg96/bin/pg_dumpall

if [ ! -d $LOG_FILE_DIRECTORY ]
then
        /bin/mkdir  -p $LOG_FILE_DIRECTORY
else
        echo "Log File Directory exist"

fi

echo "Starting Backup of all databases"

if [ ! -d $BACKUP_DIRECTORY ]
then
 /bin/mkdir  -p $BACKUP_DIRECTORY
else
 echo "Backup Directory exist"
 
fi

LIST_DB=`$PSQL -t -d postgres -c "select datname from pg_database where datname not like 'template0' and datname not like 'template1';"`

array1=($LIST_DB)

for database in "${array1[@]}"

do
 BACKUP_DATABASE="$PG_DUMP -Fc -U$BACKUP_USER -p $PORT -d $database -f $BACKUP_DIRECTORY/backup_$database.sql"
    echo "Taking backup of DATABASE $database"
 $BACKUP_DATABASE
 
 if [ $? -eq 0 ] 
 then
            echo "Backup of $database completed successfully" 
 else
            echo "Backup of $database Failed" 
 fi

done

echo "BACKUP_GLOBAL_ROLES"

$DUMPALL -U$BACKUP_USER -p $PORT -g -f $BACKUP_DIRECTORY/backup_roles.sql

echo "Backup Completed sucessfully for all roles and databases"

echo " --------------------------"

echo "Cleaning up backup directory more than 7 days"

/bin/find /opt/postgresql/postgres_backup/* -type d -ctime +7 -exec rm -rf {} \;
/bin/find $LOG_FILE_DIRECTORY/* -mtime +7 -exec rm {} \;
#/bin/find /log/archivelog/* -mtime +2 -exec rm {} \;

echo "Cleanup job completed"

Replica Lag in PostgreSQL

Replica Lag in PostgreSQL

It can be calculated in two ways - 

1. Time 

2. Bytes

Time

SELECT

CASE

WHEN pg_last_wal_receive_lsn() = pg_last_wal_replay_lsn() THEN 0

ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())::INTEGER

END

AS replication_lag;

replication_lag

-----------------

              28

(1 row)

Bytes

postgres=# SELECT client_addr, pg_wal_lsn_diff(pg_stat_replication.sent_lsn, pg_stat_replication.replay_lsn) AS byte_lag FROM pg_stat_replication;

client_addr | byte_lag

-------------+----------

127.0.0.1   |        0

(1 row)

postgres=#

Free Space Mapping Vs Visibility Map

Free Space Map ( FSM) Vs Visibility Map (VM)

Free Space Map ( FSM)

When inserting a heap or an index tuple, PostgreSQL uses the FSM of the corresponding table or index to select the page which can be inserted it. All tables and indexes have respective FSMs. Each FSM stores the information about the free space capacity of each page within the corresponding table or index file. All FSMs are stored with the suffix 'fsm', and they are loaded into shared memory if necessary. The extension pg_freespacemap provides the freespace of the specified table/index. 

Visibility Map ( VM)

Vacuum processing is costly; thus, the VM was been introduced in version 8.4 to reduce this cost. The basic concept of the VM is simple. Each table has an individual visibility map that holds the visibility of each page in the table file. The visibility of pages determines whether each page has dead tuples. Vacuum processing can skip a page that does not have dead tuples.

Suppose that the table consists of three pages, and the 0th and 2nd pages contain dead tuples and the 1st page does not. The VM of this table holds information about which pages contain dead tuples. In this case, vacuum processing skips the 1st page by referring to the VM's information.

Create Read only User in PostgreSQL ( RDS and On-Prem)

-- Create a group

CREATE ROLE readaccess;

-- Grant access to existing tables

GRANT USAGE ON SCHEMA public TO readaccess;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password

CREATE USER rahul WITH PASSWORD 'password';

GRANT readaccess TO rahul;

CREATE USER rahul_saha WITH PASSWORD 'password';

GRANT readaccess TO rahul_saha;

It is allowed by default for every user to create tables in public schema. If you want to mitigate this, do the following:

REVOKE ALL ON SCHEMA public FROM public

GRANT ALL ON SCHEMA public TO writeuser