Project

General

Profile

Database configuration » History » Version 6

« Previous - Version 6/7 (diff) - Next » - Current version
Greg Sutcliffe, 12/12/2012 10:52 AM


Database configuration

Where is the DB?!

By default, Foreman will use sqlite3 as a database, its configuration can be found at

config/database.yml

By default, the database can be found at the db subdirectory.
Foreman is a rails application, therefor, anything that is supported under RAILS (sqlite, mysql, postgresql, ...) can be used.
At this time, Oracle DB is known to not work. Patches are welcome!

I want to use MySQL

Edit your config/database.yml and modify:

production:
  adapter: mysql
  database: puppet
  username: puppet
  password: password
  host: localhost
  socket: "/var/run/mysqld/mysqld.sock" 

If you use foreman 1.0 with foreman-mysql2 use 'adapter: mysql2' instead

afterwards you would need to re populate your database, simply execute extras/dbmigrate script.

I want to use PostGreSQL

Edit your config/database.yml and modify:

production:
  adapter: postgresql
  database: foreman
  username: foreman
  password: password
  host: localhost

I want to switch from SQlite to Mysql/Psql and maintain my data

We have a rake task for this. First setup your database.yml to have the sqlite db as production and the mysql/psql db as dev:

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000

development:
  adapter: postgresql
  database: foreman
  username: foreman
  password: password
  host: localhost

Now migrate both dbs so they're consistent:

bundle exec rake db:migrate RAILS_ENV=production
bundle exec rake db:migrate RAILS_ENV=development

Now move the data to the new db

bundle exec rake db:convert:prod2dev

Special note for migrating to Postgres

The psql sequence numbers will be wrong after the prod2dev execution. You can fix them like this:

cat <<EOF > reset.sql
SELECT  'SELECT SETVAL(' ||quote_literal(S.relname)|| ', MAX(' ||quote_ident(C.attname)|| ') ) FROM ' ||quote_ident(T.relname)|| ';'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
ORDER BY S.relname;
EOF
psql -Atq -f reset.sql -o temp foreman
psql -f temp foreman
rm temp reset.sql

(big thanks to http://wiki.postgresql.org/wiki/Fixing_Sequences for the fix)