puppetdb has no tables

[Tim Dunphy]

Hey all, 

I've installed puppetdb on my puppetmaster. I have puppet-server-3.7.1,  puppetdb-2.2 and puppetdb-terminus-2.2. 

I've setup puppetdb like this:

[root@puppet:/etc/puppet] #cat /etc/puppetdb/conf.d/database.ini

[database]

classname = org.postgresql.Driver

subprotocol = postgresql

subname = //127.0.0.1:5432/puppetdb

username = puppetdb

password = secret

log-slow-statements = 10

[root@puppet:/etc/puppet] #cat /etc/puppet/puppetdb.conf

[main]

server = puppet.jokefire.com

port = 8081

[root@puppet:/etc/puppet] #cat /etc/puppet/routes.yaml

---

master:

  facts:

    terminus: puppetdb

    cache: yaml

[root@puppet:/etc/puppet] #grep storeconfigs puppet.conf

    storeconfigs = true

    storeconfigs_backend = puppetdb

I'm running puppet via passenger and using foreman 1.6.0. And I've selected postgres as the database for puppetdb as you can see above. When I perform puppet runs everything seems to go OK. Which I thought meant that everything as good between puppet and the puppetdb.

However, when I connect to the postgres database and try to show tables for the puppetdb database, it says that 'no relations are found'.

[root@puppet:/etc/puppet] #su - postgres

-bash-4.1$ psql

psql (8.4.20)

Type "help" for help.

postgres=# \c puppetdb

psql (8.4.20)

You are now connected to database "puppetdb".

puppetdb=# \dt

No relations found.

So my question is, if I have everything setup correctly, why am I not seeing any tables inside the puppetdb database? Am I missing any steps that I need in order for this to work?

Thanks

Tim

--
GPG me!!

gpg --keyserver pool.sks-keyservers.net --recv-keys F186197B

[Ken Barber]

Is PuppetDB actually working at all? Or is this just a symptom you are
trying to track down.

Well couple of things, can you show your puppetdb.log for starters,
perhaps the initial startup might be useful.

I'm hoping this isn't it, but there is a small chance you have a
duplicate entry in your conf.d directory, I've always found this
methology to be a good one for checking this:

# cd /etc/puppetdb/conf.d
# grep '' *

Can you show us the results of this (with secrets removed also) it
might give us another clue.

It sounds to me like if its working its either using a different
database than we think it is, or potentially HSQLDB which is the
embedded one. You should be able to see clues if you are accidentally
using HSQLDB in this regard in /var/lib/puppetdb/db:

root@puppetdb1:~# ls /var/lib/puppetdb/db/
db.log db.properties db.script db.tmp

None of what you have provided shows this to be true so far, but worth
double checking :-).

ken.

[Christopher Wood]

Regex persnicketiness. ;)

# cd /etc/puppetdb/conf.d
# egrep -vh '^$' * | sort | uniq -d

Grep any lines to find where they're duped:

grep -l 'line string in quotes' *

ALso, this thing is useful:

https://forge.puppetlabs.com/puppetlabs/puppetdb

> Can you show us the results of this (with secrets removed also) it
> might give us another clue.
>
> It sounds to me like if its working its either using a different
> database than we think it is, or potentially HSQLDB which is the
> embedded one. You should be able to see clues if you are accidentally
> using HSQLDB in this regard in /var/lib/puppetdb/db:
>
> root@puppetdb1:~# ls /var/lib/puppetdb/db/
> db.log db.properties db.script db.tmp
>
> None of what you have provided shows this to be true so far, but worth
> double checking :-).
>
> ken.
>

> --
> 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 view this discussion on the web visit https://groups.google.com/d/msgid/puppet-users/CAE4bNTnUcKLxxfCdp2NjkVdSekNL-%2BwSOvUvVpu_w%3DgvzZFaiw%40mail.gmail.com.
> For more options, visit https://groups.google.com/d/optout.

[Tim Dunphy]

Hello and thanks for your reply.

Actually I take back what I said about it working. I still have the config above in place. And I did complete a run successfully on the puppet server itself. But when I looked at all the clients (I'm using foreman) I saw the clients were failing runs. So I went back to regular puppet with no puppetdb by moving my routes.yaml file to routes.yaml.bak and comment out the following two lines in /etc/puppet/puppet.conf:

    #storeconfigs = true

    #storeconfigs_backend = puppetdb

And puppet and all the clients are playing nice again. But I really want to get puppetdb working! So I reversed the steps I that I took above to put puppetdb back in place so I could show you guys what's going on. 

Now it's really apparent that puppetdb is not working at this point. I hope I can get it going at some point. 

I was using this guide to try to get this to work and followed all the steps, I believe, correctly:

http://www.tokiwinter.com/adding-puppet-db-to-a-centos-6-4-based-puppetmaster/

But now when I perform puppet runs with the puppetdb in place I get back the following result: 

[root@puppet:/etc/puppet] #puppet agent --test

Warning: Unable to fetch my node definition, but the agent run will continue:

Warning: Error 400 on SERVER: Could not retrieve facts for puppet.jokefire.com: Failed to find facts from PuppetDB at puppet.jokefire.com:8081: [404 Not Found] <html><head><meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/><title>Error 404 </title></head><body><h2>HTTP ERROR: 404</h2><p>Problem accessing /v3/nodes/puppet.jokefire.com/facts. Reason:<pre>    Not Found</pre></p><hr /><i><small>Powered by Jetty://</small></i></body></html>

Info: Retrieving pluginfacts

Info: Retrieving plugin

Info: Loading facts

Info: Loading facts

Error: Could not retrieve catalog from remote server: Error 400 on SERVER: Failed to submit 'replace facts' command for puppet.jokefire.com to PuppetDB at puppet.jokefire.com:8081: [404 Not Found] <html><head><meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"/><title>Error 404 </title></head><body><h2>HTTP ERROR: 404</h2><p>Problem accessing /v3/commands. Reason:<pre>    Not Found</pre></p><hr /><i><small>Powered by Jetty://</small></i></body></html>

Warning: Not using cache on failed catalog

Error: Could not retrieve catalog; skipping run

When I have a look at the logs  I see that I'm getting password authentication failures for the puppetdb user:

[root@puppet:/etc/puppet] #tail -30 /var/log/puppetdb/puppetdb.log

2014-10-05 16:25:36,339 ERROR [c.j.b.h.AbstractConnectionHook] Failed to acquire connection Sleeping for 7000ms and trying again. Attempts left: 1. Exception: null

2014-10-05 16:25:43,340 ERROR [c.j.b.PoolWatchThread] Error in trying to obtain a connection. Retrying in 7000ms

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "puppetdb"

.........

This is what I have in my /etc/puppetdb/conf.d/database.ini

[database]

classname = org.postgresql.Driver

subprotocol = postgresql

subname = //127.0.0.1:5432/puppetdb

username = puppetdb

password = secret

log-slow-statements = 10

Yet when I try to log into the postgres database using those credentials I get no error:

[root@puppet:~] #su - postgres

-bash-4.1$ psql -h 127.0.0.1 -p 5432 -U puppetdb -W puppetdb

Password for user puppetdb:

psql (8.4.20)

Type "help" for help.

puppetdb=>

Here's the output of that command you showed me. 

[root@puppet:/etc/puppetdb/conf.d] #grep '' *

config.ini:# See README.md for more thorough explanations of each section and

config.ini:# option.

config.ini:

config.ini:[global]

config.ini:# Store mq/db data in a custom directory

config.ini:vardir = /var/lib/puppetdb

config.ini:# Use an external logback config file

config.ini:logging-config = /etc/puppetdb/logback.xml

config.ini:

config.ini:

config.ini:[command-processing]

config.ini:# How many command-processing threads to use, defaults to (CPUs / 2)

config.ini:# threads = 4

config.ini:

config.ini:# Maximum amount of disk space (in MB) to allow for ActiveMQ persistent message storage

config.ini:# store-usage = 102400

config.ini:

config.ini:# Maximum amount of disk space (in MB) to allow for ActiveMQ temporary message storage

config.ini:# temp-usage = 51200

database.ini:[database]

database.ini:classname = org.postgresql.Driver

database.ini:subprotocol = postgresql

database.ini:subname = //127.0.0.1:5432/puppetdb

database.ini:username = puppetdb

database.ini:password = secret

database.ini:log-slow-statements = 10

jetty.ini:[jetty]

jetty.ini:# Hostname or IP address to listen for clear-text HTTP.  Default is localhost

jetty.ini:# host = <host>

jetty.ini:

jetty.ini:# Port to listen on for clear-text HTTP.

jetty.ini:port = 8080

jetty.ini:

jetty.ini:

jetty.ini:# The following are SSL specific settings. They can be configured

jetty.ini:# automatically with the tool `puppetdb ssl-setup`, which is normally

jetty.ini:# ran during package installation.

jetty.ini:

jetty.ini:# The host or IP address to listen on for HTTPS connections

jetty.ini:ssl-host = 0.0.0.0

jetty.ini:

jetty.ini:# The port to listen on for HTTPS connections

jetty.ini:ssl-port = 8081

jetty.ini:

jetty.ini:# Private key path

jetty.ini:ssl-key = /etc/puppetdb/ssl/private.pem

jetty.ini:

jetty.ini:# Public certificate path

jetty.ini:ssl-cert = /etc/puppetdb/ssl/public.pem

jetty.ini:

jetty.ini:# Certificate authority path

jetty.ini:ssl-ca-cert = /etc/puppetdb/ssl/ca.pem

jetty.ini.bak.1412460343:[jetty]

jetty.ini.bak.1412460343:# Hostname or IP address to listen for clear-text HTTP.  Default is localhost

jetty.ini.bak.1412460343:# host = <host>

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# Port to listen on for clear-text HTTP.

jetty.ini.bak.1412460343:port = 8080

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# The following are SSL specific settings. They can be configured

jetty.ini.bak.1412460343:# automatically with the tool `puppetdb ssl-setup`, which is normally

jetty.ini.bak.1412460343:# ran during package installation.

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# The host or IP address to listen on for HTTPS connections

jetty.ini.bak.1412460343:# ssl-host = <host>

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# The port to listen on for HTTPS connections

jetty.ini.bak.1412460343:# ssl-port = <port>

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# Private key path

jetty.ini.bak.1412460343:# ssl-key = <private_key_path>

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# Public certificate path

jetty.ini.bak.1412460343:# ssl-cert = <public_cert_path>

jetty.ini.bak.1412460343:

jetty.ini.bak.1412460343:# Certificate authority path

jetty.ini.bak.1412460343:# ssl-ca-cert = <ca_cert_path>

jetty.ini.bak.1412462037:[jetty]

jetty.ini.bak.1412462037:# Hostname or IP address to listen for clear-text HTTP.  Default is localhost

jetty.ini.bak.1412462037:# host = <host>

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# Port to listen on for clear-text HTTP.

jetty.ini.bak.1412462037:port = 8080

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# The following are SSL specific settings. They can be configured

jetty.ini.bak.1412462037:# automatically with the tool `puppetdb ssl-setup`, which is normally

jetty.ini.bak.1412462037:# ran during package installation.

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# The host or IP address to listen on for HTTPS connections

jetty.ini.bak.1412462037:# ssl-host = <host>

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# The port to listen on for HTTPS connections

jetty.ini.bak.1412462037:# ssl-port = <port>

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# Private key path

jetty.ini.bak.1412462037:# ssl-key = <private_key_path>

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# Public certificate path

jetty.ini.bak.1412462037:# ssl-cert = <public_cert_path>

jetty.ini.bak.1412462037:

jetty.ini.bak.1412462037:# Certificate authority path

jetty.ini.bak.1412462037:# ssl-ca-cert = <ca_cert_path>

repl.ini:[repl]

repl.ini:# Set to true to enable the remote REPL

repl.ini:enabled = false

repl.ini:

repl.ini:# Either nrepl or swank or telnet

repl.ini:type = nrepl

repl.ini:

repl.ini:# What port the REPL should listen on

repl.ini:port = 8082

Lastly I can show that the puppetdb service is actually running: 

[root@puppet:~] #service puppetdb status

puppetdb (pid  26297) is running...

And listening on port 8081:

[root@puppet:~] #lsof -i :8081

COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

java    26297 puppetdb   23u  IPv6 505111      0t0  TCP *:tproxy (LISTEN)

Thanks again for your input. It'll be a great day when I can finally get this to work!

Thanks

Tim

ken.

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/puppet-users/CAE4bNTnUcKLxxfCdp2NjkVdSekNL-%2BwSOvUvVpu_w%3DgvzZFaiw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Ken Barber]

So this might potentially be a problem with your pg_hba.conf, I'm
guessing though.

Try the above again, but as the root user, not as postgres, and show
us the results.

Here is my pg_hba.conf fwiw:
https://gist.github.com/kbarber/0fd98ec424687c1bea9d. Send us yours
and we can take a look.

ken.

[Ken Barber]

Having said this, and actually tested my theory myself, I don't
believe this is the case - but worth looking into. When I tested
'breaking' my pg_hba.conf I get this result specifically:

2014-10-04 11:52:53,300 ERROR [c.j.b.h.AbstractConnectionHook] Failed

to acquire connection Sleeping for 7000ms and trying again. Attempts

left: 5. Exception: null
2014-10-04 11:53:00,310 ERROR [c.j.b.h.AbstractConnectionHook] Failed

to acquire connection Sleeping for 7000ms and trying again. Attempts

left: 4. Exception: null
2014-10-04 11:53:07,320 ERROR [c.j.b.h.AbstractConnectionHook] Failed

to acquire connection Sleeping for 7000ms and trying again. Attempts

left: 3. Exception: null
2014-10-04 11:53:14,330 ERROR [c.j.b.h.AbstractConnectionHook] Failed

to acquire connection Sleeping for 7000ms and trying again. Attempts

left: 2. Exception: null
2014-10-04 11:53:21,341 ERROR [c.j.b.h.AbstractConnectionHook] Failed

to acquire connection Sleeping for 7000ms and trying again. Attempts
left: 1. Exception: null

2014-10-04 11:53:28,347 ERROR [c.j.b.PoolWatchThread] Error in trying

to obtain a connection. Retrying in 7000ms

org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for
host "192.168.182.132", user "puppetdb", database "puppetdb", SSL off
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:398)
~[puppetdb.jar:na]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173)
~[puppetdb.jar:na]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
~[puppetdb.jar:na]
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
~[puppetdb.jar:na]

Which is very specific, and definitely not a password auth error. Hmm.
Of course this is with 9.3, there is a chance the protocol has
changed.

I know it probably seems very dumb at this point, but have you tried
changing your password for the puppetdb user to something simple ...
and trying again?

http://www.postgresql.org/docs/8.4/static/sql-alteruser.html

I'm not saying you're typing the password incorrectly or anything, but
the process of changing the password for the `puppetdb` pg user might
shake something up. I'm really grasping at straws, an incorrect
password error is usually just that ... but your tests don't align
with this potentiality :-). I guess what I'm saying is that it feels
like something really silly that we are missing.

Still, try the `psql` auth test as root as I mentioned, pass us your
pg_hba.conf, you never know it might also shake something up that we
didn't notice before.

ken.

[Tim Dunphy]

hey ken,

 Thanks again for your help. I changed the password on a temporary basis to an absurdly simple one. I'm both happy to say that puppetdb is working now. And sad to have taken up your time with this. Sorry about that. 

But after changing to the absurdly simple password and then being really careful about setting the password back to a complex one, I can now get successful puppet runs with the puppetdb in place. And I can also see tables in the postgres database. 

puppetdb=# \dt

                 List of relations

 Schema |         Name          | Type  |  Owner

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

 public | catalog_resources     | table | puppetdb

 public | catalogs              | table | puppetdb

 public | certnames             | table | puppetdb

 public | edges                 | table | puppetdb

 public | environments          | table | puppetdb

 public | fact_paths            | table | puppetdb

 public | fact_values           | table | puppetdb

 public | facts                 | table | puppetdb

 public | factsets              | table | puppetdb

 public | latest_reports        | table | puppetdb

 public | report_statuses       | table | puppetdb

 public | reports               | table | puppetdb

 public | resource_events       | table | puppetdb

 public | resource_params       | table | puppetdb

 public | resource_params_cache | table | puppetdb

 public | schema_migrations     | table | puppetdb

 public | value_types           | table | puppetdb

(17 rows)

Thanks again for your help!

Tim

ken.

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/puppet-users/CAE4bNTnmXHx2vWp8GuYRQkXNrrya9HT5SFeqr0TDQjb629G-AQ%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

[Ken Barber]

> Thanks again for your help. I changed the password on a temporary basis to
> an absurdly simple one. I'm both happy to say that puppetdb is working now.
> And sad to have taken up your time with this. Sorry about that.

No problem mate, glad it was something simple in the end :-).

ken.