PostgreSQL cluster: partitioning with plproxy (part II)

In the last post i described how you can setup plproxy and create a basic horizontally partitioned cluster. Now we will take a look at another real life usage: building a read-only cluster for your database

distributing read-only load

The simplest real world usage for plproxy would be it’s use for redirecting read-only querIEs to read-only replicas of master database. The replicated databases can be filled with data via Londiste that is part of the SkyTools package,setup tutorial can be found here or with Slony which is a more heavyweight solution but from my own experIEnce also harder to setup and maintain though definitely at the time being better documented.
A typical read-only cluster Could look like on the following schema. The databases with the letter (P) on them are connection poolers. We ourself use PgBouncer but pgpool is also a choice.
The poolers are needed to minimize the number of open connections to a database also execution plans are cached on a connection basis. Of course everything will work fine also without the poolers. Dashed bold arrows represent replicas.

In this setup the plproxy functions determine the database to which the query is redirected. Read&write querIEs go to master database and read-only querIEs are distributed based on the algorithm you define to read-only replicas.
Setting up replication itself is relatively easy once you have passed the painful skytools installation process.
First let us create both replicas from write database toward ro1 & ro2. ro1 configuration file looks like this:
replica1.ini

[londiste]job_name = londiste_master_to_r1provIDer_db = dbname=writesubscriber_db = dbname=ro1# it will be used as sql IDent so no dots/spacespgq_queue_name = londiste.writepIDfile = %(job_name)s.pIDlogfile = %(job_name)s.loguse_skylog = 0

replica2.ini is basically the same only job name and database name need to be changed. Now let’s install Londiste on provIDer (write) and subscribers (ro1,ro2) and start the replication daemons:

mbpro:~/temp kristokaiv$ londiste.py replica1.ini provIDer installmbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber installmbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber installmbpro:~/temp kristokaiv$ londiste.py replica1.ini replay -dmbpro:~/temp kristokaiv$ londiste.py replica2.ini replay -d

The next thing you need to do is to setup the ticker process on the database where write is performed. The ticker creates sync events so running it with shorter intervals will reduce latency. My configuration file looks like this:
ticker_write.ini

[pgqadm]job_name = ticker_writedb = dbname=write# how often to run maintenance [minutes]maint_delay_min = 1# how often to check for activity [secs]loop_delay = 0.1logfile = %(job_name)s.logpIDfile = %(job_name)s.pIDuse_skylog = 0

mbpro:~/temp kristokaiv$ pgqadm.py ticker_write.ini ticker -d

mbpro:~/temp kristokaiv$ psql -c "CREATE table users (username text primary key,password text);" writembpro:~/temp kristokaiv$ psql -c "CREATE table users (username text primary key,password text);" ro1mbpro:~/temp kristokaiv$ psql -c "CREATE table users (username text primary key,password text);" ro2

mbpro:~/temp kristokaiv$ londiste.py replica1.ini provIDer add usersmbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber add usersmbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber add users

CREATE OR REPLACE FUNCTION public.add_user(    in i_username text,in i_password text,out status_code text) AS $BEGIN    PERFORM 1 FROM users WHERE username = i_username;    IF NOT FOUND THEN        INSERT INTO users (username,password) VALUES (i_username,i_password);        status_code = 'OK';    ELSE        status_code = 'user exists';    END IF;    RETURN;END; $ LANGUAGE plpgsql Security defineR;GRANT EXECUTE ON FUNCTION public.add_user(    in i_username text,out status_code text) TO plproxy;CREATE OR REPLACE FUNCTION login(    in i_username text,out status_code text) AS $BEGIN    SELECT 'OK' FROM users u WHERE username = i_username AND password = i_password INTO status_code;    IF NOT FOUND THEN status_code = 'Failed'; END IF;    RETURN;END; $ LANGUAGE plpgsql Security defineR;GRANT EXECUTE ON FUNCTION login(    in i_username text,out status_code text) TO plproxy;

CREATE OR REPLACE FUNCTION plproxy.get_cluster_partitions (cluster_name text)RETURNS SetoF text AS $BEGIN   IF cluster_name = 'Readonly' THEN        RETURN NEXT 'host=127.0.0.1 dbname=ro1';        RETURN NEXT 'host=127.0.0.1 dbname=ro2';        RETURN;    ELSIF cluster_name = 'write' THEN        RETURN NEXT 'host=127.0.0.1 dbname=write';        RETURN;    END IF;    RAISE EXCEPTION 'no such cluster%',cluster_name;END; $ LANGUAGE plpgsql Security defineR;CREATE OR REPLACE FUNCTION plproxy.get_cluster_config(    in cluster_name text,out key text,out val text)RETURNS SetoF record AS $BEGIN    RETURN;END;$ LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION plproxy.get_cluster_version(cluster_name text) RETURNS int AS $    SELECT 1;$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION public.login(    in i_username text,out status_code text) AS $CLUSTER 'Readonly'; RUN ON ANY;$ LANGUAGE plproxy;CREATE OR REPLACE FUNCTION public.add_user(    in i_username text,out status_code text) AS $CLUSTER 'write';$ LANGUAGE plproxy;