Обсуждение: Upgrading 9.6.9 to 10.4
Hi all, My employer is in the process of upgrading PostgreSQL, and they would like me to make this happen without any real interruptionof service. As I understand it, 9.6 does not support the publish/subscribe model like 10.4 does, but logicalreplication is the basis of that model. So, does anyone have any suggestions for how to implement the receiver sideon a 10.4 server? Or could you at least point me to some sort of how-to page? TIA, Jay Sent from my iPad
Take a look at pglogical ... we've used it for 9.4 -> 9.6 and the same theories apply going to 10.x I imagine. People haveused it in combination with pgbouncer to achieve near outage-less upgrades. Some helpful links below: https://blog.2ndquadrant.com/pglogical-logical-replication-postgresql-10/ https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud-part-ii/ On 27/06/18, 10:38 AM, "John Scalia" <jayknowsunix@gmail.com> wrote: Hi all, My employer is in the process of upgrading PostgreSQL, and they would like me to make this happen without any real interruptionof service. As I understand it, 9.6 does not support the publish/subscribe model like 10.4 does, but logicalreplication is the basis of that model. So, does anyone have any suggestions for how to implement the receiver sideon a 10.4 server? Or could you at least point me to some sort of how-to page? TIA, Jay Sent from my iPad
Thanks, David Sent from my iPad > On Jun 26, 2018, at 6:47 PM, David Morton <david.morton@eroad.com> wrote: > > Take a look at pglogical ... we've used it for 9.4 -> 9.6 and the same theories apply going to 10.x I imagine. People haveused it in combination with pgbouncer to achieve near outage-less upgrades. Some helpful links below: > https://blog.2ndquadrant.com/pglogical-logical-replication-postgresql-10/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud-part-ii/ > > > On 27/06/18, 10:38 AM, "John Scalia" <jayknowsunix@gmail.com> wrote: > > Hi all, > > My employer is in the process of upgrading PostgreSQL, and they would like me to make this happen without any real interruptionof service. As I understand it, 9.6 does not support the publish/subscribe model like 10.4 does, but logicalreplication is the basis of that model. So, does anyone have any suggestions for how to implement the receiver sideon a 10.4 server? Or could you at least point me to some sort of how-to page? > > TIA, > Jay > > > Sent from my iPad > >
I managed to download the sources for the pglogical extension and successfully built it for my 9.6.9 instance. I then triedissuing a “make clean”, then reset the PATH setting, so it could find version 10, but the “make USE_PGXS=1” then failedwith: pglogical_output_plugin.h:20:30: fatal error: pglogical_compat.h: No such file or directory But I do see the required file in the compat10 sub directory, so why can’t make find it? Sent from my iPhone > On Jun 26, 2018, at 6:47 PM, David Morton <david.morton@eroad.com> wrote: > > Take a look at pglogical ... we've used it for 9.4 -> 9.6 and the same theories apply going to 10.x I imagine. People haveused it in combination with pgbouncer to achieve near outage-less upgrades. Some helpful links below: > https://blog.2ndquadrant.com/pglogical-logical-replication-postgresql-10/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud-part-ii/ > > > On 27/06/18, 10:38 AM, "John Scalia" <jayknowsunix@gmail.com> wrote: > > Hi all, > > My employer is in the process of upgrading PostgreSQL, and they would like me to make this happen without any real interruptionof service. As I understand it, 9.6 does not support the publish/subscribe model like 10.4 does, but logicalreplication is the basis of that model. So, does anyone have any suggestions for how to implement the receiver sideon a 10.4 server? Or could you at least point me to some sort of how-to page? > > TIA, > Jay > > > Sent from my iPad > >
Ok, I figured out why the Makefile is broken for pglogical for PostgreSQL V.10. The pg_config utility returned 10.4 for theline setting Makefile parameter PGVER. Adding a simple “| cut -c1-2 to that line allows the Makefile to run for 9.6 and10.4, probably other versions as well. There are compile problems, however, in several source files. I don’t have the code in front of me, but one doing heap workcalls replorigin_drop with a single argument, but that changed in V10 to need 2 args. Fairly easy to fix that one. Another source calls ExecARUpdateTriggers, and its friends Insert and Delete, those signatures have changed as well, andI haven’t yet figured out how to alter pglogical for compiling these. If anyone reading here is from 2nd Quadrant,I’m curious if this is actually being maintained. I did post these as an issueon github. — Jay Sent from my iPad > On Jun 26, 2018, at 6:47 PM, David Morton <david.morton@eroad.com> wrote: > > Take a look at pglogical ... we've used it for 9.4 -> 9.6 and the same theories apply going to 10.x I imagine. People haveused it in combination with pgbouncer to achieve near outage-less upgrades. Some helpful links below: > https://blog.2ndquadrant.com/pglogical-logical-replication-postgresql-10/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud/ > https://blog.2ndquadrant.com/near-zero-downtime-automated-upgrades-postgresql-clusters-cloud-part-ii/ > > > On 27/06/18, 10:38 AM, "John Scalia" <jayknowsunix@gmail.com> wrote: > > Hi all, > > My employer is in the process of upgrading PostgreSQL, and they would like me to make this happen without any real interruptionof service. As I understand it, 9.6 does not support the publish/subscribe model like 10.4 does, but logicalreplication is the basis of that model. So, does anyone have any suggestions for how to implement the receiver sideon a 10.4 server? Or could you at least point me to some sort of how-to page? > > TIA, > Jay > > > Sent from my iPad > >
Hi John, On Wed, Jun 27, 2018 at 03:53:20PM -0400, John Scalia wrote: > Ok, I figured out why the Makefile is broken for pglogical for > PostgreSQL V.10. The pg_config utility returned 10.4 for the line > setting Makefile parameter PGVER. Adding a simple “| cut -c1-2 to that > line allows the Makefile to run for 9.6 and 10.4, probably other > versions as well. (10.4 isn't a major version, it's the fourth minor/patch release of the v10 major version). If you don't have the `| cut -c 1-2' in your Makefile then you're probably looking at the wrong branch, the pglogical master branch on github is stale, you need (for reasons that escape me) the REL2_x_STABLE branch. Or probably the 2.2.0 release tarball would work just as fine. > There are compile problems, however, in several source files. I don’t > have the code in front of me, but one doing heap work calls > replorigin_drop with a single argument, but that changed in V10 to > need 2 args. Fairly easy to fix that one. > > Another source calls ExecARUpdateTriggers, and its friends Insert and > Delete, those signatures have changed as well, and I haven’t yet > figured out how to alter pglogical for compiling these. I assume those failues are addressed in the above branch/release as well, but I have not verified that. Cheers Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.banck@credativ.de credativ GmbH, HRB Mönchengladbach 12080 USt-ID-Nummer: DE204566209 Trompeterallee 108, 41189 Mönchengladbach Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer Unser Umgang mit personenbezogenen Daten unterliegt folgenden Bestimmungen: https://www.credativ.de/datenschutz
Tried several branches including the one you specified. None of them mentioned the newly revised PostgreSQL functions thatwere causing the compilation to break. I did manage to alter the code so it will now build with either 9.6 or the 10.4instance. Was just sharing my experience as it was certainly broken prior. Sent from my iPhone > On Jul 11, 2018, at 12:36 PM, Michael Banck <michael.banck@credativ.de> wrote: > > Hi John, > >> On Wed, Jun 27, 2018 at 03:53:20PM -0400, John Scalia wrote: >> Ok, I figured out why the Makefile is broken for pglogical for >> PostgreSQL V.10. The pg_config utility returned 10.4 for the line >> setting Makefile parameter PGVER. Adding a simple “| cut -c1-2 to that >> line allows the Makefile to run for 9.6 and 10.4, probably other >> versions as well. > > (10.4 isn't a major version, it's the fourth minor/patch release of the > v10 major version). > > If you don't have the `| cut -c 1-2' in your Makefile then you're > probably looking at the wrong branch, the pglogical master branch on > github is stale, you need (for reasons that escape me) the REL2_x_STABLE > branch. Or probably the 2.2.0 release tarball would work just as fine. > >> There are compile problems, however, in several source files. I don’t >> have the code in front of me, but one doing heap work calls >> replorigin_drop with a single argument, but that changed in V10 to >> need 2 args. Fairly easy to fix that one. >> >> Another source calls ExecARUpdateTriggers, and its friends Insert and >> Delete, those signatures have changed as well, and I haven’t yet >> figured out how to alter pglogical for compiling these. > > I assume those failues are addressed in the above branch/release as > well, but I have not verified that. > > > Cheers > > Michael > > -- > Michael Banck > Projektleiter / Senior Berater > Tel.: +49 2166 9901-171 > Fax: +49 2166 9901-100 > Email: michael.banck@credativ.de > > credativ GmbH, HRB Mönchengladbach 12080 > USt-ID-Nummer: DE204566209 > Trompeterallee 108, 41189 Mönchengladbach > Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer > > Unser Umgang mit personenbezogenen Daten unterliegt > folgenden Bestimmungen: https://www.credativ.de/datenschutz