Thread: Upgrading 9.6.9 to 10.4

Upgrading 9.6.9 to 10.4

From
John Scalia
Date:
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

Re: Upgrading 9.6.9 to 10.4

From
David Morton
Date:
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
    


Re: Upgrading 9.6.9 to 10.4

From
John Scalia
Date:
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
>
>


Re: Upgrading 9.6.9 to 10.4

From
John Scalia
Date:
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
>
>


Re: Upgrading 9.6.9 to 10.4

From
John Scalia
Date:
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
>
>


Re: Upgrading 9.6.9 to 10.4

From
Michael Banck
Date:
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


Re: Upgrading 9.6.9 to 10.4

From
John Scalia
Date:
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