795223 - support postgres 9.2 for vagrant/puppet/dev (vagrant broken on master)

Status: RESOLVED FIXED

(Socorro :: Infra, task)

Description

[Robert Helmer [:rhelmer]]

Two things are keeping this from working:

1) 9.0 was removed from the postgres backports PPA, and socorro doesn't work on > 9.0 yet
2) the schema dump for 18 hasn't been generated yet

I think I know what we need to do to fix these now. We've been stuck since we depend on the schema dump to bootstrap the DB, but this comes from postgres 9.0 and we are having CITEXT problems when loading that as-is on postgres 9.2 (the migration scripts that run with "CREATE EXTENSION citext FROM unpackaged" don't seem to work, we get collation errors when running Socorro unit tests)

Josh suggested having the setupdb script do "CREATE EXTENSION citext" first, then loading the schema and ignoring the errors.. the script is already able to detect postgres version and can ignore specific errors, so as long as this approach works it should be easy to do.

I'll test this and put up a PR if it works.

Comment 1

[Robert Helmer [:rhelmer]]

(In reply to Robert Helmer [:rhelmer] from comment #0)
> Josh suggested having the setupdb script do "CREATE EXTENSION citext" first,
> then loading the schema and ignoring the errors.. the script is already able
> to detect postgres version and can ignore specific errors, so as long as
> this approach works it should be easy to do.
> 
> I'll test this and put up a PR if it works.

So this does work, but it generates so many errors that it's not worth making the script handle this. Instead I will do this process manually and then pg_dump the result, which gives us a clean 9.2 schema.

This means that you probably won't be able to use sql/schema.sql to load anything less than 9.2 ... I think this is probably ok until we have a new solution for bootstrapping new DBs.

Comment 2

[[github robot]]

Commits pushed to master at https://github.com/mozilla/socorro

https://github.com/mozilla/socorro/commit/cc56db2dafbf1c131127ef63af9c211c48d3c27e
bug 795223 - support postgres 9.2 for dev

https://github.com/mozilla/socorro/commit/a229a53f65f55027b6e7fc94d6f41cc0dec6d8c0
Merge pull request #849 from rhelmer/bug795223-use-pg92

Bug795223 use postgres 9.2 for dev