AWS - Create Aurora Cluster using Ruby SDK

Below is the script which can used to create Aurora Cluster using Ruby SDK

ec2-user@ip-172-31-46-131 .aws]$ cat create.rb
require 'aws-sdk-rds'  # v2: require 'aws-sdk'

rds = Aws::RDS::Client.new(region: 'us-east-1')
     
dbcluster = rds.create_db_cluster({
db_cluster_identifier: "apg-cluster3",
vpc_security_group_ids: ["sg-52630c23"],
db_subnet_group_name: "newsubnetgroup",
engine: "aurora-postgresql",
master_username: "master",
master_user_password: "password",
})

Below is the command which is used to run the ruby script.

[ec2-user@ip-172-31-46-131 .aws]$ ruby reate.rb

Find out blocked queries in PostgreSQL version 9.5 and earlier

Below is the query that can be used to find out blocked queries in PostgreSQL version 9.6 onwards -

SELECT blockingl.relation::regclass,

  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,

  blockedl.mode as blocked_mode,

  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,

  blockingl.mode as blocking_mode

FROM pg_catalog.pg_locks blockedl

JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid

JOIN pg_catalog.pg_locks blockingl ON(blockingl.relation=blockedl.relation

  AND blockingl.locktype=blockedl.locktype AND blockedl.pid != blockingl.pid)

JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid

WHERE NOT blockedl.granted;

Once we figure out the blocked query, we can kill the blocked query using the PID and below command -

postgres=# select pg_terminate_backend(3014);

We can also figure out if queries are waiting from OS using the below command -

[rahul@linux] ps -ef | grep wait 

Find out blocked queries in PostgreSQL version 9.6 onwards

Below is the query that can be used to find out blocked queries in PostgreSQL version 9.6 onwards -

select pid,
       usename,
       pg_blocking_pids(pid) as blocked_by,
       query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;

AWS - Create Aurora PostgreSQL Cluster and 2 Instance ( Random Identifier) Using CloudFormation

Below is the CloudFormation Template for spinning up Aurora Cluster and 2 instance(One write and one reader node) along with Random Cluster and node identifier name -

AWSTemplateFormatVersion: 2010-09-09
Description: A basic CloudFormation template for an RDS Aurora PostgreSQL cluster.
Parameters:
    DatabaseInstanceType:
        Default: db.r4.large
        AllowedValues:
            - db.r4.large
            - db.r4.xlarge
            - db.r4.2xlarge
            - db.r4.4xlarge
            - db.r4.8xlarge
            - db.r4.16xlarge
        Description: The instance type to use for the database.
        Type: String
    DatabasePassword:
        Default: password
        AllowedPattern: "[a-zA-Z0-9]+"
        ConstraintDescription: must contain only alphanumeric characters.
        Description: The database admin account password.
        MaxLength: '41'
        MinLength: '8'
        NoEcho: 'true'
        Type: String
    DatabaseUsername:
        Default: aurorapg
        AllowedPattern: "[a-zA-Z0-9]+"
        ConstraintDescription: must contain only alphanumeric characters.
        Description: The database admin account user name.
        MaxLength: '16'
        MinLength: '1'
        Type: String
    DatabaseClusterParameterGroupName:
        Type: String
        Default: "default.aurora-postgresql9.6"
    DatabaseBackupRetentionPeriod:
        Type: String
        Default: 35
        Description: The database backup retention period in days.
    DatabaseSubnets:
        Default: "subnet-6749a72f,subnet-c3033698"
        Description: The subnets to place database instances in.
        Type: List<AWS::EC2::Subnet::Id>
    DatabaseSecurityGroups:
        Default: sg-0ee3d27f
        Type: List<AWS::EC2::SecurityGroup::Id>
        Description: Security groups to apply to the RDS cluster.
Metadata:
    AWS::CloudFormation::Interface:
        ParameterGroups:
            - Label:
                default: default.aurora-postgresql9.6
              Parameters:
                - DatabaseInstanceType
                - DatabaseName
                - DatabaseUsername
                - DatabasePassword
                - DatabaseSubnets
                - DatabaseSecurityGroups
                - DatabaseBackupRetentionPeriod
                - DatabaseClusterParameterGroupName
        ParameterLabels:
            DatabaseInstanceType:
                default: Database Instance Type
            DatabaseClusterParameterGroupName:
                default: DB cluster parameter group
            DatabasePassword:
                default: Database Password
            DatabaseUsername:
                default: Database Username
            DatabaseBackupRetentionPeriod:
                default: Database Backup Retention Period
            DatabaseSubnets:
                default: Database Subnets
            DatabaseSecurityGroups:
                default: Database Security Groups
Resources:
    DatabaseSubnetGroup:
        Type: AWS::RDS::DBSubnetGroup
        Properties:
            DBSubnetGroupDescription: CloudFormation managed DB subnet group.
            SubnetIds:
                Ref: DatabaseSubnets
    DatabaseCluster:
        Type: AWS::RDS::DBCluster
        Properties:
            Engine: aurora-postgresql
            MasterUsername:
                Ref: DatabaseUsername
            MasterUserPassword:
                Ref: DatabasePassword
            BackupRetentionPeriod:
                Ref: DatabaseBackupRetentionPeriod
            DBClusterParameterGroupName:
                Ref: DatabaseClusterParameterGroupName
            PreferredBackupWindow: 02:00-03:00
            PreferredMaintenanceWindow: mon:03:00-mon:04:00
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup
            VpcSecurityGroupIds:
                Ref: DatabaseSecurityGroups
    DatabasePrimaryInstance:
        Type: AWS::RDS::DBInstance
        Properties:
            Engine: aurora-postgresql
            DBClusterIdentifier:
                Ref: DatabaseCluster
            DBInstanceClass:
                Ref: DatabaseInstanceType
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup
    DatabaseReplicaInstance:
        Type: AWS::RDS::DBInstance
        Properties:
            Engine: aurora-postgresql
            DBClusterIdentifier:
                Ref: DatabaseCluster
            DBInstanceClass:
                Ref: DatabaseInstanceType
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup

AWS - Launch Aurora PostgreSQL using CloudFormation With Custom Instance Identifier

Below is the CloudFormation Template for spinning up Aurora PostgreSQL Cluster and 2 instance( Read and Write Node) with custom Instance Identifier using CloudFormation With Custom Instance Identifier:


AWSTemplateFormatVersion: 2010-09-09
Description: A basic CloudFormation template for an RDS Aurora PostgreSQL cluster.
Parameters:
    DatabaseInstanceType:
        Default: db.r4.large
        AllowedValues:
            - db.r4.large
            - db.r4.xlarge
            - db.r4.2xlarge
            - db.r4.4xlarge
            - db.r4.8xlarge
            - db.r4.16xlarge
        Description: The instance type to use for the database.
        Type: String
    DatabasePassword:
        Default: password
        AllowedPattern: "[a-zA-Z0-9]+"
        ConstraintDescription: must contain only alphanumeric characters.
        Description: The database admin account password.
        MaxLength: '41'
        MinLength: '8'
        NoEcho: 'true'
        Type: String
    DatabaseUsername:
        Default: aurorapg
        AllowedPattern: "[a-zA-Z0-9]+"
        ConstraintDescription: must contain only alphanumeric characters.
        Description: The database admin account user name.
        MaxLength: '16'
        MinLength: '1'
        Type: String
    DatabaseClusterParameterGroupName:
        Type: String
        Default: "default.aurora-postgresql9.6"
    DatabaseBackupRetentionPeriod:
        Type: String
        Default: 35
        Description: The database backup retention period in days.
    DatabaseSubnets:
        Default: "subnet-6749a72f,subnet-c3033698"
        Description: The subnets to place database instances in.
        Type: List<AWS::EC2::Subnet::Id>
    DatabaseSecurityGroups:
        Default: "sg-0ee3d27f"
        Type: List<AWS::EC2::SecurityGroup::Id>
        Description: Security groups to apply to the RDS cluster.
    DBIdentifierNameMaster:
        Default: "aur1"
        Description: "The string that will be prefixed to each instance name"
        Type: "String"
        MinLength: "3"
        MaxLength: "10"
        AllowedPattern: "[a-z0-9]*"
        ConstraintDescription: "Identifier names must be 3-6 characters and contain only a-z and 0-9."
    DBIdentifierNameReplica:
        Default: "aur2rr"
        Description: "The string that will be prefixed to each instance name"
        Type: "String"
        MinLength: "3"
        MaxLength: "10"
        AllowedPattern: "[a-z0-9]*"
        ConstraintDescription: "Identifier names must be 3-10 characters and contain only a-z and 0-9."
Metadata:
    AWS::CloudFormation::Interface:
        ParameterGroups:
            - Label:
                default: default.aurora-postgresql9.6
              Parameters:
                - DatabaseInstanceType
                - DatabaseName
                - DatabaseUsername
                - DatabasePassword
                - DatabaseSubnets
                - DatabaseSecurityGroups
                - DatabaseBackupRetentionPeriod
                - DatabaseClusterParameterGroupName
        ParameterLabels:
            DatabaseInstanceType:
                default: Database Instance Type
            DatabaseClusterParameterGroupName:
                default: DB cluster parameter group
            DatabasePassword:
                default: Database Password
            DatabaseUsername:
                default: Database Username
            DatabaseBackupRetentionPeriod:
                default: Database Backup Retention Period
            DatabaseSubnets:
                default: Database Subnets
            DatabaseSecurityGroups:
                default: Database Security Groups
Resources:
    DatabaseSubnetGroup:
        Type: AWS::RDS::DBSubnetGroup
        Properties:
            DBSubnetGroupDescription: CloudFormation managed DB subnet group.
            SubnetIds:
                Ref: DatabaseSubnets
    DatabaseCluster:
        Type: AWS::RDS::DBCluster
        Properties:
            Engine: aurora-postgresql
            MasterUsername:
                Ref: DatabaseUsername
            MasterUserPassword:
                Ref: DatabasePassword
            BackupRetentionPeriod:
                Ref: DatabaseBackupRetentionPeriod
            DBClusterParameterGroupName:
                Ref: DatabaseClusterParameterGroupName
            PreferredBackupWindow: 02:00-03:00
            PreferredMaintenanceWindow: mon:03:00-mon:04:00
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup
            VpcSecurityGroupIds:
                Ref: DatabaseSecurityGroups
    DatabasePrimaryInstance:
        Type: AWS::RDS::DBInstance
        Properties:
            Engine: aurora-postgresql
            DBClusterIdentifier:
                Ref: DatabaseCluster
            DBInstanceClass:
                Ref: DatabaseInstanceType
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup
            DBInstanceIdentifier:
                Ref: DBIdentifierNameMaster
    DatabaseReplicaInstance:
        Type: AWS::RDS::DBInstance
        Properties:
            Engine: aurora-postgresql
            DBClusterIdentifier:
                Ref: DatabaseCluster
            DBInstanceClass:
                Ref: DatabaseInstanceType
            DBSubnetGroupName:
                Ref: DatabaseSubnetGroup
            DBInstanceIdentifier:
                Ref: DBIdentifierNameReplica

Install Pgbouncer in Linux

Environment - Amazon Linux/RHEL

Pre-Requiste:

Should have PG installed.( Please refer - http://postgresql-open-source.blogspot.in/2017/12/postgresql-installation-script-source.html)

Installation Steps-

1. Download and install libevent from 'http://libevent.org/'(latest release) which is necessary for pgbouncer

sudo su -
yum install wget
wget https://github.com/libevent/libevent/releases/download/release-2.1.8-stable/libevent-2.1.8-stable.tar.gz
tar -xzf libevent-2.1.8-stable.tar.gz
cd libevent-2.1.8-stable
yum install gcc
./configure
make
make install

2. Download pgbouncer from this link 'http://pgfoundry.org/frs/?group_id=1000258'

wget http://pgfoundry.org/frs/download.php/3393/pgbouncer-1.5.4.tar.gz
tar -xzf pgbouncer-1.5.4.tar.gz
cd pgbouncer-1.5.4
./configure --prefix=/usr/local
make
make install

3. Enter the lib file location into '/etc/ld.so.conf.d/libevent-i386.conf' and save the file and load

vi /etc/ld.so.conf.d/libevent-i386.conf and enter /usr/local/lib/ and save the file and load
ldconfig

4. Create log directory

mkdir -p /var/log/pgbouncer
mkdir -p /var/run/pgbouncer

5. Change the ownership to 'postgres' for both the directory
chown postgres:postgres /var/log/pgbouncer
chown postgres:postgres /var/run/pgbouncer


6. Change the ownership to 'postgres' for pgbouncer binary

cd /usr/local/bin/
chown -R postgres:postgres pgbouncer


7. copy all the roles and passoword of postgres

[postgres@ip-172-31-74-77 pgsql10]$ psql

postgres=# \o /usr/local/pgsql10/pgbouncer.auth
postgres=# select '"'||rolname||'"'||' "'||rolpassword||'"' from pg_authid;
postgres=# \o
postgres=# \! cat /usr/local/pgsql10/pgbouncer.auth
                     ?column?                   
--------------------------------------------------
 "new_user" "md500084d8780bcdf83b7345e04c09e0584"
 "postgres" "md532e12f215ba27cb750c9e093ce4b5127"
(7 rows)


8. Edit the 'pgbouncer.auth' file to have only 'role' and 'password'

vi /usr/local/pgsql10/pgbouncer.auth

"new_user" "md500084d8780bcdf83b7345e04c09e0584"
"postgres" "md532e12f215ba27cb750c9e093ce4b5127"


9. Edit the Configuration File

vi /etc/pgbouncer.ini

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp
#auth_type = any
auth_type = trust
auth_file = /usr/local/pgsql10/pgbouncer.auth
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1



10. Start pgbouncer

[postgres@ip-172-31-74-77 bin]$ /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
2017-12-15 04:15:34.821 4794 LOG File descriptor limit: 1024 (H:4096), max_client_conn: 1000, max fds possible: 1010


11. Check if pgbouncer is installed successfully -

[postgres@ip-172-31-74-77 pgbouncer]$ psql -p 6432 -U postgres -d pgbouncer
psql (10.1, server 1.5.4/bouncer)
Type "help" for help.

pgbouncer=# show help;
NOTICE:  Console usage
DETAIL: 
SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
SHOW DNS_HOSTS|DNS_ZONES
SET key = arg
RELOAD
PAUSE [<db>]
RESUME [<db>]
KILL <db>
SUSPEND
SHUTDOWN
SHOW
pgbouncer=#

PostgreSQL Installation Script - Source Installation

Just copy the below lines and create a file. Your linux system will be ready with custom PostgreSQL installation. You can modify the script based on your need. You can consider the below lines as a framework. Cheers !!

Environment - Amazon Linux

#!/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/v10.1/postgresql-10.1.tar.gz

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

cd postgresql-10.1

/root/postgresql-10.1/configure --prefix=/usr/local/pgsql10/

make

make install

/bin/mkdir /usr/local/pgsql10/data

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

EOF

sudo su - postgres <<EOF

cd /usr/local/pgsql10/bin

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

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

EOF

sudo su - <<EOF

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

EOF

sudo su - postgres <<EOF

/usr/bin/perl -i -p -e "s/\#listen_addresses = 'localhost'/listen_addresses = '*'/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/shared_buffers = 128MB/shared_buffers = 2GB/;" /usr/local/pgsql10/data/postgresql.conf

/usr/bin/perl -i -p -e "s/\#port = 5432/port = 5433/;" /usr/local/pgsql10/data/postgresql.conf

/usr/local/pgsql10/bin/pg_ctl -D /usr/local/pgsql10/data restart

EOF