Azure Postgresql Flexi instance - How to check the table creation date and time

subhash-DBA 45 Reputation points
2024-04-24T02:29:38.51+00:00

HI

Can someone help me to find the tables/objects create date/time in Azure postgresql flexi instance ?

we used to use this one , select * from pg_stat_file( pg_relation_filepath('alert') ) in on-prem but that's not working in azure with admin user due to permission issues

Azure Database for PostgreSQL
Accepted answer
  1. ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee
    2024-05-07T03:40:08.6933333+00:00

    Hi @subhash-DBA ,

    We are sorry about the inconvenience.

    As suggested by Nacho Alonso Portillo •, product PM:

    For now, we cannot offer you an alternative since pg_stat_file requires superuser privileges to be executed.

    We will consider providing an alternative function to allow you to get that information.

    However, this won't be available before six months from now, at least.

    Thanks

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Nacho Alonso Portillo 5 Reputation points Microsoft Employee
    2024-04-27T19:08:25.0166667+00:00

    @subhash-DBA In on-prem systems and also in Azure Database for PostgreSQL Flexible Server, you can use the set of Database Object Size Functions https://www.postgresql.org/docs/16/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE which provide you the information you need, and are not limited to superusers.

    Thanks,
    Nacho

    1 person found this answer helpful.
  2. Vahid Ghafarpour 20,475 Reputation points
    2024-04-24T03:40:51.5866667+00:00

    Thanks for posting your question in the Microsoft Q&A forum.

    Unfortunately, there isn’t a direct equivalent to the pg_stat_file method you mentioned.

    In addition looking at the table files won't necessarily give you the right information - there are table operations that will create a new file for you, in which case the date would reset.

    PS. Did you check extensions? maybe you can find good alternative for your requirement.

    You can find and install extensions as described in this article:

    https://video2.skills-academy.com/en-us/azure/postgresql/flexible-server/concepts-extensions

    ** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **

    0 comments