Re: pg_restore Question

> On Jun 22, 2025, at 12:57 PM, Ron Johnson <ronljohnsonjr(at)gmail(dot)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’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)