Postgresql – Is it possible to tell when databases, schemas and tables were created in Postgres

pgadminpostgresql

Is it possible to tell when databases, schemas and tables were created in Postgres?

I've serached through PgAdmin and tried the command line interface with no joy. Also can't find any reference to this functionality anywhere online. Maybe I'm missing something obvious!

Best Answer

From this post:

;WITH CTE AS
(
    SELECT 
        table_name 

        ,
        (
            SELECT 
                MAX(pg_ls_dir::int)::text 
            FROM pg_ls_dir('./base') 
            WHERE pg_ls_dir <> 'pgsql_tmp' 
            AND  pg_ls_dir::int  <= (SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name)
        ) as folder 


        ,(SELECT relfilenode FROM pg_class WHERE relname ILIKE table_name) filenode

    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE'
    AND table_schema = 'public'
)

SELECT 
    table_name 
    ,(
        SELECT creation 
        FROM pg_stat_file(
            './base/' || folder || '/' || filenode 
        )
    ) as creation_time
FROM CTE;

But, from the comment to this post here, this method is sometimes unreliable. Use at your own risk! You might also be interested in this - it appears that PostgreSQL is finally going down the road of an Oracle wait interface.

If you'd care to file a feature request and post the link in a comment here (with @Vérace in the body), I'd be happy to file a "me-too".

Related Question