PDB External PostgreSQL Troubleshooting - Shottracker

attempt1 output.txt

[root@ip-10-98-10-242 ~]# /opt/puppetlabs/puppet/bin/puppet resource service puppet ensure=stopped
Notice: /Service[puppet]/ensure: ensure changed 'running' to 'stopped'
service { 'puppet':
  ensure => 'stopped',
}
[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/code/environments/production/hieradata/common.yaml
[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/console-services/conf.d/activity-database.conf
[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/console-services/conf.d/rbac-database.conf
[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/console-services/conf.d/classifier-database.conf
[root@ip-10-98-10-242 ~]# systemctl restart pe-console-services
[root@ip-10-98-10-242 ~]# puppet agent -t
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Loading facts
Info: Caching catalog for ip-10-98-10-242.us-west-2.compute.internal
Info: Applying configuration version '1495147679'
Notice: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Activity[console-services]/Pe_hocon_setting[activity.database.subname]/value: value changed ['//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-activity?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'] to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-activity'
Info: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Activity[console-services]/Pe_hocon_setting[activity.database.subname]: Scheduling refresh of Service[pe-console-services]
Notice: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Rbac[console-services]/Pe_hocon_setting[console-services.rbac.database.subname]/value: value changed ['//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-rbac?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'] to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-rbac'
Info: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Rbac[console-services]/Pe_hocon_setting[console-services.rbac.database.subname]: Scheduling refresh of Service[pe-console-services]
Notice: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Classifier[console-services]/Pe_hocon_setting[console-services.classifier.database.subname]/value: value changed ['//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-classifier?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'] to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-classifier'
Info: /Stage[main]/Puppet_enterprise::Profile::Console/Puppet_enterprise::Trapperkeeper::Classifier[console-services]/Pe_hocon_setting[console-services.classifier.database.subname]: Scheduling refresh of Service[pe-console-services]
Info: Puppet_enterprise::Trapperkeeper::Rbac[console-services]: Scheduling refresh of Service[pe-console-services]
Info: Puppet_enterprise::Trapperkeeper::Classifier[console-services]: Scheduling refresh of Service[pe-console-services]
Info: Puppet_enterprise::Trapperkeeper::Activity[console-services]: Scheduling refresh of Service[pe-console-services]
Notice: /Stage[main]/Puppet_enterprise::Console_services/Puppet_enterprise::Trapperkeeper::Pe_service[console-services]/Service[pe-console-services]: Triggered 'refresh' from 6 events

attempt2 output.txt

Added string to orchestrator.conf and database.ini, ran puppet agent -t

[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/orchestration-services/conf.d/orchestrator.conf
[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/puppetdb/conf.d/database.ini
[root@ip-10-98-10-242 ~]# puppet agent -t
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Loading facts
Info: Caching catalog for ip-10-98-10-242.us-west-2.compute.internal
Info: Applying configuration version '1495147975'
Notice: /Stage[main]/Puppet_enterprise::Profile::Orchestrator/Puppet_enterprise::Trapperkeeper::Orchestrator[orchestration-services]/Pe_hocon_setting[orchestration-services.orchestrator.database.subname]/value: value changed ['//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-orchestrator?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'] to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-orchestrator'
Info: /Stage[main]/Puppet_enterprise::Profile::Orchestrator/Puppet_enterprise::Trapperkeeper::Orchestrator[orchestration-services]/Pe_hocon_setting[orchestration-services.orchestrator.database.subname]: Scheduling refresh of Service[pe-orchestration-services]
Notice: /Stage[main]/Puppet_enterprise::Puppetdb::Database_ini/Puppet_enterprise::Puppetdb::Shared_database_settings[database]/Pe_ini_setting[[database]-puppetdb_subname]/value: value changed '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem' to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb'
Info: Class[Puppet_enterprise::Puppetdb::Database_ini]: Scheduling refresh of Service[pe-puppetdb]
Notice: /Stage[main]/Puppet_enterprise::Puppetdb::Service/Puppet_enterprise::Trapperkeeper::Pe_service[puppetdb]/Service[pe-puppetdb]: Triggered 'refresh' from 1 events
Info: Puppet_enterprise::Trapperkeeper::Orchestrator[orchestration-services]: Scheduling refresh of Service[pe-orchestration-services]
Notice: /Stage[main]/Puppet_enterprise::Profile::Orchestrator/Puppet_enterprise::Trapperkeeper::Pe_service[orchestration-services]/Service[pe-orchestration-services]: Triggered 'refresh' from 2 events
Notice: Applied catalog in 13.06 seconds

attempt3 output.txt

changed the common.yaml file to use the puppet_enterprise::profile::puppetdb::database_properties: ?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem


[root@ip-10-98-10-242 ~]# vi /etc/puppetlabs/code/environments/production/hieradata/common.yaml
[root@ip-10-98-10-242 ~]# puppet agent -t
Info: Using configured environment 'production'
Info: Retrieving pluginfacts
Info: Retrieving plugin
Info: Loading facts
Info: Caching catalog for ip-10-98-10-242.us-west-2.compute.internal
Info: Applying configuration version '1495148522'
Notice: /Stage[main]/Puppet_enterprise::Puppetdb::Database_ini/Puppet_enterprise::Puppetdb::Shared_database_settings[database]/Pe_ini_setting[[database]-puppetdb_subname]/value: value changed '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb' to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'
Info: Class[Puppet_enterprise::Puppetdb::Database_ini]: Scheduling refresh of Service[pe-puppetdb]
Notice: /Stage[main]/Puppet_enterprise::Puppetdb/Puppet_enterprise::Puppetdb::Shared_database_settings[read-database]/Pe_ini_setting[[read-database]-puppetdb_subname]/value: value changed '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb' to '//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-puppetdb?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem'
Info: Puppet_enterprise::Puppetdb::Shared_database_settings[read-database]: Scheduling refresh of Service[pe-puppetdb]
Notice: /Stage[main]/Puppet_enterprise::Puppetdb::Service/Puppet_enterprise::Trapperkeeper::Pe_service[puppetdb]/Service[pe-puppetdb]: Triggered 'refresh' from 2 events
Notice: Applied catalog in 11.99 seconds

common.yaml

---
puppet_enterprise::profile::puppetdb::database_properties: ?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem
puppet_enterprise::database_ssl: true
puppet_enterprise::database_cert_auth: true

ext_pdb_shottracker.txt

*Connect to DB via command line
psql -h pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com -d pdb04 -U pdbuser

* Download RDS Cert
cd /etc/puppetlabs/puppet/ssl/
wget https://s3.amazonaws.com/rds-downloads/rds-ca-2015-root.pem
chown -R pe-puppet:pe-puppet rds-ca-2015-root.pem
-rw-r--r--. 1 pe-puppet pe-puppet 1432 Mar  6  2015 rds-ca-2015-root.pem

Stop Puppet
/opt/puppetlabs/puppet/bin/puppet resource service puppet ensure=stopped

* CERT Location
/etc/puppetlabs/puppet/ssl/external/rds-ca-2015-root.pem
database_properties = ?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem

* Add string to files
/etc/puppetlabs/console-services/conf.d/activity-database.conf
subname: "//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-activity?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem"
/etc/puppetlabs/console-services/conf.d/classifier-database.conf
subname: "//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-activity?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem"
/etc/puppetlabs/console-services/conf.d/rbac-database.conf
subname: "//pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com:5432/pe-activity?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem"

* DB instance
pdb04.cwgutxb2rxqb.us-west-2.rds.amazonaws.com
Puppetlabs

* Set hiera data in common.yaml (see common.yaml file in this gist.)
puppet_enterprise::database_properties: ?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem
puppet_enterprise::database_ssl: true
puppet_enterprise::database_cert_auth: true



## NOTES
* Disconnect sessions to DB.
select pg_terminate_backend(pid) from pg_stat_activity where datname='<databasename>'

* /opt/puppetlabs/server/bin/validate_postgresql_connection.sh

solution.txt

SOLUTION

Create file /etc/puppetlabs/code/environment/production/hieradata/common.yaml

Use these values (note, make sure the --- is the first line.)

---
puppet_enterprise::profile::puppetdb::database_properties: ?ssl=true&sslfactory=org.postgresql.ssl.jdbc4.LibPQFactory&sslmode=verify-full&sslrootcert=/etc/puppetlabs/puppet/ssl/rds-ca-2015-root.pem
puppet_enterprise::database_ssl: true
puppet_enterprise::database_cert_auth: true

Raw version of the common.yaml file

https://gist.githubusercontent.com/tspeigner/5d83f630a107e5bb50d4e2be0127abe5/raw/02b557b61d435a824bb1fd8a5db049e1f5a7a777/common.yaml