[NUUG fiksgatami] Updating FiksGataMi

[Matthew Somerville]

Hi,

On 4 December 2014 at 14:46, Marius Halden <marius.h at lden.org> wrote:
> If I'm going to do the setup in prod I would like some more info about
> the setup procedure (or a recipe like Petter mentioned).

Sure. I could probably create a script for most of it if necessary,
there were just a few manual steps that I neglected to do in the form
of a script, my bad.

>> 1. Ran the install script for fixmystreet
>> 2. Ran the install script for mapit
> I see at least the fixmystreet install script needs some arguments. Do
> you remember which arguments you used so we can keep some consistency
> between dev and prod?

Yes, the scripts were run as follows:
    curl -O https://raw.githubusercontent.com/mysociety/commonlib/master/bin/install-site.sh
    sudo su install-site.sh fixmystreet fms fiksgatami.no
    sudo sh install-site.sh mapit mapit mapit.nuug.no

They ran to completion without problem.

>> 3. Added dev host names to nginx config (and increased server name bucket
>> size to allow that)
> Except server_name, which settings are do we have to change exactly? Is
> the server name bucket you mention called server_names_hash_bucket_size
> in the config?

This was needed because the dev server names I used were long; it
won't be necessary if you're only using shorter server names. If you
do need it, yes, it is that variable in /etc/nginx/nginx.conf (my
change to uncomment it is in commit 25c9b0c7 in /etc).

>> 4. Updated the site configs - e.g. COUNTRY on mapit, ALLOWED_COBRANDS,
>> MAPIT_URL, etc. on FixMyStreet. This will need checking against your
>> current configuration, and may not be correct in all areas.
> Although I have to match it against our current config, some more
> specific pointer to what was done here would be nice.

Sure. In FixMyStreet, after the install script I changed the following lines:

    CONTACT_EMAIL: 'fiksgatami at rt.nuug.no'
    CONTACT_NAME: 'FiksGataMi'
    DO_NOT_REPLY_EMAIL: 'fiksgatami at rt.nuug.no'
    MAPIT_URL: 'http://mapit.nuug.no'
    ALLOWED_COBRANDS:
      - fiksgatami
    AREA_LINKS_FROM_PROBLEMS: '1'

In MapIt:
    COUNTRY: 'NO'

I then ran sudo /etc/init.d/fixmystreet reload and sudo
/etc/init.d/mapit reload to restart the app servers.

>> 5. Disabled the crontab for FixMyStreet! :)
> Was this done because this is a dev system? I assume we would disable it
> on prod while doing the data import and then re-enable it when it's
> done, is this correct?

Yes, that's correct. I didn't want it sending out duplicate
questionnaire emails or anything :-) It is a crontab of the fms user,
so can just be removed entirely and put back after.

>> 6. Imported the database dumps for FixMyStreet and MapIt.
> Was there any issues during the import I should be aware of?

I don't believe so. The only things I considered were making sure the
ownership of the tables was correct for the users on the system - the
dump was all in the 'www-data' user - and that I did not create the
template_postgis database from the dump as the install script had
already created one. What I actually did was search the dump for
\connect by byte to parse out the separate databases and loaded them
in separately:
zgrep -b '^\\\connect' /srv/dbdump-fixmystret-2014-11-18.sql.gz | grep
-m 1 -A 1 "fixmystreet$"
zgrep -b '^\\\connect' /srv/dbdump-fixmystret-2014-11-18.sql.gz | grep
-m 1 -A 1 "mapit$"
zcat /srv/dbdump-fixmystret-2014-11-18.sql.gz | tail -c +1134 | head
-c 564341616 > ~/fixmystreet.sql
zcat /srv/dbdump-fixmystret-2014-11-18.sql.gz | tail -c +564342750 |
head -c 4986354 > ~/mapit.sql

You wouldn't have to do this, I think, as long as you cleaned up the
ownerships afterward, either by switching the ownership of the tables,
or changing how the hosts access the database - up to you.

>> 7. Ran update-schema on FixMyStreet.
> How is this done? Are there some sort of script or other tool to do this
> automatically?

Yes, `bin/cron-wrapper update-schema` to see what it wants to do, and
then `bin/cron-wrapper update-schema --commit` to run it.

When I originally ran it, I had not updated MAPIT_URL, and so it gave
an error at the end (but had worked from a schema point of view, just
not updated the body table names), but with a set MAPIT_URL it should
be fine and run without error.

>> 8. Your old installation of MapIt predates any use of Django migrations, so
>> I had to manually tweak the database to the start of its migrations
>> (renaming some tables and adding the country/type tables), at which point
>> the migrations then ran okay (with the loading of the fixture too).
> Which tables are modified, and how? I think the sql statements used
> during this step would help me a lot.

Sure. I had to do the following to get it to the start of the
migrations. First, some SQL:
    -- Rename tables and sequences
    alter table postcodes_postcode rename to mapit_postcode;
    alter table postcodes_postcode_id_seq rename to mapit_postcode_id_seq;
    alter table postcodes_postcode_areas rename to mapit_postcode_areas;
    alter table postcodes_postcode_areas_id_seq rename to
mapit_postcode_areas_id_seq;
    alter table areas_area rename to mapit_area;
    alter table areas_area_id_seq rename to mapit_area_id_seq;
    alter table areas_code rename to mapit_code;
    alter table areas_code_id_seq rename to mapit_code_id_seq;
    alter table areas_name rename to mapit_name;
    alter table areas_name_id_seq rename to mapit_name_id_seq;
    alter table areas_generation rename to mapit_generation;
    alter table areas_generation_id_seq rename to mapit_generation_id_seq;
    alter table areas_geometry rename to mapit_geometry;
    alter table areas_geometry_id_seq rename to mapit_geometry_id_seq;

    -- Create new tables
    CREATE TABLE "mapit_country" ( "id" serial NOT NULL PRIMARY KEY,
"code" varchar(1) NOT NULL UNIQUE, "name"varchar(100) NOT NULL UNIQUE
);
    CREATE TABLE "mapit_type" ( "id" serial NOT NULL PRIMARY KEY,
"code" varchar(3) NOT NULL UNIQUE, "description" varchar(200) NOT NULL
);

    -- Did this as postgres user, so needed to then change owner, you
might not need to
    alter table mapit_country owner to mapit;
    alter table mapit_country_id_seq owner to mapit;
    alter table mapit_type owner to mapit;
    alter table mapit_type_id_seq owner to mapit;

    -- Update table to use new tables
    alter table mapit_area add type_id integer;
    alter table mapit_area add country_id integer;

At this point I needed some data for these new tables, so I ran the following:
    /var/www/mapit.nuug.no/virtualenv-mapit/bin/python manage.py
syncdb # to get the migration table itself
    /var/www/mapit.nuug.no/virtualenv-mapit/bin/python manage.py
migrate mapit 0001 --fake # We need to fake what we've already done
    /var/www/mapit.nuug.no/virtualenv-mapit/bin/python manage.py
migrate mapit 0002 # Get tables fixture data needs
    /var/www/mapit.nuug.no/virtualenv-mapit/bin/python manage.py
loaddata norway # load in new fixture data

Then back in SQL, now we have stuff in the country/type tables:
    update mapit_area set country_id = (select id from mapit_country
where code=country);
    update mapit_area set type_id = (select id from mapit_type where code=type);
    alter table mapit_area alter type_id set not null;
    alter table mapit_area ADD CONSTRAINT mapit_area_type_id_fkey
FOREIGN KEY (type_id) REFERENCES "mapit_type" ("id") DEFERRABLE
INITIALLY DEFERRED;
    alter table mapit_area ADD CONSTRAINT mapit_area_country_id_fkey
FOREIGN KEY (country_id) REFERENCES "mapit_country" ("id") DEFERRABLE
INITIALLY DEFERRED;
    alter table mapit_area drop country;
    alter table mapit_area drop type;

And then we can hopefully migrate the rest as normal:
    /var/www/mapit.nuug.no/virtualenv-mapit/bin/python manage.py migrate mapit

Sorry that's a bit of a mess, to do with the fixture data needing new
tables that don't yet exist because you need a migration to get them
etc, so they have to be inserted at just the right point. Let me know
if you have any questions about anything.

ATB,
Matthew