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
