Solved psql ignores psqlrc config file for some reason (PostgreSQL 16)

ShelLuser

ShelLuser

Hi gang,

So, I set up databases/postgresql16-client (and the server of course) and everything runs fine. By default PostgreSQL places its socket files in /tmp which is something I'm not really a fan of, so I edited postgresql.conf, changed unix_socket_directories and all is well.

But... psql continues to look for the socket files in /tmp. A quick peek in psql(1) led me to psqlrc which can either be used globally or on a per-user basis (~/.psqlrc (<= fixed typoe)). The only problem: for some reason psql completely ignores this:

Code: 
peter@bsd:/usr/local/etc/postgresql $ cat psqlrc
--host /var/run/postgresql

peter@bsd:/usr/local/etc/postgresql $ pg_config --sysconfdir
/usr/local/etc/postgresql
peter@bsd:/usr/local/etc/postgresql $ truss -o ~/sql.dump psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
peter@bsd:/usr/local/etc/postgresql $ grep sqlrc /home/peter/sql.dump
peter@bsd:/usr/local/etc/postgresql $
For now I temporarily resolved the issue by adding "export PGHOST=/var/run/postgresql" to /etc/profile but even so, it doesn't explain why the default config file gets fully ignored.

Anyone got an idea? Bug or... something I overlooked?
 
I've never used the system-wide version of the file, only the local version where the name of the file should be ~/.psqlrc (note the leading period). You must also set the permissions of the local file to 0600 (i.e. readable/writable by the user only) or psql will ignore it. If you really want to use a system-wide version of the file you can force psql to look for the psqlrc file in the specified location by setting the PGSYSCONFDIR environment variable to point to it. The local file location can be overridden by setting the PSQLRC environment variable.
 
fraxamo said:
The name of the file should be ~/.psqlrc (note the leading period).
Click to expand...
Agreed, however PSQL supports 2 versions: a global one (which I used) and a personal one which I only mentioned with a typoe (which I'm about to fix).

fraxamo said:
You must also set the permissions to 0600 (i.e. readable/writable by the user only) or psql will ignore it.
Click to expand...
Appreciate your comment, but it's not relevant in my situation because this doesn't explain why psql doesn't try to access psqlrc at all; neither the global nor personal version.
 
ShelLuser said:
For now I temporarily resolved the issue by adding "export PGHOST=/var/run/postgresql" to /etc/profile but even so, it doesn't explain why the default config file gets fully ignored.
Click to expand...
How does setting "export PGHOST=/var/run/postgresql" fix things? The environment variable PGHOST is used to set the host connection parameter i.e. 'localhost' or an IP address where the database server is located.
 
I also don't think that what you have in your global file will work, so it's possible that psql is reading your global file but ignoring the contents (i.e. --host /var/run/postgresql). Can you try adding the following line to your global file: \echo 'Global file' and the following line to your local file: \echo 'Local file' and then run psql? You should see the output of both strings (global first and then local) if psql runs successfully. I just tried it with PG17 and it works correctly.
 
Found it... Meh, I'm still a bit rusty after all those years ;)

Anyway, I can confirm that this is a bug of some sorts... As you can see above truss didn't give me any results, but I forgot all about ktrace!

And guess what?

Code: 
 13166 psql     CALL  access(0x167f27c1a600,0x4<R_OK>)
 13166 psql     NAMI  "/usr/local/etc/postgresql/psqlrc-16.8"
 13166 psql     RET   access -1 errno 2 No such file or directory
That explains a thing or two... Definitely odd because this doesn't match the documentation, not even the official stuff. At this point it's too early for me to tell what's causing this, but that's something to dive into later this week; a quick glance at the patch files for database/postgresql16-server didn't reveal much but... it's been a while since I dug through all that ;)

Thanks for your comments fraxamo !

(edit)

fraxamo said:
How does setting "export PGHOST=/var/run/postgresql" fix things? The environment variable PGHOST is used to set the host connection parameter i.e. 'localhost' or an IP address where the database server is located.
Click to expand...
I know, that's what I was trying to do with psqlrc.

As mentioned in my OP I changed the socket location in my server config, but that doesn't have any effect on your client(s); definitely not psql. So the reason why I'm messing with psqlrc in the first place is to propagate --host, I prefer to use config files to keep things clean and easier to administrate.

But since that didn't work and I still wanted to get things done I used this fallback.
 
ShelLuser said:
That explains a thing or two... Definitely odd because this doesn't match the documentation, not even the official stuff.
Click to expand...
It's good that you got it working. Although it's unusual that psql is looking for a version-specific psqlrc file as I thought that was an enhancement and not the default. If the documentation doesn't explicitly state that this is the case then there's definitely something else strange going on.
 
ShelLuser said:
So the reason why I'm messing with psqlrc in the first place is to propagate --host, I prefer to use config files to keep things clean and easier to administrate.
Click to expand...
In the local .psqlrc and global psqlrc files, leading dashes (i.e. '--') are used for comments, so I don't think that your command would ever work, it just gets ignored. The way to set a variable in the psql config file is to use \set like this:

SQL: 
-- Hard-code the connection details
\set DBNAME <dbname>
\set USER <username>
\set HOST <hostname>
\set PORT <port>

-- Output and check the value of the variables
\echo :DBNAME
\echo :USER
\echo :HOST
\echo :PORT

-- Create a connection using the variable values
\c :DBNAME :USER :HOST :PORT
 
You must log in or register to reply here.
Back
Top