Thread: Last insert/update/delete time for a table

Hi all,

I'm in the process of writing a backup script which only dumps those tables which were changed in the last 24 hours. We

have hundreds of tables (one per user), but most of them don't get changed very often, so there's no point in dumping
them every night.

Is there a way to get the time of the last insert, update or delete statement for a specific table?

Regards,
Aleksander

On Fri, Apr 25, 2008 at 02:14:17PM +0200, Aleksander Kmetec - INTERA wrote:
> Is there a way to get the time of the last insert, update or delete
> statement for a specific table?

Only if you put a trigger on each table to collect that information.

A

Aleksander Kmetec - INTERA wrote:
> Hi all,
>
> I'm in the process of writing a backup script which only dumps those
> tables which were changed in the last 24 hours. We have hundreds of
> tables (one per user), but most of them don't get changed very often, so
> there's no point in dumping them every night.
>
> Is there a way to get the time of the last insert, update or delete
> statement for a specific table?

You can always look at pg_stat_user_tables on a regular basis (see ch 26
of the docs). Note that there is a small chance not all updates will be
recorded here.

--
   Richard Huxton
   Archonet Ltd

2008/4/25 Aleksander Kmetec - INTERA <aleksander.kmetec@intera.si>:
>  Is there a way to get the time of the last insert, update or delete
> statement for a specific table?

You can check the time stamp of the file corresponding the table
after checkpoint. The relationship between the table name and
the file name is in pg_class.

   SELECT relfilenode FROM pg_class WHERE relname = 'tablename';