2

I have PostgreSQL 9.4 and 9.5 on the same host with Ubuntu 14.04. My problems are:

  1. When I run service postgresql start|stop, both versions start/stop. But I want to control which one to do.
  2. 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:

  1. 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?
  2. Unix socket can be configured by unix_socket_directories = '/var/run/postgresql'. But both versions have the same config dir, within which there're subdirs 9.4-main.pg_stat_tmp 9.4-main.pid 9.5-main.pg_stat_tmp 9.5-main.pid for different versions. When I do psql -h /var/run/postgresql, it just connects to the old 9.4 version.

Could anyone help? Thanks!

3

Well, after some more effort, I get the answers myself:

  1. Ubuntu and Debian offer a pg_ctlcluster as well as a serial pg_xxxcluster commands to manage multiple versions/instances of PostgreSQL on the same host. To find out the version and cluster name, just do pg_lscluster, which outputs like 9.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 use pg_ctlcluster stop 9.4 main. BTW, to disable autostart 9.4, edit /etc/postgresql/9.4/main/start.conf
  2. psql need a --port, -p option to know which instance to connect, even for peer auth by Unix Socket, since all versions have the same unix_socket_directories. For example, psql -p 5433 dbname can connect by Unix Socket to version 9.5 running with port 5433, while default psql dbname connects to default port 5432, which belongs to my old 9.4 version.

That's it!

  • psql doesn't need --port if you use --cluster or the PGCLUSTER env variable. See man pg_wrapper. Incidentally this will also automatically select the version of psql that corresponds to the target instance. – Daniel Vérité Sep 7 '16 at 12:14
  • For a machine with postgres 10 as well as 9.6, the pg_ctlcluster command arguments had to be inverted: pg_ctlcluster 9.6 main stop (i.e., stop at end) – perlyking Jul 20 '18 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.