pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRegarding query on the postgresql upgrade
9+ messages / 5 participants
[nested] [flat]
* Regarding query on the postgresql upgrade
@ 2020-10-15 08:09 ramesh penumalli <penumallir@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: ramesh penumalli @ 2020-10-15 08:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear All,
We have a database server which is installed postgresql 9.2.13 version
which is connected to our Atlassian applications.We have connected single
database server to all the Atlassian applications
(Jira,Confluence,BitBucket,Eazybi,Fisheye/Crucible).
We are planning to upgrade the postgres version from 9.2.13 to 9.4.25 by
keeping the support version of Atlassian in mind.
We are able to perform the upgrade on our test database environment which
is connected to (Jira,Confluence,BitBucket,Fisheye/Crucible) applications
and connected to all the applications without any issues.
But to proceed the same in production, we have tried to run the pgdump
command to take the backup of all the databases and getting the below error
when we tried to take the dump of Eazybi database which is not present on
our stage environment.
pg_dump: Dumping the contents of table "source_cubes" failed: PQgetResult()
failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for
toast value 11934509 in pg_toast_1179885
Upon investigation we have found that the issue is because of the
corruption in the public schema of the eazybi database and to resolve the
issue we have found the steps in the link
https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a.
But we are not sure if the reindexing will cause any harm to the databases
to the other application databases which are present in the same server.
Could you please help us to understand the below queries.
1. If we do an upgrade, keeping the corrupted database will there be
any issues ?
2. During upgrade does postgres have any changes to the schema's of the
existing databases?
3. Is there any recommended cleanup procedure to clean the postgres
database to give good performance?
4. How can we resolve the connection pool spikes on the database?
5. Any other tip/suggestions which we need to keep in mind while performing
a postgres upgrade.
Thanks and Regards,
Ramesh Penuballi
^ permalink raw reply [nested|flat] 9+ messages in thread
* Regarding query on the postgresql upgrade
@ 2020-10-15 08:27 Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 09:49 ` Re: Regarding query on the postgresql upgrade Thomas Kellerer <shammat@gmx.net>
2020-10-15 10:26 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
0 siblings, 2 replies; 9+ messages in thread
From: Ramesh Penuballi @ 2020-10-15 08:27 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear All,
We have a database server which is installed postgresql 9.2.13 version
which is connected to our Atlassian applications.We have connected single
database server to all the Atlassian applications
(Jira,Confluence,BitBucket,Eazybi,Fisheye/Crucible).
We are planning to upgrade the postgres version from 9.2.13 to 9.4.25 by
keeping the support version of Atlassian in mind.
We are able to perform the upgrade on our test database environment which
is connected to (Jira,Confluence,BitBucket,Fisheye/Crucible) applications
and connected to all the applications without any issues.
But to proceed the same in production, we have tried to run the pgdump
command to take the backup of all the databases and getting the below error
when we tried to take the dump of Eazybi database which is not present on
our stage environment.
pg_dump: Dumping the contents of table "source_cubes" failed: PQgetResult()
failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for
toast value 11934509 in pg_toast_1179885
Upon investigation we have found that the issue is because of the
corruption in the public schema of the eazybi database and to resolve the
issue we have found the steps in the link
https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a.
But we are not sure if the reindexing will cause any harm to the databases
to the other application databases which are present in the same server.
Could you please help us to understand the below queries.
1. If we do an upgrade, keeping the corrupted database will there be
any issues ?
2. During upgrade does postgres have any changes to the schema's of the
existing databases?
3. Is there any recommended cleanup procedure to clean the postgres
database to give good performance?
4. How can we resolve the connection pool spikes on the database?
5. Any other tip/suggestions which we need to keep in mind while performing
a postgres upgrade.
Kindly update us on the above queries as it is blocking our production
upgrade and we don't have any postgres admin help in our organization to
guide me in the right direction to proceed further on the upgradation.
Thanks and Regards,
Ramesh Penuballi
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
@ 2020-10-15 09:49 ` Thomas Kellerer <shammat@gmx.net>
2020-10-15 10:27 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
1 sibling, 1 reply; 9+ messages in thread
From: Thomas Kellerer @ 2020-10-15 09:49 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Ramesh Penuballi schrieb am 15.10.2020 um 10:27:
> We are planning to upgrade the postgres version from 9.2.13 to
> 9.4.25 by keeping the support version of Atlassian in mind.
>
> But to proceed the same in production, we have tried to run the
> pgdump command to take the backup of all the databases and getting
> the below error when we tried to take the dump of Eazybi database
> which is not present on our stage environment.
For a minor version upgrade you only need to install the new binaries and restart the Postgres service.
There is no need to use pg_dump/pg_restore in that case
Thoma
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 09:49 ` Re: Regarding query on the postgresql upgrade Thomas Kellerer <shammat@gmx.net>
@ 2020-10-15 10:27 ` Magnus Hagander <magnus@hagander.net>
2020-10-15 11:24 ` Re: Regarding query on the postgresql upgrade Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 9+ messages in thread
From: Magnus Hagander @ 2020-10-15 10:27 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Oct 15, 2020 at 11:49 AM Thomas Kellerer <shammat@gmx.net> wrote:
> Ramesh Penuballi schrieb am 15.10.2020 um 10:27:
> > We are planning to upgrade the postgres version from 9.2.13 to
> > 9.4.25 by keeping the support version of Atlassian in mind.
> >
> > But to proceed the same in production, we have tried to run the
> > pgdump command to take the backup of all the databases and getting
> > the below error when we tried to take the dump of Eazybi database
> > which is not present on our stage environment.
>
> For a minor version upgrade you only need to install the new binaries and
> restart the Postgres service.
>
> There is no need to use pg_dump/pg_restore in that case
>
9.2 to 9.4 is not a minor upgrade though. It's a major upgrade, so either
pg_dump/pg_restore or pg_upgrade will need to be used.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 09:49 ` Re: Regarding query on the postgresql upgrade Thomas Kellerer <shammat@gmx.net>
2020-10-15 10:27 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
@ 2020-10-15 11:24 ` Thomas Kellerer <shammat@gmx.net>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas Kellerer @ 2020-10-15 11:24 UTC (permalink / raw)
To: ; +Cc: pgsql-admin@lists.postgresql.org
Magnus Hagander schrieb am 15.10.2020 um 12:27:
> Ramesh Penuballi schrieb am 15.10.2020 um 10:27:
> > We are planning to upgrade the postgres version from 9.2.13 to
> > 9.4.25 by keeping the support version of Atlassian in mind.
> >
> > But to proceed the same in production, we have tried to run the
> > pgdump command to take the backup of all the databases and getting
> > the below error when we tried to take the dump of Eazybi database
> > which is not present on our stage environment.
>
> For a minor version upgrade you only need to install the new binaries and restart the Postgres service.
>
>
> 9.2 to 9.4 is not a minor upgrade though. It's a major upgrade, so
> either pg_dump/pg_restore or pg_upgrade will need to be used.
A shoot. For some reason I thought it was 9.4.13 to 9.4.25
Sorry for the noise.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
@ 2020-10-15 10:26 ` Magnus Hagander <magnus@hagander.net>
2020-10-15 11:28 ` Re: Regarding query on the postgresql upgrade ramesh penumalli <penumallir@gmail.com>
2020-10-15 11:34 ` Re: Regarding query on the postgresql upgrade Thomas Kellerer <shammat@gmx.net>
1 sibling, 2 replies; 9+ messages in thread
From: Magnus Hagander @ 2020-10-15 10:26 UTC (permalink / raw)
To: Ramesh Penuballi <ramesh.252@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
On Thu, Oct 15, 2020 at 10:27 AM Ramesh Penuballi <ramesh.252@gmail.com>
wrote:
> Dear All,
>
> We have a database server which is installed postgresql 9.2.13 version
> which is connected to our Atlassian applications.We have connected single
> database server to all the Atlassian applications
> (Jira,Confluence,BitBucket,Eazybi,Fisheye/Crucible).
>
> We are planning to upgrade the postgres version from 9.2.13 to 9.4.25 by
> keeping the support version of Atlassian in mind.
>
Note that PostgreSQL 9.4 is *not* a supported version. Whether it's
supported by Atlassian or not is a different question, but it's not a
supported version of PosgreSQL.
We are able to perform the upgrade on our test database environment which
> is connected to (Jira,Confluence,BitBucket,Fisheye/Crucible) applications
> and connected to all the applications without any issues.
>
> But to proceed the same in production, we have tried to run the pgdump
> command to take the backup of all the databases and getting the below error
> when we tried to take the dump of Eazybi database which is not present on
> our stage environment.
>
> pg_dump: Dumping the contents of table "source_cubes" failed:
> PQgetResult() failed.
>
> pg_dump: Error message from server: ERROR: missing chunk number 0 for
> toast value 11934509 in pg_toast_1179885
>
This means that your data on disk is corrupt. This can be because of
hardware issues, or it can be because you've been running an extremely out
of date unpatched version of PostgreSQL which may have had bugs.
Your best choice is to restore a backup from before this problem exists, if
you can.
Upon investigation we have found that the issue is because of the
> corruption in the public schema of the eazybi database and to resolve the
> issue we have found the steps in the link
> https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a.
>
>
> But we are not sure if the reindexing will cause any harm to the databases
> to the other application databases which are present in the same server.
>
Reindexing does not cause any harm. It is not certain to solve the problem,
but it will not make it worse.
If the problem requires deleting the data, then obviously that causes harm,
in that it deletes the data. That is why it is recommended to restore from
backup instead.
Could you please help us to understand the below queries.
>
>
> 1. If we do an upgrade, keeping the corrupted database will there be
> any issues ?
>
Yes. Your database is corrupt. If you upgrade, it will remain corrupt.
> 2. During upgrade does postgres have any changes to the schema's of the
> existing databases?
>
PostgreSQL makes no changes to the user schema. It will make changes to its
internal schemas.
Mind that you are upgrading both from and to an unsupported version, so
there may be issues of course, but there should be no change to the scyhema.
> 3. Is there any recommended cleanup procedure to clean the postgres
> database to give good performance?
>
The number one recommendation is to run a supported version of PostgreSQL.
There aren't really any recommendations for unsupported, that's one of the
things unsupported means.
> 4. How can we resolve the connection pool spikes on the database?
>
Configure the connection pool properly.
(If you want any form of more details, you'll have to explain to people
what your actual problem is).
> 5. Any other tip/suggestions which we need to keep in mind while
> performing a postgres upgrade.
>
Don't bother upgrade to an unsupported version. Upgrade to something
supported.
Given that you have data corruption, I'd try to resolve that *before*
upgrading. And if possible, also validate the contents of other tables
(given that you have some data corruption, there is certainly possible you
have it elsewhere as well).
Kindly update us on the above queries as it is blocking our production
> upgrade and we don't have any postgres admin help in our organization to
> guide me in the right direction to proceed further on the upgradation.
>
>
>
The simple summary is, solve the corruption first, then upgrade, and don't
upgrade to something unsupported. In the future, make sure you astay
supported and not on a version that went end of life several years ago.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 10:26 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
@ 2020-10-15 11:28 ` ramesh penumalli <penumallir@gmail.com>
2020-10-15 13:49 ` Re: Regarding query on the postgresql upgrade hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 1 reply; 9+ messages in thread
From: ramesh penumalli @ 2020-10-15 11:28 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Ramesh Penuballi <ramesh.252@gmail.com>; pgsql-admin@lists.postgresql.org
Hi Magnus,
Thanks alot for your help.
Currently we are upgrading the version to postgresql to 9.4 as an
intermediate version as it is supported by Atlassian applications.
In another 2 months we have plans to upgrade the postgresql to 9.6 to
support the latest version of all the Atlassian applications.
Could you please provide the list of supported postgres versions to have a
look.
Thanks and regards,
Ramesh Penuballi
On Thu, Oct 15, 2020 at 3:57 PM Magnus Hagander <magnus@hagander.net> wrote:
>
>
> On Thu, Oct 15, 2020 at 10:27 AM Ramesh Penuballi <ramesh.252@gmail.com>
> wrote:
>
>> Dear All,
>>
>> We have a database server which is installed postgresql 9.2.13 version
>> which is connected to our Atlassian applications.We have connected single
>> database server to all the Atlassian applications
>> (Jira,Confluence,BitBucket,Eazybi,Fisheye/Crucible).
>>
>> We are planning to upgrade the postgres version from 9.2.13 to 9.4.25 by
>> keeping the support version of Atlassian in mind.
>>
>
> Note that PostgreSQL 9.4 is *not* a supported version. Whether it's
> supported by Atlassian or not is a different question, but it's not a
> supported version of PosgreSQL.
>
>
> We are able to perform the upgrade on our test database environment which
>> is connected to (Jira,Confluence,BitBucket,Fisheye/Crucible) applications
>> and connected to all the applications without any issues.
>>
>> But to proceed the same in production, we have tried to run the pgdump
>> command to take the backup of all the databases and getting the below error
>> when we tried to take the dump of Eazybi database which is not present on
>> our stage environment.
>>
>> pg_dump: Dumping the contents of table "source_cubes" failed:
>> PQgetResult() failed.
>>
>> pg_dump: Error message from server: ERROR: missing chunk number 0 for
>> toast value 11934509 in pg_toast_1179885
>>
>
> This means that your data on disk is corrupt. This can be because of
> hardware issues, or it can be because you've been running an extremely out
> of date unpatched version of PostgreSQL which may have had bugs.
>
> Your best choice is to restore a backup from before this problem exists,
> if you can.
>
>
> Upon investigation we have found that the issue is because of the
>> corruption in the public schema of the eazybi database and to resolve the
>> issue we have found the steps in the link
>> https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a.
>>
>>
>> But we are not sure if the reindexing will cause any harm to the
>> databases to the other application databases which are present in the same
>> server.
>>
>
> Reindexing does not cause any harm. It is not certain to solve the
> problem, but it will not make it worse.
>
> If the problem requires deleting the data, then obviously that causes
> harm, in that it deletes the data. That is why it is recommended to restore
> from backup instead.
>
>
> Could you please help us to understand the below queries.
>>
>>
>> 1. If we do an upgrade, keeping the corrupted database will there be
>> any issues ?
>>
>
> Yes. Your database is corrupt. If you upgrade, it will remain corrupt.
>
>
>
>> 2. During upgrade does postgres have any changes to the schema's of
>> the existing databases?
>>
>
> PostgreSQL makes no changes to the user schema. It will make changes to
> its internal schemas.
>
> Mind that you are upgrading both from and to an unsupported version, so
> there may be issues of course, but there should be no change to the scyhema.
>
>
>
>> 3. Is there any recommended cleanup procedure to clean the postgres
>> database to give good performance?
>>
>
> The number one recommendation is to run a supported version of PostgreSQL.
> There aren't really any recommendations for unsupported, that's one of the
> things unsupported means.
>
>
>
>> 4. How can we resolve the connection pool spikes on the database?
>>
>
> Configure the connection pool properly.
>
> (If you want any form of more details, you'll have to explain to people
> what your actual problem is).
>
>
>
>> 5. Any other tip/suggestions which we need to keep in mind while
>> performing a postgres upgrade.
>>
>
> Don't bother upgrade to an unsupported version. Upgrade to something
> supported.
>
> Given that you have data corruption, I'd try to resolve that *before*
> upgrading. And if possible, also validate the contents of other tables
> (given that you have some data corruption, there is certainly possible you
> have it elsewhere as well).
>
>
> Kindly update us on the above queries as it is blocking our production
>> upgrade and we don't have any postgres admin help in our organization to
>> guide me in the right direction to proceed further on the upgradation.
>>
>>
>>
> The simple summary is, solve the corruption first, then upgrade, and don't
> upgrade to something unsupported. In the future, make sure you astay
> supported and not on a version that went end of life several years ago.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/;
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/;
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 10:26 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
2020-10-15 11:28 ` Re: Regarding query on the postgresql upgrade ramesh penumalli <penumallir@gmail.com>
@ 2020-10-15 13:49 ` hubert depesz lubaczewski <depesz@depesz.com>
0 siblings, 0 replies; 9+ messages in thread
From: hubert depesz lubaczewski @ 2020-10-15 13:49 UTC (permalink / raw)
To: ramesh penumalli <penumallir@gmail.com>; +Cc: Magnus Hagander <magnus@hagander.net>; Ramesh Penuballi <ramesh.252@gmail.com>; pgsql-admin@lists.postgresql.org
On Thu, Oct 15, 2020 at 04:58:01PM +0530, ramesh penumalli wrote:
> Hi Magnus,
>
> Thanks alot for your help.
>
> Currently we are upgrading the version to postgresql to 9.4 as an
> intermediate version as it is supported by Atlassian applications.
> In another 2 months we have plans to upgrade the postgresql to 9.6 to
> support the latest version of all the Atlassian applications.
>
> Could you please provide the list of supported postgres versions to have a
> look.
Sure: https://www.postgresql.org/support/versioning/
Please note that 9.6 will stop being supported in a year, as it's been
released 4 years ago.
Best regards,
depesz
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Regarding query on the postgresql upgrade
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 10:26 ` Re: Regarding query on the postgresql upgrade Magnus Hagander <magnus@hagander.net>
@ 2020-10-15 11:34 ` Thomas Kellerer <shammat@gmx.net>
1 sibling, 0 replies; 9+ messages in thread
From: Thomas Kellerer @ 2020-10-15 11:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Magnus Hagander schrieb am 15.10.2020 um 12:26:
> Note that PostgreSQL 9.4 is *not* a supported version. Whether it's
> supported by Atlassian or not is a different question, but it's not a
> supported version of PosgreSQL.
According to their manual, the supported Postgres versions are 9.6, 10 and 11
https://confluence.atlassian.com/adminjiraserver/supported-platforms-938846830.html#Supportedplatfor...
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2020-10-15 13:49 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-10-15 08:09 Regarding query on the postgresql upgrade ramesh penumalli <penumallir@gmail.com>
2020-10-15 08:27 Regarding query on the postgresql upgrade Ramesh Penuballi <ramesh.252@gmail.com>
2020-10-15 09:49 ` Thomas Kellerer <shammat@gmx.net>
2020-10-15 10:27 ` Magnus Hagander <magnus@hagander.net>
2020-10-15 11:24 ` Thomas Kellerer <shammat@gmx.net>
2020-10-15 10:26 ` Magnus Hagander <magnus@hagander.net>
2020-10-15 11:28 ` ramesh penumalli <penumallir@gmail.com>
2020-10-15 13:49 ` hubert depesz lubaczewski <depesz@depesz.com>
2020-10-15 11:34 ` Thomas Kellerer <shammat@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox