PgPool II Managing and Testing

Managing the PgPool II servers

After you're finished editing the config files you can start the PgPool II server by running sudo pgpool -n > /tmp/pgpool.log 2>&1 & disown

Just to break down what's happening in this command:

-n - enables foreground mode so we get log output

> /tmp/pgpool.log - pipe the logs to a file

2>&1 - PgPool also writes stuff to stderr, this redirects stderr to stdout

& disown - detach PgPool from the shell, so it continues to run even after you log out

You can also simply run sudo pgpool but you don't get logs this way :(

To stop it run sudo pgpool stop or sudo pgpool -m fast stop if you don't want to wait for the clients to disconnect.

Use pcp_attach_node and pcp_detach_node to add and remove PostgreSQL servers from PgPool's cluster. Check their arguments.

So, for example, to attach node 2 you run:

pcp_attach_node 10 192.168.1.111 9898 postgres postgres 2 - Virtual IP

And to detach node 1 you do:

pcp_detach_node 10 192.168.1.111 9898 postgres postgres 1 - Virtual IP

Testing

Check if all nodes are connected:

psql -U postgres -h 192.168.1.111 -c "show pool_nodes" - Virtual IP

If all nodes have status 2 that means they're connected and ready. You can check what the other status codes mean here.

Start running your queries: psql -U postgres -h 192.168.1.111 - Virtual IP

You can also use pgbench to test performance or to make sure all nodes are being used.