Migrating Foreman database from 1.1stable to 1.4

Hello,

Trying to move from my current Foreman/Puppet server (Foreman 1.1stable)
to a brand new Foreman 1.4 server.

Installation via foreman-installer OK specifying MySQL database.

Now I would like to load the current Foreman 1.1 database into the new
server. I dumped and imported the data this way :

Foreman1.1> mysqldump -uforeman -pxxxxxx --single-transaction --opt
foreman > export-06mai2014.sql

Foreman1.4> mysql -u root -pxxxxx foreman < export-06mai2014.sql

And tried the migration script :

sudo -u foreman /usr/share/foreman/extras/dbmigrate

but it fails at this point :

[…]
== RemoveDuplicateSnippets: migrating

··· ======================================== rake aborted! An error has occurred, all later migrations canceled:

Mysql2::Error: Unknown column ‘hostgroups.label’ in ‘order clause’:
SELECT hostgroups.* FROM hostgroups INNER JOIN
template_combinations ON hostgroups.id =
template_combinations.hostgroup_id WHERE
template_combinations.config_template_id = 15 ORDER BY hostgroups.label

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Any idea ?

Thanks

JM

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

Hello,

Found a solution here :

http://projects.theforeman.org/projects/foreman/wiki/Migration_043_to_14

It is not enough, I had also to comment-out the action of the following
scripts because tables already existed :

/usr/share/foreman/db/migrate/20131224153518_create_compute_profiles.rb
/usr/share/foreman/db/migrate/20131224153743_create_compute_attributes.rb

Then dbmigrate succeeded

I hope I can trust the result.

Now to put this new server in production replacing the current one,
I have 2 choices :

a) copy all /var/lib/puppet/ca/ssl directory from the current server
to the new; stop the current server, give its name and IP to the
newly installed server, probably rerun foreman-installer and that's
it.

b) copy all /var/lib/puppet/ca/ssl directory from the current server
somewhere; stop the current server; restart installation from
scratch on the new server with the right IP and name; redo the
DB load and migrate operation.

What you people would you prefer ? a) or b) similar experiences in
replacing a foreman/puppet master server by a new one ?

Thank you

JM

··· On 05/06/2014 04:25 PM, Jean-Michel Barbet wrote:

And tried the migration script :

sudo -u foreman /usr/share/foreman/extras/dbmigrate

but it fails at this point :

[…]
== RemoveDuplicateSnippets: migrating

rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: Unknown column ‘hostgroups.label’ in ‘order clause’:
SELECT hostgroups.* FROM hostgroups INNER JOIN
template_combinations ON hostgroups.id =
template_combinations.hostgroup_id WHERE
template_combinations.config_template_id = 15 ORDER BY hostgroups.label

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

>
>> And tried the migration script :
>>
>> sudo -u foreman /usr/share/foreman/extras/dbmigrate
>>
>> but it fails at this point :
>>
>> […]
>> == RemoveDuplicateSnippets: migrating
>> ========================================
>> rake aborted!
>> An error has occurred, all later migrations canceled:
>>
>> Mysql2::Error: Unknown column 'hostgroups.label' in 'order clause':
>> SELECT hostgroups.* FROM hostgroups INNER JOIN
>> template_combinations ON hostgroups.id =
>> template_combinations.hostgroup_id WHERE
>> template_combinations.config_template_id = 15 ORDER BY hostgroups.label
>
> Hello,
>
> Found a solution here :
>
> Migration 043 to 14 - Foreman
>
> It is not enough, I had also to comment-out the action of the following
> scripts because tables already existed :
>
> /usr/share/foreman/db/migrate/20131224153518_create_compute_profiles.rb
> /usr/share/foreman/db/migrate/20131224153743_create_compute_attributes.rb
>
> Then dbmigrate succeeded
>
> I hope I can trust the result.

Strange, but sounds OK. It sounds like some of the migrations ran twice

  • maybe the tables were created once, and the hostgroups.label column
    had already been renamed to 'title' (this happened relatively recently).

> Now to put this new server in production replacing the current one,
> I have 2 choices :
>
> a) copy all /var/lib/puppet/ca/ssl directory from the current server
> to the new; stop the current server, give its name and IP to the
> newly installed server, probably rerun foreman-installer and that's
> it.
>
> b) copy all /var/lib/puppet/ca/ssl directory from the current server
> somewhere; stop the current server; restart installation from
> scratch on the new server with the right IP and name; redo the
> DB load and migrate operation.
>
> What you people would you prefer ? a) or b) similar experiences in
> replacing a foreman/puppet master server by a new one ?

Not sure, but I'd probably opt for a new name as you're less likely to
run into strange conflicts. The new name can still be signed from the
existing CA (and you can even add an alt-name to the cert for the old
hostname or a CNAME if necessary), so little needs to change.

··· On 07/05/14 09:02, Jean-Michel Barbet wrote: > On 05/06/2014 04:25 PM, Jean-Michel Barbet wrote:


Dominic Cleal
Red Hat Engineering

Thank you Dominic,

The puppet managed machines have the puppet master name in their
/etc/puppet/puppet.conf. If I put a new server with a new name, I would
have to modify all clients… Unless I am missing sth.

JM

··· On 05/07/2014 10:38 AM, Dominic Cleal wrote:

Not sure, but I’d probably opt for a new name as you’re less likely to
run into strange conflicts. The new name can still be signed from the
existing CA (and you can even add an alt-name to the cert for the old
hostname or a CNAME if necessary), so little needs to change.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

Hello all,

This is actually a report, it seems that I succeeded installing a new
Foreman 1.4/Puppetmaster server and import my data in it.

Before this I attempted to install directly a Foreman 1.5 server and
import my data but the db:migrate script gave so many errors that I
gave up and decided to install Foreman 1.4 and consider updating to 1.5
later.

However, here is a list of issues I came across, some already reported
here :

  1. db:migrate

Mysql2::Error: Unknown column 'hostgroups.label' in 'order clause':
SELECT hostgroups.* FROM hostgroups INNER JOIN
template_combinations ON hostgroups.id =
template_combinations.hostgroup_id WHERE
template_combinations.config_template_id = 15 ORDER BY hostgroups.label

=> Solution: ALTER TABLE hostgroups ADD label character varying(255);

Mysql2::Error: Duplicate column name 'label': ALTER TABLE hostgroups
ADD labe l varchar(255)

=> Solution: ALTER TABLE hostgroups DROP label;

Mysql2::Error: Table 'compute_profiles' already exists: CREATE TABLE
compute_profiles (id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, name varchar(255), created_at datetime NOT NULL, updated_at
datetime NOT NULL) ENGINE=InnoDB

=> Solution: comment-out code in :
/usr/share/foreman/db/migrate/20131224153518_create_compute_profiles.rb

Mysql2::Error: Table 'compute_attributes' already exists: CREATE TABLE
compute_attributes (id int(11) DEFAULT NULL auto_increment PRIMARY
KEY, compute_profile_id int(11), compute_resource_id int(11),
name varchar(255), vm_attrs text, created_at datetime NOT NULL,
updated_at datetime NOT NULL) ENGINE=InnoDB

=> Solution: drop table compute_attributes;

After these 4 failures, db:migrate completed.
  1. After some time the external node classifier /etc/puppet/node.rb
    would fail. This was because I forgot to launch db:seed as
    explained : Bug #4303: Foreman fails to import reports after upgrade to 1.4 - Foreman

=> BTW: What is the role of this piece of code db:seed ?

  1. I have this bug : http://projects.puppetlabs.com/issues/19680

  2. Puppet-Proxy import classes works but there are classes that are
    alternatively found modified each time to add even if I do it.

    Exemple :
    a) Run Import-from-Puppet-Proxy :
    [X] development Update: os
    [X] jmichel Update: os
    [X] pierrick Update: os
    [X] production Update: os
    OK (import)

    b) Run Import-from-Puppet-Proxy :
    [X] jerome Update: os
    OK (import)

    Then if I run again Import-from-Puppet-Proxy, it starts again at a),
    then b), etc.

Thanks for the help that I already got from the list and from Dominic
specifically.

Jean-Michel

··· On 05/07/2014 10:38 AM, Dominic Cleal wrote:

Now to put this new server in production replacing the current one,
I have 2 choices :

a) copy all /var/lib/puppet/ca/ssl directory from the current server
to the new; stop the current server, give its name and IP to the
newly installed server, probably rerun foreman-installer and that’s
it.

b) copy all /var/lib/puppet/ca/ssl directory from the current server
somewhere; stop the current server; restart installation from
scratch on the new server with the right IP and name; redo the
DB load and migrate operation.

What you people would you prefer ? a) or b) similar experiences in
replacing a foreman/puppet master server by a new one ?

Not sure, but I’d probably opt for a new name as you’re less likely to
run into strange conflicts. The new name can still be signed from the
existing CA (and you can even add an alt-name to the cert for the old
hostname or a CNAME if necessary), so little needs to change.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

That's right, maybe option 1 then if that's difficult for you. Or it's
a good opportunity to switch over to a CNAME and adding this as an alt
name in your Puppet master's certificate.

··· On 07/05/14 10:50, Jean-Michel Barbet wrote: > On 05/07/2014 10:38 AM, Dominic Cleal wrote: > >> Not sure, but I'd probably opt for a new name as you're less likely to >> run into strange conflicts. The new name can still be signed from the >> existing CA (and you can even add an alt-name to the cert for the old >> hostname or a CNAME if necessary), so little needs to change. > > Thank you Dominic, > > The puppet managed machines have the puppet master name in their > /etc/puppet/puppet.conf. If I put a new server with a new name, I would > have to modify all clients... Unless I am missing sth.


Dominic Cleal
Red Hat Engineering

You mean an IP alias ? I have one already but the newly installed
machines get the real name instead of the alias. Where in Foreman
should I declare the IP alias so that this is what appears in the
/etc/puppet/puppet.conf on the clients ? (I would still have to
create a new cert for my puppet master with alternate subject, I know)

JM

··· On 05/07/2014 11:52 AM, Dominic Cleal wrote:

That’s right, maybe option 1 then if that’s difficult for you. Or it’s
a good opportunity to switch over to a CNAME and adding this as an alt
name in your Puppet master’s certificate.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

When provisioning, the hostname of the registered Puppet smart proxy is
used to populate puppet.conf. So if you change the URL via
Infrastructure > Smart proxies to the alias, this should be what's
referenced.

··· On 07/05/14 10:57, Jean-Michel Barbet wrote: > On 05/07/2014 11:52 AM, Dominic Cleal wrote: > >> That's right, maybe option 1 then if that's difficult for you. Or it's >> a good opportunity to switch over to a CNAME and adding this as an alt >> name in your Puppet master's certificate. > > You mean an IP alias ? I have one already but the newly installed > machines get the real name instead of the alias. Where in Foreman > should I declare the IP alias so that this is what appears in the > /etc/puppet/puppet.conf on the clients ? (I would still have to > create a new cert for my puppet master with alternate subject, I know)


Dominic Cleal
Red Hat Engineering

Thank you Dominic,

I see, unfortunately, the fact that my current puppetmaster certificate
does not include the alias as an alternate subject is kind of a
showstopper in the sense that I would have to make a new certificate
for the puppet master and (if I understand well) reissue all client
hosts certificates so that they are signed by the new puppet master
certificate. Right ?

JM

··· On 05/07/2014 12:03 PM, Dominic Cleal wrote:

When provisioning, the hostname of the registered Puppet smart proxy is
used to populate puppet.conf. So if you change the URL via
Infrastructure > Smart proxies to the alias, this should be what’s
referenced.

Jean-michel BARBET | Tel: +33 (0)2 51 85 84 86
Laboratoire SUBATECH Nantes France | Fax: +33 (0)2 51 85 84 79
CNRS-IN2P3/Ecole des Mines/Universite | E-Mail: barbet@subatech.in2p3.fr

That shouldn't be necessary. Your "puppet master certificate" is
actually two certificates: the Puppet CA, and a certificate for your
puppetmaster, signed by that CA. By using "puppet cert generate", you
can create more of the second (which are the same as agent certs). The
CA is perfectly reusable, no need to replace it or your existing agent
certs.

··· On 07/05/14 13:52, Jean-Michel Barbet wrote: > On 05/07/2014 12:03 PM, Dominic Cleal wrote: > >> When provisioning, the hostname of the registered Puppet smart proxy is >> used to populate puppet.conf. So if you change the URL via >> Infrastructure > Smart proxies to the alias, this should be what's >> referenced. > > Thank you Dominic, > > I see, unfortunately, the fact that my current puppetmaster certificate > does not include the alias as an alternate subject is kind of a > showstopper in the sense that I would have to make a new certificate > for the puppet master and (if I understand well) reissue all client > hosts certificates so that they are signed by the new puppet master > certificate. Right ?


Dominic Cleal
Red Hat Engineering