I'm using PostgreSQL 14.2 and am trying to replicate the inserts/updates/deletes for a database. Using
pg_waldump --rmgr=Transaction 000000010000000000000001
returns all transactions and I can see my commits, for example:
rmgr: Transaction len (rec/tot): 34/ 34, tx: 742, lsn: 0/0171DF08, prev 0/0171DE70, desc: ABORT 2022-10-05 08:50:55.236768 UTC
rmgr: Transaction len (rec/tot): 34/ 34, tx: 743, lsn: 0/0171E048, prev 0/0171DFA8, desc: COMMIT 2022-10-05 08:51:07.259488 UTC
I would like to access the contents of this commit, for example INSERT INTO table1 VALUES (5, 6);. Is there a way to access this?
The WAL level is set to replica. Or is the only way to do this by setting the level to logical and then using one of the logical decoding plugins, such as wal2json? If the level needs to be set to logical, how much would that increase the space of WALs?
WAL does not contain logical information (SQL statements), but which bytes in which block of which table is modified. You need logical decoding. How much more WAL you get when you set wal_level = logical depend on your workload and your table definitions. You'll have to try it.