1093228 - Upgrade Postgres to 9.2.9

Status: RESOLVED FIXED

(Socorro :: Database, task)

Description

[Selena Deckelmann :selenamarie :selena]

We look ok for going from 9.2.6 -> 9.2.9.  However, I'm wondering if we did the recommended maintenance before going to 9.2.6 regarding a data corruption error. The fix is as follows:

> The issue can be ameliorated by, after upgrading, vacuuming all tables in all databases while having vacuum_freeze_table_age set to zero. This will fix any latent corruption but will not be able to fix all pre-existing data errors. However, an installation can be presumed safe after performing this vacuuming if it has executed fewer than 2^31 update transactions in its lifetime (check this with SELECT txid_current() < 2^31).

I know I did work to fix a couple tables that had corruption before the upgrade, but I did not perform a full database VACUUM.  I think 'plugins' was an issue.

We probably need to do a full database VACUUM - or at least target a few important tables - and then an upgrade.

Comment 1

[Matt Pressman [:mpressman]]

Since the machines will be essentially out of service. They will all be the new hosts, we can put them in and run a full database vacuum without any impact. For prod, we have socorro[4-6].db.phx1 up and ready to replace socorro[1-3].db.phx1 and socorro-reporting2.db.phx1 to replace socorro-reporting1.db.phx1. We also have socorro2.dev.db.phx1 to replace socorro1.dev.db.phx1 for dev and socorro2.stage.db.phx1 to replace socorro1.stage.db.phx1 for stage. Currently, only socorro4.db.phx1 is in puppet, but it's trivial to get the rest in there.

Comment 2

[Matt Pressman [:mpressman]]

We will need to find a 9.2.6 version in order to put on those new hosts. If we can do that, we don't necessarily even need to move to 9.2.9 if there isn't anything important that we need. I just can't find that RPM version. However, we should definitely take advantage of running a full database vacuum on these hosts once we can find that 9.2.6 rpm

Comment 3

[Sheeri Cabral [:sheeri]]

Sounds like the choices are:

0) find a 9.2.6 binary so we can vacuum before migrating to new hardware and 9.2.9;
1) upgrade to 9.2.9 and then vacuum so we can migrate to new hardware;
2) migrate to new hardware then vacuum.

As far as I know this is what's blocking using the new hardware....socorro1 in production had its hardware warranty expire 4/30/2014, so I feel like anything causing needless delays is a risk not work taking. Let's figure out what the plan is and get off the old hardware ASAP.

Comment 4

[Selena Deckelmann :selenamarie :selena]

(In reply to Matt Pressman [:mpressman] from comment #2)
> We will need to find a 9.2.6 version in order to put on those new hosts. If
> we can do that, we don't necessarily even need to move to 9.2.9 if there
> isn't anything important that we need. I just can't find that RPM version.
> However, we should definitely take advantage of running a full database
> vacuum on these hosts once we can find that 9.2.6 rpm

I altered the URL for getting a RHEL RPM for 9.2.9 directly and was able to get: 

http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/postgresql92-9.2.6-1PGDG.rhel6.x86_64.rpm

Is that what you're looking for? Let me know what else you might need!

Comment 5

[Selena Deckelmann :selenamarie :selena]

This was taken care of in the migration from socorro1->socorro4