SetupTasks » History » Version 10
Version 9 (Chris Cannam, 2010-12-07 08:43 PM) → Version 10/36 (Chris Cannam, 2010-12-07 09:19 PM)
h1. Setup on new server tasks
# -Install Apache, Postgres, Varnish, etc-
# -Install "Ruby EE":http://www.rubyenterpriseedition.com/ and "Phusion Passenger":http://modrails.org/-
# -Install (via gem) rake, rails version 2.3.5, pg, rmagick, ruby-openid-
# -Clone current soundsoftware code-
# -Set up postgresql user and initialise database-
# -Redmine initialisation-
# -Redmine permissions and ownership-
# -Basic Apache config for site on :81-
# -Varnish config for :80->:81-
# -PHP5 via fcgi-
# -Basic Drupal install-
# Initial database load
# Pull across initial hg repos
# Configure SSL server
# Set up hgwebdir, reposman-soundsoftware, soundsoftware Apache auth (depend on SSL, db, web service key in db etc)
# Pull across remaining bits of redmine server that are not in hg (fonts, files, etc)
# Pull across Drupal theme etc
# Ensure machine can be routed to!
# Set up test instances
in the future: put old host in admin mode, re-point dns, final hg / database / redmine files update
h2. 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":http://pgfoundry.org/projects/mysql2pgsql/ does a reasonable job of converting the data, as far as it goes. Some problems remain:
<pre>$ 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
$
</pre>
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.
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?
# -Install Apache, Postgres, Varnish, etc-
# -Install "Ruby EE":http://www.rubyenterpriseedition.com/ and "Phusion Passenger":http://modrails.org/-
# -Install (via gem) rake, rails version 2.3.5, pg, rmagick, ruby-openid-
# -Clone current soundsoftware code-
# -Set up postgresql user and initialise database-
# -Redmine initialisation-
# -Redmine permissions and ownership-
# -Basic Apache config for site on :81-
# -Varnish config for :80->:81-
# -PHP5 via fcgi-
# -Basic Drupal install-
# Initial database load
# Pull across initial hg repos
# Configure SSL server
# Set up hgwebdir, reposman-soundsoftware, soundsoftware Apache auth (depend on SSL, db, web service key in db etc)
# Pull across remaining bits of redmine server that are not in hg (fonts, files, etc)
# Pull across Drupal theme etc
# Ensure machine can be routed to!
# Set up test instances
in the future: put old host in admin mode, re-point dns, final hg / database / redmine files update
h2. 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":http://pgfoundry.org/projects/mysql2pgsql/ does a reasonable job of converting the data, as far as it goes. Some problems remain:
<pre>$ 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
$
</pre>
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.
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?