Please document search path for eg pg_service.conf when having pip installed psycopg2-binary

[cthart]

This is a bug tracker
If you have a question, such has "how do you do X with Python/PostgreSQL/psycopg2" please write to the mailing list or open a question instead.

Please complete the following information:

• OS: Ubuntu 20.04.3 LTS with all updates installed
• Psycopg version: 2.9.1
• Python version: 3.8.10
• PostgreSQL version: 10 (on different server)
• pip version: 21.2.4

Describe the bug
Used
pip install psycopg2-binary
while previously using
pip install psycopg2
with OS installed libpq (from the official Postgres APT repository).

I was briefly flumoxed by

definition of service "xyzzy" not found

errors, even though I had pg_service.conf in /etc/postgresql-common as normal.
After running
strings libpq-6f24e430.so.5.13 | grep postgresql
I noticed
/usr/local/etc/postgresql
in the output. I symlinked it to /etc/postgresql-common and everything started working.

I couldn't find anything about this in the documentation. Could you add it somewhere?

[dvarrazzo]

I wasn't aware of this settings, which is probably a compile setting of the libpq. /etc/postgresql-common seems hardly a standard location (but probably it is the standard for Debian system? What about RPM based ones?). Surely /usr/local/etc/postgresql isn't better though.

In psycopg2 we build the libpq from source and we already make an adjustment to use a more popular socket dir:

psycopg2/scripts/build/build_libpq.sh

Lines 104 to 108 in 3430dcd

	# Match the default unix socket dir default with what defined on Ubuntu and
	# Red Hat, which seems the most common location
	sed -i 's|#define DEFAULT_PGSOCKET_DIR .*'\
	'|#define DEFAULT_PGSOCKET_DIR "/var/run/postgresql"|' \
	src/include/pg_config_manual.h

would you please like to take a look if there are other directories which might use an adjustment?

[dvarrazzo]

Just stumbled for other reasons on the env vars page and it says:

The following environment variables determine internal behavior of libpq; they override compiled-in defaults.

PGSYSCONFDIR sets the directory containing the pg_service.conf file and in a future version possibly other system-wide configuration
PGLOCALEDIR sets the directory containing the locale files for message localization.

so probably we shoud tweak these settings from the source default to the values expected on major distros.

[jscottbranson]

I'm not 100%, but I think this is related - on Debian 10 with Python 3.7 and psycopg2-binary 2.8.0 I get:
psycopg2.errors.UndefinedFile: could not open extension control file "/usr/share/pgsql/extension/citext.control": No such file or directory

The path on vanilla Debian 10 w/ postgresql-contrib is: /usr/share/postgresql/xx/extension/citext.control where xx is the version (e.g., "11").

Unfortunately ln -s /usr/share/postgresql/11 /usr/share/pgsql did not work for me.

~Scott

[dvarrazzo]

@CrypticRabbit that error comes from the server, not from Psycopg, and looks like contribs have not been installed correctly

[jscottbranson]

Thanks, @dvarrazzo. Do you have a sense of what contribs I need to reinstall to get this working?

IIRC, the postgresql-contrib package is depreciated after v10, and they recommend just installing postgresql-xx (with xx being the major version, 14 in my case).

[cthart]

I wasn't aware of this settings, which is probably a compile setting of the libpq. /etc/postgresql-common seems hardly a standard location (but probably it is the standard for Debian system? What about RPM based ones?). Surely /usr/local/etc/postgresql isn't better though.

In psycopg2 we build the libpq from source and we already make an adjustment to use a more popular socket dir:

psycopg2/scripts/build/build_libpq.sh

Lines 104 to 108 in 3430dcd

	# Match the default unix socket dir default with what defined on Ubuntu and
	# Red Hat, which seems the most common location
	sed -i 's|#define DEFAULT_PGSOCKET_DIR .*'\
	'|#define DEFAULT_PGSOCKET_DIR "/var/run/postgresql"|' \
	src/include/pg_config_manual.h

would you please like to take a look if there are other directories which might use an adjustment?

I haven't been able to determine what standards are used on the various platforms. I only use Ubuntu and Debian and they use the same directory layout. As far as I'm concerned just documenting the locations is a good start.

[dvarrazzo]

No, if we document them they become part of the interface, whereas now they are there just by accident. I'd rather put them in the right place before documenting where they are (if we would still feel the need of it).

[cthart]

There's no standard location. See extended discussion about such here: https://postgrespro.com/list/thread-id/1674314

The absolute default location if you download the PG source and build from that without specifying anything is /usr/local/pgsql/etc See https://www.postgresql.org/docs/14/install-procedure.html#CONFIGURE-OPTIONS-LOCATIONS

RPM distros seem to use /etc (at least CentOS 8 does)
DEB distros use /etc/postgresql-common

So which to choose? Use the absolute default location /usr/local/pgsql/etc ?
Why/how/where is the location now set to /usr/local/etc/postgresql ? It's different from the default in the source distribution.

[dvarrazzo]

I have no idea, because we don't customise it in our build chain. I thought it was the source default.