Thread: pg_waldump
Hi,
Is it possible to visualize the DDL with the pg_waldump tool. I created a postgres user but I cannot find the creation command in the wals
Thanks for help
Fabrice
Hello It is something like rmgr: Heap len (rec/tot): 143/ 143, tx: 748, lsn: 0/01530AF0, prev 0/01530AB8, desc: INSERT off: 17, flags:0x00, blkref #0: rel 1664/0/1260 blk 0 just insertion into pg_authid (oid=1260) regards, Sergei
On Tue, 19 Dec 2023, 12:27 Fabrice Chapuis, <fabrice636861@gmail.com> wrote: > > Hi, > Is it possible to visualize the DDL with the pg_waldump tool. I created a postgres user but I cannot find the creationcommand in the wals Not really, no. PostgreSQL does not log DDL or DML as such in WAL. Essentially all catalog updates are logged only as changes on a certain page in some file: a new user getting inserted would be approximately "Insert tuple [user's pg_role row data] on page X in file [the file corresponding to the pg_role table]". You could likely derive most DDL commands from Heap/Insert, Heap/Delete, and Heap/Update records (after cross-referencing the database's relfilemap), as most DDL is "just" a lot of in-memory operations plus some record insertions/updates/deletes in catalog tables. You'd also need to keep track of any relfilemap changes while processing the WAL, as VACUUM FULL on the catalog tables would change the file numbering of catalog tables... Kind regards, Matthias van de Meent Neon (https://neon.tech)
Ok thanks for all these precisions
Regards
Fabrice
On Tue, Dec 19, 2023 at 2:00 PM Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
On Tue, 19 Dec 2023, 12:27 Fabrice Chapuis, <fabrice636861@gmail.com> wrote:
>
> Hi,
> Is it possible to visualize the DDL with the pg_waldump tool. I created a postgres user but I cannot find the creation command in the wals
Not really, no. PostgreSQL does not log DDL or DML as such in WAL.
Essentially all catalog updates are logged only as changes on a
certain page in some file: a new user getting inserted would be
approximately "Insert tuple [user's pg_role row data] on page X in
file [the file corresponding to the pg_role table]".
You could likely derive most DDL commands from Heap/Insert,
Heap/Delete, and Heap/Update records (after cross-referencing the
database's relfilemap), as most DDL is "just" a lot of in-memory
operations plus some record insertions/updates/deletes in catalog
tables. You'd also need to keep track of any relfilemap changes while
processing the WAL, as VACUUM FULL on the catalog tables would change
the file numbering of catalog tables...
Kind regards,
Matthias van de Meent
Neon (https://neon.tech)