How to recover from compressed wal archieve in windows

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
adrian.klaver@aklaver.com

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

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.

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 <adrian.klaver@aklaver.com> 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
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

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
adrian.klaver@aklaver.com

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

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
adrian.klaver@aklaver.com

At Wed, 20 May 2020 19:25:50 -0700, Adrian Klaver <adrian.klaver@aklaver.com> 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

Hi Adrian,

On 21. May, 2020, at 01:03, Adrian Klaver <adrian.klaver@aklaver.com> 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

At Thu, 21 May 2020 06:55:41 +0200, Paul Förster <paul.foerster@gmail.com> wrote in

Hi Adrian,

On 21. May, 2020, at 01:03, Adrian Klaver <adrian.klaver@aklaver.com> 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

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

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.