Tag Archives: postgres

Install and Configure phpPgAdmin

18 Nov

Install and Configure phpPgAdmin

  • Option 1: Using Yum
    • Note that this install the latest stable version. As of this writing phpPgAdmin 4.x.
    • yum install phpPgAdmin
  • Option 2: Building from source
    • Note that this is required to build a beta package. As of this writing phpPgAdmin 5.x.
    • Download a beta package from http://phppgadmin.sourceforge.net/?page=download
    • Extract the zip file to the document root. For example: /var/www/html/phppgadmin
    • Follow the instructions in the INSTALL file in the extracted directory.
  • Setup a userid/password for phpPgAdmin
    • su – postgres
    • createuser -P pgadmin
    • Enter password for new role: xxxx
    • Enter it again: xxxx
    • Shall the new role be a superuser? (y/n) y
  • Restart Postgres
    • service postgresql-9.0 restart
  • Ensure RewriteRules allow phppgadmin requests. For example, the following Redirect rule matches redirects all request except xhprof, phpmyadmin and phppgadmin to the abc.php.

    RewriteRule !^(/phpmyadmin|/xhprof_html|/phpPgAdmin) /var/www/html/myapp/abc.php

Setup PhpPgAdmin remote access

  • If you want to access phpPgAdmin remotely ensure you set the “Allow from all” entry in /etc/httpd/conf.d/phpPgAdmin.conf

Configure PhpPgAdmin hostname

  • cd /usr/share/phpPgAdmin/conf
  • rm config.inc.php (remove the existing soft link)
  • cp config.inc.php-dist config.inc.php
  • vi config.inc.php and set the $conf.servers.host entry to the server running postgres

Edit open_basedir setting in /etc/php.ini

  • If open_basedir is set in /etc/php.ini add the following to open_basedir
    • open_basedir = /var/www/html:/usr/share/phpPgAdmin/conf:/usr/share/phpPgAdmin

Setup Export/Import tools.

  • If you want to use Export/Import features in phpMyAdmin, run the following command:
  • cp /usr/pgsql-9.0/bin/* /usr/bin

Setting up Postgres 9.0 Streaming Replication

1 Nov

Goal

  • The instructions in this page are specific to the CentOS VM being used for development purposes. For development all the masters/slave are running in the same server/VM. In production, the masters/slaves will
    be in different servers. Make sure you take into different server IP address settings and shared folder settings if you are using this document for a production deployment.
  • 8 total pgsql instances (listening on different ports).
    • pgsql master shard 1 (pg1)
    • 3 pgsql slaves to master shard 1 (pg1_s1, pg1_s2, pg1_s3)
    • pgsql master shard 2 (pg2)
    • 3 pgsql slaves to master shard 2 (pg2_s1, pg2_s2, pg2_s3)

Shutdown the existing Postgres Service

  • service postgresql-9.0 stop

Create the directories that will contain the 6 pgsql instances:

{info:title=Make Note}

  • It’s uncommon to have multiple postgres instances/services running in the same server. However, for development
    environment purposes we will create multiple instances by creating separate DB directories and specifying different
    ports for each postgres service.

{info}

  • There is already a Database currently in /var/lib/pgsql/9.0/data. We will move this to a new folder as follows:
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1
    • cp -r /var/lib/pgsql/9.0/data /var/lib/pgsql/9.0/shards/pg1

Now the remaining 7 pgsql instances:

  • Shard1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg1_s3
  • Shard2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s1
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s2
    • mkdir -p /var/lib/pgsql/9.0/shards/pg2_s3

Setup File permissions

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Setup and Configure Master and Slaves for Shard 1(Ports 5432,5433,5434,5435)

Create an archive directory

  • This is a directory that is shared by the Shard1 masters and slaves.
  • In production this would probably be a Network shared directory. For example, NFS.
  • mkdir -p /var/lib/pgsql/9.0/shards/pg1/archive
  • chown postgres:postgres /var/lib/pgsql/9.0/shards/pg1/archive
  • chmod -R go-rwx /var/lib/pgsql/9.0/shards/pg1/archive

Configure Master Server for Shard1

Edit Master Server pg_hba.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg1/data/pg_hba.conf
  • This sets up authentication for remote slave servers.
  • Add a line at the end of this file as follows. Note for production servers, replace samehost with
    the appropriate slave ip addresses.

/var/lib/pgsql/9.0/shards/pg1/pg_hba.conf

host    replication     pgadmin     samehost           md5

Edit Master Server postgresql.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg1/data/postgresql.conf
  • Edit the following entries as follows:

/var/lib/pgsql/9.0/shards/pg1/data/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby  

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/9.0/shards/pg1/archive/%f'  

max_wal_senders = 5  

wal_keep_segments = 32

Setup and Start Postgres Service Start/Stop Scripts for Master Shard1

  • Rename the existing Postgres service name from postgresql-9.0 to pg1
    • cd /etc/init.d
    • mv postgresql-9.0 pg1
  • Remove the existing Postgres service name from CentOS:
    • chkconfig postgresql-9.0 off
    • chkconfig –del postgresql-9.0
  • Edit the pg1 script so it points to the Database directory. Verify the following entries (LINES 61-63):

    /etc/init.d/pg1

    PGPORT=5432
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1.log
  • Setup the Postgres service name in CentOS:
    • chkconfig –add pg1
    • chkconfig pg1 on
  • Start the service for Master Shard1
  • service pg1 start
  • If startup failed or to ensure proper startup check the latest log file in:
    /var/lib/pgsql/9.0/shards/pg1/data/pg_log/

Get a Snapshot of the Master Server Database

  • Start a Psql session:
    • psql –username=pgadmin –port=5432 –dbname=shard_1
  • After login exectute the following statement to prepare the backup
    • SELECT pg_start_backup(‘shard1_backup’);
    • This command will create a snapshot of the master server
  • Open another terminal window and archive the entire Master Server Data folder
    • cd /var/lib/pgsql/9.0/shards/pg1
    • tar -cjf postgres-pg1.tar.bz2 data
  • In the psql session execute the following command to stop backup mode
    • SELECT pg_stop_backup();

      {info:title=Make Note}

      • Executing the pg_stop_backup() is very important otherwise postgres remains in a backup state
        and might not respond to regular queries.

      {info}

Create Slave instances for Master Shard1

Update Slave DB folder using Master Server Snapshot

  • For pg1_s1
    • cd /var/lib/pgsql/9.0/shards/pg1_s1
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg1_s2
    • cd /var/lib/pgsql/9.0/shards/pg1_s2
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg1_s3
    • cd /var/lib/pgsql/9.0/shards/pg1_s3
    • tar -xjf ../pg1/postgres-pg1.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*

Setup the Slave Service Start/Stop Scripts

  • cd /etc/init.d
  • cp pg1 pg1_s1
  • cp pg1 pg1_s2
  • cp pg1 pg1_s3
  • Edit pg1_s1 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s1

    PGPORT=5433
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s1.log
  • Edit pg1_s2 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s2

    PGPORT=5434
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s2.log
  • Edit pg1_s3 and change the following entries (LINES 61-63):

    /etc/init.d/pg1_s3

    PGPORT=5435
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg1_s3/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg1_s3.log
  • Enable the Slaves as a CentOS service:
    • chkconfig –add pg1_s1
    • chkconfig pg1_s1 on
    • chkconfig –add pg1_s2
    • chkconfig pg1_s2 on
    • chkconfig –add pg1_s3
    • chkconfig pg1_s3 on

Configure the Slave instances for Streaming Replication

  • For pg1_s1
    • cd /var/lib/pgsql/9.0/shards/pg1_s1/data
    • Edit postgresql.conf and mark this instance as hot standby

      /var/lib/pgsql/9.0/shards/pg1_s1/postgresql.conf

      hot_standby=on
    • Create a new file called recovery.conf with the entries below.

      /var/lib/pgsql/9.0/shards/pg1_s1/recovery.conf

      standby_mode          = 'on'
      primary_conninfo      = 'host=localhost port=5432 user=pgadmin password=pgadmin'
      # Specifies a trigger file whose presence should cause streaming replication to
      # end (i.e., failover).
      trigger_file = '/var/lib/pgsql/9.0/shards/pg1/data/trigger'
      restore_command = 'cp /var/lib/pgsql/9.0/shards/pg1/archive/%f %p'
  • For pg1_s2
    • cd /var/lib/pgsql/9.0/shards/pg1_s2/data
    • cp ../../pg1_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg1_s1/data/recovery.conf .
  • For pg1_s3
    • cd /var/lib/pgsql/9.0/shards/pg1_s3/data
    • cp ../../pg1_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg1_s1/data/recovery.conf .

Setup Slave File permissions

We repeat the setting up file permission to ensure any new files created have the same privileges.

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Start Slave Instances

  • service pg1_s1 start
  • service pg1_s2 start
  • service pg1_s3 start
  • If startup failed or to ensure proper startup view these files.
    • For pg1_s1 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s1/data/pg_log/
    • For pg1_s2 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s2/data/pg_log/
    • For pg1_s3 see the latest log file in /var/lib/pgsql/9.0/shards/pg1_s3/data/pg_log/

Verify Streaming Replication processes

  • To verify streaming replication run the following command:
    ps -ef | grep wal
  • If your setup is valid you should see:
    • a “wal write” process – one per master
    • 3 “wal receiver” processes – one for each slave
    • 3 “wal sender” processes – one for each slave

Test Replication

  • In a terminal window start a psql session and connect to the Master:
    psql –username=pgadmin –port=5432 –dbname=shard_1
  • Execute the following SQL statements:
    CREATE TABLE test (test varchar(30));
    INSERT INTO test VALUES (‘Testing 1 2 3’);
  • In another terminal window start a psql session and connect to slave 1:
    psql –username=pgadmin –dbname=shard_1 –port=5433
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 2:
    psql –username=pgadmin –dbname=shard_1 –port=5434
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 3:
    psql –username=pgadmin –dbname=shard_1 –port=5435
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • Now in the Master psql session execute the statement:
    DROP TABLE test;
  • In any one of the slave psql sessions run:
    SELECT * from test;
  • The previous command will generate an error.

Setup and Configure Master and Slaves for Shard 2(Ports 7432,7433,7434,7435)

Initialize Shard2 Master with Shard1 Master Database files

  1. cd /var/lib/pgsql/9.0/shards/pg2
  2. tar -xjf ../pg1/postgres-pg1.tar.bz2
  3. cd data
  4. rm -f postmaster.pid
  5. rm -f pg_log/*

Create an archive directory for Shard2

  • This is a directory that is shared by the Shard2 masters and slaves.

{info:title=Make Note}

  • In production this would probably be a Network shared directory. For example, NFS.

{info}

  • mkdir -p /var/lib/pgsql/9.0/shards/pg2/archive
  • chown postgres:postgres /var/lib/pgsql/9.0/shards/pg2/archive
  • chmod -R go-rwx /var/lib/pgsql/9.0/shards/pg2/archive

Configure Master Server for Shard2

Edit Shard2 Master Server pg_hba.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg2/data/pg_hba.conf
  • This sets up authentication for remote slave servers.
  • Add a line at the end of this file as follows. Note for production servers, replace samehost with
    the appropriate slave ip addresses.

/var/lib/pgsql/9.0/shards/pg2/pg_hba.conf

host    replication     pgadmin     samehost           md5

Edit Shard2 Master Server postgresql.conf file

  • Edit /var/lib/pgsql/9.0/shards/pg2/data/postgresql.conf
  • Edit the following entries as follows:

/var/lib/pgsql/9.0/shards/pg2/data/postgresql.conf

listen_addresses = '*'

wal_level = hot_standby  

archive_mode = on

archive_command = 'cp %p /var/lib/pgsql/9.0/shards/pg2/archive/%f'  

max_wal_senders = 5  

wal_keep_segments = 32

Setup Shard_2 File permissions

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Setup and Start Postgres Service Start/Stop Scripts for Master Shard2

  • Create pg2 startup script using pg1
    • cd /etc/init.d
    • cp pg1 pg2
  • Edit the pg2 script with new PORT and the Database directory. Verify the following entries (LINES 61-63):

    /etc/init.d/pg2

    PGPORT=7432
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2.log
  • Setup the Postgres service name in CentOS:
    • chkconfig –add pg2
    • chkconfig pg2 on
  • Start the service for Master Shard1
  • service pg2 start
  • If startup failed or to ensure proper startup check the latest log file in:
    /var/lib/pgsql/9.0/shards/pg2/data/pg_log/

Dump Shard1 Schema and Create Shard2 Database with schema only

  • cd /var/lib/pgsql/9.0/shards/pg2/data
  • pg_dump –username=pgadmin –port=7432 shard_1 > myapp.schema
  • dropdb –username=pgadmin –port=7432 shard_1
  • createdb –username=pgadmin –port=7432 shard_2
  • psql –username=pgadmin –port=7432 -f myapp.schema shard_2

Get a Snapshot of the Shard2 Master Server Database

  • Start a Psql session:
    • psql –username=pgadmin –port=7432 –dbname=shard_2
  • After login exectute the following statement to prepare the backup
    • SELECT pg_start_backup(‘shard2_backup’);
    • This command will create a snapshot of the master server
  • Open another terminal window and archive the entire Master Server Data folder
    • cd /var/lib/pgsql/9.0/shards/pg2
    • tar -cjf postgres-pg2.tar.bz2 data
  • In the psql session execute the following command to stop backup mode
    • SELECT pg_stop_backup();

      {info:title=Make Note}

      • Executing the pg_stop_backup() is very important otherwise postgres remains in a backup state
        and might not respond to regular queries.

      {info}

Create Shard2 Slave instances for Master Shard2

Update Shard2 Slave DB folder using Master Server Snapshot

  • For pg2_s1
    • cd /var/lib/pgsql/9.0/shards/pg2_s1
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg2_s2
    • cd /var/lib/pgsql/9.0/shards/pg2_s2
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*
  • For pg2_s3
    • cd /var/lib/pgsql/9.0/shards/pg2_s3
    • tar -xjf ../pg2/postgres-pg2.tar.bz2
    • cd data
    • rm -f postmaster.pid
    • rm -f pg_log/*

Setup the Shard2 Slave Service Start/Stop Scripts

  • cd /etc/init.d
  • cp pg2 pg2_s1
  • cp pg2 pg2_s2
  • cp pg2 pg2_s3
  • Edit pg2_s1 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s1

    PGPORT=7433
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s1/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s1.log
  • Edit pg2_s2 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s2

    PGPORT=7434
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s2/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s2.log
  • Edit pg2_s3 and change the following entries (LINES 61-63):

    /etc/init.d/pg2_s3

    PGPORT=7435
    PGDATA=/var/lib/pgsql/${PGMAJORVERSION}/shards/pg2_s3/data
    PGLOG=/var/lib/pgsql/${PGMAJORVERSION}/pg2_s3.log
  • Enable the Slaves as a CentOS service:
    • chkconfig –add pg2_s1
    • chkconfig pg2_s1 on
    • chkconfig –add pg2_s2
    • chkconfig pg2_s2 on
    • chkconfig –add pg2_s3
    • chkconfig pg2_s3 on

Configure the Shard2 Slave instances for Streaming Replication

  • For pg2_s1
    • cd /var/lib/pgsql/9.0/shards/pg2_s1/data
    • Edit postgresql.conf and mark this instance as hot standby

      /var/lib/pgsql/9.0/shards/pg2_s1/postgresql.conf

      hot_standby=on
    • Create a new file called recovery.conf with the entries below.

      /var/lib/pgsql/9.0/shards/pg2_s1/recovery.conf

      standby_mode          = 'on'
      primary_conninfo      = 'host=localhost port=7432 user=pgadmin password=pgadmin'
      # Specifies a trigger file whose presence should cause streaming replication to
      # end (i.e., failover).
      trigger_file = '/var/lib/pgsql/9.0/shards/pg2/data/trigger'
      restore_command = 'cp /var/lib/pgsql/9.0/shards/pg2/archive/%f %p'
  • For pg2_s2
    • cd /var/lib/pgsql/9.0/shards/pg2_s2/data
    • cp ../../pg2_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg2_s1/data/recovery.conf .
  • For pg2_s3
    • cd /var/lib/pgsql/9.0/shards/pg2_s3/data
    • cp ../../pg2_s1/data/postgresql.conf .
      • enter yes to overwrie
    • cp ../../pg2_s1/data/recovery.conf .

Setup Shard2 Slave File permissions

We repeat the setting up file permission to ensure any new files created have the same privileges.

  • The postgres process runs as the user postgres and so we make this directory and everything under it owned by the user postgres
    • chown -R postgres:postgres /var/lib/pgsql/9.0/shards
  • Postgres requires that DB directory folders are not world readable/writeable. Set the permissions to restrict this:
    • chmod -R go-rwx /var/lib/pgsql/9.0/shards

Start Shard2 Slave Instances

  • service pg2_s1 start
  • service pg2_s2 start
  • service pg2_s3 start
  • If startup failed or to ensure proper startup view these files.
    • For pg2_s1 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s1/data/pg_log/
    • For pg2_s2 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s2/data/pg_log/
    • For pg2_s3 see the latest log file in /var/lib/pgsql/9.0/shards/pg2_s3/data/pg_log/

Verify Streaming Replication processes for Shard2

  • To verify streaming replication run the following command:
    ps -ef | grep wal
  • If your setup is valid you should see:
    • 2 “wal write” process – one per master
    • 6 “wal receiver” processes – one for each slave
    • 6 “wal sender” processes – one for each slave

Test Replication for Shard2

  • In a terminal window start a psql session and connect to the Master:
    psql –username=pgadmin –port=7432 –dbname=shard_2
  • Execute the following SQL statements:
    CREATE TABLE test (test varchar(30));
    INSERT INTO test VALUES (‘Testing 1 2 3’);
  • In another terminal window start a psql session and connect to slave 1:
    psql –username=pgadmin –dbname=shard_2 –port=7433
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 2:
    psql –username=pgadmin –dbname=shard_2 –port=7434
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • In another terminal window start a psql session and connect to slave 3:
    psql –username=pgadmin –dbname=shard_2 –port=7435
  • Execute the following SQL statements:
    SELECT * from test;

    • If replication was successul you should see one record displayed.
  • Now in the Master psql session execute the statement:
    DROP TABLE test;
  • In any one of the slave psql sessions run:
    SELECT * from test;
  • The previous command will generate an error.

References

  1. http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
  2. http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use
  3. http://brandonkonkle.com/blog/2010/oct/20/postgres-9-streaming-replication-and-django-balanc/

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.)

Conclusion

  • 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.

References

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 http://php.net/downloads.php#v5
    • 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 libphp5.so file. Might take a few minutes to complete.
  • make install
  • The previous command will install the libphp5.so in /usr/local/lib. We need to copy this to the /usr/lib
    • cp /usr/local/lib/libphp5.so /usr/lib
  • Update Shared Library Configuration:
    • cd /etc/ld.so.conf.d
    • vi plphp-libs.conf (Create this file)
    • Enter /usr/lib/libphp5.so 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 http://ftp.gnu.org/gnu/autoconf/autoconf-latest.tar.gz
  • 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.

{info}

Download and extract the PL-PHP source code at http://github.com/commandprompt/PL-php

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 (https://public.commandprompt.com/projects/plphp/wiki/CreateLang):
    • CREATE LANGUAGE PLPHP
  • Copy/Paste the following command. The commented out section can be used to validate our php environment.
CREATE FUNCTION test_plphp() RETURNS text AS $$

  ob_start();
  phpinfo();
  $variable = ob_get_contents();
  ob_get_clean();
  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

PL-PHP USAGE

Pgbouncer installation

22 Oct

Install PgBouncer

  1. Download and install latest version ( 2.0 or higher) from http://monkey.org/~provos/libevent/
  2. Extract the compressed file and run:
    1. ./configure
    2. make
    3. make install
  1. Download the latest version of Pgbouncer from http://pgfoundry.org/frs/?group_id=1000258
  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/ld.so.conf.d/libevent-i386.conf
    • 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 http://pgbouncer.projects.postgresql.org/doc/config.html#_pool_configuration# for complete descriptions.

/etc/pgbouncer.ini

;; database name = connect string
[databases]

template1 = host=pgsql.mydomain.net dbname=template1 user=pgadmin password=pgadmin pool_size=1
shard_1  =  host=pgsql.mydomain.net dbname=shard_1 user=pgadmin password=pgadmin 

; fallback connect string
;* = host=testserver

;; Configuation section
[pgbouncer]

;;;
;;; Administrative settings
;;;

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

;;;
;;; Where to wait for clients
;;;

; ip address or * which means all ip-s
;listen_addr = 127.0.0.1
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+:
;   DISCARD ALL;
;
; Older versions:
;   RESET ALL; SET SESSION AUTHORIZATION DEFAULT
;
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.

{info}

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 http://pgbouncer.projects.postgresql.org/doc/usage.html#_quick_start for list of admin commands.

References

  1. http://wiki.postgresql.org/wiki/PgBouncer
  2. http://filip.rembialkowski.net/pgbouncer-mini-howto-benchmark/
  3. http://blog.enricostahn.com/2009/06/25/phps-pg_connect-via-pgpool-ii-vs-pgbouncer-vs-native.html