Unable to execute pg_dump
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
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.
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 directory Is 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