Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to upgrade production PostgreSQL from 9.4 to 11.3

how to upgrade production PostgreSQL from 9.4 to 11.3

From
Rahul Chordiya
Date:
Hi,

We have 2 TB of data on our production server, we want to use 11.3 or latest stable version of PostgreSQL as our DB server on aws EC2 instance.

I have tried couple of way to upgrade but they are not production friendly:

I have tried pg_upgrade but as it require to stop the server we can't do it.
I have tried with --link option too but it also take time.
I have created replication and I tried to take pg_basebackup using 11.3 version but the backup was created with 9.4 version.
I have also tried to do the same with pglogical but it is showing pglogical schema not found.

please tell me the proper way to upgrade our 2TB of production database.


--
Thanks & Regards

Rahul Chordiya
Tech-Geek && DevOps && Full Stack Developer
Mobile: +91 998 153 4313

🌳

 Please do not print this email unless absolutely necessary. Go GREEN, Go PAPERLESS 🌳



Re: how to upgrade production PostgreSQL from 9.4 to 11.3

From
Andreas Kretschmer
Date:

Am 12.06.19 um 14:50 schrieb Rahul Chordiya:
> postgres=#
> postgres=# select subscription_name, status FROM 
> pglogical.show_subscription_status();
>  subscription_name | status
> -------------------+--------
> (0 rows)
>
> postgres=# select pglogical.create_subscription(subscription_name := 
> 'subscription1', provider_dsn := 'host=10.101.40.70 port=5432 
> dbname=registration user=repuser password=password', replication_sets 
> := '{my_set}');
> *ERROR:  could not fetch remote node info: ERROR:  schema "pglogical" 
> does not exist*
>
> What should I do?
>

you have create the replication set (on the provider) in the 
postgres-database, but now you referencing to an other database, 
registration. There isn't this replication set defined.


Regards, Andreas

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

Re: how to upgrade production PostgreSQL from 9.4 to 11.3

From
Avinash Kumar
Date:

On Wed, Jun 12, 2019 at 10:31 AM Andreas Kretschmer <andreas@a-kretschmer.de> wrote:


Am 12.06.19 um 14:50 schrieb Rahul Chordiya:
> postgres=#
> postgres=# select subscription_name, status FROM
> pglogical.show_subscription_status();
>  subscription_name | status
> -------------------+--------
> (0 rows)
>
> postgres=# select pglogical.create_subscription(subscription_name :=
> 'subscription1', provider_dsn := 'host=10.101.40.70 port=5432
> dbname=registration user=repuser password=password', replication_sets
> := '{my_set}');
> *ERROR:  could not fetch remote node info: ERROR:  schema "pglogical"
> does not exist*
>
> What should I do?
>

you have create the replication set (on the provider) in the
postgres-database, but now you referencing to an other database,
registration. There isn't this replication set defined.


Regards, Andreas

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





--
9000799060

RE: how to upgrade production PostgreSQL from 9.4 to 11.3

From
"Ahmed, Nawaz (Fuji Xerox Australia)"
Date:

Your best bet is to use Slony, can be time consuming and a lot of hassle for the initial setup but worth all that effort when the downtime is reduced (between seconds to a couple of minutes) for such a large system.

Fuji Xerox Australia

Nawaz Ahmed

Database Administrator

http://xww.aus.xerox.com/staffdirectory/ebizcard/images/facebook.png  http://xww.aus.xerox.com/staffdirectory/ebizcard/images/twitter.png  http://xww.aus.xerox.com/staffdirectory/ebizcard/images/linkedin.png  http://xww.aus.xerox.com/staffdirectory/ebizcard/images/youtube.png  http://xww.aus.xerox.com/staffdirectory/ebizcard/images/blog.png

http://xww.aus.xerox.com/staffdirectory/ebizcard/images/feedback.png



Please think before you print and use duplex printing to preserve resourcesPlease think before you print and use duplex printing to preserve resources

From: Rahul Chordiya <rahulchordiya@gmail.com>
Sent: Wednesday, 12 June 2019 9:33 PM
To: pgsql-general@lists.postgresql.org
Subject: how to upgrade production PostgreSQL from 9.4 to 11.3

 

Hi,

 

We have 2 TB of data on our production server, we want to use 11.3 or latest stable version of PostgreSQL as our DB server on aws EC2 instance.

 

I have tried couple of way to upgrade but they are not production friendly:

 

I have tried pg_upgrade but as it require to stop the server we can't do it.

I have tried with --link option too but it also take time.

I have created replication and I tried to take pg_basebackup using 11.3 version but the backup was created with 9.4 version.

I have also tried to do the same with pglogical but it is showing pglogical schema not found.

 

please tell me the proper way to upgrade our 2TB of production database.

 

 

--

Thanks & Regards

 

Rahul Chordiya

Tech-Geek && DevOps && Full Stack Developer

Mobile: +91 998 153 4313


🌳

 Please do not print this email unless absolutely necessary. Go GREEN, Go PAPERLESS 

🌳

 

 


IMPORTANT NOTE: Fuji Xerox email transmission, including any attachments, is private and confidential and may contain legally privileged information. It is for the addressee's attention only. If you are not the intended recipient and have received this transmission, you must not use, edit, print, copy or disclose its contents to any person or disseminate the information contained herein or hereto attached, and you must notify sender immediately by return email and delete this transmission from your system. Any confidentiality, privilege or copyright is not waived or lost because this e-mail has been sent to you in error. We have used reasonable efforts to protect this transmission from computer viruses and other malicious software, but no warranty is made and the sender takes no responsibility for any loss or damage incurred from using this email or the information contained in this email.