Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uTOqe-0042Gb-Sg for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 17:51:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uTOqb-00EAvU-VE for pgsql-admin@arkaria.postgresql.org; Sun, 22 Jun 2025 17:51:18 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uTOqb-00EAvM-AY for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 17:51:18 +0000 Received: from p-east3-cluster5-host9-snip4-7.eps.apple.com ([57.103.86.218] helo=outbound.qs.icloud.com) by makus.postgresql.org with smtp (Exim 4.96) (envelope-from ) id 1uTOqX-003PtW-1D for pgsql-admin@lists.postgresql.org; Sun, 22 Jun 2025 17:51:17 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=icloud.com; s=1a1hai; bh=mMqGN3qSGaxfU3kb/KVG60SI1jm/bLWxow9Ta5P/xUs=; h=From:Message-Id:Content-Type:Mime-Version:Subject:Date:To:x-icloud-hme; b=1AvDNKlfk/g/DQKQ83iy1CBb/pFu563ZLE7ZVidG0UnzP6YA61CShqkst75mKzj9L Cs5GRAKchRJ7/+svaateC6mjC/2r1SQ20m/B+nkOx5roZqMU6O/ukwSkKuF1rGAG7o GV5b4xoKqTCusWJodJdj9LueDndrMlJrvj4QQPSlZTdfsSXMWKf5jbY7SMNPPMqGR5 t/Km/rkgbRtR7dvrKBSky/PGhHAE9SX0z0exyy6OhhUQ+zF7oS8cA3R1oVx8S2gtMX u9uMyvTNSzd6oh/k3qB58zMZxHpPF5GSPBL9oFOsNXRhN+xbzpiXGmRNOzdQiQmAe2 NB/pDiI27HG9w== Received: from outbound.qs.icloud.com (unknown [127.0.0.2]) by outbound.qs.icloud.com (Postfix) with ESMTPS id C49AE1800146; Sun, 22 Jun 2025 17:51:10 +0000 (UTC) Received: from smtpclient.apple (qs-asmtp-me-k8s.p00.prod.me.com [17.57.155.37]) by outbound.qs.icloud.com (Postfix) with ESMTPSA id 3C34A184A236; Sun, 22 Jun 2025 17:51:10 +0000 (UTC) From: Rui DeSousa Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_13E43850-F432-4FF6-99C9-22AF698233CA" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.10\)) Subject: Re: pg_restore Question Date: Sun, 22 Jun 2025 13:51:06 -0400 In-Reply-To: Cc: Pgsql-admin To: Ron Johnson References: X-Mailer: Apple Mail (2.3696.120.41.1.10) X-Proofpoint-ORIG-GUID: OOnaPyOqo4eVwYq9KwICG89UP-AzUuD8 X-Proofpoint-GUID: OOnaPyOqo4eVwYq9KwICG89UP-AzUuD8 X-Proofpoint-Spam-Details-Enc: AW1haW4tMjUwNjIyMDExMyBTYWx0ZWRfXxYmp1WiG7wBI v56sfQ9Cje76o7MRvbt1jDh+k1pHzBlTT9wUaLi6SoUXYiOkGV4UeElZj/Ee/2UEFZb9vZaa9l7 jnW1+J/ZF9n88iwedqfXzgd9x2WViHNgAH/Tab1dbZuws/24nHGPbUjAhtT6VhzO1HT649hayIj mgCCPWpZxamPxkhNB+Iy4goE7SgKH5KY4vvyc7gYNvOCH298yWCfY+Xdeu3AoDYFOxXpdRW47ZF u8JUwxdvibXg790TNzY73BQSgSMdxMAD/GnqiZ6L7kCwfr6u23fH0QTybh5UNKTIvWSpkKsoI= X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.293,Aquarius:18.0.1099,Hydra:6.0.736,FMLib:17.12.80.40 definitions=2025-06-22_06,2025-06-20_01,2025-03-28_01 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 mlxscore=0 suspectscore=0 malwarescore=0 bulkscore=0 phishscore=0 adultscore=0 mlxlogscore=999 clxscore=1011 spamscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.22.0-2506060001 definitions=main-2506220113 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_13E43850-F432-4FF6-99C9-22AF698233CA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Jun 22, 2025, at 12:57 PM, Ron Johnson = wrote: >=20 > It would be handy if pg_class had created_on timestamp, created_by = oid, altered_on timestamp, altered_by oid fields, but alas they don't = exist.=20 >=20 Here=E2=80=99s a script that will achieve just that; including when the = table is dropped. Regards, Rui demo=3D# \i audit.sql=20 CREATE SCHEMA CREATE EXTENSION SET CREATE TABLE CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE EVENT TRIGGER CREATE EVENT TRIGGER CREATE TABLE demo=3D# create table xo(i int); CREATE TABLE demo=3D# alter table xo add column d text; ALTER TABLE demo=3D# drop table xo; DROP TABLE demo=3D# select * from ddl_audit; audit_id | transaction_xact | transaction_time | inet_addr = | application | session | sql_id | = command | object_type | object_name =20 = ----------+------------------+-------------------------------+------------= +-------------+----------+--------------------------------------+---------= --------+-------------+--------------------------------- 1 | 27156 | 2025-06-22 12:43:13.139543-04 | = dead::beef | psql | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | alter sequence | sequence | = dba.ddl_audit_audit_id_seq 2 | 27156 | 2025-06-22 12:43:13.139543-04 | = dead::beef | psql | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create sequence | sequence | = dba.ddl_audit_audit_id_seq 3 | 27156 | 2025-06-22 12:43:13.139543-04 | = dead::beef | psql | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create table | table | = dba.ddl_audit 4 | 27156 | 2025-06-22 12:43:13.139543-04 | = dead::beef | psql | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create index | index | = dba.ddl_audit_pkey 5 | 27157 | 2025-06-22 12:43:22.64344-04 | = dead::beef | psql | postgres | = 85cca161-e608-52e8-b1f7-077b71af28b5 | create table | table | = dba.xo 6 | 27158 | 2025-06-22 12:43:38.602159-04 | = dead::beef | psql | postgres | = 49721262-8953-588f-a587-9791fabbe326 | alter table | table | = dba.xo 7 | 27159 | 2025-06-22 12:43:42.338722-04 | = dead::beef | psql | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table | table | = dba.xo 8 | 27159 | 2025-06-22 12:43:42.338722-04 | = dead::beef | psql | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table | type | = dba.xo 9 | 27159 | 2025-06-22 12:43:42.338722-04 | = dead::beef | psql | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table | type | = dba.xo[] 10 | 27159 | 2025-06-22 12:43:42.338722-04 | = dead::beef | psql | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table | toast table | = pg_toast.pg_toast_1109086 11 | 27159 | 2025-06-22 12:43:42.338722-04 | = dead::beef | psql | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table | index | = pg_toast.pg_toast_1109086_index (11 rows) demo=3D# select * from sql_audit; sql_id | created_on | = sql = =20 = --------------------------------------+-------------------------------+---= --------------------------------------------------------------------------= --------- eaf15c1c-881a-5982-ab98-c8ceb14163cf | 2025-06-22 12:43:13.139543-04 | = create table if not exists ddl_audit ( = + | | = audit_id bigint generated always as identity primary key = + | | = , transaction_xact xid8 not null default pg_current_xact_id() = + | | = , transaction_time timestamptz not null default transaction_timestamp() = + | | = , inet_addr inet not null default coalesce(inet_client_addr(), = 'dead::beef'::inet)+ | | = , application text not null default current_setting('application_name') = + | | = , session name not null default session_user = + | | = , sql_id uuid not null default sql_id(current_query()) = + | | = , command varchar(25) not null = + | | = , object_type varchar(20) not null = + | | = , object_name text = + | | = ); 85cca161-e608-52e8-b1f7-077b71af28b5 | 2025-06-22 12:43:22.64344-04 | = create table xo(i int); 49721262-8953-588f-a587-9791fabbe326 | 2025-06-22 12:43:38.602159-04 | = alter table xo add column d text; 036fb819-e8d9-5030-8071-8dc3a78bc0eb | 2025-06-22 12:43:42.338722-04 | = drop table xo; (4 rows) --Apple-Mail=_13E43850-F432-4FF6-99C9-22AF698233CA Content-Type: multipart/mixed; boundary="Apple-Mail=_FF0D16A6-C282-4553-93EE-7CAA78744954" --Apple-Mail=_FF0D16A6-C282-4553-93EE-7CAA78744954 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
On = Jun 22, 2025, at 12:57 PM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

It would be handy if pg_class had = created_on timestamp, created_by oid, altered_on timestamp, altered_by = oid fields, but alas they don't exist. 


Here=E2=80=99s a script that will = achieve just that; including when the table is dropped.
Regards,
Rui

demo=3D# \i = audit.sql 
CREATE SCHEMA
CREATE EXTENSION
SET
CREATE TABLE
CREATE = FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE EVENT = TRIGGER
CREATE EVENT TRIGGER
CREATE TABLE
demo=3D# create table = xo(i int);
CREATE TABLE
demo=3D# alter table xo add column d = text;
ALTER TABLE
demo=3D# drop table xo;
DROP TABLE
demo=3D# select * from = ddl_audit;
 audit_id | transaction_xact |       = transaction_time        | inet_addr  | = application | session  |             =    sql_id               =  |     command     | object_type |   =         object_name         =   
----------+------------------+-------------------------------+-= -----------+-------------+----------+-------------------------------------= -+-----------------+-------------+---------------------------------=
  =       1 |            27156 = | 2025-06-22 12:43:13.139543-04 | dead::beef | psql       =  | postgres | eaf15c1c-881a-5982-ab98-c8ceb14163cf | alter sequence =  | sequence    | = dba.ddl_audit_audit_id_seq
        2 |   =          27156 | 2025-06-22 12:43:13.139543-04 = | dead::beef | psql        | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create sequence | sequence   =  | dba.ddl_audit_audit_id_seq
        3 |   =          27156 | 2025-06-22 12:43:13.139543-04 = | dead::beef | psql        | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create table    | table =       | dba.ddl_audit
        4 |   =          27156 | 2025-06-22 12:43:13.139543-04 = | dead::beef | psql        | postgres | = eaf15c1c-881a-5982-ab98-c8ceb14163cf | create index    | index =       | dba.ddl_audit_pkey
      =   5 |            27157 | 2025-06-22 = 12:43:22.64344-04  | dead::beef | psql        | = postgres | 85cca161-e608-52e8-b1f7-077b71af28b5 | create table   =  | table       | dba.xo
      =   6 |            27158 | 2025-06-22 = 12:43:38.602159-04 | dead::beef | psql        | = postgres | 49721262-8953-588f-a587-9791fabbe326 | alter table   =   | table       | dba.xo
      =   7 |            27159 | 2025-06-22 = 12:43:42.338722-04 | dead::beef | psql        | = postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table   =    | table       | dba.xo
      =   8 |            27159 | 2025-06-22 = 12:43:42.338722-04 | dead::beef | psql        | = postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table   =    | type        | dba.xo
      =   9 |            27159 | 2025-06-22 = 12:43:42.338722-04 | dead::beef | psql        | = postgres | 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table   =    | type        | = dba.xo[]
       10 |         =    27159 | 2025-06-22 12:43:42.338722-04 | dead::beef | psql =        | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | = toast table | pg_toast.pg_toast_1109086
       11 |   =          27159 | 2025-06-22 12:43:42.338722-04 = | dead::beef | psql        | postgres | = 036fb819-e8d9-5030-8071-8dc3a78bc0eb | drop table      | = index       | = pg_toast.pg_toast_1109086_index
(11 rows)

demo=3D# select * from sql_audit;
      =           sql_id         =        |         =  created_on           |     =                     =                 sql   =                     =                   =  
--------------------------------------+------------------------= -------+------------------------------------------------------------------= --------------------
 eaf15c1c-881a-5982-ab98-c8ceb14163cf | 2025-06-22 = 12:43:13.139543-04 | create table if not exists ddl_audit (   =                     =                     =    +
                =                     =   |                   =             |   audit_id bigint = generated always as identity primary key         =                 =  +
                =                     =   |                   =             |   , transaction_xact = xid8 not null default pg_current_xact_id()         =             +
      =                     =             |         =                     =   |   , transaction_time timestamptz not null default = transaction_timestamp()           = +
 =                     =                 |     =                     =       |   , inet_addr inet not null default = coalesce(inet_client_addr(), 'dead::beef'::inet)+
      =                     =             |         =                     =   |   , application text not null default = current_setting('application_name')           = +
 =                     =                 |     =                     =       |   , session name not null default = session_user                 =                     =  +
                =                     =   |                   =             |   , sql_id uuid not = null default sql_id(current_query())           =                 =  +
                =                     =   |                   =             |   , command varchar(25) = not null                   =                     =              +
      =                     =             |         =                     =   |   , object_type varchar(20) not null       =                     =                     =  +
                =                     =   |                   =             |   , object_name text =                     =                     =                     =    +
                =                     =   |                   =             | );
 85cca161-e608-52e8-b1f7-077b71af28b5 | 2025-06-22 = 12:43:22.64344-04  | create table xo(i int);
 49721262-8953-588f-a587-9791fabbe326 | 2025-06-22 = 12:43:38.602159-04 | alter table xo add column d text;
 036fb819-e8d9-5030-8071-8dc3a78bc0eb | 2025-06-22 = 12:43:42.338722-04 | drop table xo;
(4 rows)



= --Apple-Mail=_FF0D16A6-C282-4553-93EE-7CAA78744954 Content-Disposition: attachment; filename=audit.sql Content-Type: application/octet-stream; x-unix-mode=0644; name="audit.sql" Content-Transfer-Encoding: 7bit \set dba dba create schema if not exists :dba authorization postgres; create extension if not exists "uuid-ossp" with schema :dba; set search_path=:dba; create table if not exists sql_audit ( sql_id uuid not null primary key , created_on timestamp with time zone not null default transaction_timestamp() , sql text not null ); create or replace function sql_id(_sql text) returns uuid as $function$ declare _sql_ns constant uuid := 'eafb1ce6-8ee5-53a7-acc7-ca5ab62cfade'::uuid; _sql_id uuid; begin if _sql is null then return uuid_nil(); end if; _sql_id := uuid_generate_v5(_sql_ns, _sql); insert into sql_audit (sql_id, sql) values (_sql_id, _sql) on conflict do nothing ; return _sql_id; end; $function$ language plpgsql set search_path = :dba ; create or replace function audit_ddl() returns event_trigger as $body$ declare _event record; begin for _event in select lower(e.command_tag) as command , lower(e.object_type) as object_type , e.object_identity from pg_event_trigger_ddl_commands() e left join pg_class c on c.oid = e.objid where coalesce (c.relpersistence = 't', false) = false loop insert into ddl_audit (command, object_type, object_name) values (_event.command, _event.object_type, _event.object_identity) ; end loop; end; $body$ language plpgsql security definer set search_path = :dba ; create or replace function audit_drop() returns event_trigger as $body$ declare _event record; begin for _event in select object_type , object_identity , is_temporary from pg_event_trigger_dropped_objects() where is_temporary = false loop insert into ddl_audit (command, object_type, object_name) values (lower(tg_tag), _event.object_type, _event.object_identity) ; end loop; end; $body$ language plpgsql security definer set search_path = :dba ; create event trigger audit_ddl on ddl_command_end execute procedure audit_ddl() ; create event trigger audit_drop on sql_drop execute procedure audit_drop() ; create table if not exists ddl_audit ( audit_id bigint generated always as identity primary key , transaction_xact xid8 not null default pg_current_xact_id() , transaction_time timestamptz not null default transaction_timestamp() , inet_addr inet not null default coalesce(inet_client_addr(), 'dead::beef'::inet) , application text not null default current_setting('application_name') , session name not null default session_user , sql_id uuid not null default sql_id(current_query()) , command varchar(25) not null , object_type varchar(20) not null , object_name text ); --Apple-Mail=_FF0D16A6-C282-4553-93EE-7CAA78744954 Content-Transfer-Encoding: 7bit Content-Type: text/html; charset=us-ascii
--Apple-Mail=_FF0D16A6-C282-4553-93EE-7CAA78744954-- --Apple-Mail=_13E43850-F432-4FF6-99C9-22AF698233CA--