how to upgrade production PostgreSQL from 9.4 to 11.3

[Rahul Chordiya]

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 <919981534313>

*[image: 🌳]*

* Please do not print this email unless absolutely necessary. Go GREEN, Go
PAPERLESS **[image: 🌳]*

Re: how to upgrade production PostgreSQL from 9.4 to 11.3

[Andreas Kretschmer]

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

[Avinash Kumar]

Hi  Rahul,

You may look into the following blogs.

https://www.percona.com/blog/2019/04/12/fast-upgrade-of-legacy-postgresql-with-minimum-downtime-using-pg_upgrade/
https://www.percona.com/blog/2019/04/04/replication-between-postgresql-versions-using-logical-replication/
https://www.percona.com/blog/2019/04/09/continuous-replication-from-legacy-postgresql-version-using-slony/
https://www.percona.com/blog/2019/03/27/postgresql-upgrade-using-pg_dump-pg_restore/
https://www.percona.com/blog/2019/03/18/postgresql-upgrade-using-pg_dumpall/

https://www.percona.com/resources/webinars/upgrading-migrating-your-legacy-postgresql-newer-postgresql-versions

Thanks,
Avinash.

On Wed, Jun 12, 2019 at 10:31 AM Andreas Kretschmer 
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: found xmin * from before relfrozenxid *

[Andrew Gierth]

> "Evaldas" == Evaldas Užpalis  writes:

 Evaldas> Hello PostgresSQL users and admins,
 Evaldas> I need some help with my problem.

 Evaldas> I looked at postgresql logs and it is littered with error messages 
like:

 Evaldas> ERROR:  found xmin 3875696185 from before relfrozenxid 1599104090
 Evaldas> CONTEXT:  automatic vacuum of table "FMS.pg_catalog.pg_database"
 Evaldas> ERROR:  found xmin 3875696185 from before relfrozenxid 1599104090
 Evaldas> CONTEXT:  automatic vacuum of table "FMS.pg_catalog.pg_authid"

 Evaldas> ERROR:  found xmin 158590964 from before relfrozenxid 1599104090
 Evaldas> CONTEXT:  automatic vacuum of table "FMS.pg_catalog.pg_auth_members"

 Evaldas> First error occured 2 months ago. Database has never been crashed.
 Evaldas> Year ago it was upgraded with pg_upgrade from version 9.2.

 Evaldas> DB version:PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on
 Evaldas> x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0
 Evaldas> 20170516, 64-bit

 Evaldas> How can I solve theese errors?

Removing global/pg_internal.init may work as a temporary fix (this file
is safe to remove since it is just a cache, and will be regenerated).
Then update to 10.8.

-- 
Andrew (irc:RhodiumToad)

Re: found xmin * from before relfrozenxid *

[Evaldas Užpalis]

Updating to version 10.8 helped.
thank You all for your help.

On Wed, 12 Jun 2019 at 20:49, Andrew Gierth 
wrote:

> > "Evaldas" == Evaldas Užpalis  writes:
>
>  Evaldas> Hello PostgresSQL users and admins,
>  Evaldas> I need some help with my problem.
>
>  Evaldas> I looked at postgresql logs and it is littered with error
> messages like:
>
>  Evaldas> ERROR:  found xmin 3875696185 from before relfrozenxid 1599104090
>  Evaldas> CONTEXT:  automatic vacuum of table "FMS.pg_catalog.pg_database"
>  Evaldas> ERROR:  found xmin 3875696185 from before relfrozenxid 1599104090
>  Evaldas> CONTEXT:  automatic vacuum of table "FMS.pg_catalog.pg_authid"
>
>  Evaldas> ERROR:  found xmin 158590964 from before relfrozenxid 1599104090
>  Evaldas> CONTEXT:  automatic vacuum of table
> "FMS.pg_catalog.pg_auth_members"
>
>  Evaldas> First error occured 2 months ago. Database has never been
> crashed.
>  Evaldas> Year ago it was upgraded with pg_upgrade from version 9.2.
>
>  Evaldas> DB version:PostgreSQL 10.4 (Debian 10.4-2.pgdg90+1) on
>  Evaldas> x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1)
> 6.3.0
>  Evaldas> 20170516, 64-bit
>
>  Evaldas> How can I solve theese errors?
>
> Removing global/pg_internal.init may work as a temporary fix (this file
> is safe to remove since it is just a cache, and will be regenerated).
> Then update to 10.8.
>
> --
> Andrew (irc:RhodiumToad)
>


-- 
Evaldas Užpalis
Vyr. programuotojas
UAB "Baltic Car Equipment"
Chemijos g. 15, Kaunas, Lithuania

RE: how to upgrade production PostgreSQL from 9.4 to 11.3

[Ahmed, Nawaz (Fuji Xerox Australia)]

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 
resources[Please think before you print and use duplex printing to preserve 
resources]
From: Rahul Chordiya 
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.