Обсуждение: What locking mechanism is used for database backup and restore and Master-Slave Replication?

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

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

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

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.