Re: Regarding query on the postgresql upgrade

On Thu, Oct 15, 2020 at 10:27 AM Ramesh Penuballi <ramesh(dot)252(at)gmail(dot)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/>