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
