Re: Questions on recovery situations (not urgent)

Thanks, Laurenz!

We do use compression (wal_compression="on"), which looks to default to
pglz. How might I decompress them? I tried gunzip in several iterations,
but no dice.

On Wed, May 29, 2024 at 10:05 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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
>

--
Charles Schultz