How to recover from compressed wal archieve in windows

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

How to recover from compressed wal archieve in windows

Andrus Moor
In windows 10

pg_receivewal --directory="d:\wallog" --verbose --compress=9

is used to archieve WAL.

This creates .gz files

For restore

restore_command='copy "D:\\wallog\\%f" "%p"'

is used.
Restore shows "file not found" errors in console. Thi sis probably because %f argument is WAL file name without extension.
How to use compressed WAL files for WAL archieve and restore in windows ?

Andrus.



Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Adrian Klaver-4
On 5/20/20 8:08 AM, Andrus wrote:

> In windows 10
> pg_receivewal --directory="d:\wallog" --verbose --compress=9
>
> is used to archieve WAL.
>
> This creates .gz files
>
> For restore
>
> restore_command='copy "D:\\wallog\\%f" "%p"'

I'm guessing:

restore_command='copy "D:\\wallog\\%f.gz" "%p"'

will get you the file.

The next problem is that I'm pretty sure a WAL file with *.gz extension
will not be able to be processed directly by the server. So you are
going to have to uncompress it at some point before it gets restored.


> is used.
> Restore shows "file not found" errors in console. Thi sis probably
> because %f argument is WAL file name without extension.
> How to use compressed WAL files for WAL archieve and restore in windows ?
>
> Andrus.
>
>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Paul Förster
Hi Andrus, hi Adrian,

see:

25.3.6.2. Compressed Archive Logs
https://www.postgresql.org/docs/12/continuous-archiving.html#BACKUP-TIPS

Hope this helps in dealing with compressed WAL files.

Cheers,
Paul


> On 20. May, 2020, at 20:36, Adrian Klaver <[hidden email]> wrote:
>
> On 5/20/20 8:08 AM, Andrus wrote:
>> In windows 10
>> pg_receivewal --directory="d:\wallog" --verbose --compress=9
>> is used to archieve WAL.
>> This creates .gz files
>> For restore
>> restore_command='copy "D:\\wallog\\%f" "%p"'
>
> I'm guessing:
>
> restore_command='copy "D:\\wallog\\%f.gz" "%p"'
>
> will get you the file.
>
> The next problem is that I'm pretty sure a WAL file with *.gz extension will not be able to be processed directly by the server. So you are going to have to uncompress it at some point before it gets restored.
>
>
>> is used.
>> Restore shows "file not found" errors in console. Thi sis probably because %f argument is WAL file name without extension.
>> How to use compressed WAL files for WAL archieve and restore in windows ?
>> Andrus.
>
>
> --
> Adrian Klaver
> [hidden email]
>
>



Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Andrus Moor
In reply to this post by Adrian Klaver-4
Hi!

>I'm guessing:
>restore_command='copy "D:\\wallog\\%f.gz" "%p"'
>will get you the file.
>The next problem is that I'm pretty sure a WAL file with *.gz extension
>will not be able to be processed directly by the server. So you are
>going to have to uncompress it at some point before it gets restored.

How to decompress it automatically in windows ?
In Linux

restore_command = 'gunzip < "archived_wal/%f" > "%p"'

maybe works.

Will wal_compression=on will produce compressed wal files to additional compression is not needed?

Andrus.


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Adrian Klaver-4
In reply to this post by Paul Förster
On 5/20/20 11:43 AM, Paul Förster wrote:
> Hi Andrus, hi Adrian,
>
> see:
>
> 25.3.6.2. Compressed Archive Logs
> https://www.postgresql.org/docs/12/continuous-archiving.html#BACKUP-TIPS
>
> Hope this helps in dealing with compressed WAL files.

Yeah, the issue would be getting a version of gunzip that works on
Windows. In the past I have had luck with:

https://www.7-zip.org/

Not sure how well it works with redirects/pipes.

>
> Cheers,
> Paul
>
>
>> On 20. May, 2020, at 20:36, Adrian Klaver <[hidden email]> wrote:
>>
>> On 5/20/20 8:08 AM, Andrus wrote:
>>> In windows 10
>>> pg_receivewal --directory="d:\wallog" --verbose --compress=9
>>> is used to archieve WAL.
>>> This creates .gz files
>>> For restore
>>> restore_command='copy "D:\\wallog\\%f" "%p"'
>>
>> I'm guessing:
>>
>> restore_command='copy "D:\\wallog\\%f.gz" "%p"'
>>
>> will get you the file.
>>
>> The next problem is that I'm pretty sure a WAL file with *.gz extension will not be able to be processed directly by the server. So you are going to have to uncompress it at some point before it gets restored.
>>
>>
>>> is used.
>>> Restore shows "file not found" errors in console. Thi sis probably because %f argument is WAL file name without extension.
>>> How to use compressed WAL files for WAL archieve and restore in windows ?
>>> Andrus.
>>
>>
>> --
>> Adrian Klaver
>> [hidden email]
>>
>>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Adrian Klaver-4
In reply to this post by Andrus Moor
On 5/20/20 12:09 PM, Andrus wrote:

> Hi!
>
>> I'm guessing:
>> restore_command='copy "D:\\wallog\\%f.gz" "%p"'
>> will get you the file.
>> The next problem is that I'm pretty sure a WAL file with *.gz
>> extension will not be able to be processed directly by the server. So
>> you are going to have to uncompress it at some point before it gets
>> restored.
>
> How to decompress it automatically in windows ?
> In Linux
> restore_command = 'gunzip < "archived_wal/%f" > "%p"'
>
> maybe works.
>
> Will wal_compression=on will produce compressed wal files to additional
> compression is not needed?

Yes. Not sure how it will play with the streaming that pg_receivewal does.

Maybe the best thing is to back up a bit and let us know what it is you
are trying to achieve?

>
> Andrus.


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Michael Paquier-2
In reply to this post by Adrian Klaver-4
On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote:
> The next problem is that I'm pretty sure a WAL file with *.gz extension will
> not be able to be processed directly by the server. So you are going to have
> to uncompress it at some point before it gets restored.

The short answer to that question is no.  The backend does not
uncompress the segment file.  What happens is that the restore command
copies the file defined by %f to the location of %p where is gets
renamed to RECOVERYXLOG, and we expect the restore command to drop a
16MB file in og_wal/.  There is a check on the size, which would fail
if the WAL segment is still compressed.  This logic is in
RestoreArchivedFile() in xlogarchive.c.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Adrian Klaver-4
On 5/20/20 6:27 PM, Michael Paquier wrote:

> On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote:
>> The next problem is that I'm pretty sure a WAL file with *.gz extension will
>> not be able to be processed directly by the server. So you are going to have
>> to uncompress it at some point before it gets restored.
>
> The short answer to that question is no.  The backend does not
> uncompress the segment file.  What happens is that the restore command
> copies the file defined by %f to the location of %p where is gets
> renamed to RECOVERYXLOG, and we expect the restore command to drop a
> 16MB file in og_wal/.  There is a check on the size, which would fail
> if the WAL segment is still compressed.  This logic is in
> RestoreArchivedFile() in xlogarchive.c.

I figured that would be the case.

So how is this handled?:

wal_compression (boolean)

     When this parameter is on, the PostgreSQL server compresses a full
page image written to WAL when full_page_writes is on or during a base
backup. A compressed page image will be decompressed during WAL replay.
The default value is off. Only superusers can change this setting.


> --
> Michael
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Kyotaro Horiguchi-4
At Wed, 20 May 2020 19:25:50 -0700, Adrian Klaver <[hidden email]> wrote in

> On 5/20/20 6:27 PM, Michael Paquier wrote:
> > On Wed, May 20, 2020 at 11:36:09AM -0700, Adrian Klaver wrote:
> >> The next problem is that I'm pretty sure a WAL file with *.gz
> >> extension will
> >> not be able to be processed directly by the server. So you are going
> >> to have
> >> to uncompress it at some point before it gets restored.
> > The short answer to that question is no.  The backend does not
> > uncompress the segment file.  What happens is that the restore command
> > copies the file defined by %f to the location of %p where is gets
> > renamed to RECOVERYXLOG, and we expect the restore command to drop a
> > 16MB file in og_wal/.  There is a check on the size, which would fail
> > if the WAL segment is still compressed.  This logic is in
> > RestoreArchivedFile() in xlogarchive.c.
>
> I figured that would be the case.
>
> So how is this handled?:
>
> wal_compression (boolean)
>
>     When this parameter is on, the PostgreSQL server compresses a full
>     page image written to WAL when full_page_writes is on or during a base
>     backup. A compressed page image will be decompressed during WAL
>     replay. The default value is off. Only superusers can change this
>     setting.

Difference from decompression by restore_command?

A WAL (segment) file is filled with multiple WAL records. The "full
page image", which is described to be compressed by the parameter, is
a part of WAL record. A WAL file is filled with maybe-compressed WAL
records and has the same size in the case where wal_compression is on.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Paul Förster
In reply to this post by Adrian Klaver-4
Hi Adrian,

> On 21. May, 2020, at 01:03, Adrian Klaver <[hidden email]> wrote:
>
> Yeah, the issue would be getting a version of gunzip that works on Windows. In the past I have had luck with:
>
> https://www.7-zip.org/
>
> Not sure how well it works with redirects/pipes.

I'm no windoze guru, so I don't know about that, but you may have a look at:

http://gnuwin32.sourceforge.net/packages/gzip.htm

Cheers,
Paul

Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Kyotaro Horiguchi-4
At Thu, 21 May 2020 06:55:41 +0200, Paul Förster <[hidden email]> wrote in

> Hi Adrian,
>
> > On 21. May, 2020, at 01:03, Adrian Klaver <[hidden email]> wrote:
> >
> > Yeah, the issue would be getting a version of gunzip that works on Windows. In the past I have had luck with:
> >
> > https://www.7-zip.org/
> >
> > Not sure how well it works with redirects/pipes.
>
> I'm no windoze guru, so I don't know about that, but you may have a look at:
>
> http://gnuwin32.sourceforge.net/packages/gzip.htm

Or cygwin or mingw?

http://www.mingw.org/

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center


Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Michael Paquier-2
In reply to this post by Kyotaro Horiguchi-4
On Thu, May 21, 2020 at 01:38:11PM +0900, Kyotaro Horiguchi wrote:
> Difference from decompression by restore_command?
>
> A WAL (segment) file is filled with multiple WAL records. The "full
> page image", which is described to be compressed by the parameter, is
> a part of WAL record. A WAL file is filled with maybe-compressed WAL
> records and has the same size in the case where wal_compression is on.

Well, to be more precise, one or more full page images may be included
in a single record, and they may, or may actually not, have up to two
level of "compression" applied to them:
- A full page image may be logged without the hole in the middle of
the page, reducing its actually size logged.
- It would be reduced in size further more if wal_compression is
enabled.

So you actually have four possible cases for a full-page image:
- Logged with its full 8kB data, with its hole included.
- Logged without its hole.
- Logged with its 8kB data and its hole, compressed with pglz.
- Logged without its hole, and compressed with pglz.

When a page is logged, we register in the record if the hole of the
logged full-page image is included or not, and if the page is
compressed with pglz or not.  Then WAL replay looks at those flags,
and determines what to do by itself when the page image is needed.
--
Michael

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: How to recover from compressed wal archieve in windows

Andrus Moor
In reply to this post by Adrian Klaver-4
Hi!

>> Will wal_compression=on will produce compressed wal files to additional
>> compression is not needed?
>Yes. Not sure how it will play with the streaming that pg_receivewal does.

I looked into WAL files created with wal_compression=on in pg_wal directory.
They still contain lot of ascii data from database which is not packed.
It looks like file WAL files content is not compressed.

>Maybe the best thing is to back up a bit and let us know what it is you
>are trying to achieve?

Trying to add PITR possibility from hot standby server which uses async binary streaming replication without named slot.
Server creates 1 GB WAL files per day.
pg_receivewal with --compress=9 produces 3..5MB WAL files instead of 16MB
Trying to save disk space.
I set windows directory attribute to "compressed" in wal archieve directory. Transparent compression is then applied by OS.

However when WAL files are copied to other device, copy is perfomed probably in uncompressed form in windows.
How to set compressed attribute to pg_wal directory in Linux ext4 file system ?

Andrus.