Replication and PITR

In response to Bo Lorentsen <bl@netgroup.dk>:

Hi ...

I have been trying to find a replication to a payment system at the
company I work, and Slony-I is of cause the first thing that game into
my attention. But when reading chapter 23.3 in the PG manual, there is
this comment of PITR used as a replication tool.

I also saw the "pgpitrha" project, and this sounds really nice too, but
is this a good way to go ? Will PITR be more replication friendly
in the future or even form the basis for a future buildin async
replication form ?

I may be naive, but to me it sound like we/I only need some kind of
protocol (or API in postgres) to move PITR data from one server to
another, and we could end up with a nice async replication system.

pros :
- DDL replications
- low overhead
- no trickers

Cons:
- binary alike master slave

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

--
Bill Moran
Collaborative Fusion Inc.

Cons:

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

That's gone with 8.2, it will be possible to stream the last
modifications, or force a WAL recycle periodically, whatever fits you
better. There is some new infrastructure which allows these things,
although I didn't have the time to play with them.

The big improvement would be indeed to have the infrastructure to start
up a standby by simply pointing it to the master server, no other setup
needed. Implement that, make it reliable, and any beginner to postgres
will be able to easily set up a WAL shipping based standby. Right now
you still have to do some complicated scripting to make it work (no idea
how much 8.2 will help here, didn't try yet).

Cheers,
Csaba.

In response to Csaba Nagy <nagy@ecircle-ag.com>:

Cons:

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

That's gone with 8.2, it will be possible to stream the last
modifications, or force a WAL recycle periodically, whatever fits you
better. There is some new infrastructure which allows these things,
although I didn't have the time to play with them.

The big improvement would be indeed to have the infrastructure to start
up a standby by simply pointing it to the master server, no other setup
needed. Implement that, make it reliable, and any beginner to postgres
will be able to easily set up a WAL shipping based standby. Right now
you still have to do some complicated scripting to make it work (no idea
how much 8.2 will help here, didn't try yet).

That will be some neat stuff. I didn't know that was coming up.

--
Bill Moran
Collaborative Fusion Inc.

On Thu, 2006-09-21 at 17:30 +0200, Bo Lorentsen wrote:

Hi ...

I have been trying to find a replication to a payment system at the
company I work, and Slony-I is of cause the first thing that game into
my attention. But when reading chapter 23.3 in the PG manual, there is
this comment of PITR used as a replication tool.

I also saw the "pgpitrha" project, and this sounds really nice too, but
is this a good way to go ? Will PITR be more replication friendly
in the future or even form the basis for a future buildin async
replication form ?

8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
a WAL might sit around for a while before it becomes full and then sent
off. 8.2 allows you to force a WAL to be sent, and it also allows a
standby mode.

Slony is a good system now, and it's nice because you can use different
versions of PostgreSQL. PITR requires that it's the same version.

However, if you're working with a payment system or accounting system,
you may need synchronous replication. With any asynchronous solution
(Slony or PITR standby), there is a possibility (although not likely) to
lose *committed* transactions.

If you do need synchronous replication, consider using two-phase commit
to prepare transactions on several machines before committing them. This
ensures that the data will be on multiple machines before committing to
any of them.

Regards,
Jeff Davis

Csaba Nagy wrote:

Cons:

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

That's gone with 8.2, it will be possible to stream the last
modifications, or force a WAL recycle periodically, whatever fits you
better. There is some new infrastructure which allows these things,
although I didn't have the time to play with them.

Keep in mind that while Slony-I provides you with a read-only replica
(able to service queries). At present WAL log replication (in 8.2 or
otherwise) would allow you to have a 'warm standby' type database -
which would be somewhat in sync (pending the latest transactions), but
would be unable to service queries (essentially, you'd have a server
that was sitting with postmaster in a recovery state).

Chander Ganesan
Open Technology Group, Inc. - Expert PostgreSQL Training
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: 877-258-8987/919-463-0999
http://www.otg-nc.com

Bill Moran wrote:

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

So it is all right for backup but for replication it could end up laking
too much behind, and a fail over could be hours behind.

So PITR can be used, but one of the cons is the unpredictable delay of
data. I thought one of the ideas behind the PITR system was to get
rather reason backups of data ...

/BL

Csaba Nagy wrote:

That's gone with 8.2, it will be possible to stream the last
modifications, or force a WAL recycle periodically, whatever fits you
better. There is some new infrastructure which allows these things,
although I didn't have the time to play with them.

This sound very nice, where can I find more info about this ... and when
is 8.2 expected to be released ? And are there anyone else that plays
with making build in replication for PG 8.2 ?

The big improvement would be indeed to have the infrastructure to start
up a standby by simply pointing it to the master server, no other setup
needed. Implement that, make it reliable, and any beginner to postgres
will be able to easily set up a WAL shipping based standby. Right now
you still have to do some complicated scripting to make it work (no idea
how much 8.2 will help here, didn't try yet).

Do you know what the motivation for these changes have been ? Better
PITR or is replication a direct goal ?

/BL

Chander Ganesan wrote:

Keep in mind that while Slony-I provides you with a read-only replica
(able to service queries). At present WAL log replication (in 8.2 or
otherwise) would allow you to have a 'warm standby' type database -
which would be somewhat in sync (pending the latest transactions), but
would be unable to service queries (essentially, you'd have a server
that was sitting with postmaster in a recovery state).

Interesting note ... do you know how fare PG would be from being able to
be in "read-only" state when receiving PITR data ? Is it a complex
problem or a simple one to solve ?

/BL

Jeff Davis wrote:

8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
a WAL might sit around for a while before it becomes full and then sent
off. 8.2 allows you to force a WAL to be sent, and it also allows a
standby mode.

This sounds really neat !

To me this sound like we may be able to replicate using different
priorities, balancing performance and data integrity. But what do you
mean by "standby mode" ? That PG ma� be able to serve as a readonly DB
and replication client at the same time ?

Slony is a good system now, and it's nice because you can use different
versions of PostgreSQL. PITR requires that it's the same version.

The PITR requirement all makes sense, and I don't like the missing DDL
updates and the fact that Slony is trigger.

However, if you're working with a payment system or accounting system,
you may need synchronous replication. With any asynchronous solution
(Slony or PITR standby), there is a possibility (although not likely) to
lose *committed* transactions.

Hmm, yes but again this have to be balanced with performance, and at the
moment I can live with a replication that is a little behind but, I will
try to keep the overhead down.

If you do need synchronous replication, consider using two-phase commit
to prepare transactions on several machines before committing them. This
ensures that the data will be on multiple machines before committing to
any of them.

Hmm, I saw that feature announced in 8.1, and I am sure it will come in
handy one day, but right now async is acceptable.

/BL

On Fri, 2006-09-22 at 08:12 +0200, Bo Lorentsen wrote:

Jeff Davis wrote:

8.2 makes PITR much easier to use for the situation you'd like. In 8.1,
a WAL might sit around for a while before it becomes full and then sent
off. 8.2 allows you to force a WAL to be sent, and it also allows a
standby mode.

This sounds really neat !

To me this sound like we may be able to replicate using different
priorities, balancing performance and data integrity. But what do you
mean by "standby mode" ? That PG maý be able to serve as a readonly DB
and replication client at the same time ?

Standby mode means that the database is kept almost up to date with the
master, but is not "up". When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries on that machine.

If you want more of a master/slave setup for performance, you should
take a second look at Slony. A PITR standby doesn't help you with
performance at all.

Slony is a good system now, and it's nice because you can use different
versions of PostgreSQL. PITR requires that it's the same version.

The PITR requirement all makes sense, and I don't like the missing DDL
updates and the fact that Slony is trigger.

Why don't you like the fact that Slony is trigger-based? Does that cause
you a problem?

And missing DDL is mainly a problem when you want to provide postgresql
to many people, and you have no idea how they will use it. If that's the
case, standby PITR might be a better solution for you. Slony has nice
"execute script" functionality that is useful for making DDL changes on
all machines.

However, if you're working with a payment system or accounting system,
you may need synchronous replication. With any asynchronous solution
(Slony or PITR standby), there is a possibility (although not likely) to
lose *committed* transactions.

Hmm, yes but again this have to be balanced with performance, and at the
moment I can live with a replication that is a little behind but, I will
try to keep the overhead down.

Asynchronous does have very good performance.

If you do need synchronous replication, consider using two-phase commit
to prepare transactions on several machines before committing them. This
ensures that the data will be on multiple machines before committing to
any of them.

Hmm, I saw that feature announced in 8.1, and I am sure it will come in
handy one day, but right now async is acceptable.

I prefer working with async when possible because it's easier to do
well. I was just making sure you knew that it is possible to lose
transactions.

By the way, no matter what you do, you probably do want to use the PITR
to at least do backups for you. It won't help to use replication if
someone accidentally does an unqualified "DELETE FROM mytable".

Regards,
Jeff Davis

On Fri, 2006-09-22 at 07:47 +0200, Bo Lorentsen wrote:

Bill Moran wrote:

- No reliability. On slow days, WAL logs could take a long time to
rotate, so small but important transactions might not be replicated
for a long time.

So it is all right for backup but for replication it could end up laking
too much behind, and a fail over could be hours behind.

So PITR can be used, but one of the cons is the unpredictable delay of
data. I thought one of the ideas behind the PITR system was to get
rather reason backups of data ...

8.2 will fix this. You can send the WALs periodically even if they're
not full. In general, PITR will be substantially improved in 8.2 (thanks
Simon!).

The beta should be out soon enough. Download it (or the CVS) and try it
out. It never hurts to actually simulate a failure and see how quickly
and effectively you actually can recover. That is especially true in
8.1, where PITR is still somewhat rough around the edges.

Regards,
Jeff Davis

Jeff Davis wrote:

8.2 will fix this. You can send the WALs periodically even if they're
not full. In general, PITR will be substantially improved in 8.2 (thanks
Simon!).

This sounds very nice, and this will make PG an even more reliable tool.

The beta should be out soon enough. Download it (or the CVS) and try it
out. It never hurts to actually simulate a failure and see how quickly
and effectively you actually can recover. That is especially true in
8.1, where PITR is still somewhat rough around the edges.

Yes, this is a good idea, and I will try it some time.

/BL

Jeff Davis wrote:

Standby mode means that the database is kept almost up to date with the
master, but is not "up". When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries on that machine.

Do you know if this will change in the future ?

If you want more of a master/slave setup for performance, you should
take a second look at Slony. A PITR standby doesn't help you with
performance at all.

Ok, I can see that ... so PITR is for a standby backup DB, with at the
best ... manual fail over ?

Why don't you like the fact that Slony is trigger-based? Does that cause
you a problem?

Hmm, well i guess i dislike the idea of having a high level mechanism to
collect data, not a rational argument, sorry. The PITR just seemed so
right as it has a more prober low level approach, but it sound to me
like Slony is the only real choice at the moment, and it will do the job
with a relatively low overhead.

And missing DDL is mainly a problem when you want to provide postgresql
to many people, and you have no idea how they will use it. If that's the
case, standby PITR might be a better solution for you. Slony has nice
"execute script" functionality that is useful for making DDL changes on
all machines.

Ok, I think that the only thing I really need to do is to try to work
more with Slony and learn to understand it. And the DDL problem is more
when others need to maintain the system, and I then have to explain how
to do this and that, and I think I am a bit spoiled by the easy working
of the mysql replication :-)

Asynchronous does have very good performance.

So, Slony also do some queuing to gain low overhead ?

I prefer working with async when possible because it's easier to do
well. I was just making sure you knew that it is possible to lose
transactions.

Thanks, one never know :-)

By the way, no matter what you do, you probably do want to use the PITR
to at least do backups for you. It won't help to use replication if
someone accidentally does an unqualified "DELETE FROM mytable".

Yeps, a master and and slave with Slony replication and backup from the
slave database, that seems to be the plan at the moment.

/BL

On Mon, 2006-09-25 at 13:48 +0200, Bo Lorentsen wrote:

Jeff Davis wrote:

Standby mode means that the database is kept almost up to date with the
master, but is not "up". When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries on that machine.

Do you know if this will change in the future ?

I don't know for sure, but I would guess not any time soon. A PITR
standby works by operating in recovery mode while it's waiting for the
WAL files to arrive. When you bring the database up, you're telling it
there are no more files to wait for, and to finish recovering and start
up. I have no idea how difficult it would be to try to allow read
queries while in recovery mode. In recovery mode, I don't think you can
create new backends.

I would think that the data pages are written and consistent while in
recovery mode, so maybe it's reasonable to do. However, I'm only
speculating and anything like this would probably not be coming soon.

If you want more of a master/slave setup for performance, you should
take a second look at Slony. A PITR standby doesn't help you with
performance at all.

Ok, I can see that ... so PITR is for a standby backup DB, with at the
best ... manual fail over ?

There's no reason it can't be automated. But the database doesn't know
when you want to fail over, so you just need to tell it. In 8.1, you can
have restore_command return a non-zero exit status and that will stop
the recovery mode and start up the backup database. Then have some
scripts redirect the traffic from the other database to the backup
database.

Since we're talking about async replication, a failover is the process
that could result in lost transactions. That's the reason the database
can't make the decision to fail over automatically.

And missing DDL is mainly a problem when you want to provide postgresql
to many people, and you have no idea how they will use it. If that's the
case, standby PITR might be a better solution for you. Slony has nice
"execute script" functionality that is useful for making DDL changes on
all machines.

Ok, I think that the only thing I really need to do is to try to work
more with Slony and learn to understand it. And the DDL problem is more
when others need to maintain the system, and I then have to explain how
to do this and that, and I think I am a bit spoiled by the easy working
of the mysql replication :-)

Sometimes "easy working" means that it's not doing what you think it's
doing. Replication is complicated and heavily dependent on what your
business needs it for, and what should be done in the case of failure.
There are no perfect answers to those questions, and if MySQL is making
the decisions for you maybe it's making choices wrong for your business.

Disclaimer: I don't know much about MySQL's replication.

Asynchronous does have very good performance.

So, Slony also do some queuing to gain low overhead ?

As I understand it, Slony does batch updates on the slaves, which would
be better performance than re-executing every transaction.

Regards,
Jeff Davis

Jeff Davis wrote:

I don't know for sure, but I would guess not any time soon. A PITR
standby works by operating in recovery mode while it's waiting for the
WAL files to arrive. When you bring the database up, you're telling it
there are no more files to wait for, and to finish recovering and start
up. I have no idea how difficult it would be to try to allow read
queries while in recovery mode. In recovery mode, I don't think you can
create new backends.

I would think that the data pages are written and consistent while in
recovery mode, so maybe it's reasonable to do. However, I'm only
speculating and anything like this would probably not be coming soon.

Ok, but this gives me a clear picture of what I am able to do at the
moment, and no matter what I think, Slony is the replication method I
will be using and PITR is nice for backup, as it is designed for.

Since we're talking about async replication, a failover is the process
that could result in lost transactions. That's the reason the database
can't make the decision to fail over automatically.

Ok, makes sense, it has to be some external logic that makes this
failover happened, and that logic must be related to whatever system the
database is supporting.

Sometimes "easy working" means that it's not doing what you think it's
doing. Replication is complicated and heavily dependent on what your
business needs it for, and what should be done in the case of failure.
There are no perfect answers to those questions, and if MySQL is making
the decisions for you maybe it's making choices wrong for your business.

MySQL only takes care of the replication, not the failover ... but it
seems like they have some kind of statement queue (no trigger setup) and
a transfer protocol all integrated in the server, and that makes it
"simpel". There is no understanding regarding transactions, as far as I
have seen.

Disclaimer: I don't know much about MySQL's replication.

That is ok.

As I understand it, Slony does batch updates on the slaves, which would
be better performance than re-executing every transaction.

That makes sense ... then the only thing to worry about is where these
"baches" are written. On the same disk as the master database or on the
client side, or will it be advisable to use a NFS mount between these to
machines to balance the disk writing ?

Thanks for your valuable answers !

/BL

I would think that the data pages are written and consistent while in
recovery mode, so maybe it's reasonable to do. However, I'm only
speculating and anything like this would probably not be coming soon.

I was thinking at one point about what problems could prevent the
standby to allow read only access, and got to the conclusion that it
would be difficult to assure consistency.

Think about it: you start a read only transaction on the standby, but if
the standby continues to apply changes from the master, it is entirely
possible it will rewrite/delete some pages which contain data which your
read only transaction should be able to see, but it won't see it.

So to make the read only transactions consistent, the standby would be
required to stop applying changes which are newer than the oldest read
only transaction running. That would mean you will have a backlog of WAL
records to apply at least as big as the age of your oldest running read
only transaction, therefore the data will be delayed the same compared
to the master, which might be OK for some scenarios...

Of course there might be other problems too.

Cheers,
Csaba.

On Tue, Sep 26, 2006 at 11:00:56AM +0200, Csaba Nagy wrote:

Of course there might be other problems too.

Another thing would be that the read-only transaction still needs a
snapshot, and whatever transaction ID it uses will have been used by
the server also.

I think the visibility issue may be solvable as long as the transaction
ID on the slave doesn't pass the VACUUM horizon of the server. But it
would require careful studying of the WAL write order to be confident
it would actually work.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Fri, Sep 22, 2006 at 07:58:41AM +0200, Bo Lorentsen wrote:

Interesting note ... do you know how fare PG would be from being able to
be in "read-only" state when receiving PITR data ? Is it a complex
problem or a simple one to solve ?

I don't know that it's even possible. The PITR replica is
essentially a node in permanent crash-recovery mode until it's caught
up. Think of Oracle's comparable product -- you can't read from
those replicas either.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.
--Josh Hamilton, on the US FEMA

On Tue, Sep 26, 2006 at 08:21:44AM +0200, Bo Lorentsen wrote:

seems like they have some kind of statement queue (no trigger setup) and
a transfer protocol all integrated in the server, and that makes it
"simpel". There is no understanding regarding transactions, as far as I
have seen.

Note that, the last time I looked at it, there was no interlock to
ensure that your statement queue (which is basically just a log of
statements as executed on the "master") was not accidentally blown
away by your cleanup process before your target replicas were up to
date. This might have improved recently, but when I looked at it
MySQL's async replication was high on the "ease of use" and low on
the "works in sticky situations". As I say, they may have fixed it;
but I advise people to look very carefully at how it works before
deciding it is adequate.

The important thing to remember about database replicas is that
you're _already_ planning for the small percentage of cases where
things break. Therefore, an 80/20 solution is not good enough: the
thing has to work when most things have broken, or it's no use to
you.

That makes sense ... then the only thing to worry about is where these
"baches" are written. On the same disk as the master database or on the
client side, or will it be advisable to use a NFS mount between these to
machines to balance the disk writing ?

No. I suggest you have a look at the docs, and take these questions
to the (again functioning) Slony list, where people can advise about
that.

The short answer is that the things to write are stored in the origin
for the table (don't think of it as a database replica, because you
can have different tables originating in different nodes). You can
_also_ write sets out to disk, if you like. Someone (my colleagues,
in fact) appear to have a nasty bug in that functionality that they
can't nail down; nobody else has reproduced it.

A

--
Andrew Sullivan | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris