Infrastructure at your Service

Daniel Westermann

Setting your cluster name in PostgreSQL 9.5

PostgreSQL 9.5 introduces a new parameter which is called: cluster_name. So, what is this good for?

Imagine you have two (or even more) PostgreSQL clusters running on the same host. When looking at the operating system processes this might look like this:

[email protected]:/home/postgres/ [PG6] ps -ef | grep "postgres:"
postgres  2325  2324  0 14:01 ?        00:00:00 postgres: logger process   
postgres  2327  2324  0 14:01 ?        00:00:00 postgres: checkpointer process   
postgres  2328  2324  0 14:01 ?        00:00:00 postgres: writer process   
postgres  2329  2324  0 14:01 ?        00:00:00 postgres: wal writer process   
postgres  2330  2324  0 14:01 ?        00:00:00 postgres: autovacuum launcher process   
postgres  2331  2324  0 14:01 ?        00:00:00 postgres: archiver process   last was 0000000100000002000000B2
postgres  2332  2324  0 14:01 ?        00:00:00 postgres: stats collector process   
postgres  2709  2708  0 14:01 ?        00:00:00 postgres: logger process   
postgres  2711  2708  0 14:01 ?        00:00:00 postgres: checkpointer process   
postgres  2712  2708  0 14:01 ?        00:00:00 postgres: writer process   
postgres  2713  2708  0 14:01 ?        00:00:00 postgres: wal writer process   
postgres  2714  2708  0 14:01 ?        00:00:00 postgres: autovacuum launcher process   
postgres  2715  2708  0 14:01 ?        00:00:00 postgres: stats collector process   
postgres  3191  2771  0 14:09 pts/0    00:00:00 grep --color=auto postgres:

It is not possible to see immediately to which instance which process belongs to. Sure, you can look at the postmaster:

[email protected]:/home/postgres/ [PG6] ps -ef | grep "postgres -D"
postgres  2324     1  0 14:01 ?        00:00:00 /u01/app/postgres/product/95/db_2/bin/postgres -D /u02/pgdata/PG6
postgres  2708     1  0 14:01 ?        00:00:00 /u01/app/postgres/product/95/db_b2/bin/postgres -D /u02/pgdata/PG7

… and from there make your way to the child processes. But this is not very convenient. This is where cluster_name comes in. Now, with PostgreSQL 9.5 (the community just released RC1), you can set this parameter to value of your choice:

([email protected][local]:4448) [postgres] > alter system set cluster_name='my_cluster';
ALTER SYSTEM
Time: 29.964 ms
([email protected][local]:4448) [postgres] > select pending_restart 
                                       from pg_settings 
                                      where name = 'cluster_name';
 pending_restart 
-----------------
 t
(1 row)

As you can see from the last select above: for this parameter to have any effect we need to restart the cluster:

[email protected]:/home/postgres/ [PG6] pg_ctl -D /u02/pgdata/PG6/ restart
waiting for server to shut down.... done
server stopped
server starting
[email protected]:/home/postgres/ [PG6] 

From now on all the processes belonging to this instance can be easily identified:

[email protected]:/home/postgres/ [PG6] ps -ef | grep my_cluster
postgres  3336  3335  0 14:18 ?        00:00:00 postgres: my_cluster: logger process   
postgres  3338  3335  0 14:18 ?        00:00:00 postgres: my_cluster: checkpointer process   
postgres  3339  3335  0 14:18 ?        00:00:00 postgres: my_cluster: writer process   
postgres  3340  3335  0 14:18 ?        00:00:00 postgres: my_cluster: wal writer process   
postgres  3341  3335  0 14:18 ?        00:00:00 postgres: my_cluster: autovacuum launcher process   
postgres  3342  3335  0 14:18 ?        00:00:00 postgres: my_cluster: archiver process   
postgres  3343  3335  0 14:18 ?        00:00:00 postgres: my_cluster: stats collector process   
postgres  3360  2771  0 14:19 pts/0    00:00:00 grep --color=auto my_cluster

A little, but very nice addition which made it into PostgreSQL 9.5.

2 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure