I have PostgreSQL 9.4 and 9.5 on the same host with Ubuntu 14.04. My problems are:
- When I run
service postgresql start|stop
, both versions start/stop. But I want to control which one to do. - When I use
psql
to connect to PostgreSQL, it always connects to 9.4. But I want to control which server to do. I know I can connect by different port, 5432 for 9.4 and 5433 for 9.5. But I want to connect by peer auth, that is via Unix socket.
I managed to find these clues for above two questions separately but haven't resolved my problems:
- I find a command
pg_ctlcluster
may do the work but I don't know how to specify correct parameters, say, cluster-name. How could I figure it out? Or you have some other way? - Unix socket can be configured by
unix_socket_directories = '/var/run/postgresql'
. But both versions have the same config dir, within which there're subdirs9.4-main.pg_stat_tmp 9.4-main.pid 9.5-main.pg_stat_tmp 9.5-main.pid
for different versions. When I dopsql -h /var/run/postgresql
, it just connects to the old 9.4 version.
Could anyone help? Thanks!
Best Answer
Well, after some more effort, I get the answers myself:
pg_ctlcluster
as well as a serialpg_xxxcluster
commands to manage multiple versions/instances of PostgreSQL on the same host. To find out the version and cluster name, just dopg_lscluster
, which outputs like9.4 main 5432 online postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log 9.5 main 5433 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
For example, to stop 9.4, you usepg_ctlcluster stop 9.4 main
. BTW, to disable autostart 9.4,edit /etc/postgresql/9.4/main/start.conf
psql
need a--port, -p
option to know which instance to connect, even for peer auth by Unix Socket, since all versions have the sameunix_socket_directories
. For example,psql -p 5433 dbname
can connect by Unix Socket to version 9.5 running with port 5433, while defaultpsql dbname
connects to default port 5432, which belongs to my old 9.4 version.That's it!