Thread: Stream pg_dumpall directly from CentOS7 to Red Hat server

Am 28.07.24 um 14:29 schrieb Laurenz Albe:
> On Sun, 2024-07-28 at 13:09 +0530, Wasim Devale wrote:
>> Can I use below command directly stream pg_dumpall from CentOS7 to RedHat 9:
>>
>> pg_dumpall -U postgres | ssh postgres@redhat_ip_address "psql -U postgres"
> No idea, but the correct way is
>
>    pg_dumpall -U postgres | psql -h redhat_ip_address -U postgres
>
> Yours,
> Laurenz Albe
>
This way the (old) psql tool of the current system (CentOS) would be
used. If in doubt, it's better to use the psql of the destination (Red
Hat) machine. Therefore, the idea using ssh isn't a bad one.

You could also execute the following command on the Red Hat machine:

pg_dumpall -h centos_ip_address -U postgres | psql -U postgres

This way, you'll use the newer versions of pg_dumpall and psql installed
on the Red Hat machine.
--

Holger Jakobs, Bergisch Gladbach

On Sun, Jul 28, 2024 at 8:30 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Sun, 2024-07-28 at 13:09 +0530, Wasim Devale wrote:
> Can I use below command directly stream pg_dumpall from CentOS7 to RedHat 9:
>
> pg_dumpall -U postgres | ssh postgres@redhat_ip_address "psql -U postgres"

No idea, but the correct way is

  pg_dumpall -U postgres | psql -h redhat_ip_address -U postgres

The correct way, if port 5432 is open.  Which isn't always the case.

Brock Henry <brock.henry@gmail.com> writes:
> I personally would create an ssh tunnel for port 5432, if ssh was open but
> 5432 was not.

+1, but I think your example is not quite right:

> ssh -L 5432:localhost:5432 redhat_ip_address
> pg_dumpall -U postgres | psql -h localhost -U postgres

If you have a local PG server, it's probably using 5432 so that ssh
can't bind to that.  I think you want something like

ssh -L 5433:localhost:5432 redhat_ip_address
pg_dumpall -U postgres | psql -h localhost -p 5433 -U postgres

where "5433" can be any locally-unused port number (caution: untested;
the ssh arguments may still not be quite right).

                        regards, tom lane

Brock Henry <brock.henry@gmail.com> writes:
> I personally would create an ssh tunnel for port 5432, if ssh was open but
> 5432 was not.

+1, but I think your example is not quite right:

> ssh -L 5432:localhost:5432 redhat_ip_address
> pg_dumpall -U postgres | psql -h localhost -U postgres

If you have a local PG server, it's probably using 5432 so that ssh
can't bind to that.  I think you want something like

ssh -L 5433:localhost:5432 redhat_ip_address
pg_dumpall -U postgres | psql -h localhost -p 5433 -U postgres

where "5433" can be any locally-unused port number (caution: untested;
the ssh arguments may still not be quite right).

                        regards, tom lane

(Before I answer: WHY?)

Not intrinsically.  You might be able to play weird games with ssh tunneling, but then I'd say "find a different solution to your problem."

On Sun, Jul 28, 2024 at 11:13 PM Zaid Shabbir <zaidshabbir@gmail.com> wrote:

Hello,

One relevant question..

Is there any way to Stream pg_dumpall directly from single CentOS7 to Multiple RHEL server directly through a single command ?

On Mon, Jul 29, 2024 at 7:12 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Brock Henry <brock.henry@gmail.com> writes:
> I personally would create an ssh tunnel for port 5432, if ssh was open but
> 5432 was not.

+1, but I think your example is not quite right:

> ssh -L 5432:localhost:5432 redhat_ip_address
> pg_dumpall -U postgres | psql -h localhost -U postgres

If you have a local PG server, it's probably using 5432 so that ssh
can't bind to that.  I think you want something like

ssh -L 5433:localhost:5432 redhat_ip_address
pg_dumpall -U postgres | psql -h localhost -p 5433 -U postgres

where "5433" can be any locally-unused port number (caution: untested;
the ssh arguments may still not be quite right).

                        regards, tom lane