pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestions on recovery situations (not urgent)
6+ messages / 3 participants
[nested] [flat]
* Questions on recovery situations (not urgent)
@ 2024-05-29 12:27 Charles Schultz <sacrophyte@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Charles Schultz @ 2024-05-29 12:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Good day!
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.
2. As I cannot dump the WALs, how do I determine the LSN or XID of a
transaction in the past?
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?
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.
Thanks for your time and attention!
--
Charles Schultz
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions on recovery situations (not urgent)
@ 2024-05-29 15:05 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Charles Schultz <sacrophyte@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Laurenz Albe @ 2024-05-29 15:05 UTC (permalink / raw)
To: sacrophyte@gmail.com; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions on recovery situations (not urgent)
@ 2024-06-04 01:38 vignesh kumar <vigneshkumar.venugopal@outlook.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 6+ messages in thread
From: vignesh kumar @ 2024-06-04 01:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; sacrophyte@gmail.com <sacrophyte@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
Get Outlook for Android<https://aka.ms/AAb9ysg;
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Wednesday, May 29, 2024 8:35:54 PM
To: sacrophyte@gmail.com <sacrophyte@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions on recovery situations (not urgent)
@ 2024-06-10 18:41 Charles Schultz <sacrophyte@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 1 reply; 6+ messages in thread
From: Charles Schultz @ 2024-06-10 18:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
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.albe@cybertec.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions on recovery situations (not urgent)
@ 2024-06-11 01:37 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Charles Schultz <sacrophyte@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Laurenz Albe @ 2024-06-11 01:37 UTC (permalink / raw)
To: sacrophyte@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
On Mon, 2024-06-10 at 13:41 -0500, Charles Schultz wrote:
> On Wed, May 29, 2024 at 10:05 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > Did you compress the files? If yes, you have to uncompress them before feeding them
> > to "pg_waldump".
>
> 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.
That will compress full-page images *within* the WAL segment, but not the file
itself. The file should still have the regular size.
Perhaps your archiver truncated the file somehow.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Questions on recovery situations (not urgent)
@ 2024-06-11 11:22 Charles Schultz <sacrophyte@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 6+ messages in thread
From: Charles Schultz @ 2024-06-11 11:22 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
It turns out that we are using TDE (which I knew, but I assumed pg_waldump
was decrypting - bad assumption). It took a few tries, but I finally
figured out how to get pg_waldump to work with the decrypt flags.
Thanks for the feedback!
On Mon, Jun 10, 2024 at 8:37 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Mon, 2024-06-10 at 13:41 -0500, Charles Schultz wrote:
> > On Wed, May 29, 2024 at 10:05 AM Laurenz Albe <laurenz.albe@cybertec.at>
> wrote:
> > > Did you compress the files? If yes, you have to uncompress them
> before feeding them
> > > to "pg_waldump".
> >
> > 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.
>
> That will compress full-page images *within* the WAL segment, but not the
> file
> itself. The file should still have the regular size.
>
> Perhaps your archiver truncated the file somehow.
>
> Yours,
> Laurenz Albe
>
--
Charles Schultz
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-11 11:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-29 12:27 Questions on recovery situations (not urgent) Charles Schultz <sacrophyte@gmail.com>
2024-05-29 15:05 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-06-04 01:38 ` vignesh kumar <vigneshkumar.venugopal@outlook.com>
2024-06-10 18:41 ` Charles Schultz <sacrophyte@gmail.com>
2024-06-11 01:37 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-06-11 11:22 ` Charles Schultz <sacrophyte@gmail.com>
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