Обсуждение: Regarding query on the postgresql upgrade
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
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
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.
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
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.
--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 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#Supportedplatforms-Databases