Re: Backup taking long time !!!

On 1/22/17 11:32 AM, Stephen Frost wrote:
> The 1-second window concern is regarding the validity of a subsequent
> incremental backup.

BTW, there's a simpler scenario here:

Postgres touches file.
rsync notices file has different timestamp, starts copying.
Postgres touches file again.

If those 3 steps happen in the same second, you now have an invalid
backup. There's probably other scenarios as well.

In short, if you're using rsync, it's *critical* that you give it the
--checksum option, which tells rsync to ignore file size and timestamp.

>>>> my latter explanation is: use pg_basebackup, it will do it automatically
>>>> for you.
>>> Yes, if you are unsure about how to perform a safe backup properly,
>>> using pg_basebackup or one of the existing backup tools is, by far, the
>>> best approach. Attempting to roll your own backup system based on rsync
>>> is not something I am comfortable recommending any more because it is
>>> *not* simple to do correctly.
>> OK, that is fine, and actually we are using that.
> You must be sure to use one of the methods with pg_basebackup that keeps
> all of the WAL created during the full backup. That would be one of:
> pg_basebackup -x, pg_basebackup -X stream, or pg_basebackup +
> pg_receivexlog.
>
>> the reason why i explain about start_backup and stop_backup is to give a
>> gradual understand, and hoping that people will get the mechanism in the
>> back understandable.
> I'm more than happy to have people explaining about
> pg_start/stop_backup, but I do have an issue when the explanation is
> incorrect and could cause a user to use a backup method which will
> result in an invalid backup.

The other *critical* thing with PITR backups: you must test EVERY backup
that you take. No test == no backup. There's far, far too many things
that can go wrong, especially if you're rolling your own tool.

The complexities around PITR are why I always recommend also using
pg_dump on a periodic (usually weekly) basis as part of your full DR
strategy. You'll probably never use the pg_dump backups, but (in most
cases) they're a really cheap insurance policy.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)