physical backup of PostgreSQL
After further thought I do think that a physical restore of a backup
done with e.g. tar and pg_log as first file of backup does indeed work.
I had concerns with the incompletely written pg pages, but
those will always be last pages in table data files. The problem
with this page is imho not existent since the offsets for rows inside the
page
stay the same, data after the currently last added row will stay the same.
Thus we only have a problem, that a new row can be half added because
it is split between two system pages. But since it will have an open
(to be rolled back) [x?]tid in respect to pg_log we are certainly not
interested in this row.
Yes, indexes will need to be rebuilt, since they do change page layout
and pointers (e.g. page split) during transactions.
But the simplicity of backing up your database as part of your normal
system backup makes this very interesting to me, and imho to the community.
The speed difference compared to pg_dump is also tremendous.
One helpful improvement for this would be to add a type of file ending
to our files, like *.dat for data and *.idx for indexes, since you will not
want to backup index files. Missing indexes would need to be recreated
on first backend startup.
Andreas
At 12:13 26/06/00 +0200, Zeugswetter Andreas SB wrote:
After further thought I do think that a physical restore of a backup
done with e.g. tar and pg_log as first file of backup does indeed work.
Even if the file backup occurs in the middle of a vacuum?
I like the idea of a non-SQL-based backup method, but it would be nice to
see some kind of locking being done to ensure that the backup is a valid
database snapshot. Can some kind of consistent page dump be done? (Rather
than a file copy)
I believe Vadim's future plans involve reuse of data pages - would this
have an impact on backup integrity?
My experience with applying journals to restored databases suggests that
the journals need to be applied to a 'known' state of the database -
usually a snapshot as of a certain TX Seq No.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
At 12:13 26/06/00 +0200, Zeugswetter Andreas SB wrote:
After further thought I do think that a physical restore of a backup
done with e.g. tar and pg_log as first file of backup doesindeed work.
Even if the file backup occurs in the middle of a vacuum?
I guess not, since at vacuum time the rows are moved inside the pages,
but that does not seem like a real world limitation to me.
I like the idea of a non-SQL-based backup method, but it
would be nice to
see some kind of locking being done to ensure that the backup
is a valid
database snapshot. Can some kind of consistent page dump be
done? (Rather
than a file copy)
Yes, it is the current smgr that makes any locking obsolete.
I believe Vadim's future plans involve reuse of data pages -
would this
have an impact on backup integrity?
Yes.
My experience with applying journals to restored databases
suggests that
the journals need to be applied to a 'known' state of the database -
usually a snapshot as of a certain TX Seq No.
Yes, if Vadim changes things to overwrite.
Of course we could have a db mode where nothing is overwritten,
since we have the code for that.
Andreas
Import Notes
Resolved by subject fallback