🐛 [PostgresSQL] does not get shutdown properly when stopping the addon

[paviro]

Description

Hi!
Every time I start the database I get this log.
database system was not properly shut down; automatic recovery in progress
Is there no way to properly stop the PostgresSQL server before stopping the addon?

Best,
Paul

Reproduction steps

1. Stop the addon
2. Start the addon

Addon Logs

Starting...
/etc/cont-init.d/00-banner.sh: executing
-----------------------------------------------------------
 Add-on: Postgres 15
 Postgres 15 with pgvecto.rs support
-----------------------------------------------------------
 Add-on version: 15.5-7
 You are running the latest version of this add-on.
 System: Home Assistant OS 13.2  (aarch64 / raspberrypi5-64)
 Home Assistant Core: 2024.11.3
 Home Assistant Supervisor: 2024.11.4
-----------------------------------------------------------
 Please, share the above information when looking for help
 or support in, e.g., GitHub, forums
-----------------------------------------------------------
 Provided by: https://github.com/alexbelgium/hassio-addons 
-----------------------------------------------------------
/etc/cont-init.d/00-global_var.sh: executing
POSTGRES_PASSWORD=******
Timezone set from Etc/UTC to Europe/Berlin
/etc/cont-init.d/01-config_yaml.sh: executing
Setting permissions for the config.yaml directory
Load environment variables from /config/config.yaml if existing
If accessing the file with filebrowser it should be mapped to /addon_configs/db21ed7f-postgres/config.yaml
---------------------------------------------------------
Wiki here on how to use : github.com/alexbelgium/hassio-addons/wiki/Add‐ons-feature-:-add-env-variables
... no env variables found, exiting
/etc/cont-init.d/01-custom_script.sh: executing
Execute /addon_configs/db21ed7f-postgres/postgres.sh if existing
Wiki here : github.com/alexbelgium/hassio-addons/wiki/Add-ons-feature-:-customisation
/etc/cont-init.d/20-folders.sh: executing
/etc/cont-init.d/99-run.sh: executing
[23:57:35] WARNING: The config.env file found in /config will be used (mapped to /addon_configs/xxx-postgres when accessing from Filebrowser). Please customize according to https://hub.docker.com/_/postgres and restart the add-on
[23:57:35] INFO: Starting the app
[23:57:35] INFO: Waiting for port 5432 to open...
PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-12-01 23:57:35.205 CET [224] LOG:  starting PostgreSQL 15.5 (Debian 15.5-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-12-01 23:57:35.205 CET [224] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-12-01 23:57:35.205 CET [224] LOG:  listening on IPv6 address "::", port 5432
2024-12-01 23:57:35.211 CET [224] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-12-01 23:57:35.220 CET [255] LOG:  database system was interrupted; last known up at 2024-12-01 23:53:29 CET
2024-12-01 23:57:35.659 CET [260] LOG:  incomplete startup packet
[23:57:35] INFO: Enabling vector.rs
2024-12-01 23:57:35.792 CET [270] FATAL:  the database system is starting up
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL:  the database system is starting up
2024-12-01 23:57:38.627 CET [255] LOG:  database system was not properly shut down; automatic recovery in progress
2024-12-01 23:57:38.631 CET [255] LOG:  redo starts at 0/E6C71D8
2024-12-01 23:57:38.631 CET [255] LOG:  invalid record length at 0/E6C7210: wanted 24, got 0
2024-12-01 23:57:38.631 CET [255] LOG:  redo done at 0/E6C71D8 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-12-01 23:57:38.639 CET [253] LOG:  checkpoint starting: end-of-recovery immediate wait
2024-12-01 23:57:38.663 CET [253] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.007 s, sync=0.005 s, total=0.028 s; sync files=2, longest=0.003 s, average=0.003 s; distance=0 kB, estimate=0 kB
2024-12-01 23:57:38.671 CET [224] LOG:  database system is ready to accept connections

Architecture

aarch64

OS

HAos

[alexbelgium]

Hi, thanks for reporting I had noticed that but given there was no visible impact I didn't follow up. Looks like docker-library/postgres#544 I wonder if it's possible to use those levers within the homeassistant's limitations

[paviro]

Mhh interesting! I was just wondering if not properly shutting the database down could increase the likelihood of database corruptions.

[alexbelgium]

I will implement their solution it actually seems easy please tell me how it goes !

[alexbelgium]

I've pushed version 15.6 (and above) with a potential fix

[alexbelgium]

Did not work, I'll try something else the script does not capture sigterm as not PID 1

[alexbelgium]

Argh I can't find how to pass SIGTERM to the script ;-)

[alexbelgium]

All good now on version -3

[paviro]

Noice thank you!

[paviro]

Not sure if this is a new issue but I now see
DROP EXTENSION CREATE EXTENSION
on every startup. Is this a problem?
Dropping an extension causes its member objects, and other explicitly dependent routines (see [ALTER ROUTINE](https://www.postgresql.org/docs/current/sql-alterroutine.html), the DEPENDS ON EXTENSION extension_name action), to be dropped as well.

[alexbelgium]

Ah no it's normal just ensuring pgvecto.rs is enabled

[alexbelgium]

Hi error came back solving #1654. I'll find something

[alexbelgium]

Ok, version -4 is good!