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 _______________________________________________ CentOS mailing list CentOS@xxxxxxxxxx https://lists.centos.org/mailman/listinfo/centos
