CODEWITHSUNDEEP
databasepostgresql
Jakub Zalas
Jakub Zalas

Reputation: 36191

How to get last access/modification date of a PostgreSQL database?

On development server I'd like to remove unused databases. To realize that I need to know if database is still used by someone or not.

Is there a way to get last access or modification date of given database, schema or table?

Upvotes: 25

Views: 67896

Answers (5)

Thirumal
Thirumal

Reputation: 9536

PostgreSQL 9.5 let us to track last modified commit.

  1. Check track commit is on or off using the following query

    show track_commit_timestamp;

  2. If it return "ON" go to step 3 else modify postgresql.conf

    cd /etc/postgresql/9.5/main/
vi postgresql.conf

    Change 

    track_commit_timestamp = off

    to 

    track_commit_timestamp = on

    Restart the postgres / system

    Repeat step 1.

  3. Use the following query to track last commit 

    SELECT pg_xact_commit_timestamp(xmin), * FROM  YOUR_TABLE_NAME;

SELECT pg_xact_commit_timestamp(xmin), * FROM YOUR_TABLE_NAME where COLUMN_NAME=VALUE;

Upvotes: 5

Craig Ringer
Craig Ringer

Reputation: 324375

There is no built-in way to do this - and all the approaches that check the file mtime described in other answers here are wrong. The only reliable option is to add triggers to every table that record a change to a single change-history table, which is horribly inefficient and can't be done retroactively.

If you only care about "database used" vs "database not used" you can potentially collect this information from the CSV-format database log files. Detecting "modified" vs "not modified" is a lot harder; consider SELECT writes_to_some_table(...).

If you don't need to detect old activity, you can use pg_stat_database, which records activity since the last stats reset. e.g.:

-[ RECORD 6 ]--+------------------------------
datid          | 51160
datname        | regress
numbackends    | 0
xact_commit    | 54224
xact_rollback  | 157
blks_read      | 2591
blks_hit       | 1592931
tup_returned   | 26658392
tup_fetched    | 327541
tup_inserted   | 1664
tup_updated    | 1371
tup_deleted    | 246
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 0
blk_write_time | 0
stats_reset    | 2013-12-13 18:51:26.650521+08

so I can see that there has been activity on this DB since the last stats reset. However, I don't know anything about what happened before the stats reset, so if I had a DB showing zero activity since a stats reset half an hour ago, I'd know nothing useful.

Upvotes: 10

Falco
Falco

Reputation: 162

My way to get the modification date of my tables:

Python Function

CREATE OR REPLACE FUNCTION py_get_file_modification_timestamp(afilename text)
  RETURNS timestamp without time zone AS
$BODY$
    import os
    import datetime
    return datetime.datetime.fromtimestamp(os.path.getmtime(afilename))
$BODY$
  LANGUAGE plpythonu VOLATILE
  COST 100;

SQL Query

SELECT
    schemaname,
    tablename,
    py_get_file_modification_timestamp('*postgresql_data_dir*/*tablespace_folder*/'||relfilenode)
FROM
    pg_class
INNER JOIN
    pg_catalog.pg_tables ON (tablename = relname)
WHERE
    schemaname = 'public'

I'm not sure if things like vacuum can mess this aproach, but in my tests it's a pretty acurrate way to get tables that are no longer used, at least, on INSERT/UPDATE operations.

Upvotes: 2

tinychen
tinychen

Reputation: 2099

You can do it via checking last modification time of table's file. In postgresql,every table correspond one or more os files,like this:

select relfilenode from pg_class where relname = 'test';

the relfilenode is the file name of table "test".Then you could find the file in the database's directory.

in my test environment:

cd /data/pgdata/base/18976

ls -l -t | head

the last command means listing all files ordered by last modification time.

Upvotes: 16

pcent
pcent

Reputation: 2029

I guess you should activate some log options. You can get information about logging on postgreSQL here.

Upvotes: 1

Related Questions