RE: PostgreSQL DB checkpoint error!

Classification: External

Hi David,
I am running cronjob at the same time which will do data pruning for zabbix database(psql). Please let me know if that warning is related to reindex and vacuum ?

^C-bash-4.2$ cat /Data/zabbix_hkp/scripts/data_Pruning.sh
psql -d zabbix -f /Data/zabbix_hkp/scripts/three_months_datapurge.sql 1>/Data/zabbix_hkp/scripts_log/data_purging_`date +%m%d%y`.log 2>data_cleaning_err_`date +%m%d%y`.log
-bash-4.2$ cat /Data/zabbix_hkp/scripts/three_months_datapurge.sql
-- Delete alerts which are older than 1 day -> tested on 8th april 2020
delete FROM alerts where age(to_timestamp(alerts.clock)) > interval '90 days';
VACUUM FULL alerts ;
REINDEX TABLE ALERTS;
delete FROM acknowledges where age(to_timestamp(acknowledges.clock)) > interval '90 days';
VACUUM FULL acknowledges;
REINDEX TABLE acknowledges;
-- Delete events which are older than 1 day -> tested on 8th april 2020
delete FROM events where age(to_timestamp(events.clock)) > interval '90 days';
VACUUM FULL events;
REINDEX TABLE events;
delete FROM history where age(to_timestamp(history.clock)) > interval '90 days';
VACUUM FULL history;
REINDEX TABLE history;
delete FROM history_uint where age(to_timestamp(history_uint.clock)) > interval '90 days' ;
VACUUM FULL history_uint;
REINDEX TABLE history_uint;
delete FROM history_str where age(to_timestamp(history_str.clock)) > interval '90 days' ;
VACUUM TABLE history_str;
REINDEX TABLE history_str;
delete FROM history_text where age(to_timestamp(history_text.clock)) > interval '90 days' ;
VACUUM FULL history_text;
REINDEX TABLE history_text;
delete FROM history_log where age(to_timestamp(history_log.clock)) > interval '90 days' ;
VACUUM FULL history_log;
REINDEX TABLE history_log;
delete FROM trends where age(to_timestamp(trends.clock)) > interval '90 days';
VACUUM FULL trends;
REINDEX TABLE trends;
delete FROM trends_uint where age(to_timestamp(trends_uint.clock)) > interval '90 days' ;
VACUUM FULL trends_uint;
REINDEX TABLE trends_uint;
-- Delete orphaned alerts entries
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM actions WHERE alerts.actionid = actions.actionid);
DELETE FROM alerts WHERE NOT EXISTS (SELECT 1 FROM events WHERE alerts.eventid = events.eventid);
DELETE FROM alerts WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM alerts WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
-- Delete orphaned application entries that no longer map back to a host
DELETE FROM applications WHERE NOT hostid IN (SELECT hostid FROM hosts);
-- Delete orphaned auditlog details (such as logins)
DELETE FROM auditlog_details WHERE NOT auditid IN (SELECT auditid FROM auditlog);
DELETE FROM auditlog WHERE NOT userid IN (SELECT userid FROM users);
-- Delete orphaned conditions
DELETE FROM conditions WHERE NOT actionid IN (SELECT actionid FROM actions);
-- Delete orphaned functions
DELETE FROM functions WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM functions WHERE NOT triggerid IN (SELECT triggerid FROM triggers);
-- Delete orphaned graph items
DELETE FROM graphs_items WHERE NOT graphid IN (SELECT graphid FROM graphs);
-- Delete orphaned host macro's
DELETE FROM hostmacro WHERE NOT hostid IN (SELECT hostid FROM hosts);
-- Delete orphaned item data
DELETE FROM items WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM items_applications WHERE applicationid NOT IN (SELECT applicationid FROM applications);
DELETE FROM items_applications WHERE itemid NOT IN (SELECT itemid FROM items);
-- Delete orphaned HTTP check data
DELETE FROM httpstep WHERE NOT httptestid IN (SELECT httptestid FROM httptest);
DELETE FROM httpstepitem WHERE NOT httpstepid IN (SELECT httpstepid FROM httpstep);
DELETE FROM httpstepitem WHERE NOT itemid IN (SELECT itemid FROM items);
DELETE FROM httptest WHERE applicationid NOT IN (SELECT applicationid FROM applications);
-- Delete orphaned maintenance data
DELETE FROM maintenances_groups WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_groups WHERE groupid NOT IN (SELECT groupid FROM groups);
DELETE FROM maintenances_hosts WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_hosts WHERE hostid NOT IN (SELECT hostid FROM hosts);
DELETE FROM maintenances_windows WHERE maintenanceid NOT IN (SELECT maintenanceid FROM maintenances);
DELETE FROM maintenances_windows WHERE timeperiodid NOT IN (SELECT timeperiodid FROM timeperiods);
-- Delete orphaned mappings
DELETE FROM mappings WHERE NOT valuemapid IN (SELECT valuemapid FROM valuemaps);
-- Delete orphaned media items
DELETE FROM media WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM media WHERE NOT mediatypeid IN (SELECT mediatypeid FROM media_type);
DELETE FROM rights WHERE NOT groupid IN (SELECT usrgrpid FROM usrgrp);
DELETE FROM rights WHERE NOT id IN (SELECT groupid FROM groups);
DELETE FROM sessions WHERE NOT userid IN (SELECT userid FROM users);
-- Delete orphaned screens
DELETE FROM screens_items WHERE screenid NOT IN (SELECT screenid FROM screens);
-- Delete orphaned events & triggers
DELETE FROM trigger_depends WHERE triggerid_down NOT IN (SELECT triggerid FROM triggers);
DELETE FROM trigger_depends WHERE triggerid_up NOT IN (SELECT triggerid FROM triggers);
-- Delete records in the history/trends table for items that no longer exist
DELETE FROM history WHERE NOT EXISTS (SELECT 1 FROM items WHERE history.itemid = items.itemid);
DELETE FROM history_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_uint.itemid = items.itemid);
DELETE FROM history_log WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_log.itemid = items.itemid);
DELETE FROM history_str WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_str.itemid = items.itemid);
DELETE FROM history_text WHERE NOT EXISTS (SELECT 1 FROM items WHERE history_text.itemid = items.itemid);
DELETE FROM trends WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends.itemid = items.itemid);
DELETE FROM trends_uint WHERE NOT EXISTS (SELECT 1 FROM items WHERE trends_uint.itemid = items.itemid);
-- Delete records in the events table for triggers/items that no longer exist
DELETE FROM events WHERE source = 0 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers);
DELETE FROM events WHERE source = 3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items);
-- Delete all orphaned acknowledge entries
DELETE FROM acknowledges WHERE NOT eventid IN (SELECT eventid FROM events);
DELETE FROM acknowledges WHERE NOT userid IN (SELECT userid FROM users);
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE (source = 0 OR source=3) AND object = 0 AND objectid NOT IN (SELECT triggerid FROM triggers));
DELETE FROM acknowledges WHERE eventid IN (SELECT eventid FROM events WHERE source=3 AND object = 4 AND objectid NOT IN (SELECT itemid FROM items));
-bash-4.2$

Best Wishes,

Ashokkumar Mani
Database Architect\DBA
OCP | AWSCSA | M103

From: David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
Sent: Tuesday, April 28, 2020 10:27 AM
To: Ashok kumar Mani <amani(at)accelaero(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org; pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: PostgreSQL DB checkpoint error!

Information Security Email Alert: This email is from an EXTERNAL source. Please use caution when clicking on links or opening attachments from an unknown or suspicious sender. To report a suspected phishing email, Send us an Email on Servicedesk(at)accelaero(dot)com<mailto:Servicedesk(at)accelaero(dot)com>

There are no errors in the logs you provided, only "weak warnings"

On Monday, April 27, 2020, Ashok kumar Mani <amani(at)accelaero(dot)com<mailto:amani(at)accelaero(dot)com>> wrote:

Database is getting hung every day at 5AM. I found the below message from log file , Could you please help me , should I need to increase the max_wal_size value ?
Probably not, you should probably look for insertion code that does high volume via the insert sql command without transactions and/or batching.
Why it say "incomplete startup packet" in the log.

Because you have a software client beginning but not completing a connection to the database. Usually its some form of monitoring.

David J.