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