Thread: Unable to execute pg_dump

Unable to execute pg_dump

From
Joseph Maruca
Date:

Hello everyone,

I am trying to work out a way to perform a PostgreSQL pg_dump of a single DB via RHEL v6.10 command line. Now the system I am working on is that you have to shell into Bash 4.1 first before you get to PSQL. Now I can access the actual PSQL db using the following syntax from the command line which works fine:

'''sudo -u postgres -H --psql -pxxxxx -d db_name'''

If I enter the following syntax from the RHEL command line:

'''sudo su postgres'''

I end up in the bash-4.1 shell. When executing the following command from within the shell: bash-4.1$ pg_dump db_name > /tmp/my_database.sql I am presented with the following error:

pg_dump: [archiver (db)] connection to database "db_name" failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I am expecting the db to be exported to /tmp/my_database.sql file. I have tried various combinations of commands from both RHEL and bash command lines but cannot achieve the desired outcome. Your assistance would be greatly appreciated, thank you.

Regards,

Joe28a

Re: Unable to execute pg_dump

From
"David G. Johnston"
Date:
On Sunday, June 14, 2020, Joseph Maruca <joseph.maruca@masimo.com> wrote:

'''sudo -u postgres -H --psql -pxxxxx -d db_name'''

If I enter the following syntax from the RHEL command line:

'''sudo su postgres'''

I end up in the bash-4.1 shell. When executing the following command from within the shell: bash-4.1$ pg_dump db_name > /tmp/my_database.sql I am presented with the following error:

pg_dump: [archiver (db)] connection to database "db_name" failed: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Not a RHEL user but...

For psql you specify the port but not for pg_dump.
For psql you seem to be switching to the postgres user’s environment but don’t do that for pg_dump.

In short it seems like you need to trace down your O/S environment differences when each command is run.

David J.

Re: Unable to execute pg_dump

From
Tom Lane
Date:
Joseph Maruca <joseph.maruca@masimo.com> writes:
> I end up in the bash-4.1 shell. When executing the following command from within the shell: bash-4.1$ pg_dump db_name
>/tmp/my_database.sql I am presented with the following error: 

> pg_dump: [archiver (db)] connection to database "db_name" failed: could not connect to server: No such file or
directoryIs the server running locally and accepting connections on Unix domain socket
"/var/run/postgresql/.s.PGSQL.5432"?

Ah, this is a symptom we've seen before.  There is disagreement about
where the postmaster ought to put its socket file --- traditionally
PG puts it in /tmp/, but some people feel that's a security hazard and
want to put it in a bespoke directory such as /var/run/postgresql/.

It appears from these symptoms that (a) you are running a server that
puts the socket in /tmp (probably you got that server from a PGDG RPM?)
but (b) you are trying to connect to it using a Red-Hat-provided libpq
(which defaults to expecting the file to be in /var/run/postgresql/).

You have various possible workarounds:

* tell the server to create a socket file in /var/run/postgresql/ too
(see unix_socket_directories);

* tell libpq where to find the socket, e.g. with "psql -h /tmp";

* tell libpq not to use a Unix socket at all, e.g. "psql -h localhost";

* make sure to invoke psql+libpq from the PGDG distribution rather than
using ones supplied by Red Hat.

Generally speaking, mixing PGDG RPMs with vendor-supplied Postgres RPMs
is a recipe for headaches.  If you can drop the Red Hat Postgres RPMs
without causing dependency problems, do that.  Otherwise, the
two-socket-files solution is probably the best.

            regards, tom lane