Content-Type: multipart/alternative;
 boundary="------------Y05GE0H7iUfzpazjlQfz050T"
Message-ID: <3b0f2745-39dc-4b62-94ff-45641cef65c3@hot.ee>
Date: Tue, 18 Mar 2025 21:28:39 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: et
To: pgsql-general <pgsql-general@postgresql.org>
From: Andrus <kobruleht2@hot.ee>
Subject: How to convert wal log to sql commands starting from specified time
Archived-At: <https://www.postgresql.org/message-id/3b0f2745-39dc-4b62-94ff-45641cef65c3%40hot.ee>
Precedence: bulk

This is a multi-part message in MIME format.
--------------Y05GE0H7iUfzpazjlQfz050T
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------Y05GE0H7iUfzpazjlQfz050T
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>Hi!</p>
    <p>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.<br>
      <br>
      Command<br>
      <br>
       pg_recvlogical -P pgoutput -o proto_version=1 -o
      publication_names=koiktabelid_pub --create-slot --start
      --slot=naitategevust --dbname=sba --file=tulem.log<br>
      <br>
      writes modification commands to file<br>
      <br>
      Command<br>
      <br>
      pg_recvlogical --create-slot --start --slot=naitategevust
      --dbname=sba --file=-<br>
      <br>
      show modification commands in screen.<br>
      <br>
      How to specify start time and number of commands to decode as
      pg_recvlogical parameters?<br>
      <br>
      Output is too verbose, contains data types.<br>
      <br>
      How show next 100 sql commands starting from from some date, eq
      from March 18,2025 14:21:00 ?<br>
      <br>
      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.<br>
      <br>
      Replication sample in <a class="moz-txt-link-freetext" href="https://www.npgsql.org/doc/replication.html">https://www.npgsql.org/doc/replication.html</a><br>
      shows how to decode realtime replication messages for single
      table.<br>
      <br>
      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?<br>
    </p>
    <p>Posted also in</p>
    <p><a class="moz-txt-link-freetext" href="https://stackoverflow.com/questions/79518438/how-to-convert-wal-log-to-sql-commands-from-given-time">https://stackoverflow.com/questions/79518438/how-to-convert-wal-log-to-sql-commands-from-given-time</a><br>
    </p>
    <p>Andrus<br>
    </p>
  </body>
</html>

--------------Y05GE0H7iUfzpazjlQfz050T--