Replication in PostgreSQL 9.0

Word "Replication" means a process of sharing information so as to ensure consistency between redundant resources, such as software or hardware components, to improve reliability, fault-tolerance, or accessibility.

Replication is very interesting subject in any databases. In database competition world, PostgreSQL has its own uniqueness in RDBMS Open source for High availability. Latest PostgreSQL 9.1 has in-built support of Synchronous and Asynchronous replication.  In-built Asynchronous replications are Warm Standby, Hot Standby and Streaming Replication and with third party tools Slony,Londiste,Mammoth etc. Below chart will help you to understand about available Synchronous and Asynchronouse replication.

WAL Shipping (Hot Standby and Warm Standby): 

PostgreSQL has the ability to ship WAL's to another Server i.e, Standby. The Standby server will be running in recovery mode with the pg_standby utility applying the WAL's. Primary Server generates archives (a copy of WAL, usually 16 MB file) and sends them to multiple slaves, later it will be applied by pg_standby utility. 

• Warm Standby: Primary generates archives and feed them to Slave. Its a WAL Shipping to slave. Slave will be in continous recovery and not accessible for reads.
• Hot Standby: Hot Standby is the name for the capability to run queries on a database that is currently performing archive recovery. In Hot Standby slaves can be used for read-only access.

Disadvantages:

• Slave applie's WAL's periodically not continously, means only completed XLOG's will be available to slave as WAL archives and those will be applied. So, lag will be the unfilled or uncompleted WAL which has not generated archive. Data loss will be minimum of one WAL(16 MB).

Trigger Based Replication :

In trigger based replication, tools like Slony,Londiste, Mammoth uses ON INSERT, ON UPDATE, ON DELETE triggers on tables to maintain replication between Master and Slave. Slave will hold consistent Snapshots. 

Streaming Replication :

Its also called as Binary replication. PostgreSQL, XLOG's records generated at primary will be shipped to Standby via network.  Lag in streaming replication is very minimum like single transaction depending on Network Speed and Hot Standby Settings. Multiple Slave can be configured. Streaming replication comes with additional process 'WAL SENDER' at Primary and 'WAL RECEIVER' at Standby.

Advantages:

• On Primary Crash, standby can be recovered in very less time.
• Standby can be opened and it will be in READ ONLY mode.
• It can be used for Reporting Server.
• Load balancing can be configured using pgpool-II between Primary and Standby.

Disadvantages:

• Standby Server should hold same amount of Memory/Disk/CPU etc., because, in case of Primary crashes the Slave acts as Primary.
• Minimal Lag i.e. (one transaction behind Primary)

Slony Replication:

Slony is a asynchronous trigger-based replication. Its a single master to multiple slave replication system for PostgreSQL. Every table or sequence on Master will be replicated via remote triggers to Slave. Updates are committed to one database and are applied to Slave later as EVENTs. Using Slony Switchover and switchback is possible.

Limitations of Slony-I

• Tables must have a primary key or a unique.
• Only Tables and sequeces are allowed for replication.
• Slave databases cannot be modified.

Advantages:

• Slony-I supports switchback.
• Using Slony-I, we can upgrade PG from one version to another version without any downtime.

Disadvantages:

• Slony cannot detect the network failuer, hence causing all the EVENT's created at primary will be queued and are released once Network catch ups.
• NO DDL changes allowed on the replication Tables while Slony Daemons running. 

Do post your comments, they will be highly appreciated. 

--Raghav

How to get Database Creation Time in PostgreSQL 9.0 ?

In PostgreSQL, database creation time is not stored in any of the pg_catalogs. So question arises, how do we know when was database created.

For every database, a directory is created with database-oid number under $PGDATA/base along with a set of OID's,OID_fsm,OID_vm, PG_VERSION files for each Object(Tables/Indexes/View/ etc.,).

Every OID,OID_fsm,OID_vm, files will be get updated as per the changes made at database-level. However, PG_VERSION file will never get updated on any changes made to the database. So, we going to use timestamp of PG_VERSION file as database creation time. I believe that there will be a chance of changing PG_VERSION timestamp, but I am not sure in which case this changes happen.

To get timestamp of PG_VERSION, I need something which executes OS command at PG Instance-level. So, I used pl/perlu function created by one of my collegue Vibhor Kumar.

http://vibhork.blogspot.com/2011/04/plperl-functions-for-getting-number-of.html

pl/perlu Function

CREATE OR REPLACE FUNCTION execute_shell(text) returns setof text
as
$$
$output=`$_[0] 2>&1`;
@output=split(/[\n\r]+/,$output);
foreach $out (@output)
{ return_next($out);
}
return undef;
$$ language plperlu;

And, one function to get database oid.

CREATE OR REPLACE FUNCTION public.get_pg_version_loc(dbname varchar) RETURNS text AS
$body$
DECLARE
       dbname ALIAS FOR $1;
       data_dir text;
       db_oid text;
       os_execute text;
BEGIN
     SELECT INTO db_oid oid from pg_database where datname = dbname;
     show data_directory into data_dir;
     os_execute := 'stat -c "%y" '||data_dir||'/base/'||db_oid||'/PG_VERSION';
     return os_execute;
END;
$body$
LANGUAGE 'plpgsql';

Output:

=# select datname,execute_shell(get_pg_version_loc(datname::text)) as "DB_Createion_Time"
-# from pg_database where datname not in ('template0','template1');
   datname    |          DB_Createion_Time
--------------+-------------------------------------
 postgres     | 2011-01-10 21:48:37.222016571 +0530
 provider     | 2011-05-26 11:40:14.253434477 +0530
 pgbench_test | 2011-08-14 16:52:21.689198728 +0530
 pgpool       | 2011-08-26 12:30:19.864134713 +0530
(4 rows)

Will be back with more stuff :). Do post your comments if any, they will be highly appreciated.

--Raghav

Connection Pooling with Pgbouncer on PostgreSQL 9.0

Connection pooling, Why we go for connection pooling in PostgreSQL, When your application demands for very good number of concurrent connection hits then you need to approach it, because Connection pool sits between your application and the database.

Idea behind connection pool is that you have enough connections to use of all the available resources and any incoming requests are re-used without dropping the database connection and keeping ready for a new connection to use.

pgbouncer is lightweight connection pooler. pgBouncer runs as a single process, not spawning a process per connection, which relies on library named libevent for connection pooling.

pgbouncer setup on PostgreSQL 9.0 is very simple, however there is small change with the latest version you need to create manual pg_auth file. pgbouncer uses pg_auth file for user authentication. Earlier verion of PostgreSQL 9.0, you can find the pg_auth file under $PGDATA/global/pg_auth, now in the latest version that file has been removed and placed in pg_catalog as table 'pg_auth'.

pgbouncer Setup:

1. First, download libevent library for pgbouncer.
Download link for libevent:
http://www.monkey.org/~provos/libevent-2.0.12-stable.tar.gz

tar -xvf libevent-2.0.12-stable.tar.gz
cd libevent-2.0.12-stable
./configure 
make
make install

2. Download the latest pgbouncer tar and configure to your PostgreSQL 9.0.
http://pgfoundry.org/frs/download.php/2912/pgbouncer-1.4.tgz

tar -xvf pgbouncer-1.4
cd pgbouncer-1.4
./configure --prefix=/opt/PostgreSQL/9.0/bin
make
make install

3. Create a libevent-i386.conf file in /etc/ld.so.conf.d directory

vi /etc/ld.so.conf.d/libevent-i386.conf
/usr/local/lib
:wq!

4. Run the ldconfig to apply new changes.

#ldconfig

5. Change the ownership of pgbouncer utility in PostgreSQL binary to postgres user.

chown -R postgres:postgres /opt/PostgreSQL/9.0/bin/bin/pgbouncer

6. Create the pgbouncer_auth file for users authentication.

7. Create pgbouncer.ini file with postgres user permission under /etc directory.

8. Start pgbouncer

-bash-4.1$ ./pgbouncer -d /etc/pgbouncer.ini
2011-08-14 11:42:00.925 1949 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 1000, max fds possible: 1010

9. Connect to the databases using pgbouncer

10. Getting help: Connect to pgbouncer database and get helped.

$ psql -p 6432 -U postgres pgbouncer
pgbouncer=# show help;

For better understanding on pg_auth you can find in below link by 'depesz'.
http://www.depesz.com/index.php/2010/12/04/auto-refreshing-password-file-for-pgbouncer/

Do post your comments which are highly appreciated.

--Raghav

pgmemcache vs Infinite Cache

In my recent post on pgmemcache, there were couple of questions asked which were really interesting and made me to work on it. I should thank for it :)

Questions:
1. Is pgmemcache application transparent ?
2. Is there any synchronization between memcached and PostgreSQL Shared buffers ?

Answer:

pgmemcache(memcached) is not application transparent, you need to do changes in the application for pushing or retreiving the data from the cache.

EnterpriseDB, product PostgresPlus Advance Server includes a feature called Infinite Cache, which is based on production proven technology memcached the open source distributed object cache.

About EnterpriseDB, the Enterprise PostgreSQL Company, provides enterprise-class PostgreSQL products of the world's most advanced open source database. The company's Postgres Plus products are ideally suited for transaction-intensive applications requiring superior performance, massive scalability and compatibility with proprietary database products.

Overview

Above diagram helps to understand the architecture of pgmemcache vs infinite cache. In infinite cache, all the pages are first searched in shared_buffers and then in Infinite Cache. Synchronization between shared buffer cache and infinite cache makes application transparency, which is not the case with pgmemcache.

Infinite Cache, is faster and completely application transparent. No special code is needed from developers. Warms up your cache with multiple parallel processes and pre-loads cache at startup reducing warming time.

To avail infinite cache you have to download the PostgresPlus Advance Server which is Oracle Compatible product bundled with Infinite Cache.
Download Link:
http://www.enterprisedb.com/downloads/postgres-postgresql-downloads

Implementation of Infinite cache is as simple as memcached, below link will help in setting up the infinite cache.

http://www.enterprisedb.com/docs/en/8.4/perf/Postgres_Plus_Advanced_Server_Performance_Guide-04.htm

Very informative discussion on PostgreSQL Community Forum:-

http://archives.postgresql.org/pgsql-performance/2011-07/msg00001.php

--Raghav

pgmemcache Setup and Usage

Preloading or Caching the table in PostgreSQL is a tough task, because PostgreSQL doesnt offer a Single big synchronize-level memory managment. All the memories are independent. Caching is possible with the third party tools like memcached.

pgmemcache is a set of PostgreSQL user-defined functions(API's) that provide an interface to memcached. pgmemcache, pre-requisites recommends to have libmemcached, however its also recommended to install memcached along with it. My presentation consist of installation/caching/monitoring using pgmemcache API's. As am not the Developer or Hacker :), so my way of implementation is in very simple method.

Points:

• Stores value in cache on the basis of Key/Value means, keeping table with primary key/unique key is recommended.
• No Data redundancy - If memcached goes down or runs out of space, new records and updates will be lost.
• Supports all memcached commands (set/get(single/multi)/delete/replace/incr/stats)
• After keeping the data into memcached and if you drop the table from backend,  memcached won't  throw any errors. Its all your management how you maintain it.
• No ability to iterate over data or determine what keys have been stored.
• You can never bring a memcached server down or add a new one to the pool while people are playing or connected.
• If the background updating process stops for any reason, updates do not occur and there is a possiblity that the memcached server could fill up.
• Every PostgreSQL backend has to bind to memcached port before accessing the data.
• Memcached runs on default port 11211

Pre-requisites:

1. PostgreSQL 8.4. or above
2. libevent
3. memcached
4. libmemcached
5. pgmemcache
6. Monitoring-Tools (monitoring-tools,damemtop,etc.,)

Installation:
Step 1. (libevent)

Libevent API is important when configuring pgmemcache, I prefer to have libraries as first step of installation. So lets start with libevent library configuring in default location.

Download link for libevent:
http://www.monkey.org/~provos/libevent-2.0.12-stable.tar.gz
tar -xvf libevent-2.0.12-stable.tar.gz
cd libevent-2.0.12-stable
./configure 
make
make install

Step 2 (memcached)

Install memcached by enabling the libevent.

Download link for memcached:
http://memcached.googlecode.com/files/memcached-1.4.6.tar.gz
cd /usr/local/src/memcached-1.4.6
------on 32-bit
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib
------on 64-bit
export LD_LIBRARY_PATH=/usr/lib64:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib64
make
make install

Step 3. (libmemcached)

pgmemcache is built on top of libmemcached. Libmemcached looks for memcache binary location, so set the path to memcached binaries before proceeding it.

export PATH=/opt/PostgreSQL/9.0/bin/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
Download link:
http://launchpad.net/libmemcached/1.0/0.50/+download/libmemcached-0.50.tar.gz
cd libmemcached-0.50
./configure
make
make install

Step 4 (pgmemcache)

pgmemcache API will help in, interacting with memcached like caching/retreiving data.

Download link:
http://pgfoundry.org/frs/download.php/3018/pgmemcache_2.0.6.tar.bz2
cd pgmemcache
PATH=/opt/PostgreSQL/9.0/bin:$PATH make USE_PGXS=1 install
or 
make
make install

Installation will create pgmemcache.sql file with all API's to interact with memcache under PG contrib location. To create pgmemcache API's, just exectute pgmemcache.sql file in all the database.

psql -p PGPORT -d PGDATABASE -f /opt/PostgreSQL/9.0/share/postgresql/contrib/pgmemcache.sql

pgmemcache API's list:

Note: While executing .sql file you may face error like "ISTFATAL: could not load library "/opt/PostgreSQL/9.0/lib/postgresql/pgmemcache.so": libmemcached.so.8: cannot open shared object file: No such file or directory". Means, PG instance didnt loaded with newly created library. Resolution, set the PATH and LD_LIBRARY_PATH and restart the instance to recognize the libraries.

Eg:-
export PATH=/opt/PostgreSQL/9.0/bin/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
$pg_ctl -D $PGDATA restart

If you want to load the pgmemcache as default to your PG instance, edit the postgresql.conf file and change the following parameters and restart the cluster.

shared_preload_libraries='pgmemcache'
custom_variable_classes='pgmemcache'

Configuration:
Step 1.

For caching data, first you need to initialize the memory, once the memory is allotted, later PG backends responsibility to bind and push the data into the cache. Here, I have started my memcache on localhost with 512MB on default port 11211. -d means start the daemon. All my exercise is  on localhost.

$./memcached -d -m 512 -u postgres -l localhost -p 11211

Note: To retreive data from the cache, every PostgreSQL backend should first bind and retreive the data.

Step 2.

Bind the instance to the running memcache port. After binding, checkout for the memcached statistics.

Step 3.

Now, its time to cache data into memcached, Memcached uses keys/value to reside data in its memory, so make sure your table has Primary/Unique key so retrieving will be easy. As mentioned, there are very good API's to play around on keeping the value and accessing them, in my example, I use memcache_set() to keep the value and memcache_get() to retrive data.

Once the value is set in the memcached, now its your responsibility to bind your backend to memcached and with the help of pgmemcache API's you can access the data. Each Postgres backend must bind before accessing. Please find the example below.

Getting data from cache

Monitoring

If you are very good in linux you can pull maximum information on memcached memory, however there are few tools which come along with memcached source pack like monitoring-tools,damemtop etc.,. Am using monitoring-tools utility for monitoring memcache.

usage:-
memcached-tool localhost display
memcached-tool localhost dump
memcached-tool localhost stats | grep bytes

Example:

A small effort from my side to setup pgmemcache and understand the basics. Hope it was helpful. Keep posting your comments or suggestion which are highly appreciated.

--Raghav