On Thu, June 9, 2016 1:14 pm, John R Pierce wrote:> where Rsync falls down, is if you need a point in time snapshot... Rsync > processes one file at a time, so if the files are being updated while > its running, the differnet files will be copied at different times. > This is usually fine for static archives of files and such, but > unsuitable for a database server where random updates are being made of > various files and they all have to be consistent. >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). I hope, this helps. Valeri> > -- > john r pierce, recycling bits in santa cruz > > _______________________________________________ > CentOS mailing list > CentOS at centos.org > https://lists.centos.org/mailman/listinfo/centos >++++++++++++++++++++++++++++++++++++++++ Valeri Galtsev Sr System Administrator Department of Astronomy and Astrophysics Kavli Institute for Cosmological Physics University of Chicago Phone: 773-702-4247 ++++++++++++++++++++++++++++++++++++++++
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
On Jun 9, 2016, at 1:11 PM, John R Pierce <pierce at hogranch.com> wrote:> > 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.This. If your DBMS does not recover a filesystem-level snapshot, you should stop using that DBMS because it will fail you under other real-world error scenarios, too. This is not specific to ZFS. The same can be said for UFS2, btrfs, NTFS, ReFS? (The latter two via Volume Shadow Copy Service, a feature of Windows often used specifically for its ability to snapshot a running DBMS store.)
On 06/09/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.Dumping and restoring files can be *really* slow, so "better" is highly subjective. Instead, you could quiesce your databases to get a filesystem snapshot. I wrote a framework for doing this that is filesystem and application agnostic, which I mentioned in a previous message.
Gordon Messmer wrote:> On 06/09/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. > > Dumping and restoring files can be *really* slow, so "better" is highly > subjective. > > Instead, you could quiesce your databases to get a filesystem snapshot. > I wrote a framework for doing this that is filesystem and application > agnostic, which I mentioned in a previous message.That's what "middle of the night maintenance window" is for. mark
On Thu, June 9, 2016 3:03 pm, Gordon Messmer wrote:> On 06/09/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. > > Dumping and restoring files can be *really* slow, so "better" is highly > subjective.Agree. In my case I used it in a meaning of least questionable consistency of database records (and least questionable again from my own point of view of a sysadmin who definitely has restricted knowledge of database server in question internals). I didn't write it in that level of detail, sorry: usually I'm tempted to write as short and simple as I can - at the expense of accuracy -, just to save effort to whoever is kind enough to read what I wrote ;-) Valeri> > Instead, you could quiesce your databases to get a filesystem snapshot. > I wrote a framework for doing this that is filesystem and application > agnostic, which I mentioned in a previous message. > _______________________________________________ > CentOS mailing list > CentOS at centos.org > https://lists.centos.org/mailman/listinfo/centos >++++++++++++++++++++++++++++++++++++++++ Valeri Galtsev Sr System Administrator Department of Astronomy and Astrophysics Kavli Institute for Cosmological Physics University of Chicago Phone: 773-702-4247 ++++++++++++++++++++++++++++++++++++++++