From: Khusro Jaleel <mailing-lists@kerneljack.com>
To: pgsql-admin@postgresql.org
Subject: Backing up a replication set every 30 mins
Date: Wed, 15 Feb 2012 12:42:14 +0000
Message-ID: <4F3BA826.9000708@kerneljack.com> (raw)
Hi,
I have setup replication on Postgresql 9.1 in a simple scenario using a
master and a slave. I now need a reliable way (and preferably fast way)
to do backups of the database.
I tried using 'archive_command' on the master and created a script that
simply bzips files and copies them to a directory, but after 1 day that
directory was so huge that even a simple 'rm' complained that the
'argument list is too long'.
I can see that even though I have specified 30 minutes in the
postgresql.conf, a new archive file is written every *minute* resulting
in way too many files in the archive directory. This happens even when
*nobody* is using the DB, or with just very light use. However I have
noticed that sometimes this *does* work correctly and an archive is only
written out every 30 mins.
Is there a way to force this to just write to the archive directory
*only* every 30 mins as I only really need backups to be current to the
last 30 mins or so?
===============
checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 30min # range 30s-1h
archive_timeout = 30 # force a logfile segment switch after this
wal_keep_segments = 14375
===============
If this is NOT the right way to backup the database, then can you please
suggest an alternative method? I could write a script that calls
'pg_start_backup' and 'pg_stop_backup' with an rsync in between to a
backup server and run it every 30 mins. I am thinking that this would
work, even if people are busy using the DB (reads/writes)?
I want to avoid using pg_dump as I think that would require I pause
writes to the DB until the backup is finished?
Any help appreciated, thanks,
Khusro
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mailing-lists@kerneljack.com
Subject: Re: Backing up a replication set every 30 mins
In-Reply-To: <4F3BA826.9000708@kerneljack.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox