As part of maintaining the Puppet Enterprise (PE) databases, optimize to improve performance, or back up and restore the databases.
Databases in Puppet Enterprise
PE uses PostgreSQL as the backend for its databases. Use the native tools in PostgreSQL to perform database exports and imports. At a minimum, back ups to a remote system nightly, or as specified by your company policy.
The PE PostgreSQL database includes the following databases:
|pe-activity||Activity data from the Classifier, including who, what and when|
|pe-classifier||Classification data, all node group information|
|pe-puppetdb||PuppetDB data, including exported resources, catalogs, facts, and reports|
|pe-rbac||Role-based access control data, including users, permissions, and AD/LDAP info|
|pe-orchestrator||Orchestrator data, including user, node, and result details about job runs|
Optimize a database
If your databases are slow, begin taking up too much disk space, or need general performance enhancements, use the PostgreSQL
vacuum command to optimize any of the PE databases.
su - pe-postgres -s /bin/bash -c "vacuumdb -z --verbose <DATABASE NAME>"
Backing up Puppet Enterprise databases
You should regularly back up the databases.
Although you can back up all the databases at one time, it's better to back up each database individually, for the following reasons:
- Individual backups can be saved in a compressed format, saving space and reducing disk input and output.
- Individual backups can be restored independently from the other databases. A backup of all of them has to also restore all databases at the same time.
- Individual backups can be scheduled independently.
Back up and restore databases individually
pg_dump command to back up each database to a compressed binary file on disk, which you can restore by running the
- To back up each database individually, run the following commands:
sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-activity -f /tmp/pe-activity_`date +%m_%d_%y_%H_%M`.bin sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-rbac -f /tmp/pe-rbac_`date +%m_%d_%y_%H_%M`.bin sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-classifier -f /tmp/pe-classifier_`date +%m_%d_%y_%H_%M`.bin sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-puppetdb -f /tmp/pe-puppetdb_`date +%m_%d_%y_%H_%M`.bin sudo -u pe-postgres /opt/puppetlabs/server/bin/pg_dump -Fc pe-orchestrator -f /tmp/pe-orchestrator_`date +%m_%d_%y_%H_%M`.bin
- To restore an individual database backup, run the following command:
sudo -u pe-postgres /opt/puppetlabs/server/apps/postgresql/bin/pg_restore -Cc -d template1 <BACKUP_FILE>.bin
This command connects to the
template1database and then drops the database indicated in <BACKUP_FILE>.bin. The command then re-creates that database and connects to the newly created database to perform the restoration.
If you omit the
pg_restorewill output the SQL commands needed to restore a database, but it will not perform the actual restoration.
- After restoring the database, fix access privileges for that database with Puppet. Run the following command:
puppet infrastructure configure
Back up and restore all databases at one time
pg_dumpall command to back up the complete set of PE databases to a SQL file on disk. After you've created a backup of your PE databases, you can restore them with the
It isn’t necessary to stop any PE services to perform these steps.
- To back up all of the PE databases together, run the following command:
sql sudo -u pe-postgres /opt/puppetlabs/server/apps/postgresql/bin/pg_dumpall -c -f <BACKUP_FILE>.sql
<BACKUP_FILE>, specify the name and location of the backup file to the location where you want to store the backup.
- When you need to restore, stop all PE services except for
pe-postgresqlby running the following commands:
puppet resource service puppet ensure=stopped puppet resource service pe-puppetserver ensure=stopped puppet resource service pe-puppetdb ensure=stopped puppet resource service pe-console-services ensure=stopped puppet resource service pe-orchestration-services ensure=stopped puppet resource service pe-nginx ensure=stopped
- Restore all the databases by running:
sql sudo -u pe-postgres /opt/puppetlabs/server/apps/postgresql/bin/psql < <BACKUP_FILE>.sql
This command will import the complete PE database backup.
- After the restoration is complete, start all PE services by running the following commands:
puppet resource service puppet ensure=running puppet resource service pe-puppetserver ensure=running puppet resource service pe-puppetdb ensure=running puppet resource service pe-console-services ensure=running puppet resource service pe-orchestration-services ensure=running puppet resource service pe-nginx ensure=running