Thread: Upgrade PostgreSQL 9.6 to 10.6
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.
Appreciate your replies.
Thanks,
Kranthi
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 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
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 !!
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
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 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, AndreasHello 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 anyother alternative to make this work on Aurora engine ?Thanks !!
--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com
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.
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 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.
> 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 logicalreplication 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 PostGreSQLIt 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 PostGreSQLIt 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> 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)
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