Puppet Postgres
Having a look around
It’s worth remembering that on an x-large infrastructure, some of these databases such as pe-rbac still exist on the PE server where as the pe-puppetdb will be on the Postgres server. To login to psql as the puppet user run the following command sudo -u pe-postgres /opt/puppetlabs/server/bin/psql
Use \l to list all the databases.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
pe-postgres-# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------------+-------------+----------+-------------+-------------+---------------------------------------------
pe-activity | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-activity-read"=c/"pe-postgres" +
| | | | | "pe-activity-write"=c/"pe-postgres"
pe-classifier | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-classifier-read"=c/"pe-postgres" +
| | | | | "pe-classifier-write"=c/"pe-postgres"
pe-inventory | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-inventory-read"=c/"pe-postgres" +
| | | | | "pe-inventory-write"=c/"pe-postgres"
pe-orchestrator | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-orchestrator-read"=c/"pe-postgres" +
| | | | | "pe-orchestrator-write"=c/"pe-postgres"
pe-postgres | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres"
pe-puppetdb | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-puppetdb"=CT/"pe-postgres" +
| | | | | "pe-puppetdb-migrator"=c*/"pe-postgres" +
| | | | | "pe-puppetdb"=c/"pe-puppetdb-migrator" +
| | | | | "pe-puppetdb-read"=c/"pe-puppetdb-migrator"
pe-rbac | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =T/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres" +
| | | | | "pe-rbac-read"=c/"pe-postgres" +
| | | | | "pe-rbac-write"=c/"pe-postgres"
postgres | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres"
template1 | pe-postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/"pe-postgres" +
| | | | | "pe-postgres"=CTc/"pe-postgres"
(10 rows)
Use \c to connect to a database and then \d to list the schema’s.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
pe-postgres-# \c pe-classifier
You are now connected to database "pe-classifier" as user "pe-postgres".
pe-classifier-# \d
List of relations
Schema | Name | Type | Owner
--------+------------------------------+----------+---------------
public | environment_class_parameters | table | pe-classifier
public | environment_classes | table | pe-classifier
public | environments | table | pe-classifier
public | group_class_parameters | table | pe-classifier
public | group_classes | table | pe-classifier
public | group_hiera_data | table | pe-classifier
public | group_variables | table | pe-classifier
public | groups | table | pe-classifier
public | last_sync | table | pe-classifier
public | node_check_ins | table | pe-classifier
public | node_check_ins_idx_seq | sequence | pe-classifier
public | rules | table | pe-classifier
public | rules_id_seq | sequence | pe-classifier
public | schema_migrations | table | pe-classifier
(14 rows)
From here we could take a look at all the node groups configured on the system by running.
1
select * from groups;
And its \q to quit!
Dumping databases
You can dump an entire database using the following command, this will dump the entire pe-rbac database into the /tmp directory.
1
sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-rbac -f /tmp/pe-rbac.backup.bin
Make sure you’re in the /tmp directory when you run this, pe-postgres doesn’t have many permissions so it’ll error when changing directory. Restoring a database So now we have that database dumped we might want to restore it. Puppet will have connections into that database so we’ll need to shut everything down, run this to do that.
1
for svc in puppet pe-puppetserver pe-puppetdb pe-console-services pe-nginx pe-orchestration-services pxp-agent; do echo "Stopping $svc" ; puppet resource service $svc ensure=stopped; done
Next, the database still exists and we can’t restore over an existing database so we’ll need to delete it;
1
su - pe-postgres -s /bin/bash -c '/opt/puppetlabs/server/bin/psql -c "drop database \"pe-rbac\";"'
You should see a message DROP DATABASE, you can use the commands from earlier to have a look and check it’s gone.
Now create an empty database, and restore the dump to it.
1
2
su - pe-postgres -s /bin/bash -c '/opt/puppetlabs/server/bin/psql -c "create database \"pe-rbac\";"'
sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_restore -C pe-rbac.backup.bin -d pe-rbac
And restart the services.
1
for svc in puppet pe-puppetserver pe-puppetdb pe-console-services pe-nginx pe-orchestration-services pxp-agent; do echo "Starting $svc" ; puppet resource service $svc ensure=running; done
Troubleshooting
If the database restoration fails due to a session connection remaining after PE has been shut down, run the following command.
1
2
sudo -u pe-postgres /opt/puppetlabs/server/bin/psql
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '<database name>'
Dumping a table
We may want to dump an individual table/schema from a database, for example, dump the roles table from the pe-rbac database. To do that run this command.
1
sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc -d pe-orchestrator -t dumplings_node_groups > /tmp/dumplings_node_groups.sql
Restoring a table
Now we can restore it with this command.
1
sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_restore -d pe-orchestrator -a -t dumplings_node_groups /tmp/dumplings_node_groups.sql