[FEATURE] postgresql server crashes on database creation

[neildarlow]

Architecture: ARMv7h (Exynos 5422 - ODROID HC1)
Package version: postgresql-13.1-3
Environment: Running in a systemd-nspawn container (alongside MariaDB which exhibits no problems).

I am able to start the postgresql server, perform system queries and create users/roles however any attempt to create databases crashes the server with the following output in psql:

$this->bbcode_second_pass_code('', 'WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.')
The database subsystem is left in an unrecoverable state after this and rebuild from a backup is required.

Is anyone else experiencing this issue?

ATB,
Neil

[robg]

Creating a database on PostgreSQL works for me on an up-to-date system on aarch64. Did this issue arise after an update? If so, have you tried downgrading? Can you create a DB following these steps after starting postgresql.service?

[neildarlow]

Hi robg,

Thank you for your response. I've read through that documentation and I was using those exact createuser and createdb commands when the failure occurred.

llvm-libs has been updated recently (after the postgresql package build) but, as I only installed recently, I don't have the previous version in my pacman cache and, I'm guessing, archlinuxARM doesn't have a package archive like vanilla Arch.

I have several avenues to explore viz:
1) The systemd service file contains a lot of protections which might be a factor in this. Maybe try to simplify.
2) My kernel is fairly old (4.14.180). Maybe a newer kernel but this could require a distribution change.
3) A 32-bit vs. 64-bit issue. I've tried researching this but comments relating to 32-bit are like "Who runs 32-bit database servers?".
4) An updated dependency issue which I mentioned earlier. Testing another distribution might be needed to exclude this.

I have another set of hardware and am mulling over trying the Hardkernel Ubuntu-minimal image. It would address some of these concerns but introduce more dependencies.

I'm actually hoping for someone with 32-bit postgresql experience on archlinuxARM to come along.

ATB,
Neil

[neildarlow]

Hi,

The crash doesn't occur with:

$this->bbcode_second_pass_code('', 'fsync=off')
in postgresql.conf.

According to the PostgreSQL documentation this is a dangerous setting to change. It does however suggest that this isn't a configuration related problem but could be platform-specific.

I'm powering this system with a UPS so power failure shouldn't be a problem but system-wide or postgresql server crashes could be. I also wonder if a newer kernel version might make the problem disappear.

ATB,
Neil

[neildarlow]

To anyone coming across this thread, I'll leave this link here.

PANIC on fsync() failure.

The TL;DR of this is that PostgreSQL uses the return of an fsync() call as an indication of the stability of the underlying filesystem and immediately terminates the server on a non-success return.

The effect of fsync failure is kernel and, by association, filesystem type dependant. It's a ballsy move to adopt this approach because PoastgreSQL surely isn't the only process on a system which issues fsync calls and I don't see massive instability elsewhere on the system.

So, it appears that PostgreSQL (with fsync=on) makes a decision whether your environment is suitable for it to execute. Turning fsync off can defeat this test at the risk of data-loss as a result of an unmanaged power failure or crash.

This link also explains the rationale:

Highlight: Change in behavior with fsync()

ATB,
Neil