Postgres Replication Alternatives

31 Oct

Replication Related Concepts

  • Clustering vs Replication
    • Used interchangeably and similar concepts in the DBMS World
    • Clustering in the general sense is distributing load across many servers that appear to an outside application as one server
    • Replication in the general sense involves one master server handling mostly writes and one or more slaves handling reads only. A more advanced variation is that of the master-master configuration, which allows to scale writes as well by having several servers writing at the same time.
    • There are several definitions of clustering, but it can generally be viewed as something that has membership, messaging, redundancy, and automatic failover capabilities. Replication, in contrast, is simply a way to send messages (data) from one server to another.
  • Replication Types
    • Synchronous
      • Postgres 9.1 built-in Synchronous support
      • PgPool II
    • Asynchronous
      • Postgres 9.0 Streaming built-in
      • Slony-1
      • Bucardo
  • Replication Modes
    • Master-Master
    • Master-Slave
    • Statement-Based-Middleware
  • Connection Pooling
  • Load Balancing
  • High Availability
    • Automatic Failover
    • Manual Failover
  • Parallel Queries
  • Performance
    • Asynchronous is Faster
    • Synchronous is Slower

Repmgr vs PgPoolII comparison

Feature Repmgr PgPool II
Proxy Process/Service No intermediary Process/Service. Clients Access Database Directly Clients connect to PgPool II Service which delegates to Postgres servers.
Replication Type Postgres 9.1 Synchronous Replication PgPool II Synchronous Replication
Replication Mode Master Slave Statement Based Middleware
Connection Pooling None. But can be used with Pgbouncer Built-in Pgbouncer
Load Balancing None. PgBouncer can use a TCP load balancer such as LVS or HAProxy Built-In Load Balancing
High Availability Yes. Custom Scripts Available and Monitoring Daemon (repmgrd) Yes. Automatic Failover scripts available
Parallel Queries None available out of the box. Avaialble out of the box
Performance Synchronous is generally slower than Asynchronous. Synchronous (using a custom protocol.)


  • PgPoolII has more features out-of-the-box but Repmgr seems a lot more flexible.
  • RepMgr + pgbouncer should give us all the features we are looking for.



Install and Configure PL-PHP for Postgres 9.1

30 Oct

Prerequisite Packages

Note: Some or all of these packages might already be installed on your system. The Yum install command will notify you if that’s the case.

  • PHP Devel
  • Postgres Devel
  • Libxml
  • Libtool
  • Automake and Autoconf
  • GLIBC Devel
  1. yum install php-devel postgresql91-devel libtool libxml2-devel automake autoconf glibc-devel

Download PHP Source Code


  • If you have an existing PL-PHP version based on an older PHP version (for example, PHP 5.3.3) and want
    to upgrade to a later version (for example, 5.3.8) you will have to repeat the steps below. There is
    no automatic way to upgrade to a later version.


    • mkdir -p /opt/phpsrc
    • cd /opt/phpsrc
    • It’s possible you already have the latest PHP source code downloaded and extracted. If so, skip this step
    • Get the latest version of PHP from
    • tar -xvzf php-5.3.8.tar.gz

Build PHP using the “enable-embed” option

  • To build and install a shared PHP embed library, unpack the PHP tarball
  • cd /opt/phpsrc/php-5.3.8
  • make clean ( If this is first time you are building PHP this command will fail and that is ok )
    • this ensures the build is cleaned from previous runs if any.
  • ./configure –enable-embed
  • make
    • This command will build the file. Might take a few minutes to complete.
  • make install
  • The previous command will install the in /usr/local/lib. We need to copy this to the /usr/lib
    • cp /usr/local/lib/ /usr/lib
  • Update Shared Library Configuration:
    • cd /etc/
    • vi plphp-libs.conf (Create this file)
    • Enter /usr/lib/ and save file
    • ldconfig
      • this will update the CentOS library path configuration.

Download Latest Version of Autoconf

  • Check latest version of autoconf
    • /usr/local/bin/autoconf –version
  • IF version shows as 2.68 then you can skip the steps to download and install below and continue on
    to Download and building PL-PHP
  • mkdir /opt/autoconf
  • cd /opt/autoconf
  • wget
  • tar xvfz autoconf-latest.tar.gz
  • cd autoconf-<LATEST_VERSION>
  • ./configure
  • make clean
  • make
  • make install
    • LATEST autoconf will be installed in /usr/local/bin

Download PL-PHP

{info:title=Make Note}

  • If you have an existing PL-PHP installation and want to upgrade to a later version you will have to repeat the steps below. There is no automatic way to upgrade to a later version.


Download and extract the PL-PHP source code at

Compile, Install PL-PHP and restart Postgres

  • Comple and Install PL-PHP
    • cd /opt/plphp/commandprompt-PL-php-8c2f8ef
    • /usr/local/bin/autoconf (generates the configure script)
    • Set Path so to the embedded PHP that was built earlier
      • PATH=/usr/local/bin:$PATH ; export PATH
    • ./configure –with-postgres=/usr/pgsql-9.1/
    • make clean ( If this is first time you are building PL-PHP this command will fail and that is ok )
    • make
    • make install
  • Restart Postgres
    • service postgresql-9.1 restart

Setup/Configure PL-PHP language support in Postgres

  • cd /opt/plphp/commandprompt-PL-php-8c2f8ef
  • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
    • this step is required because we have both Postgres 9.0 and 9.1 on the same machine
  • psql -d template1 -U postgres < install.sql
    • This will enter pl-php as a valid language in the pg_template tables
  • In the SQL window, run the following command which will create PL/PHP as a ‘trusted’ language in pgsql (
  • Copy/Paste the following command. The commented out section can be used to validate our php environment.
CREATE FUNCTION test_plphp() RETURNS text AS $$

  $variable = ob_get_contents();
  return $variable;

$$ LANGUAGE 'plphp';
  • Validate PHP environment:
    • select test_plphp(); as a Postgres SQL command to view the phpinfo() output.

Configuring extension libraries setting for use in PL-PHP

  • IF YOU ARE UPGRADING FROM POSTGRES 9.0 to 9.x then you may skip the steps in this section.

Setup php.ini

  • PL/PHP looks for php.ini in /usr/local/lib by default.
  • If you have a copy in /etc/php.ini, copy it over to /usr/local/lib
    • cp /etc/php.ini /usr/local/lib
  • Otherwise, copy/paste the php.ini file from another location

Set the extension directory in php.ini

  • cd /usr/local/lib
  • gedit php.ini and edit the following entries
    • For 32 bit OS enter: extension_dir=/usr/lib/php/modules
    • For 64 bit OS enter: extension_dir=/usr/lib64/php/modules/

Restart Postgres

  • service postgresql-9.1 restart


Pgbouncer installation

22 Oct

Install PgBouncer

  1. Download and install latest version ( 2.0 or higher) from
  2. Extract the compressed file and run:
    1. ./configure
    2. make
    3. make install
  1. Download the latest version of Pgbouncer from
  1. # Extract the compressed file and run:
  2. ./configure –prefix=/usr/local –with-libevent=/usr/local
  3. make
  4. make install

Configure Libraries

  1. vi /etc/
    • Note : If it is not created then create a new file libevent-i386.conf in the same folder.
  2. Enter /usr/local/lib/ the save and quit
    • The path in the libevent-i386.conf is the path where the actual .so files are located at. The path is set when we run the ./configure –prefix=/usr/local/
      during the libevent compilation.
  3. Reload the ld configuration with:
    1. ldconfig

Setup Logging and Other Directories

  1. mkdir -p /var/log/pgbouncer
  2. mkdir -p /var/run/pgbouncer
  3. chown apache:apache /var/log/pgbouncer
  4. chown apache:apache /var/run/pgbouncer
  • The chown command sets the directories ownership to the user ‘apache’ and group ‘apache’.

Setup PgBouncer configuration File:

  1. vi /etc/pgbouncer.ini and make changes to match the configuration file below. You can also copy/paste the text below to the /etc/pgbouncer.ini file.
  2. The most important entries are:
    • The databases section
    • The logfile/pidfile entries in the pgbouncer section
    • The lister_addr entry in the pgbouncer section
    • The user entry in the pgbouncer section
    • The pool_mode entry in the pgbouncer section
    • THE MAX_CLIENT entry should be set high enough. In this case it’s set to 10000
  1. See for complete descriptions.


;; database name = connect string

template1 = dbname=template1 user=pgadmin password=pgadmin pool_size=1
shard_1  = dbname=shard_1 user=pgadmin password=pgadmin 

; fallback connect string
;* = host=testserver

;; Configuation section

;;; Administrative settings

logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/

;;; Where to wait for clients

; ip address or * which means all ip-s
;listen_addr =
listen_addr = *
listen_port = 6432
unix_socket_dir = /tmp

user = apache

;;; Authentication settings

; any, trust, plain, crypt, md5
auth_type = any
#auth_file = /var/lib/pgsql/data/global/pg_auth
#auth_file = /etc/pgbuserlist.txt

;;; Users allowed into database 'pgbouncer'

; comma-separated list of users, who are allowed to change settings
admin_users = postgres

; comma-separated list of users who are just allowed to use SHOW command
stats_users = stats, root

;;; Pooler personality questions

; When server connection is released back to pool:
;   session      - after client disconnects
;   transaction  - after transaction finishes
;   statement    - after statement finishes
;pool_mode = session
pool_mode = transaction

; Query for cleaning connection immidiately after releasing from client.
; Query for 8.3+:
; Older versions:
server_reset_query =

; Comma-separated list of parameters to ignore when given
; in startup packet.  Newer JDBC versions require the
; extra_float_digits here.
;ignore_startup_parameters = extra_float_digits

; When taking idle server into use, this query is ran first.
;   SELECT 1
server_check_query = select 1

; If server was used more recently that this many seconds ago,
; skip the check query.  Value 0 may or may not run in immidiately.
server_check_delay = 10

;;; Connection limits
; total number of clients that can connect
max_client_conn = 10000
default_pool_size = 10

; how many additional connection to allow in case of trouble
;reserve_pool_size = 5

; if a clients needs to wait more than this many seconds, use reserve pool
;reserve_pool_timeout = 3

log_connections = 0
log_disconnections = 0

; log error messages pooler sends to clients
log_pooler_errors = 1

; If off, then server connections are reused in LIFO manner
;server_round_robin = 0

;;; Timeouts

;; Close server connection if its been connected longer.
;server_lifetime = 1200

;; Close server connection if its not been used in this time.
;; Allows to clean unneccessary connections from pool after peak.
;server_idle_timeout = 60

;; Cancel connection attepmt if server does not answer takes longer.
;server_connect_timeout = 15

;; If server login failed (server_connect_timeout or auth failure)
;; then wait this many second.
;server_login_retry = 15

;; Dangerous.  Server connection is closed if query does not return
;; in this time.  Should be used to survive network problems,
;; _not_ as statement_timeout. (default: 0)
;query_timeout = 0

;; Dangerous.  Client connection is closed if the query is not assigned
;; to a server in this time.  Should be used to limit the number of queued
;; queries in case of a database or network failure. (default: 0)
;query_wait_timeout = 0

;; Dangerous.  Client connection is closed if no activity in this time.
;; Should be used to survive network problems. (default: 0)
;client_idle_timeout = 0

;; Disconnect clients who have not managed to log in after connecting
;; in this many seconds.
;client_login_timeout = 60

;; Clean automatically created database entries (via "*") if they
;; stay unused in this many seconds.
; autodb_idle_timeout = 3600

;;; Low-level tuning options

;; buffer for streaming packets
;pkt_buf = 2048

;; man 2 listen
;listen_backlog = 128

;; networking options, for info: man 7 tcp

;; linux: notify program about new connection only if there
;; is also data received.  (Seconds to wait.)
;; On Linux the default is 45, on other OS'es 0.
;tcp_defer_accept = 0

;; In-kernel buffer size (linux default: 4096)
;tcp_socket_buffer = 0

;; whether tcp keepalive should be turned on (0/1)
;tcp_keepalive = 0

;; following options are linux-specific.
;; they also require tcp_keepalive=1

;; count of keepaliva packets
;tcp_keepcnt = 0

;; how long the connection can be idle,
;; before sending keepalive packets
;tcp_keepidle = 0

;; The time between individual keepalive probes.
;tcp_keepintvl = 0

Pgbouncer Authentication Settings

  • Pgbouncer requires it’s own userlist file. The client authenticates with the Pgbouncer service first, this is independent
    of the Postgres Database authentication.
  • The userid/password for the Postgres database should be specified in the Databases section of the PgBouncer config file.
  • For our application, there will be a Pgbouncer service available locally for each Apache instance. As the service is available locally we can disable Pgbouncer
    authentication completely by selecting the “any” authentication option.

Start/Stop PgBouncer

  1. Run ulimit -n 20000 before running the pgbouncer start or restart command.
  1. /usr/local/bin/pgbouncer -d /etc/pgbouncer.ini
  2. /usr/local/bin/pgbouncer -R -d /etc/pgbouncer.ini (to restart)

Test PgBouncer Connectivity

  1. psql -U postgres -p 6432 shard_1

{info:title=Make Note}

  • 6432 in the above command is the port that PgBouncer is listening on. Pgbouncer will forward the request to the database mapped to shard_1 in
    the /etc/pgbouncer.ini file.


Connecting to PgBouncer via pg_connect()

  1. The following PHP pg_connect() code can now be used to connect to PgBouncer:
$connection = pg_connect('host=localhost port=6432 dbname=shard_1');

Using the PgBouncer console:

  1. psql -p 6432 pgbouncer (on the instance running pbbouncer)
  2. See for list of admin commands.



Installing Postgres 9.1 on CentOS

20 Oct

Notes on installing Postgres 9.1 on CentOS…

Dump Postgres 9.0 DB if you are upgrading from 9.0 to 9.1

  • mkdir -p /opt/pg/dump
  • Dump Postgres 9.0
    • /usr/pgsql-9.0/bin/pg_dump -U pgadmin -f /opt/pg/dump/shard1_90dump shard_1

STOP Postgres 9.0 if you are upgrading 9.0 to 9.1

  • service postgresql-9.0 stop
  • Ensure no Postgres process is running:
    • ps -ef | grep postgres
  • IF any Postgres process is running kill it:
    • pkill -f postgres
  • Turn off Postgres Service in CentOS Startup
    • chkconfig postgresql-9.0 off

Install and configure the PostgreSQL Yum Repository Configuration

  • mkdir /opt/pgdq91
  • cd /opt/pgdg1
  • Download the Repository Configuruation RPM for the Postgres 9.1
  • Run rpm -ivh pgdg-centos-9.1-1.noarch.rpm
    • The file /etc/yum.repos.d/pgdg-<version>.repo is created
  • Configure the Base repository to ignore request for Postgres RPMs
    • cd /etc/yum.repos.d
    • vi CentOS-Base.repo
      • Add exclude=postgresql* to the bottom of the file
    • vi CentOS-updates.repo
      • Add exclude=postgresql* to the bottom of the file

Install/Upgrade Postgres packages

  • yum install postgresql91 postgresql91-server postgresql91-contrib

Copy Postgres binaries to /usr/bin

  • The postgres91 packages install binaries/executables such as psql to /usr/pgsql-9.1/bin. We need to copy these to /usr/bin so it’s available in the default path.
    • Note: use the \ before cp otherwise you’ll be prompted to confirm each file that is copied/overwritten.
    • \cp /usr/pgsql-9.1/bin/* /usr/bin

If Upgrading From Postgres 9.1 to a later release

  • yum upgrade postgresql91*
  • service postgresql-9.1 restart

Important File and Directory Locations for Postgres

Postgres is installed in the following directories:

  • /usr/pgsql-9.1 – this dir contains the postgres libraries and executables for managing the postgres process.
  • /usr/pgsql-9.1/bin/* and /usr/bin/pg* (Postgres CLI commands)
  • /var/lib/pgsql/9.1 – this dir contains the postgres database and configuration files
  • /var/lib/pgsql/9.1/data/*.conf (Postgres Configuration files)
  • /var/lib/pgsql/9.1/data/pg_log (Postgres Log Files)

Intialize Postgres DB

  • The following command assumes you are using Postgres 9.1 version:
    • service postgresql-9.1 initdb

Setup Postgres service management on CentOS

  • This will automatically start/stop Postgres on system startup/shutdown
    • chkconfig postgresql-9.1 on

User Authentication Configuration

  • Change Postgres 9.1 authentication configuration to use md5
    • vi /var/lib/pgsql/9.1/data/pg_hba.conf
    • change all occurrences of ident and peer as follows. Should look like this:
      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      # IPv4 local connections:
      host    all             all               md5
      # IPv6 local connections:
      host    all             all             ::1/128                 md5


Start Postgres 9.1

  • service postgresql-9.1 start
  • To subsequently stop: service postgresql-9.1 stop

Test psql command line client and setup pgadmin user

  • Test psql access
    • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
      • this step is required because we have both Postgres 9.0 and 9.1 on the same machine
    • /usr/pgsql-9.1/bin/psql -d template1 -U postgres (or, psql -d shard_1 -U pgadmin -E, etc…)
    • Exit from psql prompt by typing Ctrl-D

Setup pgpass file for DB instance

  • Setup pgpass for user root:
    • Ensure you are in root shell, if you are still su – postgres then enter exit
    • vi /root/.pgpass
    • Enter the following, save and exit.


    • Set the correct privileges for the pgpass file
      ***chmod 0600 /root/.pgpass

Stop Postgres

  • service postgresql-9.1 stop

Start Postgres

  • service postgresql-9.1 start

Initialize and Import shard_1 Database


  • export LD_LIBRARY_PATH=/usr/pgsql-9.1/lib:$LD_LIBRARY_PATH
    • this step is required because we have both Postgres 9.0 and 9.1 on the same machine

Create the shard_1 DB

    1. /usr/pgsql-9.1/bin/createdb -U pgadmin -T template1 shard_1

Create pgadmin user

  • /usr/pgsql-9.1/bin/createuser -P pgadmin -U postgres
  • Enter password for new role: xxxx
  • Enter it again: xxxx
  • Shall the new role be a superuser? (y/n) y

Create Extension citext

  • /usr/pgsql-9.1/bin/psql -d template1 -U postgres
    • CREATE EXTENSION citext;

Import shard_1 Database contents

  • /usr/pgsql-9.1/bin/psql -U postgres shard_1 -f /opt/pg/dump/shard1_90dump &> /tmp/pgload123
  • Check the pgload file for any glaring errors.
  • THE ONLY errors you should see should be related to “citext function already exists”. This is because citext in Postgres 9.0 is a contrib module whereas in Progres 9.1 it’s an Extension.

Configuring/Tuning Postgres for Remote Access

Performance Settings.

  • Note: these settings are not suitable for development environments.
  • For a m2.4xlarge EC2 instance:
    • Within /var/lib/pgsql/9.1/data/postgresql.conf:
      • shared_buffers = 17500MB
      • max_connection = 5000
    • effective_cache_size = 34000MB
    • checkpoint_segments = 8
    • max_prepared_transactions = 1000
    • listen_addresses=’*’
    • log_min_messages= FATAL (LOG or ERROR levels cause trigger outputs to be published)

Configure Two-Phase commit

  • Within /var/lib/pgsql/9.1/data/postgresql.conf:
    • Set max_prepared_transactions = 100 in order to enable two-phase commit transactions (required for DDL updates).

TCP/IP configuration

  • For security reasons, a PostgreSQL server “out of the box” doesn’t listen on TCP/IP ports. Instead, it has to be enabled to listen for TCP/IP requests. This can be done by adding listen_addresses=’*’ for Version 8.0.x and above; this will make the server accept connections on any IP interface.
  • cd /var/lib/pgsql/9.1/data/
  • vi postgresql.conf
    • Set listen_addresses=’*’

Remote Host Connectivity Settings

  • vi /var/lib/pgsql/9.1/data/pg_hba.conf
    • This is an example of allowing connections from all hosts. But the “allowed” hosts would be controlled via Firewall settings. This
      would be how we would do it on Amazon EC2.
      Example below:

       TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      # IPv4 local connections:
      host    all             all                  md5
      # IPv6 local connections:
      host    all             all             ::/0                    md5


    • If you want just your Windows desktop to connect to this Postgres server, add ‘host all all $ipOfWindowsOs/24 md5’ in the appropriate section (scroll to the bottom of the file).
    • The $ipOfWindowOs is typically of the form 192.168.X.X, and will be provided by the pgadmin error message initially saying something along the lines of ‘this host does not access…’.
    • You can either have an entry for each host or a set of hosts.
    • See the documentation in the pg_hba.conf file for more info.
    • SEE for more information

Restart Postgres after all Configuration changes

  • service postgresql-9.1 restart


  • Email/Bug report with Postgres
    "Stan S" writes:
    > shard_1=# CREATE TABLE users (nickname CITEXT PRIMARY KEY,pass TEXT   NOT
    > NULL);
    > NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey"
    > for table "users"
    > shard_1=# INSERT INTO users VALUES ( 'larry',  'aaa' );
    > INSERT 0 1
    > shard_1=# INSERT INTO users VALUES ( 'Tom',   'bbb' );
    > ERROR:  could not determine which collation to use for string comparison
    > HINT:  Use the COLLATE clause to set the collation explicitly.
    Hmm, I can't replicate this here ...
    > shard_1=# CREATE TABLE users (nickname CITEXT COLLATE "C" PRIMARY KEY,pass
    > ERROR:  collations are not supported by type citext
    This suggests strongly that you're using a 9.0-or-earlier citext
    installation that you've not upgraded to 9.1.  If that's the right
    guess, you need to do CREATE EXTENSION citext FROM unpackaged
    to fix it.
                            regards, tom lane

Using Capistrano for EC2 Deployment

9 Sep

SSH SETUP on your Development environment

Setup ssh private key

  • Download the Amazon EC2 Private Key (xyz.pem) to your development box.
  • Copy the xyz.pem to the .ssh directory under the /root directory. If you don’t have .ssh directory create one.
    • mkdir -p /root/.ssh
    • cp xyz.pem /root/.ssh
    • chmod 0600 /root/.ssh/xyz.pem
      • Required: Have to make the private key pair NOT world readable.

Test SSH access

  • In a terminal window, login into the the EC2 instance
    • ssh -i ~/.ssh/xyz.pem
      • This will log you in as the as ec2-user
      • To switch to user root use “sudo -i”

Prerequisites for running remote deployment scritps

  • This is probably already configured on the existing EC2 instances. But if you are setting up new EC2 instances
    that you want to test, ensure the following configuration settings

Enable remote sudo access

  • Run the following on each EC2 instance that you want to remotely deploy to
  • sudo -i
  • visudo
  • Comment out the following line.
    Defaults    requiretty


pgpass file setup on DB instance

Install Capistrano on your development box

    • yum install ruby rubygems
    • gem install capistrano capistrano-ext railsless-deploy
  • Create the build file
    • mkdir -p /opt/capistrano
    • Generate the Capistrano Capfile under this directory

Run Deployment Scripts

Run deploy:setup

  • cd /opt/capistrano
  • cap deploy:setup
    • This command will create the deployment directory structure. The directories under releases will not be created just yet.
      Each time you deploy, a new directory will be created under the “releases” directory, and the new version placed there.
      Then, the “current” symbolic link will be updated to point to that directory.

      /opt/apps/appname/current -> /opt/app/appname/releases/20100819001122


Perform a code push/update

  • cd /opt/capistrano
  • cap –set scm_username=<YOUR SVN USERID> –set scm_password=<YOUR_SVN_PASSWORD> deploy
    • ALTERNATIVELY, enter your svn userid and password in the Capfile and ensure you uncomment those lines then you can run just “cap deploy”
    • The deploy task does the following:
      • get’s the latest code from subversion and copies it to /opt/apps/appname/releases on all ec2 instances
      • other custom capistrano tasks are also executed


Install CAS Client in a Java Application Server

6 Sep

Download CAS Client

Configure web.xml

  • Configure filter elements. Has to be the first filter in the web.xml file
                <filter-name>CAS Authentication Filter</filter-name>
                        <param-value></param-value>                 </init-param>
                        <param-value></param-value>                 </init-param>
                <filter-name>CAS Validation Filter</filter-name>
                        <param-value></param-value>                 </init-param>
                        <param-value></param-value>                 </init-param>

                <filter-name>CAS Authentication Filter</filter-name>


                <filter-name>CAS Validation Filter</filter-name>

Configuring Route53 and ELB on Amazon EC2

25 Aug


  • UPDATE: It’s now been determined that Route53 is not a good solution for Private IP/DNS records such as the ones used for Postgres, MYSQL, REDIS etc.
    See for more information. The only alternative now is to use startup (/etc/init.d)
    scripts to update configuration and/or /etc/hosts files on Apache instances to set the private DNS/IP addresses of Postgres, Redis etc.

This wiki page covers installation and configuration of the Route 53 DNS service and Elastic Load Balancing services.

Route 53 and Elastic Load Balancing (ELB)

  • Amazon EC2 instances don’t have static IP and DNS names assigned to them
  • Every time you stop/start an instance we lose the IP address assigned to that instance
  • Several configuration files such as Config.php, pg_hba.conf etc need to be configured with an IP address or hostname.
  • Route 53 is an Amazon EC2 DNS service we can use to map user friendly DNS names to the various EC2 instances
  • However, Route 53 by itself doesn’t resolve the issue of static IP/DNS names as it’s doesn’t assign IP addresses to EC2 instances.
  • ELB can be used to load balance EC2 instances and we get a static DNS name for the each load balancer in the process

Setup Elastic Load Balancing (ELB)

ELB introduction

Creating a Load Balancer

  • Follow the instructions on the Create Load Balancer wizard to assign create and assign instances to the Load Balancer.

Security Settings

  • After creating the Load Balancer you will have to update the Security group for each corresponding instance. This is required so that inbound connection from the load balancer to the instance can bypass the firewall.
  • You’ll need the Load Balancer owner alias and load balancer security group ID. These can be found on the Load Balancer Description tab.
  • Add the owner alias/security group id, for example amazon-elb/amazon-elb-sg to the Inbound Security Group settings for the corresponding instance.

Route 53

  • Amazon Route 53 is a Domain Name System (DNS) web service.
  • DNS records are organized into “hosted zones” that you configure with Route 53’s API.
    • For example, the top-level domains, and would EACH be a hosted zone on Route53.
  • When you create a hosted zone you will get a list of name servers for your hosted zone. You will have inform the registrar (godaddy, network solutions etc) with whom you registered your domain name to update the name servers for your domain to the ones associated with your hosted zone.
  • You can add, delete or change records in each hosted zone.
    • For example, we could create a record that maps the subdomain to the application server load balancer
    • We can also create records that map wildcard subdomain names
    • For example, a record * that points to

Configuring Route53 using a GUI

  • There are several third-party Route 53 GUI tools available. I used
    and it was a solid, easy to use interface. No CLI. No XML.

Configuring Route53 using the Amazon Perl Curl Tool and XML

DNS Alias Mapping for EC2 Loadbalancers

CNAME aliases vs Alias to ELB

  • There are 2 types of “alias record types” that can be created on Route53
    • CNAME aliases where you map one domain name to another
    • Alias to ELB where you map a domain name to a ELB name
  • For our app, we are using Alias to ELB mapping

Postgres pg_hba.conf configuration file and reverse DNS issue

  • We are unable to specify hostnames in the pg_hba.conf file and will use allowing all IPs.
  • This is not a security hole and is discussed below.
The issue is related to the way Postgres performs reverse dns lookups on the hostname specified in the pg_hba.con file. Here is a snippet for the docs: 
If a host name is specified (anything that is not an IP address or a special key word is processed as a potential host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match.

To simulate the reverse and forward dns lookups I used the "dig" network utility.

So while performing the reverse dns lookup the external/Publc IP is returned. But while doing the forward DNS lookup the internal/Private DNS name is returned. Which leads to the mismatch of the DNS names and Postgres marks the hostname resolution as failure. This error is very unique to the way Postgres parses the pb_hba.conf file and does not affect any other DNS related functionality.

To work around this issue we specify "ALL IPs" allowed in the pg_hba.conf file. This is not a security hole because the we have a security group/firewall configuration already in place that allows connections ONLY from the app server instance to the postgres instance.

BTW, according to the documentation specifying a hostname in the pg_hba.conf has an additional performance impact due to the reverse/forward dns lookups. So this setup actually works for us from performance standpoint as well..