Custom unix_socket_directories

[Roysten]

Hello,

I have a configuration with PostgreSQL 10.4 which has worked well for many years. I am now trying to upgrade to 14.2, but I run into some issues. I can reproduce this using just the base image. The following fails:

docker container run --rm --name test -e POSTGRES_DB=symfony -e POSTGRES_USER=symfony -e POSTGRES_PASSWORD=symfony postgres:14.2-alpine postgres -c unix_socket_directories=/tmp

The same configuration did work in 10.4:

docker container run --rm --name test -e POSTGRES_DB=symfony -e POSTGRES_USER=symfony -e POSTGRES_PASSWORD=symfony postgres:10.4-alpine postgres -c unix_socket_directories=/tmp

Has something changed regarding the unix_socket_directories?

I get the following logs:

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... sh: locale: not found
2022-02-21 14:25:57.360 UTC [30] WARNING:  no usable system locales were found
ok
syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2022-02-21 14:25:57.927 UTC [36] LOG:  starting PostgreSQL 14.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
2022-02-21 14:25:57.929 UTC [36] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-02-21 14:25:57.955 UTC [37] LOG:  database system was shut down at 2022-02-21 14:25:57 UTC
2022-02-21 14:25:57.964 UTC [36] LOG:  database system is ready to accept connections
 done
server started
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
	Is the server running locally and accepting connections on that socket?

[Roysten]

For anyone who runs into this, a simple workaround is including multiple comma separated directories in the unix_socket_directories, like so:

unix_socket_directories = '/var/run/postgresql/,/sock/'

The /var/run/postgresql directory keeps the Docker scripts of this image happy, the /sock directory keeps my scripts happy.

[tianon]

Huh - this is definitely a case of "how did this ever work?" for me. From my reading of unix_socket_directories, this should've always caused issues with our script. 🙈

Indeed, I can reproduce on both 10-alpine and 9-alpine:

$ docker run --rm --name test -e POSTGRES_DB=symfony -e POSTGRES_USER=symfony -e POSTGRES_PASSWORD=symfony postgres:9-alpine -c unix_socket_directories=/tmp
Unable to find image 'postgres:9-alpine' locally
9-alpine: Pulling from library/postgres
59bf1c3509f3: Already exists 
c50e01d57241: Already exists 
a0646b0f1ead: Already exists 
912227b294ee: Pull complete 
696b75eaa88e: Pull complete 
0d83746bb80b: Pull complete 
3a431a2253cd: Pull complete 
24ff05c04760: Pull complete 
587cf5e11ca1: Pull complete 
Digest: sha256:8342bcb43446694428ec6594e72e4299692854f0fc3aca090b0ab46f4c7f32a1
Status: Downloaded newer image for postgres:9-alpine
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... No usable system locales were found.
Use the option "--debug" to see details.
sh: locale: not found
ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....LOG:  database system was shut down at 2022-02-23 18:15:08 UTC
LOG:  MultiXact member wraparound protections are now enabled
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
 done
server started
psql: 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"?

Are you sure your old version wasn't using a pre-initialized data directory or something?

[Roysten]

If I try to reproduce it with alpine-10 and alpine-9 I get the same output as you. If I try it with alpine-10.4 it does work:

$ docker run --rm --name test -e POSTGRES_DB=symfony -e POSTGRES_USER=symfony -e POSTGRES_PASSWORD=symfony postgres:10.4-alpine -c unix_socket_directories=/tmp
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... sh: locale: not found
2022-02-24 09:12:13.773 UTC [26] WARNING:  no usable system locales were found
ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start


WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2022-02-24 09:12:14.231 UTC [30] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-02-24 09:12:14.265 UTC [31] LOG:  database system was shut down at 2022-02-24 09:12:14 UTC
2022-02-24 09:12:14.274 UTC [30] LOG:  database system is ready to accept connections
 done
server started
CREATE DATABASE

CREATE ROLE


/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

waiting for server to shut down....2022-02-24 09:12:14.577 UTC [30] LOG:  received fast shutdown request
2022-02-24 09:12:14.579 UTC [30] LOG:  aborting any active transactions
2022-02-24 09:12:14.579 UTC [30] LOG:  worker process: logical replication launcher (PID 37) exited with exit code 1
2022-02-24 09:12:14.579 UTC [32] LOG:  shutting down
2022-02-24 09:12:14.625 UTC [30] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2022-02-24 09:12:14.686 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-02-24 09:12:14.686 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2022-02-24 09:12:14.710 UTC [1] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-02-24 09:12:14.735 UTC [43] LOG:  database system was shut down at 2022-02-24 09:12:14 UTC
2022-02-24 09:12:14.744 UTC [1] LOG:  database system is ready to accept connections

So I guess I was just lucky using version 10.4? 😄 Did you try it with 10.4 as well?

[tianon]

Indeed, it does work on 10.4 -- I walked through versions and it works at 10.10 and fails at 10.11 -- suspiciously, 0c29c35 is right before the 10.11 update commits, which includes the major change that the "temporary" server that gets started now includes the arguments that are passed for the real server, since most of them are likely to be relevant there too.

In other words, in versions of 10 before 10.11, the arguments you pass in were completely ignored for the temporary server (which was technically a bug, fixed in the huge refactoring of #496). 🙈

[Roysten]

Ah thanks for the explanation, that makes sense. The workaround I posted works well for me, so I will close this issue because I don't think any changes are needed.