How to convert wal log to sql commands starting from specified time

From: Andrus <kobruleht2(at)hot(dot)ee>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: How to convert wal log to sql commands starting from specified time
Date: 2025-03-18 19:28:39
Message-ID: 3b0f2745-39dc-4b62-94ff-45641cef65c3@hot.ee
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Postgres 14 hot standby server server is running in Debian Linux under
WSL. It saves wal log from master to pg_wal directory. wal_keep size is
100GB.

Command

 pg_recvlogical -P pgoutput -o proto_version=1 -o
publication_names=koiktabelid_pub --create-slot --start
--slot=naitategevust --dbname=sba --file=tulem.log

writes modification commands to file

Command

pg_recvlogical --create-slot --start --slot=naitategevust --dbname=sba
--file=-

show modification commands in screen.

How to specify start time and number of commands to decode as
pg_recvlogical parameters?

Output is too verbose, contains data types.

How show next 100 sql commands starting from from some date, eq from
March 18,2025 14:21:00 ?

If there is no ready solution, I can create ASP.NET MVC 9 controller for
this. Controller should probably ask wal contents from server starting
at this time using replication protocol or directly read wal files from
pg_wal directory and decode them.

Replication sample in https://www.npgsql.org/doc/replication.html
shows how to decode realtime replication messages for single table.

How to specify start time from which replication commands are retured?
Or is there some exisising wal decoder in C# or as command-line utility?

Posted also in

https://stackoverflow.com/questions/79518438/how-to-convert-wal-log-to-sql-commands-from-given-time

Andrus

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2025-03-18 23:18:03 Re: query_id: jumble names of temp tables for better pg_stat_statement UX
Previous Message Adrian Klaver 2025-03-18 18:41:39 Re: After upgrading libpq, the same function(PQftype) call returns a different OID