Re: Hourly backup using pg_basebackup

Why can't you stream?

Did you read "The connection must be made with a superuser or a user having
REPLICATION permissions (see Section 20.2
<http://www.postgresql.org/docs/9.3/static/role-attributes.html>), and
pg_hba.conf must explicitly permit the replication connection. The server
must also be configured with max_wal_senders
<http://www.postgresql.org/docs/9.3/static/runtime-config-replication.html#GUC-MAX-WAL-SENDERS>
set
high enough to leave at least one session available for the backup."

http://www.postgresql.org/docs/9.3/static/app-pgbasebackup.html.

Also why not do one basebackup and setup archiving through the day? You can
do a incremental in the middle of the day (if you really wanted to), may
make the recovery a bit shorter. Or you can setup a streaming slave and
stop postgres and tar/compress the data dir there. Although watch the
wal_keep_segments there as well or set an restore_command if you are
archiving.

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

> Hi all,
>
> 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.
>
> 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?
>
> Thanks in advance,
> Jay
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>