Upgrading using streaming replication

Damien Churchill wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Good question. I think you have to disconnect the slave, use pg_upgrade
to upgrade the master, and make a new slave. It is something we had not
considered. I don't think you can run pg_upgrade on the slave and have
it work.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, Mar 11, 2011 at 12:58, Damien Churchill <damoxc@gmail.com> wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

That's correct, you can'd do that.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Using SR, the method will be to upgrade the master and then rebuild
the slave off that one. You can upgrade with pg_upgrade, which will
shorten your downtime, but there will be downtime.

For a downtime-less upgrade, you will need to look at one of the
"userspace replication solutions", like slony, bucardo, londiste etc.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Fri, Mar 11, 2011 at 13:02, Bruce Momjian <bruce@momjian.us> wrote:

Damien Churchill wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Good question.  I think you have to disconnect the slave, use pg_upgrade
to upgrade the master, and make a new slave.  It is something we had not
considered.  I don't think you can run pg_upgrade on the slave and have
it work.

No, you can't. Because it gets a new systemid and things like that.

If you use rsync to push from the master to the slave, the
re-provisioning of the slave will be fairly quick, but again it's well
above zero.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 11 March 2011 12:04, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, Mar 11, 2011 at 12:58, Damien Churchill <damoxc@gmail.com> wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

That's correct, you can'd do that.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Using SR, the method will be to upgrade the master and then rebuild
the slave off that one. You can upgrade with pg_upgrade, which will
shorten your downtime, but there will be downtime.

For a downtime-less upgrade, you will need to look at one of the
"userspace replication solutions", like slony, bucardo, londiste etc.

Thanks for the response!

That's the conclusion I had come to as well, I thought it best to ask
in case there was a method I hadn't come across. Is anything planned
to allow SR to be used for downtime-less upgrades, or will using one
of the other replication solutions be the only way for the foreseeable
future?

Regards,
Damien

Magnus Hagander wrote:

On Fri, Mar 11, 2011 at 13:02, Bruce Momjian <bruce@momjian.us> wrote:

Damien Churchill wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Good question. ?I think you have to disconnect the slave, use pg_upgrade
to upgrade the master, and make a new slave. ?It is something we had not
considered. ?I don't think you can run pg_upgrade on the slave and have
it work.

No, you can't. Because it gets a new systemid and things like that.

If you use rsync to push from the master to the slave, the
re-provisioning of the slave will be fairly quick, but again it's well
above zero.

Ah, rsync is a good idea because most of the data is already there.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Damien Churchill wrote:

On 11 March 2011 12:04, Magnus Hagander <magnus@hagander.net> wrote:

On Fri, Mar 11, 2011 at 12:58, Damien Churchill <damoxc@gmail.com> wrote:

Hi there,

I have done quite a bit of searching but have been unable to find any
way to do this. I am quite keen to use PostgreSQL 9.0's streaming
replication to create a master/slave write/read setup. However I am
unsure of an upgrade procedure whilst using streaming replication that
doesn't yield any downtime (including being able to write). From what
I have read the WAL won't be compatible between 9.0 and 9.1 so it
won't be possible to take the slave off-line and upgrade it, promote
it to master and then upgrade the old master.

That's correct, you can'd do that.

So I was wondering if anyone knows of a method that allows you to do
this. Sorry if this is an often asked question and I just haven't been
able to find the answer.

Using SR, the method will be to upgrade the master and then rebuild
the slave off that one. You can upgrade with pg_upgrade, which will
shorten your downtime, but there will be downtime.

For a downtime-less upgrade, you will need to look at one of the
"userspace replication solutions", like slony, bucardo, londiste etc.

Thanks for the response!

That's the conclusion I had come to as well, I thought it best to ask
in case there was a method I hadn't come across. Is anything planned
to allow SR to be used for downtime-less upgrades, or will using one
of the other replication solutions be the only way for the foreseeable
future?

I don't know of any plans to allow downtime-less upgrades with streaming
replication.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +