Thread: PostgreSQL capabilities
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 resume. Thanks, Mihai Gheorghiu
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 > resume.
See archives of this mailing list. WAL is write-ahead logging, more conventional way of assuring atomicity. (I.E. before a transaction is written to database, it is written to transaction log, which can be replayed). Replication can be achieved by streaming transaction log toyour replicated computer which will apply to its own database. I think its scheduled for postgresql 7.2... On Wed, 31 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 > >> resume. > > > >
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 >> resume. >
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. ____________
Re: Trouble-free vacuum w/concurrent writes? (was "PostgreSQL capabilities")
From
Charles Tassell
Date:
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: >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
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 > At 12:16 PM 5/31/00, Ed Loehr wrote: > >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, 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