So your postgres is not able to understand the wal segment size, as it's not able to compare between actual or expected. In Simpler terms postgres wal segment size is not set properly. Check for your pg_controldata and check for wal byte size
and then check for wal_segment_size from that of your postgresql.conf
Key is your data folder pg_version and the binary version for pg_waldump should be one and the same
From: Laurenz Albe <laurenz.albe@xxxxxxxxxxx>
Sent: Wednesday, May 29, 2024 8:35:54 PM
To: sacrophyte@xxxxxxxxx <sacrophyte@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Questions on recovery situations (not urgent)
Sent: Wednesday, May 29, 2024 8:35:54 PM
To: sacrophyte@xxxxxxxxx <sacrophyte@xxxxxxxxx>; pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Questions on recovery situations (not urgent)
On Wed, 2024-05-29 at 07:27 -0500, Charles Schultz wrote:
> We have been learning more about postgres recovery, and I have come across a couple
> questions that google has not satisfactorily answered for me.
>
> 1. pg_waldump refuses to dump a WAL saying "fatal: WAL segment size must be a power
> of two between 1 MB and 1 GB", even though I have successfully used the WALs for
> PITR. What's the magic trick to get around this fatal error? I tried using a key
> unwrap function (same as in postgresql.conf) to no avail.
That is unlikely, because WAL segments are always within these limits (and have a fixed
size that is a power of two.
Did you compress the files? If yes, you have to uncompress them before feeding them
to "pg_waldump".
> 2. As I cannot dump the WALs, how do I determine the LSN or XID of a transaction in
> the past?
That is difficult, even with "pg_waldump". WAL does not contain SQL statements.
At best, you can identify which files were modified and what was done with them,
so at best you can guess that a table was dropped, because a couple of files get
deleted. But that could also have been a VACUUM (FULL).
Essentially, you have to guess a good point in time to restore to.
> 3. When restoring forward, I have found recovery_target_time does not work very
> well (maybe lack of granularity?), and recovery_target_lsn is better, but
> pg_current_wal_lsn() only gives me the LSN before a transaction. Is there a way
> to get the XID of a specific transaction?
"recovery_target_time" works just fine.
The function pg_current_xact_id() gives you the current transaction ID.
If you know ahead of time that you may want to restore to a certain point,
you can use the function pg_create_restore_point() to define a restore point
to which you can restore using the parameter "recovery_target_name".
> 4. How would I get the OID of a database and a table when the database is down?
> I wish to map the filesystem names to database objects after a crash.
> Is there a tool/app that can parse pg datafiles? I have to assume that if
> there is not already one, it is not too hard to write such a tool if one
> has experience with PG file headers and reading the opensource code.
I am not aware of such a tool, other than "postgres".
You can start the server and use "oid2name".
Yours,
Laurenz Albe
> We have been learning more about postgres recovery, and I have come across a couple
> questions that google has not satisfactorily answered for me.
>
> 1. pg_waldump refuses to dump a WAL saying "fatal: WAL segment size must be a power
> of two between 1 MB and 1 GB", even though I have successfully used the WALs for
> PITR. What's the magic trick to get around this fatal error? I tried using a key
> unwrap function (same as in postgresql.conf) to no avail.
That is unlikely, because WAL segments are always within these limits (and have a fixed
size that is a power of two.
Did you compress the files? If yes, you have to uncompress them before feeding them
to "pg_waldump".
> 2. As I cannot dump the WALs, how do I determine the LSN or XID of a transaction in
> the past?
That is difficult, even with "pg_waldump". WAL does not contain SQL statements.
At best, you can identify which files were modified and what was done with them,
so at best you can guess that a table was dropped, because a couple of files get
deleted. But that could also have been a VACUUM (FULL).
Essentially, you have to guess a good point in time to restore to.
> 3. When restoring forward, I have found recovery_target_time does not work very
> well (maybe lack of granularity?), and recovery_target_lsn is better, but
> pg_current_wal_lsn() only gives me the LSN before a transaction. Is there a way
> to get the XID of a specific transaction?
"recovery_target_time" works just fine.
The function pg_current_xact_id() gives you the current transaction ID.
If you know ahead of time that you may want to restore to a certain point,
you can use the function pg_create_restore_point() to define a restore point
to which you can restore using the parameter "recovery_target_name".
> 4. How would I get the OID of a database and a table when the database is down?
> I wish to map the filesystem names to database objects after a crash.
> Is there a tool/app that can parse pg datafiles? I have to assume that if
> there is not already one, it is not too hard to write such a tool if one
> has experience with PG file headers and reading the opensource code.
I am not aware of such a tool, other than "postgres".
You can start the server and use "oid2name".
Yours,
Laurenz Albe
