lyspgmon

README

lyspgmon

Code for monitoring Postgres databases.

lyspgmonddl

Views and associated stores for monitoring any Postgres database, including active queries, PG settings, table bloat and unused indexes. Audit functions and stores.

Installation via the Install() func.

LoveYourStack-specific checks

CheckDb() func, which reviews a Postgres database using LoveYourStack rules and conventions. These are:

1. If a table has an "updated_at" timestamp column, this will be updated via a trigger and not set manually. CheckDb() will add this trigger if it is missing.
2. If a table has a "last_user_update_by" column, the "t_audit_update" trigger will be added, which will store data changes in system.data_update.
3. Table shortnames should be set via a "shortname: " comment. CheckDb() checks that the shortname comments are unique.
4. If a table has an associated "_archived" table for archive (soft delete) functionality, CheckDb() checks that the base table columns and _archived table columns are consistent.

Documentation

Overview

Package lyspgdb contains functions for monitoring Postgres databases.

Index

• func AddMissingAuditUpdateTriggers(ctx context.Context, ownerDb *pgxpool.Pool, infoLog *slog.Logger) (err error)
• func AddMissingUpdatedAtTriggers(ctx context.Context, ownerDb *pgxpool.Pool, infoLog *slog.Logger) (err error)
• func CheckDb(ctx context.Context, ownerDb *pgxpool.Pool, infoLog, errorLog *slog.Logger) (err error)
• func CheckDuplicateShortnames(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)
• func CheckInconsistentArchivedCols(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)
• func CheckMissingLastUserUpdateByCols(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)
• func Install(ctx context.Context, ownerDb *pgxpool.Pool, dbOwner string, ...) (err error)

Functions

func AddMissingAuditUpdateTriggers

func AddMissingAuditUpdateTriggers(ctx context.Context, ownerDb *pgxpool.Pool, infoLog *slog.Logger) (err error)

AddMissingAuditUpdateTriggers adds missing audit update triggers for all tables returned by v_missing_audit_update_trigger

func AddMissingUpdatedAtTriggers

func AddMissingUpdatedAtTriggers(ctx context.Context, ownerDb *pgxpool.Pool, infoLog *slog.Logger) (err error)

AddMissingUpdatedAtTriggers adds missing updated_at triggers for all tables returned by v_missing_updated_at_trigger

func CheckDb

func CheckDb(ctx context.Context, ownerDb *pgxpool.Pool, infoLog, errorLog *slog.Logger) (err error)

CheckDb checks the integrity of the database. It should be run after schema updates and also periodically

func CheckDuplicateShortnames

func CheckDuplicateShortnames(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)

CheckDuplicateShortnames checks for tables with the same shortname comment

func CheckInconsistentArchivedCols

func CheckInconsistentArchivedCols(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)

CheckInconsistentArchivedCols checks for archived tables that do not have the same cols as their base tables

func CheckMissingLastUserUpdateByCols

func CheckMissingLastUserUpdateByCols(ctx context.Context, ownerDb *pgxpool.Pool, errorLog *slog.Logger) (err error)

CheckMissingLastUserUpdateByCols checks for tables that have the t_audit_update trigger but are missing the last_user_update_by col

func Install

func Install(ctx context.Context, ownerDb *pgxpool.Pool, dbOwner string, infoLog *slog.Logger) (err error)

Install creates the lyspgmon schema in the database if it is not already present, and (re)-adds functions and monitoring views in the lyspgmonddl folder note that local permissions need to be granted to lyspgmon schema and objects after installation