[CentOS] remote backup
John R Pierce
pierce at hogranch.com
Thu Jun 9 19:11:13 UTC 2016
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
More information about the CentOS