Re: How to recover from compressed wal archieve in windows
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.
Re: How to recover from compressed wal archieve in windows
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 Description: PGP signature
Re: How to recover from compressed wal archieve in windows
At Thu, 21 May 2020 06:55:41 +0200, Paul Förster wrote in > Hi Adrian, > > > On 21. May, 2020, at 01:03, Adrian Klaver 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
Re: How to recover from compressed wal archieve in windows
Hi Adrian, > On 21. May, 2020, at 01:03, Adrian Klaver 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
Re: How to recover from compressed wal archieve in windows
At Wed, 20 May 2020 19:25:50 -0700, Adrian Klaver 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
Re: How to recover from compressed wal archieve in windows
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.kla...@aklaver.com
Re: How to recover from compressed wal archieve in windows
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 Description: PGP signature
Re: How to recover from compressed wal archieve in windows
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.kla...@aklaver.com
Re: How to recover from compressed wal archieve in windows
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 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.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.com
Re: How to recover from compressed wal archieve in windows
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.
Re: How to recover from compressed wal archieve in windows
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 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.kla...@aklaver.com > >
Re: 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.kla...@aklaver.com
How to recover from compressed wal archieve in windows
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.
