How to manage different versions of PostgreSQL servers on the same host?
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!
Well, after some more effort, I get the answers myself:
- Ubuntu and Debian offer a
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!
On Debian-based distributions like Ubuntu, psql
(and a few other PostgreSQL commands) is a symlink to pg_wrapper
, which offers the --cluster
option. See man pg_wrapper
for details.
For local connections, you can use
psql --cluster 9.4/main
psql --cluster 9.5/main
To see your versions, clusters and ports, use pg_lsclusters
. Sample output:
# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.6 main 5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log
11 main 5433 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log
And to set the default, you can set your preferred version to use port 5432 in /etc/postgresql/*/main/postgresql.conf
, and to some other port for the other version.
(This was also answered here: Running multiple versions of PostgreSQL on the same Ubuntu server )