[PDB-2259] Initial migration broken with non-english postgresql Created: 2015/12/16  Updated: 2016/01/11  Resolved: 2015/12/23

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: PDB 3.2.2
Fix Version/s: PDB 3.2.3

Type: Bug Priority: Normal
Reporter: Mok Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Template:
Story Points: 1
Sprint: PuppetDB 2015-12-30

 Description   

Hi,

I have a problem when I try to install PuppetDB from scratch with PostgreSQL.

Indeed, I have installed PuppetDB 3.2 for Puppet 4 on the same server as my puppet master (standalone mode) thanks to the puppetdb module :

 
puppet module install -i /data/puppetlabs/code/modules puppetlabs-puppetdb

I want to use postgreSQL so my configuration file /etc/puppetlabs/code/environments/production/manifests/node.pp contains :

node puppetmaster {
 
# Configure puppetdb and its underlying database
 class { 'puppetdb':
       listen_address => 'puppetmaster'
 }
 # Configure the puppet master to use puppetdb
 class { 'puppetdb::master::config':}
...
}

But I get the following error in my puppetdb logfile /var/log/puppetlabs/puppetdb/puppetdb.log :

2015-12-14 22:08:44,273 INFO  [o.e.j.u.log] Logging initialized @51418ms
2015-12-14 22:08:44,422 INFO  [p.t.s.w.jetty9-core] Removing buggy security provider SunPKCS11-NSS version 1.7
2015-12-14 22:08:53,169 INFO  [p.t.s.w.jetty9-service] Initializing web server(s).
2015-12-14 22:08:53,396 INFO  [p.t.s.n.nrepl-service] nREPL service disabled, not starting
2015-12-14 22:08:53,407 INFO  [p.p.pdb-routing] Starting PuppetDB, entering maintenance mode
2015-12-14 22:08:53,468 INFO  [p.t.s.w.jetty9-service] Starting web server(s).
2015-12-14 22:08:54,126 INFO  [p.t.s.w.jetty9-core] Starting web server.
2015-12-14 22:08:54,139 INFO  [o.e.j.s.Server] jetty-9.2.z-SNAPSHOT
2015-12-14 22:08:54,372 INFO  [o.e.j.s.h.ContextHandler] Started o.e.j.s.h.ContextHandler@5ac258d{/pdb,null,AVAILABLE}
2015-12-14 22:08:54,414 INFO  [o.e.j.s.ServerConnector] Started ServerConnector@69a594a1{HTTP/1.1}{puppetmaster.localdomain:8080}
2015-12-14 22:08:54,578 INFO  [o.e.j.s.ServerConnector] Started ServerConnector@4f5acf63{SSL-HTTP/1.1}{0.0.0.0:8081}
2015-12-14 22:08:54,578 INFO  [o.e.j.s.Server] Started @61726ms
2015-12-14 22:08:54,582 INFO  [p.p.metrics] Starting metrics server
2015-12-14 22:08:54,590 INFO  [o.e.j.s.h.ContextHandler] Started o.e.j.s.h.ContextHandler@5cc2bce4{/metrics,null,AVAILABLE}
2015-12-14 22:08:54,679 INFO  [p.p.c.services] PuppetDB version 3.2.2
2015-12-14 22:08:54,947 INFO  [c.j.b.BoneCP] Shutting down connection pool...
2015-12-14 22:08:54,951 INFO  [c.j.b.BoneCP] Connection pool has been shutdown.
2015-12-14 22:08:54,956 ERROR [p.t.internal] Error during service start!!!
*org.postgresql.util.PSQLException: ERREUR: la relation « schema_migrations » n'existe pas
  Position : 21*

When I check directly in the psql database, I can't find any relation (my postgresql is installed in french) :

 
postgres=# \l
                                  Liste des bases de données
    Nom    | Propriétaire | Encodage | Collationnement | Type caract. |    Droits d'accès     
-----------+--------------+----------+-----------------+--------------+-----------------------
 postgres  | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | 
 puppetdb  | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =T/postgres          +
           |              |          |                 |              | postgres=CTc/postgres+
           |              |          |                 |              | puppetdb=CTc/postgres
 template0 | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
           |              |          |                 |              | postgres=CTc/postgres
 template1 | postgres     | UTF8     | fr_FR.UTF-8     | fr_FR.UTF-8  | =c/postgres          +
           |              |          |                 |              | postgres=CTc/postgres
(4 lignes)
 
postgres=# \c puppetdb
puppetdb=# \d
Aucune relation trouvée.

When I take a look to other log files, I have no additionnal information :

  • /var/log/puppetlabs/puppetdb/puppetdb-daemon.log :

org.postgresql.util.PSQLException: ERREUR: la relation « schema_migrations » n'existe pas
  Position : 21

  • /var/log/postgresql/postgresql-9.4-main.log :

2015-12-14 22:08:54 CET [5449-1] puppetdb@puppetdb ERREUR:  la relation « schema_migrations » n'existe pas au caractère 21
2015-12-14 22:08:54 CET [5449-2] puppetdb@puppetdb INSTRUCTION :  SELECT version FROM schema_migrations ORDER BY version

  • the /opt/puppetlabs/server/data/puppetdb folder is empty (value of vardir in /etc/puppetlabs/puppetdb/conf.d)

I don't understand why my database is empty because I thought puppetdb was responsible of this task.

When I use the embedded HSQLDB I have no problem.

My version of postrgresql is :

# psql -V
psql (PostgreSQL) 9.4.5

I can connect to it with the puppetdb user :

# sudo -u puppetdb psql -h localhost -W
Mot de passe : 
psql (9.4.5)

My version of puppet is :

# puppet -V
4.3.1

My version of puppetdb is :

# /opt/puppetlabs/bin/puppetdb --version
puppetdb version: 3.2.2

Do you have any idea about this issue ?

Thanks a lot for your help,



 Comments   
Comment by Mok [ 2015/12/17 ]

For this time, I have found a temporary workaround.

In PostgreSQL I executed the following requests :

CREATE TABLE certnames (name TEXT PRIMARY KEY);
CREATE TABLE catalogs (hash VARCHAR(40) NOT NULL PRIMARY KEY, api_version INT NOT NULL, catalog_version TEXT NOT NULL);
CREATE TABLE certname_catalogs (certname TEXT UNIQUE REFERENCES certnames(name) ON DELETE CASCADE, catalog VARCHAR(40) UNIQUE REFERENCES catalogs(hash)  ON DELETE CASCADE, PRIMARY KEY (certname, catalog));
CREATE TABLE tags (catalog VARCHAR(40) REFERENCES catalogs(hash) ON DELETE CASCADE, name TEXT NOT NULL, PRIMARY KEY (catalog, name));
CREATE TABLE classes (catalog VARCHAR(40) REFERENCES catalogs(hash) ON DELETE CASCADE, name TEXT NOT NULL, PRIMARY KEY (catalog, name));
CREATE TABLE catalog_resources (catalog VARCHAR(40) REFERENCES catalogs(hash) ON DELETE CASCADE, resource VARCHAR(40), type TEXT NOT NULL, title TEXT NOT NULL, tags TEXT ARRAY[65535] NOT NULL, exported BOOLEAN NOT NULL, sourcefile TEXT, sourceline INT, PRIMARY KEY (catalog, resource));
CREATE TABLE resource_params (resource VARCHAR(40), name TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (resource, name));
CREATE TABLE edges (catalog VARCHAR(40) REFERENCES catalogs(hash) ON DELETE CASCADE, source VARCHAR(40), target VARCHAR(40), type TEXT NOT NULL, PRIMARY KEY (catalog, source, target, type));
CREATE TABLE schema_migrations (version INT NOT NULL PRIMARY KEY, time TIMESTAMP NOT NULL);
CREATE INDEX idx_catalogs_hash ON catalogs(hash);
CREATE INDEX idx_certname_catalogs_certname ON certname_catalogs(certname);
CREATE TABLE certname_facts (certname TEXT REFERENCES certnames(name) ON DELETE CASCADE, fact TEXT NOT NULL, value TEXT NOT NULL, PRIMARY KEY (certname, fact));
CREATE INDEX idx_resources_params_resource ON resource_params(resource);
CREATE INDEX idx_resources_params_name ON resource_params(name);
CREATE INDEX idx_certname_facts_certname ON certname_facts(certname);
CREATE INDEX idx_certname_facts_fact ON certname_facts(fact);
CREATE INDEX idx_catalog_resources_type ON catalog_resources(type);
CREATE INDEX idx_catalog_resources_resource ON catalog_resources(resource);
CREATE INDEX idx_catalog_resources_tags ON catalog_resources(tags);
INSERT INTO SCHEMA_MIGRATIONS VALUES(1,'2015-09-27 12:53:56.277000');

Then I restarted puppetDB.

Comment by Mok [ 2015/12/17 ]

In src/puppetlabs/puppetdb/scf/migrate.clj : applied-migrations seems to find current migration version in schema_migrations table whereas this table doesn't exist yet.
Indeed, in the puppetdb init workflow, initialize-store is called after applied-migrations which generate a SQL exception when you are trying to do this from scratch (without existing database).

See on stable branch : https://github.com/puppetlabs/puppetdb/blob/stable/src/puppetlabs/puppetdb/scf/migrate.clj

Comment by Russell Mull [ 2015/12/21 ]

Mok Thanks for reporting this issue, and doubly thanks for all your investigation and the workaround. I was able to recreate it pretty easily; we'll get a fixed release out as soon as we can.

Comment by Wyatt Alt [ 2016/01/11 ]

Mok we've released a fix for this today in 3.2.3. Thanks again for your help.

Generated at Sun Jun 16 15:44:22 PDT 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.