Обсуждение: Adding date column to system catalog "pg_database"

Поиск
Список
Период
Сортировка

Adding date column to system catalog "pg_database"

От
pavan95
Дата:
Hi all,

Is it possible to add date column to "pg_database" view by altering the
table? 

Reason is to get the created date of each database, I will schedule a
trigger to update the date column with current server time. By this I can
get the db creation date every now from the point of creation of new db.

Any ideas in accomplishment of this task are of great help!

Thanks in advance.



Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

Re: Adding date column to system catalog "pg_database"

От
Achilleas Mantzios
Дата:
On 07/06/2018 14:05, pavan95 wrote:
> Hi all,
>
> Is it possible to add date column to "pg_database" view by altering the
> table?
>
> Reason is to get the created date of each database, I will schedule a
> trigger to update the date column with current server time. By this I can
> get the db creation date every now from the point of creation of new db.
>
> Any ideas in accomplishment of this task are of great help!
Write your own function that implements this. Some OS's/filesys give the info on file/directory creation time. (Most
modernlinuxes don't out of the box). Then write your view (e.g. mypg_database) and 
 
use this.
Do not by any means try to alter any pg_catalog object.

>
> Thanks in advance.
>
>
>
> Regards,
> Pavan
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Adding date column to system catalog "pg_database"

От
Shreeyansh Dba
Дата:
Hi Pavan,

As  pg_database is a system catalog table and you can not add or alter it even you are superuser.

You can get the database related information which you are looking for using below query.

SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database;

Hope this helps..



On Thu, Jun 7, 2018 at 4:35 PM, pavan95 <pavan.postgresdba@gmail.com> wrote:
Hi all,

Is it possible to add date column to "pg_database" view by altering the
table?

Reason is to get the created date of each database, I will schedule a
trigger to update the date column with current server time. By this I can
get the db creation date every now from the point of creation of new db.

Any ideas in accomplishment of this task are of great help!

Thanks in advance.



Regards,
Pavan



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html


Re: Adding date column to system catalog "pg_database"

От
Feike Steenbergen
Дата:
On 7 June 2018 at 13:05, pavan95 <pavan.postgresdba@gmail.com> wrote:
> Any ideas in accomplishment of this task are of great help!

You could create a (structured, json?) comment on the database with this
kind of information, if you don't use the comment field on the database (yet).


feike=# SELECT json_build_object('created', now(), 'author',
CURRENT_USER)::text AS comment;
-[ RECORD 1 ]-----------------------------------------------------------------
comment | {"created" : "2018-06-09T08:33:30.994298+00:00", "author" : "feike"}

feike=# COMMENT ON DATABASE demo IS '{"created" :
"2018-06-09T08:33:30.994298+00:00", "author" : "feike"}';
COMMENT


Later on, you can read/parse that comment using pg_shdescription:


feike=#
SELECT description,
       (description::jsonb->>'created')::timestamptz
  FROM pg_shdescription
  JOIN pg_database pd ON (objoid=pd.oid and classoid =
'pg_catalog.pg_database'::regclass)
 WHERE datname = 'demo';
-[ RECORD 1 ]---------------------------------------------------------------------
description | {"created" : "2018-06-09T08:33:30.994298+00:00",
"author" : "feike"}
timestamptz | 2018-06-09 08:33:30.994298+00


regards,

Feike