Thread

1. pg_waldump

   Fabrice Chapuis <fabrice636861@gmail.com> — 2023-12-19T11:26:54Z

   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
   

2. Re:pg_waldump

   Sergei Kornilov <sk@zsrv.org> — 2023-12-19T12:07:07Z

   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
   
   
   
   

3. Re: pg_waldump

   Matthias van de Meent <boekewurm+postgres@gmail.com> — 2023-12-19T13:00:07Z

   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)
   
   
   
   

4. Re: pg_waldump

   Fabrice Chapuis <fabrice636861@gmail.com> — 2023-12-19T15:11:54Z

   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)
   >