Re: How to know if a database has changed

On 11 December 2017 at 11:48, marcelo <marcelo(dot)nicolet(at)gmail(dot)com> wrote:
> The installation I'm planning will manage several databases, but not all of
> them will change every day.
> In order to planning/scripting the pg_dump usage, I would need to know which
> databases had some change activity at the end of some day.
> How can it be done?

Hi,

Just keep a snapshot of pg_stat_database view
(https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW)

At the end of the day compare the counters of
tup_inserted/tup_updated/tup_deleted or just xact_commit/xact_rollback
if the snapshot you kept is different from the current values there
were modifications on the database.

Maybe there are some caveats, for example CREATE commands are included
because they insert data in table catalogs? i don't know, is up to you
to check that

--
Jaime Casanova www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services