From: Laurenz Albe <laurenz.albe@cybertec.at>
To: sacrophyte@gmail.com
To: pgsql-admin@lists.postgresql.org
Subject: Re: Questions on recovery situations (not urgent)
Date: Wed, 29 May 2024 17:05:54 +0200
Message-ID: <e903a39dc91f29d9e3eb3fb8bd50c455a21df0d6.camel@cybertec.at> (raw)
In-Reply-To: <CAPZQniX-EiipW_fDeVERReLky3w+kui85qSp-stHrSj_qZOAcg@mail.gmail.com>
References: <CAPZQniX-EiipW_fDeVERReLky3w+kui85qSp-stHrSj_qZOAcg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, sacrophyte@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Questions on recovery situations (not urgent)
In-Reply-To: <e903a39dc91f29d9e3eb3fb8bd50c455a21df0d6.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox