File system level backup of running database
Hi all, Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this? For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup. Best regards, Dennis Pallett
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256 On 23/02/16 15:45, Dennis Pallett wrote:
Hi all,
Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this?
For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup.
Best regards, Dennis Pallett
At any point in time, the disk image of the database should be consistent. All committed transactions should be safe on disk. But MonetDB makes use of atomic file system operations to commit transaction s. Since making a file system backup takes time, it could start before a transaction commits, and end after. If this happens, there cannot be any guarantees about the backup. Another potential for problems is when the server decides to do some database maintenance. Every once in a while, the write-ahead log (WAL) is cleaned up. It this happens during a file system backup, again there are no guarantees. If neither of these events (transaction, WAL cleanup) happens during the backup, you're probably ok. But this is not something we can guarantee in any way, so to be sure of a proper backup, you should also create a sql-level backup (msqldump) in case the file system backup gives weird results after it is restored (which one of course hopes is never needed). - -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iQEcBAEBCAAGBQJWzH8zAAoJEISMxT6LrWYgeagH/3TV//5Qa2l/ZKae7xjda4NP 5cXw4yzOGNA0ONnBL+yGN5bRPr4Mz+J6dGoWWdnFv6VZkeH4ZPzi4zQkHZdX5idH LgRliVvUq37wyYKJwjNtyBWV7zAnDMofpeayuBapFVa/3G8KH4+T1otG0JmRC13w XKpe7kmaxFZoCtrGleLQ4Iy7cYdigbkVi+w4O/pS/e9lt9g+AmF0Nf/IZOM1AAS1 Z/Klu9jOZ4WUqRtqFD1XOGCnn7A82Zx1XsDUWW2Gd92FSwB2SdCSI3wA1FT4Ma07 HkdF+LotwprN231V28qeIzOp9oXJKXnyx7P/9/YeippcJoZyTmxcP0ya2ANQL5A= =Z7zN -----END PGP SIGNATURE-----
Hi Under the assumption that there is a small timeslot and your database doesn't grow too fast during a day, then simply stop the server and run a rsync over the dbfarm (bats + sqllogs) You might use rsync without stopping the server to see the time required for such action and if it fits your timeslot boundaries. regards, Martin On 23/02/16 16:48, Sjoerd Mullender wrote:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA256
On 23/02/16 15:45, Dennis Pallett wrote:
Hi all,
Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this?
For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup.
Best regards, Dennis Pallett
At any point in time, the disk image of the database should be consistent. All committed transactions should be safe on disk. But MonetDB makes use of atomic file system operations to commit transaction s.
Since making a file system backup takes time, it could start before a transaction commits, and end after. If this happens, there cannot be any guarantees about the backup. Another potential for problems is when the server decides to do some database maintenance. Every once in a while, the write-ahead log (WAL) is cleaned up. It this happens during a file system backup, again there are no guarantees.
If neither of these events (transaction, WAL cleanup) happens during the backup, you're probably ok. But this is not something we can guarantee in any way, so to be sure of a proper backup, you should also create a sql-level backup (msqldump) in case the file system backup gives weird results after it is restored (which one of course hopes is never needed).
- -- Sjoerd Mullender -----BEGIN PGP SIGNATURE----- Version: GnuPG v2
iQEcBAEBCAAGBQJWzH8zAAoJEISMxT6LrWYgeagH/3TV//5Qa2l/ZKae7xjda4NP 5cXw4yzOGNA0ONnBL+yGN5bRPr4Mz+J6dGoWWdnFv6VZkeH4ZPzi4zQkHZdX5idH LgRliVvUq37wyYKJwjNtyBWV7zAnDMofpeayuBapFVa/3G8KH4+T1otG0JmRC13w XKpe7kmaxFZoCtrGleLQ4Iy7cYdigbkVi+w4O/pS/e9lt9g+AmF0Nf/IZOM1AAS1 Z/Klu9jOZ4WUqRtqFD1XOGCnn7A82Zx1XsDUWW2Gd92FSwB2SdCSI3wA1FT4Ma07 HkdF+LotwprN231V28qeIzOp9oXJKXnyx7P/9/YeippcJoZyTmxcP0ya2ANQL5A= =Z7zN -----END PGP SIGNATURE----- _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
When going the rsync route, you can do it like this: first use rsync on a life database, then stop the database, do another rsync to the same destination, and restart the database. The second rsync should be much faster since most of the files won't have changed and don't need to be copied again. On 02/23/2016 05:57 PM, Martin Kersten wrote:
Hi
Under the assumption that there is a small timeslot and your database doesn't grow too fast during a day, then simply stop the server and run a rsync over the dbfarm (bats + sqllogs) You might use rsync without stopping the server to see the time required for such action and if it fits your timeslot boundaries.
regards, Martin
On 23/02/16 16:48, Sjoerd Mullender wrote: On 23/02/16 15:45, Dennis Pallett wrote:
Hi all,
Is it possible to create a file system level backup of a running MonetDB database? What are the downsides of doing this?
For what it's worth, our databases are generally never updated outside of pre-fixed windows so only SELECT queries will be executed during any file system level backup.
Best regards, Dennis Pallett
At any point in time, the disk image of the database should be consistent. All committed transactions should be safe on disk. But MonetDB makes use of atomic file system operations to commit transaction s.
Since making a file system backup takes time, it could start before a transaction commits, and end after. If this happens, there cannot be any guarantees about the backup. Another potential for problems is when the server decides to do some database maintenance. Every once in a while, the write-ahead log (WAL) is cleaned up. It this happens during a file system backup, again there are no guarantees.
If neither of these events (transaction, WAL cleanup) happens during the backup, you're probably ok. But this is not something we can guarantee in any way, so to be sure of a proper backup, you should also create a sql-level backup (msqldump) in case the file system backup gives weird results after it is restored (which one of course hopes is never needed).
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
-- Sjoerd Mullender
participants (4)
-
Dennis Pallett -
Martin Kersten -
Sjoerd Mullender -
Sjoerd Mullender