Re: pg_restore Question

[Rui DeSousa <rui.desousa@xxxxxxxxxx>]

On Jun 22, 2025, at 12:57 PM, Ron Johnson <ronljohnsonjr@xxxxxxxxx> 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)

Attachment: audit.sql
Description: Binary data