public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rui DeSousa <[email protected]>
To: Ron Johnson <[email protected]>
Cc: Pgsql-admin <[email protected]>
Subject: Re: pg_restore Question
Date: Sun, 22 Jun 2025 13:51:06 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CANzqJaAkrcTxKfPu_Ui92-ovDwpMf-wSf3n_25+Rf9P+4ZSoDA@mail.gmail.com>
References: <CA+wokJ_Qz9DYU39G3ABD6Qu95LjCT3FboYE6aW3yzcxMxSRwnw@mail.gmail.com>
<CADqJLbWH4xgTGOWG+FMCEkJvyswsEa3g__keRXaAXXTRiaiY7A@mail.gmail.com>
<CANzqJaCxnDC=Nzu2cNUCwHhkz3X3-txrh7L6V9OzFbb1Sqp_Sg@mail.gmail.com>
<CA+wokJ_11i8tp-oDMkt_FRcx8xJV7ZOy2QWW80-fWk_bdGHQgw@mail.gmail.com>
<CANzqJaAkrcTxKfPu_Ui92-ovDwpMf-wSf3n_25+Rf9P+4ZSoDA@mail.gmail.com>
> On Jun 22, 2025, at 12:57 PM, Ron Johnson <[email protected]> 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’s a script that will achieve just that; including when the table is dropped.
Regards,
Rui
demo=# \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=# create table xo(i int);
CREATE TABLE
demo=# alter table xo add column d text;
ALTER TABLE
demo=# drop table xo;
DROP TABLE
demo=# 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=# 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)
Attachments:
[application/octet-stream] audit.sql (2.6K, 3-audit.sql)
download
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: pg_restore Question
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox