Thread: Connect to specific cluster on command line

Hi there,

how can I connect to a specific cluster on the command line, e. g. with 
psql, pg_dump or pg_dumpall?

pg_lsclusters returns a list of all clusters available:

Ver Cluster Port [...]
14  main    5432 ...
14  test    5433  ...

I want to connect to or dump database xyz in the test cluster. Is it 
sufficient to specify the cluster's port only?

Perl script pg_backupcluster calls psql and pg_dump with a --cluster option:

pg_dump --cluster 14/test ...

However, this option is not documented (at least I didn't find anything) 
and also I didn't find anything in the sources on GitHub.

Actually, I only have the 14/main instance and cannot really test 
accessing the `test` cluster. However, psql and pg_dump actually work 
with --cluster 14/main and report an error when I specify 14/test so, 
the option --cluster seems to work. Is it intentionally not documented?

So, whats the recommended way to connect to a specific cluster? Is it 
just the port?

Regards, Carsten

Carsten Klein <c.klein@datagis.com> writes:
> how can I connect to a specific cluster on the command line, e. g. with 
> psql, pg_dump or pg_dumpall?

> pg_lsclusters returns a list of all clusters available:

pg_lsclusters is not part of core Postgres, and neither is this
--cluster option you mention.  I'm vaguely aware that some packager
(Debian I think) has added an overlay of that sort; but you'd need
to consult the package-level documentation not the community docs
in order to find out more.

            regards, tom lane

> 
> pg_lsclusters is not part of core Postgres, and neither is this
> --cluster option you mention.  I'm vaguely aware that some packager
> (Debian I think) has added an overlay of that sort; but you'd need
> to consult the package-level documentation not the community docs
> in order to find out more.
> 

Sorry, my fault, it's Debian/Ubuntu. Actually they must have added the 
--cluster option to the PG programs through source code patches...

So, forget about the packager. With core PostgreSQL tools it is possible 
to have more than one cluster. How do you specify what cluster to 
connect to with psql or pg_dump? Do I have to specify the cluster's 
corresponding Unix domain socket directory via the --host option?

Regards, Carsten

On 5/25/22 16:48, Carsten Klein wrote:

> So, forget about the packager. With core PostgreSQL tools it is possible 
> to have more than one cluster. How do you specify what cluster to 
> connect to with psql or pg_dump?


psql, pg_dump and friends know the -p (or --port) option.
Maybe man psql and man pg_dump are your friends.

On Wed, 2022-05-25 at 16:48 +0200, Carsten Klein wrote:
> So, forget about the packager. With core PostgreSQL tools it is possible 
> to have more than one cluster. How do you specify what cluster to 
> connect to with psql or pg_dump? Do I have to specify the cluster's 
> corresponding Unix domain socket directory via the --host option?

With -h you specify the directory containing the socket, and
with -p (port) the name.

So you could

  psql -h /var/run/postgresql -p 5555

to use the socket /var/run/postgresql/.s.PGSQL.5555

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Carsten Klein <c.klein@datagis.com> writes:
> So, forget about the packager. With core PostgreSQL tools it is possible 
> to have more than one cluster. How do you specify what cluster to 
> connect to with psql or pg_dump? Do I have to specify the cluster's 
> corresponding Unix domain socket directory via the --host option?

Usually you'd use -p (--port).  You *could* specify the exact path
to the postmaster's socket via -h, but I think that's not idiomatic.

            regards, tom lane

pg_lsclusters is not part of core Postgres, and neither is this
--cluster option you mention.  I'm vaguely aware that some packager
(Debian I think) has added an overlay of that sort; but you'd need
to consult the package-level documentation not the community docs
in order to find out more.

Sorry, my fault, it's Debian/Ubuntu. Actually they must have added the --cluster option to the PG programs through source code patches...

On 5/25/22 07:30, Carsten Klein wrote:
> Hi there,
> 

> Perl script pg_backupcluster calls psql and pg_dump with a --cluster 
> option:
> 
> pg_dump --cluster 14/test ...
> 
> However, this option is not documented (at least I didn't find anything) 
> and also I didn't find anything in the sources on GitHub.

It postgresql-common provided as part of the Debian/Ubuntu packaging. If 
you want the documentation then:

man postgresql-common

> 
> Actually, I only have the 14/main instance and cannot really test 
> accessing the `test` cluster. However, psql and pg_dump actually work 
> with --cluster 14/main and report an error when I specify 14/test so, 
> the option --cluster seems to work. Is it intentionally not documented?
> 
> So, whats the recommended way to connect to a specific cluster? Is it 
> just the port?

On Debian/Ubuntu using their packaged versions then use the 
postgresql-common tool, it makes things a lot easier. Otherwise follow 
the instructions others have provided for using -h and -p. You will also 
need to do -h/-p if you are trying to reach remote instances of Postgres.

> 
> Regards, Carsten
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed 2022-05-25 at 17:05 David G. Johnston wrote:

> IIRC they write wrapper scripts they put into the version-agnostic bin 
> directory that deal with the version/cluster-name scheme they’ve setup 
> before calling the core commands located in the version-specific install 
> directory.

You are completely right. For example, /usr/bin/pg_dump is a Perl script 
which evaluates the --cluster option and then calls the real PG tool.

@all Many thanks for all helpful suggestions :)

Regards, Carsten