Horton

Thread

Commits

  1. doc: Improve {archive,restore}_command for compressed logs

  1. incoorect restore_command

    The Post Office <noreply@postgresql.org> — 2021-02-22T07:36:28Z

    The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/12/continuous-archiving.html
Description:

Hi everyone,

I've been trying out saving and restoring compressed archive logs. The
restore_command stated in the docs at "25.3.6.2. Compressed Archive Logs"
('gunzip < /mnt/server/archivedir/%f > %p') did not work for me, because the
archive_command ('gzip < %p > /var/lib/pgsql/archive/%f') alters the
filename to %f.gz
I had to change the restore_command to 'gunzip <
/mnt/server/archivedir/%f.gz > %p'.

Phil

  2. Re: incoorect restore_command

    Michael Paquier <michael@paquier.xyz> — 2021-02-24T05:49:14Z

    On Mon, Feb 22, 2021 at 07:36:28AM +0000, PG Doc comments form wrote:
> I've been trying out saving and restoring compressed archive logs. The
> restore_command stated in the docs at "25.3.6.2. Compressed Archive Logs"
> ('gunzip < /mnt/server/archivedir/%f > %p') did not work for me, because the
> archive_command ('gzip < %p > /var/lib/pgsql/archive/%f') alters the
> filename to %f.gz

On which platform please?  Using a pipe with gzip does not alter the
output file name where the data is pushed to.

> I had to change the restore_command to 'gunzip <
> /mnt/server/archivedir/%f.gz > %p'.

Now, I kind of agree that compressing a file and not using a proper
.gz extension for its name can be confusing.  So what about the
attached to tweak both archive_command and restore_command in this
section of the docs?
--
Michael

  3. Re: incoorect restore_command

    Philipp Gramzow <phil@philphonic.de> — 2021-02-24T06:24:17Z

    Am 24.02.2021 um 06:49 schrieb Michael Paquier:
> On Mon, Feb 22, 2021 at 07:36:28AM +0000, PG Doc comments form wrote:
>> I've been trying out saving and restoring compressed archive logs. The
>> restore_command stated in the docs at "25.3.6.2. Compressed Archive Logs"
>> ('gunzip < /mnt/server/archivedir/%f > %p') did not work for me, because the
>> archive_command ('gzip < %p > /var/lib/pgsql/archive/%f') alters the
>> filename to %f.gz
> On which platform please?  Using a pipe with gzip does not alter the
> output file name where the data is pushed to.
We're on RHEL 7. Thanks for pointing out my mistake, someone altered our 
archive_command to use .gz extensions without me noticing it. Please 
excuse the confusion.
>> I had to change the restore_command to 'gunzip <
>> /mnt/server/archivedir/%f.gz > %p'.
> Now, I kind of agree that compressing a file and not using a proper
> .gz extension for its name can be confusing.  So what about the
> attached to tweak both archive_command and restore_command in this
> section of the docs?
> --
> Michael
I agree, using a proper extension would be more straightforward. I'm 
sure that's the reason why someone changed our archive_command.

Phil

  4. Re: incoorect restore_command

    Michael Paquier <michael@paquier.xyz> — 2021-02-24T07:52:25Z

    On Wed, Feb 24, 2021 at 07:24:17AM +0100, Philipp Gramzow wrote:
> I agree, using a proper extension would be more straightforward. I'm sure
> that's the reason why someone changed our archive_command.

Okay, let's do so in the docs then.  Others may have comments to
offer, so I'll first wait a bit before applying my suggestion from
upthread.
--
Michael

  5. Re: incoorect restore_command

    Fujii Masao <masao.fujii@oss.nttdata.com> — 2021-02-24T11:15:59Z

    
On 2021/02/24 16:52, Michael Paquier wrote:
> On Wed, Feb 24, 2021 at 07:24:17AM +0100, Philipp Gramzow wrote:
>> I agree, using a proper extension would be more straightforward. I'm sure
>> that's the reason why someone changed our archive_command.
> 
> Okay, let's do so in the docs then.  Others may have comments to
> offer, so I'll first wait a bit before applying my suggestion from
> upthread.

I agree with this change.

But I have one question; why do those commands use different
archive directories? Isn't it better to use the same one?

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

  6. Re: incoorect restore_command

    Michael Paquier <michael@paquier.xyz> — 2021-02-25T06:54:54Z

    On Wed, Feb 24, 2021 at 08:15:59PM +0900, Fujii Masao wrote:
> But I have one question; why do those commands use different
> archive directories? Isn't it better to use the same one?

storage.sgml uses /var/lib/pgsql/data for the location of the data,
and the archive path is a mix between /mnt/server/archivedir/ and
/var/lib/pgsql/archive/.  However, the former is used for
pg_archivecleanup and in postgresql.conf.sample, so why not just using
/mnt/server/archivedir/ in backup.sgml?  Please see the attached.
--
Michael

  7. Re: incoorect restore_command

    Fujii Masao <masao.fujii@oss.nttdata.com> — 2021-02-25T09:03:57Z

    
On 2021/02/25 15:54, Michael Paquier wrote:
> On Wed, Feb 24, 2021 at 08:15:59PM +0900, Fujii Masao wrote:
>> But I have one question; why do those commands use different
>> archive directories? Isn't it better to use the same one?
> 
> storage.sgml uses /var/lib/pgsql/data for the location of the data,
> and the archive path is a mix between /mnt/server/archivedir/ and
> /var/lib/pgsql/archive/.  However, the former is used for
> pg_archivecleanup and in postgresql.conf.sample, so why not just using
> /mnt/server/archivedir/ in backup.sgml?  Please see the attached.

-archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /var/lib/pgsql/archive/%f &amp;&amp; cp %p /var/lib/pgsql/archive/%f)'
+archive_command = 'test ! -f /var/lib/pgsql/backup_in_progress || (test ! -f /mnt/server/archivedir/%f &amp;&amp; cp %p /mnt/server/archivedir/%f)'

Regarding the section "Standalone Hot Backups", all the directories and
file seem to be placed under /var/lib/pgsql, so at least for me it looks a bit
strange to change only the path of archive directory. So I don't think that
we need to do this change.


-tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
+tar -rf /var/lib/pgsql/backup.tar /mnt/server/archivedir/

Same as above.


-archive_command = 'gzip &lt; %p &gt; /var/lib/pgsql/archive/%f'
+archive_command = 'gzip &lt; %p &gt; /mnt/server/archivedir/%f.gz'
  </programlisting>
        You will then need to use <application>gunzip</application> during recovery:
  <programlisting>
-restore_command = 'gunzip &lt; /mnt/server/archivedir/%f &gt; %p'
+restore_command = 'gunzip &lt; /mnt/server/archivedir/%f.gz &gt; %p'

LGTM. Thanks for the patch!

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION

  8. Re: incoorect restore_command

    Michael Paquier <michael@paquier.xyz> — 2021-02-26T05:42:03Z

    On Thu, Feb 25, 2021 at 06:03:57PM +0900, Fujii Masao wrote:
> Regarding the section "Standalone Hot Backups", all the directories and
> file seem to be placed under /var/lib/pgsql, so at least for me it looks a bit
> strange to change only the path of archive directory. So I don't think that
> we need to do this change.
>
> -tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
> +tar -rf /var/lib/pgsql/backup.tar /mnt/server/archivedir/
> 
> Same as above.

Okay.  I found the thing a bit inconsistent while looking at the whole
picture.  Anyway, dropped those two parts.

> -archive_command = 'gzip &lt; %p &gt; /var/lib/pgsql/archive/%f'
> +archive_command = 'gzip &lt; %p &gt; /mnt/server/archivedir/%f.gz'
>  </programlisting>
>        You will then need to use <application>gunzip</application> during recovery:
>  <programlisting>
> -restore_command = 'gunzip &lt; /mnt/server/archivedir/%f &gt; %p'
> +restore_command = 'gunzip &lt; /mnt/server/archivedir/%f.gz &gt; %p'
> 
> LGTM. Thanks for the patch!

Thanks for the review.  I have applied only this part, then.
--
Michael

  9. Re: incoorect restore_command

    Stephen Frost <sfrost@snowman.net> — 2021-02-26T16:54:18Z

    Greetings,

* Philipp Gramzow (phil@philphonic.de) wrote:
> Am 24.02.2021 um 06:49 schrieb Michael Paquier:
> >On Mon, Feb 22, 2021 at 07:36:28AM +0000, PG Doc comments form wrote:
> >>I've been trying out saving and restoring compressed archive logs. The
> >>restore_command stated in the docs at "25.3.6.2. Compressed Archive Logs"
> >>('gunzip < /mnt/server/archivedir/%f > %p') did not work for me, because the
> >>archive_command ('gzip < %p > /var/lib/pgsql/archive/%f') alters the
> >>filename to %f.gz
> >On which platform please?  Using a pipe with gzip does not alter the
> >output file name where the data is pushed to.
> We're on RHEL 7. Thanks for pointing out my mistake, someone altered our
> archive_command to use .gz extensions without me noticing it. Please excuse
> the confusion.

Note that the simple commands in the documentation are really just to
give you an example of what an archive command would look like- they
shouldn't be used verbatim since they don't provide any guarantees that
the resulting compressed file has actually been written out to disk
(something that an archive command really should provide the guarantee
of- otherwise a crash will result in WAL files likely going missing and
therefore you'll lose the ability to do PITR).

Thanks,

Stephen