[CentOS] remote backup

Thu Jun 9 19:11:13 UTC 2016
John R Pierce <pierce at hogranch.com>

On 6/9/2016 11:43 AM, Valeri Galtsev wrote:
> When databases are concerned, I would never rely on a snapshot of their
> storage files. Either stop relevant daemon(s), then do fs snapshot, or
> better though do dbdump and restore databases from dump when you need to
> restore it. Also: databases usually have "hold transactions" flag or
> similar, post this flag before making dump, and remove flag after dump has
> been done. This last will ensure consistent state of everything in your
> dump. I usually use combination: I do dbdump, and back up these dump files
> on regular backup schedule (and exclude db files from backup).

for postgresql, you can use rsync style copies of the file system if you 
bracket the rsync in pg_start_backup();  and pg_stop_backup() calls.    
dumps (pg_dump) are fine for smaller databases, but become really 
unwieldy for very large ones, and a straight database file system copy 
like rsync is required to initialize a streaming replication slave 
(although this can be done with the pg_basebackup command line util, 
there's times when doing it manually is appropriate).

filesystem level snapshots such as provided by ZFS are also very 
workable for such databases, as they are point-in-time views of the 
filesystem.  even if transactions are in process when the snapshot is 
made, if its later restored and the database server is restarted, the 
results are exactly the same as if the reset button had been pulled at 
that instant, postgres does a transaction recovery/cleanup, and resumes 
normal operation, with any committed transactions intact, and any 
transactions that were in progress rolled back.

john r pierce, recycling bits in santa cruz