Re: Hourly backup using pg_basebackup

On Fri, Feb 6, 2015 at 4:53 PM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> We have a python script called by cron on an hourly basis to back up our
> production database. Currently, the script invokes pg_dump and takes more
> than hour to complete. Hence the script looks to see if it's already
> running and exits if so. I want to change the script so it uses
> pg_basebackup instead since that's so much faster.
>
>
Have you considered using incremental backup (continuous archiving) instead
of a such small backup window?

See [1]

My problem is, however, that while I'd like to just have it build a
> tarball, maybe compressed, I can't use a "-X s" option for the wal
> segments. I think I understand why I can't use the streaming option with a
> "-Ft" specified. I'm just concerned about the docs saying that the backup
> may have problems with fetch as a wal segment may have expired. Manually
> testing is showing that the Db needs about 11 minutes to backup with
> pg_basebackup, and our wal_keep_segments setting is 6. This said, an
> hour's worth of wal segments should be available, but the six that were
> there at the beginning of the backup are not the same six there at the end.
> I don't think this is really a problem, but I'd like to get it confirmed.
> Wouldn't the backup actually have to take more than hour for this to be an
> issue?
>

If you use archiving [1], you don't need to worry about saving the segments
withing the backup, just let it be done through archive_command. Isn't that
an option for you? If don't, why?

[1] http://www.postgresql.org/docs/current/static/continuous-archiving.html

Best regards,
--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres