On Tue, Jul 16, 2013 at 11:10 PM, David B Harris <dbharris@eelf.ddts.net> wrote:
> Good afternoon all,
>
> I'm trying to use pg_start_backup() and pg_stop_backup() to create
> point-in-time backups. More specifically, I'm trying to use filesystem
> tools (notably rsync or an rsync-like tool) since the production machine
> is on the other end of a (narrow, expensive) pipe. pg_dump is too
> expensive (both in time and bandwidth); the gzip-compressed database
> dump is about 30GB.
>
> These backups might be maintained/used by others who are only somewhat
> familiar with Linux and PostgreSQL, so I'm trying to keep them as simple
> as possible.
>
> Now if I read it right (and I'm concerned I'm not), then according to
> section 24.3 of the documentation (Continuous Archiving and
> Point-in-Time Recovery (PITR)), the backup procedure needs to be as
> follows:
>
> 1. Issue pg_start_backup('label')
> 2. Perform rsync of cluster directory
> 3. Issue pg_stop_backup()
> 4. Copy all logs from start of pg_start_backup() through to when
> pg_stop_backup() finished (using the backup history file, I
> guess, which I haven't actually been able to find yet :)
I assume that you use WAL archiving, so why would you do that manually
as I understand it is the case at step #4? When using pg_stop_backup
it is even ensured that all the necessary WAL files are ready to be
archived. You still need for the file to be actually archived before
starting the recovery though
>
> So far enough. Before I really grasped that, though, I was testing with
> just steps #1 through #3. And everything always seemed to work fine.
> Ultimately I tested it dozens of times. With various loads on the
> production server (certainly at times with more than enough writes to
> max out the number of allowed log segments). And the restore never
> failed (no errors at least, and spot-checking the data indicated that
> everything appeared to be in place).
>
> Am I on drugs? Just crazy lucky?
I don't believe so. Or we both are and we live together in an imaginary world.
> Is #4 actually necessary? (I can
> imagine ways of writing to the cluster files which might make it
> unnecessary, maybe somebody implemented that and didn't update the
> documentation?)
Not really, archiving would do the rest for you, and it is managed by
the server. Just be sure to set up restore_command in recovery.conf
when starting a node from the backup you took.
--
Michael