Обсуждение: physical backup of PostgreSQL

Поиск
Список
Период
Сортировка

physical backup of PostgreSQL

От
Zeugswetter Andreas SB
Дата:
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


Re: physical backup of PostgreSQL

От
Philip Warner
Дата:
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   |/