RE: PostgreSQL DB checkpoint error!

[Ashok kumar Mani <amani@xxxxxxxxxxxxx>]

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.g.johnston@xxxxxxxxx>
Sent: Tuesday, April 28, 2020 10:27 AM
To: Ashok kumar Mani <amani@xxxxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx

 

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

 

On Monday, April 27, 2020, Ashok kumar Mani <amani@xxxxxxxxxxxxx> 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.