PostgreSQL capabilities

It means Postgres can do a reliable backup (a consistent snapshot) of a
database without shutting down the database.

What you are asking for is replication, which is not easy to implement,
and almost damn impossible to get it RIGHT. (*curse at both Sybase and
Oracle replication servers*). (i.e. how do you resolve replication
conflicts, how do you resync databases for which you don't have
transaction logs, etc). I assume for Postgres, replication is a
possibility after WAL is implemented...

On Tue, 30 May 2000, Mihai Gheorghiu wrote:

Thanks a lot.
Now, what is WAL?
When is it scheduled for implementation?

It means Postgres can do a reliable backup (a consistent snapshot) of a
database without shutting down the database.

What you are asking for is replication, which is not easy to implement,
and almost damn impossible to get it RIGHT. (*curse at both Sybase and
Oracle replication servers*). (i.e. how do you resolve replication
conflicts, how do you resync databases for which you don't have
transaction logs, etc). I assume for Postgres, replication is a
possibility after WAL is implemented...

On Tue, 30 May 2000, Mihai Gheorghiu wrote:

http://networkdna.com/database/index.html mentions that PostgreSQL is
capable of "Online backup". What does that exactly mean?
I'd like to be able to run a synchronization (in MS Access terms) (or
"incremental backup"???), i.e. to have two databases in two locations,
normally using only one of them and updating the other one. (Normal full
backup looks unrealistic for 300MB over 1/3 of a T1.) In case the
communication line between the two centers fails, users at the two ends
should be able to use the local databases, and changes made during
communication downtime be appended to the other database after

communication

Alex Pilosov wrote:

http://networkdna.com/database/index.html mentions that PostgreSQL is
capable of "Online backup". What does that exactly mean?

It means Postgres can do a reliable backup (a consistent snapshot) of a
database without shutting down the database.

Hmmm. My backup procedure, based on earlier discussions in this group,
involves blocking all write-access during a pg_dump. That is effectively
shutting down the database from my perspective. Is there a quicker way
to take a consistent snapshot while still allowing writes?

Regards,
Ed Loehr

Alex Pilosov wrote:

http://networkdna.com/database/index.html mentions that PostgreSQL is
capable of "Online backup". What does that exactly mean?

It means Postgres can do a reliable backup (a consistent snapshot) of a
database without shutting down the database.

Hmmm. My backup procedure, based on earlier discussions in this group,
involves blocking all write-access during a pg_dump. That is effectively
shutting down the database from my perspective. Is there a quicker way
to take a consistent snapshot while still allowing writes?

With 6.5 and 7.0, pg_dump grabs a consistent snapshot of the database at
the time it starts, and dumps that. No reason to shut out users.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

Alex Pilosov wrote:

http://networkdna.com/database/index.html mentions that PostgreSQL is
capable of "Online backup". What does that exactly mean?

It means Postgres can do a reliable backup (a consistent snapshot) of a
database without shutting down the database.

Hmmm. My backup procedure, based on earlier discussions in this group,
involves blocking all write-access during a pg_dump. That is effectively
shutting down the database from my perspective. Is there a quicker way
to take a consistent snapshot while still allowing writes?

With 6.5 and 7.0, pg_dump grabs a consistent snapshot of the database at
the time it starts, and dumps that. No reason to shut out users.

Can other folks confirm that this is no longer a problem? Are people
successfully vacuuming while allowing full read/write access to the db?

Regards,
Ed Loehr

On Wed, 31 May 2000, Ed Loehr wrote:

# Hmmm. My backup procedure, based on earlier discussions in this group,
# involves blocking all write-access during a pg_dump. That is
# effectively shutting down the database from my perspective. Is there a
# quicker way to take a consistent snapshot while still allowing writes?

Use a filesystem that supports snapshotting.

--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

No, that's now what he said. You can backup the database while it's still
being used (the pg_dmp runs in a transaction) but you still can't vacuum a
database while it's in use. Vacuuming is more along the lines of a defrag,
it updates the indexes and maintains stats.

At 12:16 PM 5/31/00, Ed Loehr wrote:

Charles Tassell wrote:

No, that's now what he said. You can backup the database while it's still
being used (the pg_dmp runs in a transaction) but you still can't vacuum a
database while it's in use. Vacuuming is more along the lines of a defrag,
it updates the indexes and maintains stats.

Oops...I had blurred the two in my mind, as they are both a part of my
back-up process. Thanks for pointing that out. So, dumping concurrently
with reads/writes is fine, while vacuum with concurrent writes continues
to be problematic (though sounds like there will be some improvements on
the vacuum front in a coming release).

Regards,
Ed Loehr

On Wed, May 31, 2000 at 09:26:51AM -0500, Ed Loehr wrote:

Hmmm. My backup procedure, based on earlier discussions in this group,
involves blocking all write-access during a pg_dump. That is effectively
shutting down the database from my perspective. Is there a quicker way
to take a consistent snapshot while still allowing writes?

You mean write commands fail during backup? I never tried doing this but
have some experience backing up a running Oracle system. Of course you
cannot allow writes to the data files itself during backup, but they
shouldn't fail IMO.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes wrote:

On Wed, May 31, 2000 at 09:26:51AM -0500, Ed Loehr wrote:

Hmmm. My backup procedure, based on earlier discussions in this group,
involves blocking all write-access during a pg_dump. That is effectively
shutting down the database from my perspective. Is there a quicker way
to take a consistent snapshot while still allowing writes?

You mean write commands fail during backup? I never tried doing this but
have some experience backing up a running Oracle system. Of course you
cannot allow writes to the data files itself during backup, but they
shouldn't fail IMO.

There was at least one pre-7.0, pre-24Dec1999 report that writes during
vacuum caused corruption (I couldn't find the post in deja the other
day). I have no idea if that is still true, nor have I tested it.

Regards,
Ed Loehr