Upgrade PostgreSQL 9.6 to 10.6

Am 09.12.19 um 16:43 schrieb github kran:

Hello PostgreSQL Team,
I would like to know what would be the best way to do Database
migration from PostgreSQL 9.6 engine to 10.6 by creating a new cluster
in 10.6 and then copy data.

Size of the cluster is 3.8 TB.

1) It would be a new cluster we want to create on 10.6 and then copy
data form 9.6 and shutdown 9.6
2) Cluster today contains 1 master instance and another instance for
reader
3) Can this be done without a downtime ?  what are the options I have
to do this without making this complicated . We have about 30 -40
tables to do the migration.

yes, you can use pg_logical from us (2ndquadrant). Please visit our
homepage, there you can find it for 9.6. There are also some
blogs-postings about all that in our blog.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Great, thanks Andreas, So this seems to be a good feature using the core
concept of replication. Can I use this extension and do the major upgrade
without paying ?.

On Mon, Dec 9, 2019 at 10:02 AM Andreas Kretschmer <andreas@a-kretschmer.de>
wrote:

Am 09.12.19 um 23:37 schrieb github kran:

Great, thanks Andreas, So this seems to be a good feature using the
core concept of replication. Can I use this extension and do the major
upgrade without paying ?.

yes, this extension is free.

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

On Tue, Dec 10, 2019 at 2:53 AM Andreas Kretschmer <andreas@a-kretschmer.de>
wrote:

Am 09.12.19 um 23:37 schrieb github kran:

Great, thanks Andreas, So this seems to be a good feature using the
core concept of replication. Can I use this extension and do the major
upgrade without paying ?.

yes, this extension is free.

Regards, Andreas

Hello Andreas,
As a follow up on this thread, I want to ask if this extension works on
postgreSQL version 10.6 ? and also does this extension can be installed on
Aurora version of PostGreSQL.?. if it doesnt do you have any
other alternative to make this work on Aurora engine ?

Thanks !!

Sorry for the confusion, I tried to install this extension on 9.6 and it
seems to be not working. Does 9.6 PostGreSQL supports logical replication ?

On Tue, Jan 7, 2020 at 4:56 PM github kran <githubkran@gmail.com> wrote:

On Wed, Jan 8, 2020 at 7:20 AM github kran <githubkran@gmail.com> wrote:

Sorry for the confusion, I tried to install this extension on 9.6 and it
seems to be not working. Does 9.6 PostGreSQL supports logical replication ?

No. See the top of this page with supported versions listed. Prior to v10,
pg_logical extension was often used.
https://www.postgresql.org/docs/current/logical-replication.html

Great I see its supported based on the link but the problem is we are
locked into a Aurora RDS and we can't use logical replication on that
engine. Any thoughts that we can use some alternate mechanism to
migrate the delta data from database 1 ( Running 9.6) to database 2 (
Running 10.6 ) after we do the pg_dump and pg_restore ?.

On Wed, Jan 8, 2020 at 10:25 AM Michael Lewis <mlewis@entrata.com> wrote:

On Jan 8, 2020, at 15:56, github kran <githubkran@gmail.com> wrote:

Great I see its supported based on the link but the problem is we are locked into a Aurora RDS and we can't use logical replication on that engine.

You can use pglogical on RDS PostgreSQL 9.6.

--
-- Christophe Pettus
xof@thebuild.com

On Wed, Jan 8, 2020 at 5:57 PM Christophe Pettus <xof@thebuild.com> wrote:

On Jan 8, 2020, at 15:56, github kran <githubkran@gmail.com> wrote:

Great I see its supported based on the link but the problem is we are

locked into a Aurora RDS and we can't use logical replication on that
engine.

You can use pglogical on RDS PostgreSQL 9.6.

--
-- Christophe Pettus
xof@thebuild.com

You are right on RDS but I believe the problem is on Aurora PostgreSQL

where the pglogical throws an error during installation. Are you aware if
this works on Aurora PostGreSQL

On Wed, Jan 8, 2020 at 8:52 PM github kran <githubkran@gmail.com> wrote:

You are right on RDS but I believe the problem is on Aurora PostgreSQL
where the pglogical throws an error during installation. Are you aware if
this works on Aurora PostGreSQL

It seems like this question should be sent to AWS support for Aurora if you
are wanting to upgrade an Aurora 9.6 instance to 10x also in Aurora.

On Wed, Jan 8, 2020 at 11:03 PM Michael Lewis <mlewis@entrata.com> wrote:

On Wed, Jan 8, 2020 at 8:52 PM github kran <githubkran@gmail.com> wrote:

You are right on RDS but I believe the problem is on Aurora PostgreSQL
where the pglogical throws an error during installation. Are you aware if
this works on Aurora PostGreSQL

It seems like this question should be sent to AWS support for Aurora if
you are wanting to upgrade an Aurora 9.6 instance to 10x also in Aurora.

Yes you are right on that I dont believe in AWS support group but rather I
feel this community has more experience and have more knowledgeable folks.
I reached out to AWS support and got a reply as 'NO' ( This is not
supported from Aurora PostgreSQL)

On Thu, Jan 9, 2020, 21:47 github kran <githubkran@gmail.com> wrote:

On Wed, Jan 8, 2020 at 11:03 PM Michael Lewis <mlewis@entrata.com> wrote:

On Wed, Jan 8, 2020 at 8:52 PM github kran <githubkran@gmail.com> wrote:

You are right on RDS but I believe the problem is on Aurora PostgreSQL
where the pglogical throws an error during installation. Are you aware if
this works on Aurora PostGreSQL

It seems like this question should be sent to AWS support for Aurora if
you are wanting to upgrade an Aurora 9.6 instance to 10x also in Aurora.

Yes you are right on that I dont believe in AWS support group but rather I
feel this community has more experience and have more knowledgeable folks.
I reached out to AWS support and got a reply as 'NO' ( This is not
supported from Aurora PostgreSQL)

As far as I understand, Aurora is a "PostgreSQL-compatible" product. RDS is
a PostgreSQL database engine with limitations imposed by AWS. This group
will help you on standard installations, can guide you on standard features
but will send you to AWS when the questions come down to proprietary
features or configurations.

On Wed, Jan 8, 2020 at 11:03 PM Michael Lewis <mlewis@entrata.com<mailto:mlewis@entrata.com>> wrote:
On Wed, Jan 8, 2020 at 8:52 PM github kran <githubkran@gmail.com<mailto:githubkran@gmail.com>> wrote:
You are right on RDS but I believe the problem is on Aurora PostgreSQL where the pglogical throws an error during installation. Are you aware if this works on Aurora PostGreSQL

It seems like this question should be sent to AWS support for Aurora if you are wanting to upgrade an Aurora 9.6 instance to 10x also in Aurora.

Yes you are right on that I dont believe in AWS support group but rather I feel this community has more experience and have more knowledgeable folks. I reached out to AWS support and got a reply as 'NO' ( This is not supported from Aurora PostgreSQL)

You can reach out to me directly for help – I am the PM for Aurora PostgreSQL at AWS.

Kevin Jernigan
Principal Product Manager, Amazon Aurora with PostgreSQL compatibility
415-710-8828 (m)
27 Melcher Street
Boston, MA 02210