Failure to run centos/postgresql-96-centos7 with userdata on a host mount

[jandubois]

Description

I've tested this with latest lima, both with the default image (and nerdctl.lima) and the docker image (and the docker cli), using sshfs.cache set to both true and false, and in each configuration I observe failures during bootstrap like the following:

# sshfs.cache: true
$ nerdctl.lima run --rm --name postgresql -e POSTGRESQL_DATABASE=my-db -e POSTGRESQL_USER=user -e POSTGRESQL_PASSWORD=pass -p 5432:5432 -v $(pwd):/var/lib/pgsql/data centos/postgresql-96-centos7
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/pgsql/data/userdata ... 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 ... FATAL:  unexpected data beyond EOF in block 23 of relation base/1/t1_11850
HINT:  This has been seen to occur with buggy kernels; consider updating your system.
CONTEXT:  COPY tmp_pg_description, line 1589
STATEMENT:  COPY tmp_pg_description FROM E'/opt/rh/rh-postgresql96/root/usr/share/pgsql/postgres.description';

child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/pgsql/data/userdata"

Or

# sshfs.cache: false
$ nerdctl.lima run --rm --name postgresql -e POSTGRESQL_DATABASE=my-db -e POSTGRESQL_USER=user -e POSTGRESQL_PASSWORD=pass -p 5432:5432 -v $(pwd):/var/lib/pgsql/data centos/postgresql-96-centos7
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/pgsql/data/userdata ... 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 ... FATAL:  could not read block 0 in file "base/1/2667": read only 0 of 8192 bytes
STATEMENT:  INSERT INTO pg_depend SELECT 0,0,0, tableoid,oid,0, 'p'  FROM pg_constraint;

child process exited with exit code 1
initdb: removing contents of data directory "/var/lib/pgsql/data/userdata"

Sometimes (rarely) the bootstrap succeeds, but then fails because of ownership problems:

[...]
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/data/userdata -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....FATAL:  data directory "/var/lib/pgsql/data/userdata" has wrong ownership
HINT:  The server must be started by the user that owns the data directory.
 stopped waiting
pg_ctl: could not start server
Examine the log output.

Running the same command with Docker Desktop for Mac always works fine:

[...]
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ...
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/pgsql/data/userdata -l logfile start

waiting for server to start....LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".
 done
server started
/var/run/postgresql:5432 - accepting connections
=> sourcing /usr/share/container-scripts/postgresql/start/set_passwords.sh ...
ALTER ROLE
waiting for server to shut down.... done
server stopped
Starting server...
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "pg_log".

[jandubois]

I'm not sure if there is anything we can do; maybe it is just a limitation of using sshfs. But I wanted to document this as a test case to try again once we switch to 9p or whatever.

[jandubois]

Of course the workaround is to not put the userdata directory on the host mounted filesystem, which is probably a bad idea for performance reasons alone. But seeing the file corruption when using the host filesystem is concerning.

[jandubois]

Oh, in addition to default and docker I've also tested it on the alpine configuration via Rancher Desktop. Fails the same way there too, both with and without caching.

[afbjorklund]

I didn't know postgres supported having the database on a remote filesystem ? But apparently they do support NFS.

https://www.postgresql.org/docs/14/creating-cluster.html#CREATING-CLUSTER-FILESYSTEM

Previously the suggestion has been to use a named volume instead.

• mysql_install_db fails on sshfs mount #577

[jandubois]

I didn't know postgres supported having the database on a remote filesystem ?

Maybe they don't. But the point remains that it works on Docker Desktop and fails on Lima... (I picked up the particular command for testing from a Slack thread, and confirmed it)