Setting up pl/proxy database

First step is to create and configure the front-end database that will do the
request proxying to partitions. This is surprisingly called the proxy database. 
At this point you should have compiled pgcpoold and installed libplproxy.so into
postgresql dynamic_library_path.

$ createdb proxydb
$ createlang plpgsql proxydb
$ psql -f ../plproxy/db/lang.sql proxydb
$ psql -f structure/plproxy_schema.sql proxydb

Or, if you installed from the pl/proxy debian package:

$ psql -f /usr/share/postgresql/8.0/contrib/plproxy_lang.sql proxydb
$ psql -f /usr/share/postgresql/8.0/contrib/plproxy_schema.sql proxydb

You should see no errors or warnings besides the one that says 
"plpgsql is already installed"

Next we configure the cluster. The cluster config is stored in a database
and the configuration is done entirely in SQL. You could use different
databases for configuration and proxy, but for brevity we keep them in the
same database.

Now, fire up psql and start configuring the cluster:

-- First create the cluster and it's configuration, start with 2 partitions.

select * from plproxy.create_cluster('mycluster','Test cluster', 2);
select * from plproxy.set_active_conf('mycluster','mycluster_V1');

-- Set connection parameters for the 2 partitions. Use the local postgresql
-- for testing. Note that the partition names are automatically generated by 
-- create_cluster, so don't try to change them here.

select * from plproxy.alter_partition(
	'mycluster_p000',
	'dbname=p1 host=localhost port=5432',
	'example partition 1');

select * from plproxy.alter_partition(
	'mycluster_p001',
	'dbname=p2 host=localhost port=5432',
	'example partition 2');

-- Create proxy definition, this is the pgcpoold daemon that will be servicing
-- our clusters. Note that the first parameter must match the pgcpoold executable
-- name, so you need to symlink it to run multiple proxies on the same machine.

select * from plproxy.create_proxy(
	'pgcpoold',
	'pl/proxy test',
	ARRAY['mycluster'],
	1, 200);

-- Review connect strings and proxy configuration

select * from plproxy.get_proxy_config('pgcpoold');
select * from plproxy.get_proxy_connections('pgcpoold');

-- Change some configuration settings from default.

select * from plproxy.alter_proxy('pgcpoold', 'max-clients','200');

Finally - leave the psql prompt and start pgcpoold:

$ pgcpoold dbname=proxydb

The first command line argument should be the connect string to configuration
database. Standard PostgreSQL environment variables apply, so you can specify
PGDATABASE etc.

So this concludes proxy configuration, the next step is to write your application.
Look into examples/ directory for some guidance.

