Page MenuHomePhabricator

Upgrade postgres on labsdb1004 / 1005 to 9.4, and PostGis 2.1
Closed, ResolvedPublic

Description

The version of Postgis turns out to be too old to be used by the Maps team, please install Postgis 2.1.7 and Postgress 9.4. Per community input, the OSM database should be dropped and re-imported. Updating to Jessie is not a requirement.

Also, according to the OSM community (Paul), the default Postgres server configuration is very inefficient, and needs to be updated. Please see http://www.paulnorman.ca/blog/2014/11/new-server-postgresql-tuning/

Per @yuvipanda, other users are ok with this.

Details

Related Gerrit Patches:
operations/puppet : productionStop labsdb1004 replication to labsdb1005
operations/puppet : productionlabsdb1004: Reinstall as jessie

Event Timeline

yuvipanda raised the priority of this task from to Needs Triage.
yuvipanda updated the task description. (Show Details)
yuvipanda added a project: Cloud-Services.
yuvipanda added a subscriber: yuvipanda.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 3 2015, 10:28 AM
Yurik renamed this task from Upgrade postgres on labsdb1004 / 1005 to 9.4 to Upgrade postgres on labsdb1004 / 1005 to 9.4, and PostGis 2.1.Jun 3 2015, 10:32 AM
Yurik updated the task description. (Show Details)
Yurik set Security to None.
Yurik added subscribers: akosiaris, MaxSem.
Yurik updated the task description. (Show Details)Jun 3 2015, 4:49 PM
Yurik added a subscriber: Yurik.
Yurik added a project: Maps.
chasemp triaged this task as Medium priority.
chasemp added a subscriber: chasemp.

yuvi based on what I see I'm guessing you are the right assignee :)

yuvipanda removed yuvipanda as the assignee of this task.Jun 6 2015, 3:55 PM

I asked them and they're ok with it :)

And nope, not the right assignee :) This has no assignee atm.

Yurik updated the task description. (Show Details)Jun 7 2015, 1:28 AM
Yurik updated the task description. (Show Details)Jun 7 2015, 1:50 AM

This also requires labsdb1005 to be upgraded since that's the postgres slave for this instance, but it's also the master for tools-db mysql setup.

Trusty, which is the easy upgrade path, has postgis 2.1.2 and postgres 9.3. @MaxSem, @Yurik, are those sufficient ? Otherwise we are going to need a reinstall to jessie which has the versions requested. Which means some downtime. As far as reimporting the databases go, since 9.x this is not really needed for postgres. There is pg_upgrade which can do the upgrade in place. However, and given postgis 2.1, it seems indeed that the gis database will need a reimport.

Finally, postgres databases are already tuned, see https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/files/postgres/tuning.conf

Yurik added a comment.Jun 9 2015, 4:38 PM

Lets either lower the priority of this or resolve it outright - we are trying an alternative approach.

ksmith moved this task from Backlog to Stalled/Waiting on the Maps-Sprint board.
RobH closed this task as Declined.Jun 22 2015, 10:30 PM
RobH claimed this task.
RobH added a subscriber: RobH.

I'm going to go ahead and decline this outright (rather than resolve as @Yurik suggests), since if I'm incorrect, anyone can re-open the task easily enough. We didn't really do the things requested, hence declined.

yuvipanda reopened this task as Open.Jun 22 2015, 11:33 PM

It should still happen at some point - yurik isn't the only user of the machine and others will appreciate the upgrade as well.

RobH removed RobH as the assignee of this task.Jul 2 2015, 9:24 PM
RobH removed a subscriber: RobH.

This is being done right now :)

Change 225558 had a related patch set uploaded (by Alexandros Kosiaris):
labsdb1004: Reinstall as jessie

https://gerrit.wikimedia.org/r/225558

Change 225562 had a related patch set uploaded (by Alexandros Kosiaris):
Stop labsdb1004 replication to labsdb1005

https://gerrit.wikimedia.org/r/225562

Change 225558 merged by Alexandros Kosiaris:
labsdb1004: Reinstall as jessie

https://gerrit.wikimedia.org/r/225558

Change 225562 merged by Alexandros Kosiaris:
Stop labsdb1004 replication to labsdb1005

https://gerrit.wikimedia.org/r/225562

This seems to be done.

@akosiaris I've manually granted all of labs access with:

host all all 10.0.0.0/8 md5

We should puppetize this.

Actually, it was not done. It was running in a screen session and was interrupted by the restart of postgres to get the above change. I 've restarted it. Yes we should use file_line to puppetize this. My augeas approach at https://phabricator.wikimedia.org/diffusion/OPUP/browse/production/modules/postgresql/manifests/user.pp;11ebc94ab56c629ae6c5e784ec13a678d3c09d2a$73 is unfortunately not the best one around

Oh shit sorry - I did a ps and could not find a screen

Oh shit sorry - I did a ps and could not find a screen :(

akosiaris closed this task as Resolved.Jul 19 2015, 4:11 PM
akosiaris claimed this task.

The restored finished. Minor errors here and there but we should be mostly good to go. Resolving this

scfc reopened this task as Open.Dec 6 2016, 5:53 PM
scfc added a subscriber: scfc.

Reopening this because manifests/site.pp reads:

node 'labsdb1004.eqiad.wmnet' {
    # Bug: T101233
    #$postgres_slave = 'labsdb1005.eqiad.wmnet'
    #$postgres_slave_v4 = '10.64.37.9'

    role(postgres::master, labs::db::slave)
}

node 'labsdb1005.eqiad.wmnet' {
    # Bug: T101233
    # $postgres_master = 'labsdb1004.eqiad.wmnet'
    # include role::postgres::slave
    role(labs::db::master)
}

This was changed by c5e469409bc80133ff5883bb89a8229219a008d5:

commit c5e469409bc80133ff5883bb89a8229219a008d5
Author: Alexandros Kosiaris <akosiaris@wikimedia.org>
Date:   Sat Jul 18 13:43:23 2015 -0500

    Stop labsdb1004 replication to labsdb1005
    
    Upgrading labsdb1004 to jessie for postgres 9.4 means that labsdb1005
    will not be able to replicate. Remove replication until we get
    labsdb1005 updated as well
    
    Bug: T101233
    Change-Id: I5bbf14fed7677237de98ba9700164a77827c0245

I don't know if replication is now enabled (or if even labsdb1005 is still a PostgreSQL slave), but the manifest needs to be updated to reflect the status quo.

akosiaris closed this task as Resolved.Dec 14 2016, 2:07 PM

labsdb1005 is not longer a postgres slave. I 've removed both comments to fix that and no longer give that impression (fixing a few other minor stuff in the process) Re-resolving.