Re: Snapshot Copy of a Postgres DB

On 5/1/06 10:37 AM, "Samuel, Rowena" <Rowena(dot)Samuel(at)netapp(dot)com> wrote:

> Hi All,
>
> I have no Postgres knowledge. I am trying to find out if it would be
> possible to Snapshot a Postgres database for a
> PostgreSQL user who has a mixed environment and takes Snapshot copies of
> all his other databases.
>
> What we would normally do for a database Snapshot in other database
> environments is to write a script which places
> the database into a hot backup mode, takes the Snapshot, and then brings
> the database back into normal operational
> mode.
>
> I have talked to a couple of PostgreQL DBAs and I understand that, while
> there are hot backups with pg_dump, there is
> no hot backup mode that the database can be placed into in Postgres. Can
> I get confirmation of this?
>
> Assuming the answer to the above is "no", one DBA I talked to mentioned
> that Postgres writes to a buffer(?) or log(?)
> before the disk. So is it possible to just simply Snapshot the database
> on the fly? How does Postgres handle a
> situation where server running the database crashes? How does it handle
> error recovery? This would be similar to
> taking a snapshot on the fly....

pg_dump makes a dump of the database that is self-consistent. Other
transactions can be occurring during the dump, but those will not be
included in the dump. There is no need to bring the server down or to put
it in backup mode to do this. As for recovery, there is usually little you
need to do. Just start postgres again and generally all is well. If you
have disk corruption or something more serious, then the recovery is
probably similar to most other database products. You simply take your
backup and recover with it. If you need point-in-time-recovery, you will
probably want to look at the documentation more specifically, as the WAL
files will need to be backed up as well for that (see the docs for details).

Sean