Uncomment all lines in VI Editor

Uncommenting Multiple Lines

    1. Press CTRL + V to enable visual block mode.
    2. Move down and select the lines till you want to uncomment.
    3. press x and it will uncomment all the selected lines at once.

Comment out all lines in VI Editor

Commenting Multiple Lines

    1. First, press ESC

    2. Go to the line from which you want to start commenting. Then, press ctrl + v, this will enable the visual block mode.

    3. use the down arrow to select multiple lines that you want to comment.

    4. Now, press SHIFT + I to enable insert mode.

    5. Press # and it will add a comment to the first line. Then press ECS and wait for a second, # will be added to all the lines.

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"