History

Setup on new server tasks

  1. Install Apache, Postgres, Varnish, etc
  2. Install Ruby EE and Phusion Passenger
  3. Install (via gem) rake, rails version 2.3.5, pg, rmagick, ruby-openid
  4. Clone current soundsoftware code
  5. Set up postgresql user and initialise database
  6. Redmine initialisation
  7. Redmine permissions and ownership
  8. Basic Apache config for site on :81
  9. Varnish config for :80->:81
  10. PHP5 via fcgi
  11. Basic Drupal install
  12. Initial database load for Redmine
  13. Initial database load for Drupal
  14. Pull across initial hg repos
  15. Configure SSL server
  16. Pull across remaining bits of redmine server that are not in hg (fonts, files, etc)
  17. Install hgwebdir, reposman, soundsoftware Apache auth (depend on SSL, db, web service key in db etc)
  18. Configure and test the above (cannot test auth until LDAP is working)
  19. Cron jobs
  20. Pull across Drupal theme etc
  21. Make sure we can connect to LDAP!
  22. Backup regime
  23. Ensure machine can be routed to!

in the future: put old host in admin mode, re-point dns, final hg / database / redmine files update

further in the future: transplant across test servers, Survey service

even further in the future: switch off kakapo's https service, remove old sites from there

Passenger compatibility

The site doesn't work properly with Passenger 3 -- every page is rendered without stylesheet, and the logs fill up with

ActionController::RoutingError (No route matches "/dispatch.cgi" with {:method=>:get}):
  passenger (3.0.0) lib/phusion_passenger/rack/request_handler.rb:96:in `process_request'
  passenger (3.0.0) lib/phusion_passenger/abstract_request_handler.rb:513:in `accept_and_process_next_request'
...

(what's it doing in rack/ anyway? This isn't a Rack app)

Reverting to Passenger 2.2 fixes it. Just noting this here as a reminder to report / investigate...

After import

We have to clear the repositories cache (if we have imported these tables).

delete from changesets;
delete from changes;

(Do we have to rebuild indexes? With MySQL it was necessary to run optimize table on these afterwards, followed by flush tables. Probably no harm in at least running REINDEX TABLE on each table)

MySQL to Postgres migration notes

Just to make things more fun, we're migrating the existing databases from MySQL to pgsql.

For Redmine, we don't attempt to dump and reload the schema -- more sensible to use Redmine to set up the schema (in case it has any database-specific set up) and then massage the data as we load it. We just dump and reload the row data. That way we only have to fix problems once.

mysql2pgsql does a reasonable job of converting the data, as far as it goes. Some problems remain:

$ grep ERROR log/dbload.log |sort|uniq
ERROR:  column "admin" is of type boolean but expression is of type integer
ERROR:  column "assignable" is of type boolean but expression is of type integer
ERROR:  column "hide_mail" is of type boolean but expression is of type integer
ERROR:  column "is_closed" is of type boolean but expression is of type integer
ERROR:  column "is_default" is of type boolean but expression is of type integer
ERROR:  column "is_in_chlog" is of type boolean but expression is of type integer
ERROR:  column "is_public" is of type boolean but expression is of type integer
ERROR:  column "mail_notification" is of type boolean but expression is of type integer
ERROR:  column "onthefly_register" is of type boolean but expression is of type integer
ERROR:  column "protected" is of type boolean but expression is of type integer
ERROR:  duplicate key value violates unique constraint "unique_schema_migrations" 
ERROR:  INSERT has more expressions than target columns
ERROR:  invalid input syntax for type bytea
$

The boolean ones are understandable -- mysqldump writes them out as ones and zeros and you can't tell they were intended to be boolean without the schema. We can fix them readily enough in a script once we identify which table they come from.

The duplicate key values we don't care about -- in fact we're glad they're rejected, because they come from the Redmine db:migrate which we've already run anyway.

"INSERT has more expressions"... that's a strange one. Is our schema out of date?

This is from the repositories table. Columns:

 id | project_id | url | login | password | root_url | type

Values being inserted (example):

INSERT INTO "repositories" VALUES (1,2,E'/var/hg/svcore',E'',E'',E'/var/hg/svcore',E'Mercurial',E'--- {}\n\n');

So we are indeed missing something -- the checkout_settings column. We forgot to run the plugins migration (rake db:migrate:plugins).

Fixing that, we have a new one instead:

ERROR:  duplicate key value violates unique constraint "settings_pkey"

The problem is that the blimmin' redmine-checkout plugin has inserted its own settings row, which appears only later in the imported dump. We need to delete from settings before we can load.

Later,

ERROR:  duplicate key value violates unique constraint "roles_pkey" 
ERROR:  duplicate key value violates unique constraint "users_pkey"

The roles one doesn't really matter (we're inserting the same stuff anyway) but the users one does, as it has all of admin's details. So we have to do this after the migrate steps but before the data load:

delete from settings
delete from roles
delete from users

Problematic Boolean columns

We'll just script a quick hack to convert these based on counting the commas for column numbers. Hence we need to know which table each belongs to, which column it is (counting from the left), and whether any of the fields to the left can contain commas themselves.

  • admin: table users col 8 (counting from 1); no
  • assignable: table roles col 4; no
  • hide_mail: table user_preferences col 4; no
  • is_closed: table issue_statuses col 3; no
  • is_default: table issue_statuses col 4; table enumerations col 4; both no
  • is_in_chlog: table trackers col 3; no
  • is_in_roadmap: table trackers col 5; no
  • is_public: table projects col 5; yes. This is likely to be first occurrence of ,1, or ,0, however
  • mail_notification: table members col 5; no (there is also a column of this name in users but it is not boolean)
  • onthefly_register: table auth_sources col 13; yes. This is likely to be first occurrence of ,1, or ,0, however and we only have one row in auth_sources at the moment anyway
  • tls: table auth_sources col 14; yes. Last column, so look for ,0) or ,1) I guess. Yummy
  • protected: table wiki_pages col 5; no

Errors from Drupal database import

All of the foregoing is to do with the Redmine database. Here's what we get for Drupal.

Our first load failed because of something to do with the cache table -- but we don't want to load the cache anyway (do we?). After eliminating the cache from the dump and trying again,

ERROR:  duplicate key value violates unique constraint "actions_pkey" 
ERROR:  duplicate key value violates unique constraint "authmap_pkey" 
ERROR:  duplicate key value violates unique constraint "blocks_pkey" 
ERROR:  duplicate key value violates unique constraint "blocks_roles_pkey" 
ERROR:  duplicate key value violates unique constraint "boxes_pkey" 
ERROR:  duplicate key value violates unique constraint "cache_menu_pkey" 
ERROR:  duplicate key value violates unique constraint "filter_formats_pkey" 
ERROR:  duplicate key value violates unique constraint "filters_pkey" 
ERROR:  duplicate key value violates unique constraint "menu_custom_pkey" 
ERROR:  duplicate key value violates unique constraint "menu_links_pkey" 
ERROR:  duplicate key value violates unique constraint "menu_router_pkey" 
ERROR:  duplicate key value violates unique constraint "node_access_pkey" 
ERROR:  duplicate key value violates unique constraint "node_type_pkey" 
ERROR:  duplicate key value violates unique constraint "role_pkey" 
ERROR:  duplicate key value violates unique constraint "sessions_pkey" 
ERROR:  duplicate key value violates unique constraint "system_pkey" 
ERROR:  duplicate key value violates unique constraint "users_pkey" 
ERROR:  duplicate key value violates unique constraint "variable_pkey" 
ERROR:  relation "cache_views" does not exist
ERROR:  relation "image_attach" does not exist
ERROR:  relation "image" does not exist
ERROR:  relation "upload" does not exist
ERROR:  relation "views_display" does not exist
ERROR:  relation "views_view" does not exist

The first few were probably caused by the previous partial load with cache (I didn't clear out in between).

The rest by missing modules?