Confusion with puppetdb (storeconfig) and query database

[mike]

Hello,

I have configured puppet and puppetdb (storeconfig) with postgresql and the service run and the collecting  are good for example:

I can make query with the next command and responds ok .

[.........................]

[root@master]# curl -X GET -H 'Accept: application/json' http://127.0.0.1:8080/v2/facts --data-urlencode 'query=["=", "name", "operatingsystem"]'

[ {

  "certname" : "master.example.com",

  "name" : "operatingsystem",

  "value" : "CentOS"

}, {

  "certname" : "node1.example.com",

  "name" : "operatingsystem",

  "value" : "CentOS"

}, {

  "certname" : "node2.example.com",

  "name" : "operatingsystem",

  "value" : "CentOS"

} ]

[.........................]

My confusion is ¿How I can connect to database from shell and view the database structure?

Thanks.

[Ellison Marks]

Like, the postgresql database? First, I have to ask: why you want to go poking around in there?

Anyway, assuming you set it up with user: puppetdb, databasename: puppetdb and the recommended potgresql settings, you can use the psql client, like so:

psql -h 127.0.0.1 puppetdb puppetdb

[Miguel Angel Coa Morales]

Yes I can connect but when i list tables i have the next message "No relations found." 

1. Connect from my node: [root@node1 ~]# psql -h 10.0.0.132 puppetdb puppetdb

2. Show tables: puppetdb=> \d

No relations found.

3. My databases are:

puppetdb=> \l puppetdb

                                  List of databases

   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 puppetdb  | puppetdb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

                                                             : postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres

                                                             : postgres=CTc/postgres

(4 rows)

Note: The 10.0.0.132 is my puppetmaster and server puppetdb with postgres .  ¿Is possible that the information is stored elsewhere?

Thanks!!

[Ken Barber]

Check your /etc/puppetdb/database.ini, it may be configured for
hsqldb. If in doubt provide the file here and we can take a look.

Instructions for configuring this specifically for PostgreSQL are
here: http://docs.puppetlabs.com/puppetdb/1.4/configure.html#using-postgresql

> --
> You received this message because you are subscribed to the Google Groups
> "Puppet Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to puppet-users...@googlegroups.com.
> To post to this group, send email to puppet...@googlegroups.com.
> Visit this group at http://groups.google.com/group/puppet-users.
> For more options, visit https://groups.google.com/groups/opt_out.

[Miguel Angel Coa Morales]

My setting for db I've the next:

[……………..]
[root@master puppet]# cat /etc/puppetdb/conf.d/config.ini | grep -v ^#

[global]
vardir = /var/lib/puppetdb
logging-config = /etc/puppetdb/log4j.properties

resource-query-limit = 20000
[command-processing]

[database]
classname = org.postgresql.Driver
subprotocol = postgresql
subname = //localhost:5432/puppetdb
username = puppetdb
password = puppetdb

[jetty]
port = 8080
[……………..]

My routes.yaml

[……………..]
/etc/puppet/routes.yaml

master:
facts:
terminus: puppetdb
cache: yaml
[……………..]

My /etc/puppet/puppetdb.conf

[……………..]
[main]

server = master.example.com
port = 8081
[……………..]

Thanks.

> You received this message because you are subscribed to a topic in the Google Groups "Puppet Users" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/puppet-users/ELg35FS1fZg/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to puppet-users...@googlegroups.com.

[Ken Barber]

Do you see any active connections? A query like this in pgsql might
give you some information:

SELECT datname,procpid,current_query FROM pg_stat_activity;

On Thu, Aug 22, 2013 at 9:54 AM, Miguel Angel Coa Morales

[Miguel Angel Coa Morales]

Hello Ken,

I make query for check activity and the only database with activity is the postgres. 

postgres=# SELECT datname,pid,query_start from pg_stat_activity;

 datname  | pid  |          query_start

----------+------+-------------------------------

 postgres | 5866 | 2013-08-23 09:17:36.913697-04

(1 row)

Thanks,

El 22-08-2013, a las 15:18, Ken Barber <k...@puppetlabs.com> escribió:

datname

[Miguel Angel Coa Morales]

When i tried to show all config settings i see the value "incorrect" for example "storeconfig" value is "false" and the "dbadapter" is "sqlite3" but i've already configured the postgres for storeconfig.

[root@master manifests]# puppet agent --configprint all| grep dbadapter

dbadapter = sqlite3

[root@master manifests]# puppet agent --configprint all| grep storeconfigs

async_storeconfigs = false

storeconfigs = false

storeconfigs_backend = active_record

thin_storeconfigs = false

¿Why not apply my configuration? 

Thanks. 

El 22-08-2013, a las 15:18, Ken Barber <k...@puppetlabs.com> escribió:

[Ken Barber]

> postgres=# SELECT datname,pid,query_start from pg_stat_activity;
> datname | pid | query_start
> ----------+------+-------------------------------
> postgres | 5866 | 2013-08-23 09:17:36.913697-04
> (1 row)

That pretty much confirms PuppetDB is not connecting to PostgreSQL at all.

Try this, stop puppetdb, and run it in the foreground instead with the
--debug setting:

$ puppetdb-foreground --debug

Now, I'm interested in the initial setup part of the log, can you put
that in a gist so we can take a look? (double check the content
though, make sure it doesn't have any 'sensitive' data in it of course
:-).

> When i tried to show all config settings i see the value "incorrect" for
> example "storeconfig" value is "false" and the "dbadapter" is "sqlite3" but
> i've already configured the postgres for storeconfig.
>
> [root@master manifests]# puppet agent --configprint all| grep dbadapter
> dbadapter = sqlite3
>
> [root@master manifests]# puppet agent --configprint all| grep storeconfigs
> async_storeconfigs = false
> storeconfigs = false
> storeconfigs_backend = active_record
> thin_storeconfigs = false
>
> ¿Why not apply my configuration?

You really want: puppet master --configprint all | grep storeconfigs
... not puppet agent etc.

ken.

[Miguel Angel Coa Morales]

Hello,,

With the debug i've the next log

------------------
[root@master lib]# puppetdb-foreground --debug
2013-08-15 00:37:13,737 DEBUG [main] [puppetlabs.utils] Debug logging enabled
2013-08-15 00:37:13,829 DEBUG [main] [puppetlabs.ssl] Loaded PEM object of type 'class org.bouncycastle.jcajce.provider.asymmetric.x509.X509CertificateObject' from '/etc/puppetdb/ssl/ca.pem'
2013-08-15 00:37:13,847 DEBUG [main] [puppetlabs.ssl] Loaded PEM object of type 'class java.security.KeyPair' from '/etc/puppetdb/ssl/private.pem'
2013-08-15 00:37:13,854 DEBUG [main] [puppetlabs.ssl] Loaded PEM object of type 'class org.bouncycastle.jcajce.provider.asymmetric.x509.X509CertificateObject' from '/etc/puppetdb/ssl/public.pem'
2013-08-15 00:37:13,959 INFO [main] [cli.services] PuppetDB version 1.4.0
2013-08-15 00:37:13,960 DEBUG [main] [bonecp.BoneCPDataSource] JDBC URL = jdbc:postgresql://127.0.0.1:5432/puppetdb, Username = pupetdb, partitions = 1, max (per partition) = 50, min (per partition) = 1, helper threads = 3, idle max age = 60 min, idle test period = 45 min
2013-08-15 00:37:13,992 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 5. Exception: null
2013-08-15 00:37:20,993 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 4. Exception: null
2013-08-15 00:37:27,994 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 3. Exception: null
2013-08-15 00:37:34,995 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 2. Exception: null
2013-08-15 00:37:41,996 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 1. Exception: null
2013-08-15 00:37:48,998 ERROR [BoneCP-pool-watch-thread] [bonecp.ConnectionHandle] Database access problem. Killing off all remaining connections in the connection pool. SQL State = 08001
2013-08-15 00:37:48,999 ERROR [BoneCP-pool-watch-thread] [bonecp.PoolWatchThread] Error in trying to obtain a connection. Retrying in 7000ms
java.sql.SQLException: No suitable driver found for jdbc:postgresql://127.0.0.1:5432/puppetdb
at java.sql.DriverManager.getConnection(DriverManager.java:640)
at java.sql.DriverManager.getConnection(DriverManager.java:200)
at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:256)
at com.jolbox.bonecp.ConnectionHandle.obtainInternalConnection(ConnectionHandle.java:211)
at com.jolbox.bonecp.ConnectionHandle.<init>(ConnectionHandle.java:170)
at com.jolbox.bonecp.PoolWatchThread.fillConnections(PoolWatchThread.java:101)
at com.jolbox.bonecp.PoolWatchThread.run(PoolWatchThread.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:679)
2013-08-15 00:37:56,001 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 5. Exception: null
2013-08-15 00:38:03,002 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 4. Exception: null
2013-08-15 00:38:10,003 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 3. Exception: null
2013-08-15 00:38:17,004 ERROR [BoneCP-pool-watch-thread] [hooks.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 2. Exception: null
--------------

[Ken Barber]

> With the debug i've the next log
>

> 2013-08-15 00:37:13,960 DEBUG [main] [bonecp.BoneCPDataSource] JDBC URL = jdbc:postgresql://127.0.0.1:5432/puppetdb, Username = pupetdb, partitions = 1, max (per partition) = 50, min (per partition) = 1, helper threads = 3, idle max age = 60 min, idle test period = 45 min

The username you probably want here is 'puppetdb' not 'pupetdb' right ...?

> 2013-08-15 00:37:48,999 ERROR [BoneCP-pool-watch-thread] [bonecp.PoolWatchThread] Error in trying to obtain a connection. Retrying in 7000ms
> java.sql.SQLException: No suitable driver found for jdbc:postgresql://127.0.0.1:5432/puppetdb

So you might have a typo somewhere in your configuration for
[database] but looking back in your history I cannot see it, take a
look at mine and make sure it matches:
https://gist.github.com/kbarber/6326050 ... otherwise provide your
database.ini/config.ini again so I can see if there is a mistake. Your
configuration that you provided earlier does not match what you have
in your log (ie. pupetdb) as well ... which is weird.

Just to double check - where did you get the package for PuppetDB? And
what distro/release are you running? I notice all your configuration
is in /etc/puppetdb/conf.d/config.ini ... are there any other files in
your /etc/puppetdb/conf.d? This smells like a non-standard build (we
ship extra files like jetty.ini, database.ini, repl.ini and config.ini
for example).

ken.

[Miguel Angel Coa Morales]

Hello Ken,
I resolved the database problem configure the value connection in the "/etc/puppetdb/conf.d/database.ini" file. Before i've the value in the "/etc/puppetdb/conf.d/config.ini" and not found.

[…………..]
[database]
subname = file:/var/lib/puppetdb/db/db;hsqldb.tx=mvcc;sql.syntax_pgs=true
log-slow-statements = 10

[database]
classname = org.postgresql.Driver
subprotocol = postgresql
subname = //localhost:5432/puppetdb
username = puppetdb
password = puppetdb

[jetty]
port = 8080

[…………..]

Respect to variables "storeconfigs" and "dbadapter" i setting the value in the section [main] of "/etc/puppet/puppet.conf"

[…………..]
[main]
storeconfigs = true
storeconfigs_backend = puppetdb
dbadapter = postgresq
[…………..]

[…………..]
[root@master ~]# puppet agent --configprint all| egrep "dbadapter|storeconfig"
async_storeconfigs = false
dbadapter = postgresql
storeconfigs = true
storeconfigs_backend = puppetdb
thin_storeconfigs = false
[…………..]

Tables database

[…………..]
puppetdb=> \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------+-------+----------
public | catalog_resources | table | puppetdb
public | catalogs | table | puppetdb
public | certname_catalogs | table | puppetdb
public | certname_facts | table | puppetdb
public | certname_facts_metadata | table | puppetdb
public | certnames | table | puppetdb
public | edges | table | puppetdb
public | reports | table | puppetdb
public | resource_events | table | puppetdb
public | resource_params | table | puppetdb
public | schema_migrations | table | puppetdb
(11 rows)

puppetdb=>
[…………..]


Thanks.