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), and pg_hba.conf must explicitly permit the replication connection. The server must also be configured with 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
