Re: Regarding query on the postgresql upgrade

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.