pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding date column to system catalog "pg_database"
4+ messages / 4 participants
[nested] [flat]
* Adding date column to system catalog "pg_database"
@ 2018-06-07 11:05 pavan95 <pavan.postgresdba@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: pavan95 @ 2018-06-07 11:05 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Adding date column to system catalog "pg_database"
@ 2018-06-07 11:14 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Achilleas Mantzios @ 2018-06-07 11:14 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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 modern linuxes 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Adding date column to system catalog "pg_database"
@ 2018-06-07 11:42 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Shreeyansh Dba @ 2018-06-07 11:42 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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..
<http://www.shreeyansh.com;
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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Adding date column to system catalog "pg_database"
@ 2018-06-09 08:37 Feike Steenbergen <feikesteenbergen@gmail.com>
parent: pavan95 <pavan.postgresdba@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Feike Steenbergen @ 2018-06-09 08:37 UTC (permalink / raw)
To: pavan95 <pavan.postgresdba@gmail.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-06-09 08:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-06-07 11:05 Adding date column to system catalog "pg_database" pavan95 <pavan.postgresdba@gmail.com>
2018-06-07 11:14 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2018-06-07 11:42 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2018-06-09 08:37 ` Feike Steenbergen <feikesteenbergen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox