Обсуждение: Upgrading from 9.1.2 to 9.1.5
Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just using pg_upgrade? For what I could read, the only exception would be if I was using a citext column (which I am not). Thank you
On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: > Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just using pg_upgrade? > For what I could read, the only exception would be if I was using a > citext column (which I am not). You cannot use pg_upgrade for this. You just need to stop the server, install the binaries, and restart the server. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Fri, Sep 7, 2012 at 3:20 AM, Bruce Momjian <bruce@momjian.us> wrote: > On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: >> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just using pg_upgrade? >> For what I could read, the only exception would be if I was using a >> citext column (which I am not). > > You cannot use pg_upgrade for this. You just need to stop the server, > install the binaries, and restart the server. AFAIU it is not necessary to stop the server when updating binaries if one is not going to create extensions, PLs or anything else that will be dynamically linked after the binaries update and before the server restart. So with the process 1. update binaries 2. postgres restart the downtime will be shorter. Do I understand something wrong? > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
On Sat, Sep 8, 2012 at 1:26 PM, Sergey Konoplev <gray.ru@gmail.com> wrote: > On Fri, Sep 7, 2012 at 3:20 AM, Bruce Momjian <bruce@momjian.us> wrote: >> On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: >>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just using pg_upgrade? >>> For what I could read, the only exception would be if I was using a >>> citext column (which I am not). >> >> You cannot use pg_upgrade for this. You just need to stop the server, >> install the binaries, and restart the server. > > AFAIU it is not necessary to stop the server when updating binaries if > one is not going to create extensions, PLs or anything else that will > be dynamically linked after the binaries update and before the server > restart. > > So with the process > > 1. update binaries > 2. postgres restart > > the downtime will be shorter. I'm just guessing, but this is probably a bad idea. This could happen... 1. Postgres master and a bunch of clients are running 2. You start updating binaries 3. In the middle of your update, an new client connects and a new backend process starts. 4. The 9.1.2 executable links to the 9.1.5 binaries. Or a 9.1.5 executable links to the 9.1.2 libraries. Or a 9.1.5 executable starts with the right binaries, but is talking to a 9.1.2 postmaster process, which might not have the same shared-memory map. Or ... ... and so forth.
Craig James <cjames@emolecules.com> wrote: > Sergey Konoplev <gray.ru@gmail.com> wrote: >> Bruce Momjian <bruce@momjian.us> wrote: >>> On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: >>>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just >>>> using pg_upgrade? For what I could read, the only exception >>>> would be if I was using a citext column (which I am not). >>> >>> You cannot use pg_upgrade for this. "Cannot" or "don't need to"? >>> You just need to stop the server, install the binaries, and >>> restart the server. >> >> AFAIU it is not necessary to stop the server when updating >> binaries if one is not going to create extensions, PLs or >> anything else that will be dynamically linked after the binaries >> update and before the server restart. >> >> So with the process >> >> 1. update binaries >> 2. postgres restart >> >> the downtime will be shorter. > > I'm just guessing, but this is probably a bad idea. This could > happen... > > 1. Postgres master and a bunch of clients are running > > 2. You start updating binaries > > 3. In the middle of your update, an new client connects and a new > backend process starts. > > 4. The 9.1.2 executable links to the 9.1.5 binaries. Or a 9.1.5 > executable links to the 9.1.2 libraries. Or a 9.1.5 executable > starts with the right binaries, but is talking to a 9.1.2 > postmaster process, which might not have the same shared-memory > map. Or ... > > ... and so forth. That's why we put each minor release into a separate location. 1. PostgreSQL master and a bunch of clients are running against executables deployed with a prefix of /usr/local/pgsql-9.1.4. The prefix is specified in the service script for the server; clients use a symlink at /usr/local/pgsql. 2. We make and install a new build with prefix /usr/local/pgsql-9.1.5. 3. We change the symlink to point to the new build. 4. We change the appropriate service script(s) to point to the new prefix. 5. We stop and then start the server(s). (We don't use pg_ctl restart because that seems to stay on the same prefix.) 6. Later, when we confirm that nothing is still referencing the old prefix, we remove its subdirectory. PostgreSQL is down only for the time it takes for a restart. We normally do this during off-hours; but even if this is done during normal operations, properly coded clients (which retry a database transaction if it fails with a broken connection, without giving the client any error) only see a short stutter in response time. -Kevin
On Mon, Sep 10, 2012 at 12:06 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Craig James <cjames@emolecules.com> wrote: >> Sergey Konoplev <gray.ru@gmail.com> wrote: >>> Bruce Momjian <bruce@momjian.us> wrote: >>>> On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: >>>>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just >>>>> using pg_upgrade? For what I could read, the only exception >>>>> would be if I was using a citext column (which I am not). >>>> >>>> You cannot use pg_upgrade for this. > > "Cannot" or "don't need to"? > >>>> You just need to stop the server, install the binaries, and >>>> restart the server. >>> >>> AFAIU it is not necessary to stop the server when updating >>> binaries if one is not going to create extensions, PLs or >>> anything else that will be dynamically linked after the binaries >>> update and before the server restart. >>> >>> So with the process >>> >>> 1. update binaries >>> 2. postgres restart >>> >>> the downtime will be shorter. >> >> I'm just guessing, but this is probably a bad idea. This could >> happen... >> >> 1. Postgres master and a bunch of clients are running >> >> 2. You start updating binaries >> >> 3. In the middle of your update, an new client connects and a new >> backend process starts. >> >> 4. The 9.1.2 executable links to the 9.1.5 binaries. Or a 9.1.5 >> executable links to the 9.1.2 libraries. Or a 9.1.5 executable >> starts with the right binaries, but is talking to a 9.1.2 >> postmaster process, which might not have the same shared-memory >> map. Or ... >> >> ... and so forth. > > That's why we put each minor release into a separate location. > > 1. PostgreSQL master and a bunch of clients are running against > executables deployed with a prefix of /usr/local/pgsql-9.1.4. The > prefix is specified in the service script for the server; clients > use a symlink at /usr/local/pgsql. > > 2. We make and install a new build with prefix > /usr/local/pgsql-9.1.5. > > 3. We change the symlink to point to the new build. > > 4. We change the appropriate service script(s) to point to the new > prefix. > > 5. We stop and then start the server(s). (We don't use pg_ctl > restart because that seems to stay on the same prefix.) > > 6. Later, when we confirm that nothing is still referencing the old > prefix, we remove its subdirectory. > > PostgreSQL is down only for the time it takes for a restart. We > normally do this during off-hours; but even if this is done during > normal operations, properly coded clients (which retry a database > transaction if it fails with a broken connection, without giving the > client any error) only see a short stutter in response time. > > -Kevin > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
Another question, when I get a reply from the list, to which email should I then reply? To all? the User posting, or pgsql-admin@? thanks On Mon, Sep 10, 2012 at 12:06 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Craig James <cjames@emolecules.com> wrote: >> Sergey Konoplev <gray.ru@gmail.com> wrote: >>> Bruce Momjian <bruce@momjian.us> wrote: >>>> On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: >>>>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just >>>>> using pg_upgrade? For what I could read, the only exception >>>>> would be if I was using a citext column (which I am not). >>>> >>>> You cannot use pg_upgrade for this. > > "Cannot" or "don't need to"? > >>>> You just need to stop the server, install the binaries, and >>>> restart the server. >>> >>> AFAIU it is not necessary to stop the server when updating >>> binaries if one is not going to create extensions, PLs or >>> anything else that will be dynamically linked after the binaries >>> update and before the server restart. >>> >>> So with the process >>> >>> 1. update binaries >>> 2. postgres restart >>> >>> the downtime will be shorter. >> >> I'm just guessing, but this is probably a bad idea. This could >> happen... >> >> 1. Postgres master and a bunch of clients are running >> >> 2. You start updating binaries >> >> 3. In the middle of your update, an new client connects and a new >> backend process starts. >> >> 4. The 9.1.2 executable links to the 9.1.5 binaries. Or a 9.1.5 >> executable links to the 9.1.2 libraries. Or a 9.1.5 executable >> starts with the right binaries, but is talking to a 9.1.2 >> postmaster process, which might not have the same shared-memory >> map. Or ... >> >> ... and so forth. > > That's why we put each minor release into a separate location. > > 1. PostgreSQL master and a bunch of clients are running against > executables deployed with a prefix of /usr/local/pgsql-9.1.4. The > prefix is specified in the service script for the server; clients > use a symlink at /usr/local/pgsql. > > 2. We make and install a new build with prefix > /usr/local/pgsql-9.1.5. > > 3. We change the symlink to point to the new build. > > 4. We change the appropriate service script(s) to point to the new > prefix. > > 5. We stop and then start the server(s). (We don't use pg_ctl > restart because that seems to stay on the same prefix.) > > 6. Later, when we confirm that nothing is still referencing the old > prefix, we remove its subdirectory. > > PostgreSQL is down only for the time it takes for a restart. We > normally do this during off-hours; but even if this is done during > normal operations, properly coded clients (which retry a database > transaction if it fails with a broken connection, without giving the > client any error) only see a short stutter in response time. > > -Kevin > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin
On Mon, Sep 10, 2012 at 10:17 AM, Antoine Guidi <antoine.guidi@gmail.com> wrote:
Either reply-to-all or reply to the pgsql-admin address. Either way, everyone gets it who participated.
Craig
Another question, when I get a reply from the list, to which email
should I then reply?
To all? the User posting, or pgsql-admin@?
Either reply-to-all or reply to the pgsql-admin address. Either way, everyone gets it who participated.
Craig
Thank you Craig On Mon, Sep 10, 2012 at 12:30 PM, Craig James <cjames@emolecules.com> wrote: > > > On Mon, Sep 10, 2012 at 10:17 AM, Antoine Guidi <antoine.guidi@gmail.com> > wrote: >> >> Another question, when I get a reply from the list, to which email >> should I then reply? >> To all? the User posting, or pgsql-admin@? > > > Either reply-to-all or reply to the pgsql-admin address. Either way, > everyone gets it who participated. > > Craig >
On Mon, Sep 10, 2012 at 12:06:10PM -0500, Kevin Grittner wrote: > Craig James <cjames@emolecules.com> wrote: > > Sergey Konoplev <gray.ru@gmail.com> wrote: > >> Bruce Momjian <bruce@momjian.us> wrote: > >>> On Thu, Sep 6, 2012 at 05:55:05PM -0500, Antoine Guidi wrote: > >>>> Is it possible to do a pg_upgrade from 9.1.2 to 9.1.5 just > >>>> using pg_upgrade? For what I could read, the only exception > >>>> would be if I was using a citext column (which I am not). > >>> > >>> You cannot use pg_upgrade for this. > > "Cannot" or "don't need to"? Cannot. The tablespace directory names have the same embedded system catalog version for minor releases of the same major version. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +