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 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. 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. [londiste] job_name = londiste_master_to_r1 provider_db = dbname=write subscriber_db = dbname=ro1 # it will be used as sql ident so no dots/spaces pgq_queue_name = londiste.write pidfile = %(job_name)s.pid logfile = %(job_name)s.log use_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 install mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber install mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber install mbpro:~/temp kristokaiv$ londiste.py replica1.ini replay -d mbpro:~/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: [pgqadm] job_name = ticker_write db = dbname=write # how often to run maintenance [minutes] maint_delay_min = 1 # how often to check for activity [secs] loop_delay = 0.1 logfile = %(job_name)s.log pidfile = %(job_name)s.pid use_skylog = 0 To start the ticker as a daemon just run: mbpro:~/temp kristokaiv$ pgqadm.py ticker_write.ini ticker -d Lets create a simple table that we will replicate from master to read-only’s mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key,password text);" write mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key,password text);" ro1 mbpro:~/temp kristokaiv$ psql -c "CREATE TABLE users (username text primary key,password text);" ro2 And add it to replication mbpro:~/temp kristokaiv$ londiste.py replica1.ini provider add users mbpro:~/temp kristokaiv$ londiste.py replica1.ini subscriber add users mbpro:~/temp kristokaiv$ londiste.py replica2.ini subscriber add users After some time the tables should be up to date. Insert a new record in the write database and check if it’s delivered to both read-only db’s. 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; Just for the comfort of those actually trying to follow these steps,here is how the proxy databases 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; The last thing left to do is to create the plproxy function definitions that will redirect the login function calls against read-only databases and add_user calls against write database: 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; This is it,the read-only cluster is ready. Note that even though creating such a read-only cluster seems simple and a quick solution for your performance problems it is not a silver bullet solution. Asynchronous replication often creates more problems than it solves so be careful to replicate only non-timecritical data or guarantee a fallback solution when data is not found (eg. proxy function first checks readonly database and if data is not found looks the data up from write db) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |