2

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!

1

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".

  • Thanks for the info and links. Interesting... All answers are only referring to tables though, so the same question still stands for find the time and date when a database was created. – Matt Apr 26 '16 at 12:09
  • Also, @Vérace feature request can be seen here: postgresql.uservoice.com/forums/21853-general/suggestions/… – Matt Apr 26 '16 at 12:43
  • @Matt "me-too" filed :-) Check remark on query vs \dt. – Vérace Apr 26 '16 at 14:21
  • @Matt - forgot to mention that you can "embed" links in comments by putting [square] brackets around your link word (or phrase) and immediately following it with the link in (round brackets). – Vérace Apr 27 '16 at 8:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.