Project

General

Profile

Support #10291

Vacuum DB at reboot

Added by Philippe May over 4 years ago. Updated about 3 years ago.

Status:
In Progress
Priority:
Normal
Assignee:
Philippe May
Start date:
08/06/2020
Due date:
% Done:

0%

Close

Description

There are several reboots of the server due to power cuts.

Gisaf requires that the Postgres database is vacuumed.

There's an autovacuum process running (default), unfortunately it seems that it doesn't detect broken databases and doesn't run at start.

History

#1 Updated by Philippe May over 4 years ago

Trying to install a service:

root@gisdb:~# cat /etc/systemd/system/gisdb_vacuum.service
[Unit]
Description=Vacuum gisaf DB
After=postgresql@12-main.service

[Install]
WantedBy=postgresql@12-main.service

[Service]
ExecStart=/usr/bin/vacuumdb avgis
User=postgres
Group=postgres

root@gisdb:~# systemctl enable gisdb_vacuum.service 
Created symlink /etc/systemd/system/postgresql@12-main.service.wants/gisdb_vacuum.service → /etc/systemd/system/gisdb_vacuum.service.

Will check after the next crash (shouldn't last long) if it's effective.

#2 Updated by Philippe May about 3 years ago

  • Status changed from New to In Progress

After today's boot (after power failure), observed the message for the failed gisdb_vacuum.service:

could not connect to database avgis: FATAL:  the database system is starting up

So, i still cannot make After=postgresql@12-main.service work as i expected.

Let's try something else: retry the vacuum service, found https://stackoverflow.com/questions/39284563/how-to-set-up-a-systemd-service-to-retry-5-times-on-a-cycle-of-30-seconds .

Setting retry 5 times, with a 30s interval:


root@gisdb:~# cat /etc/systemd/system/gisdb_vacuum.service 
[Unit]
Description=Vacuum gisaf DB
After=postgresql@12-main.service
StartLimitInterval=200
StartLimitBurst=5

[Install]
WantedBy=postgresql@12-main.service

[Service]
ExecStart=/usr/bin/vacuumdb avgis
User=postgres
Group=postgres
Restart=always
RestartSec=30

#3 Updated by Philippe May about 3 years ago

Tried restarting the service: was restarted after success. Try with Restart=on-failure instead, seems to fix this new problem:

root@gisdb:~# cat /etc/systemd/system/gisdb_vacuum.service 
[Unit]
Description=Vacuum gisaf DB
After=postgresql@12-main.service
StartLimitInterval=200
StartLimitBurst=5

[Install]
WantedBy=postgresql@12-main.service

[Service]
ExecStart=/usr/bin/vacuumdb avgis
User=postgres
Group=postgres
Restart=on-failure
RestartSec=30

Also available in: Atom PDF