Обсуждение: What locking mechanism is used for database backup and restore and Master-Slave Replication?
What locking mechanism is used for database backup and restore and Master-Slave Replication?
От
John Mitchell
Дата:
Hi,
In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working.
I presume that while a restore is occurring that no reads or updates are allowed against the restored database.
What locking mechanism is used for Master-Slave Replication?
Thanks,
John J. Mitchell
In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working.
I presume that while a restore is occurring that no reads or updates are allowed against the restored database.
What locking mechanism is used for Master-Slave Replication?
Thanks,
John J. Mitchell
Re: What locking mechanism is used for database backup and restore and Master-Slave Replication?
От
Grzegorz Jaśkiewicz
Дата:
On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell <mitchelljj98@gmail.com> wrote: > Hi, > > In reading the documentation it states that the SQL dump backup does not > block other operations on the database while it is working. yes, pg_dump opens serializable transaction thus guarantees data to be the exact snapshot (as opposed to the default isolation level, which is called 'read commited' not without reason). > > I presume that while a restore is occurring that no reads or updates are > allowed against the restored database. nope, what restoring does, is just running all the commands in the pg_dump (whether it is binary or textual). So as soon as the database is created, it is treated just as any connection, thus allows you to connect and use it. > What locking mechanism is used for Master-Slave Replication? master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL shipping. So it doesn't require any extra locking. -- GJ
So am I to presume that the current stable version of postgres (before 8.5) does require extra locking?
John
--
John J. Mitchell
John
2010/1/21 Grzegorz Jaśkiewicz <gryzman@gmail.com>
On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell <mitchelljj98@gmail.com> wrote:yes, pg_dump opens serializable transaction thus guarantees data to be
> Hi,
>
> In reading the documentation it states that the SQL dump backup does not
> block other operations on the database while it is working.
the exact snapshot (as opposed to the default isolation level, which
is called 'read commited' not without reason).>nope, what restoring does, is just running all the commands in the
> I presume that while a restore is occurring that no reads or updates are
> allowed against the restored database.
pg_dump (whether it is binary or textual). So as soon as the database
is created, it is treated just as any connection, thus allows you to
connect and use it.master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL
> What locking mechanism is used for Master-Slave Replication?
shipping. So it doesn't require any extra locking.
--
GJ
--
John J. Mitchell
Re: What locking mechanism is used for database backup and restore and Master-Slave Replication?
От
Grzegorz Jaśkiewicz
Дата:
depends on what sort of replication you are going to use really. Most are based on triggers. So they have a bit more impact on performance. As far as locking goes, postgresql is very conservative with locks, ie - it won't abuse them, unlike for instance mysql. So I don't know whether you are just worried about performance in general, or locking in particular. But impact of likes of slony isn't that big, but obviously once the new version is out - it will be even less of a problem, since postgresql already produces transactional log for recovering, and pretty much the same log is going to be used for replication. 2010/1/21 John Mitchell <mitchelljj98@gmail.com>: > So am I to presume that the current stable version of postgres (before 8.5) > does require extra locking? > > John > > 2010/1/21 Grzegorz Jaśkiewicz <gryzman@gmail.com> >> >> On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell <mitchelljj98@gmail.com> >> wrote: >> > Hi, >> > >> > In reading the documentation it states that the SQL dump backup does not >> > block other operations on the database while it is working. >> yes, pg_dump opens serializable transaction thus guarantees data to be >> the exact snapshot (as opposed to the default isolation level, which >> is called 'read commited' not without reason). >> > >> > I presume that while a restore is occurring that no reads or updates are >> > allowed against the restored database. >> nope, what restoring does, is just running all the commands in the >> pg_dump (whether it is binary or textual). So as soon as the database >> is created, it is treated just as any connection, thus allows you to >> connect and use it. >> >> >> > What locking mechanism is used for Master-Slave Replication? >> >> master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL >> shipping. So it doesn't require any extra locking. >> >> >> >> -- >> GJ > > > > -- > John J. Mitchell > -- GJ
On 01/21/10 16:09, John Mitchell wrote: > So am I to presume that the current stable version of postgres (before > 8.5) does require extra locking? There is currently (before 8.5) no official replication mechanism in PostgreSQL. There are some 3rd party implementations, for which information can be gathered here: http://www.postgresql.org/docs/current/interactive/high-availability.html http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling > 2010/1/21 Grzegorz Jaśkiewicz <gryzman@gmail.com <mailto:gryzman@gmail.com>> > > On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell > <mitchelljj98@gmail.com <mailto:mitchelljj98@gmail.com>> wrote: > > Hi, > > > > In reading the documentation it states that the SQL dump backup > does not > > block other operations on the database while it is working. > yes, pg_dump opens serializable transaction thus guarantees data to be > the exact snapshot (as opposed to the default isolation level, which > is called 'read commited' not without reason). > > > > I presume that while a restore is occurring that no reads or > updates are > > allowed against the restored database. > nope, what restoring does, is just running all the commands in the > pg_dump (whether it is binary or textual). So as soon as the database > is created, it is treated just as any connection, thus allows you to > connect and use it. > > > > What locking mechanism is used for Master-Slave Replication? > > master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL > shipping. So it doesn't require any extra locking. > > > > -- > GJ > > > > > -- > John J. Mitchell
When is the new version of postgres (8.5) scheduled to be released as the latest stable version?
Thanks,
John
--
John J. Mitchell
Thanks,
John
2010/1/21 Grzegorz Jaśkiewicz <gryzman@gmail.com>
depends on what sort of replication you are going to use really.2010/1/21 John Mitchell <mitchelljj98@gmail.com>:
Most are based on triggers. So they have a bit more impact on
performance. As far as locking goes, postgresql is very conservative
with locks, ie - it won't abuse them, unlike for instance mysql.
So I don't know whether you are just worried about performance in
general, or locking in particular. But impact of likes of slony isn't
that big, but obviously once the new version is out - it will be even
less of a problem, since postgresql already produces transactional log
for recovering, and pretty much the same log is going to be used for
replication.--> So am I to presume that the current stable version of postgres (before 8.5)
> does require extra locking?
>
> John
>
> 2010/1/21 Grzegorz Jaśkiewicz <gryzman@gmail.com>
>>
>> On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell <mitchelljj98@gmail.com>
>> wrote:
>> > Hi,
>> >
>> > In reading the documentation it states that the SQL dump backup does not
>> > block other operations on the database while it is working.
>> yes, pg_dump opens serializable transaction thus guarantees data to be
>> the exact snapshot (as opposed to the default isolation level, which
>> is called 'read commited' not without reason).
>> >
>> > I presume that while a restore is occurring that no reads or updates are
>> > allowed against the restored database.
>> nope, what restoring does, is just running all the commands in the
>> pg_dump (whether it is binary or textual). So as soon as the database
>> is created, it is treated just as any connection, thus allows you to
>> connect and use it.
>>
>>
>> > What locking mechanism is used for Master-Slave Replication?
>>
>> master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL
>> shipping. So it doesn't require any extra locking.
>>
>>
>>
>> --
>> GJ
>
>
>
> --
> John J. Mitchell
>
GJ
--
John J. Mitchell
Re: What locking mechanism is used for database backup and restore and Master-Slave Replication?
От
Grzegorz Jaśkiewicz
Дата:
2010/1/22 John Mitchell <mitchelljj98@gmail.com>: > When is the new version of postgres (8.5) scheduled to be released as the > latest stable version? there will be no 8.5. It was decided to name it 9.0. -- GJ
Re: What locking mechanism is used for database backup and restore and Master-Slave Replication?
От
Grzegorz Jaśkiewicz
Дата:
and to answer the question of the release date, I believe sometime around summer holiday. There is a schedule, but in reality things usually slip by couple weeks, especially when you add quite few not so trivial patches like replication.