Chaining sudo on SSH to run PostgreSQL queries and dumps remotely

It is common to run some commands via the postgres account as it is the de facto PostgreSQL super user. This means that if you’re running these commands remotely, you’ll need to chain sudo:

ssh pg-host sudo -i sudo -u postgres ...

If you’re in charge of the system, you can allow direct sudo-ing to the postgres user, but often that is not the case. This means switching to root first.

As an aside, you can also integrate crossing a jumphost boundary using ssh -J (even multiple times) to reach the PostgreSQL server.

In this case you can run queries such as the following:

ssh pg-host sudo -i sudo -u postgres psql -c 'SELECT 1'

A handy side effect of STDOUT being passed to your local machine is that you can run other utilities like pg_dump to STDOUT, and redirect that to your local machine:

ssh pg-host sudo -i sudo -u postgres pg_dumpall > dump_to_local_machine.sql

The redirection at the end will create the file on your local machine. Similarly, you can pipe local SQL dumps directly to the server:

cat local.sql | ssh pg-host sudo -i sudo -u postgres psql

This bypasses issues of e.g. not having enough disk space on the PostgreSQL machine to hold both the live data and the dump file.