pe-pg_repack.md

pe-pg_repack.md

Instructions for getting started with pg_repack in Puppet Enterprise:

NOTE: there are some PE version numbers in some of the code blocks. You'll need to update these to the correct numbers for your deployment.

To create an RPM package for pg_repack (using FPM):

First install the dependencies (requires root):

yum install pe-postgresql-devel zlib-devel readline-devel gcc -y
/opt/puppetlabs/puppet/bin/gem install fpm --no-ri --no-rdoc

Next, create the RPM file (does NOT require root):

#!/bin/bash

set -xe

PATH=$PATH:/opt/puppetlabs/server/bin/
pushd /tmp
  git clone https://github.com/reorg/pg_repack
  pushd pg_repack
    git checkout ver_1.4.2
    make
    make install DESTDIR=/tmp/installdir
  popd
popd

/opt/puppetlabs/puppet/bin/fpm -s dir -t rpm -n pe-pg_repack -v 1.4.2 -C /tmp/installdir \
  -p pe-pg_repack-1.4.2-1.pe.el7.x86_64.rpm \
  --rpm-user root \
  --rpm-group root \
  -d "pe-postgresql-server >= 2016.4.9.4.14-1"

rm -rf /tmp/pg_repack
rm -rf /tmp/installdir

Install the package (requires root):

rpm -i pe-pg_repack-1.4.2-1.pe.el7.x86_64.rpm

Configure the database to use the pg_repack extension (requires root):

su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c 'CREATE EXTENSION pg_repack'"

To perform a repack (requires root, to su to pe-postgres). Do the repack for each table individually, starting with smaller tables and working up to the larger ones.

su - pe-postgres -s /bin/bash
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t schema_migrations
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t value_types
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t producers
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t report_statuses
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t environments
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t certnames
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t factsets
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t catalogs
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t fact_paths
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t fact_values
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t edges
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t resource_events
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t resource_params_cache
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t catalog_resources
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t resource_params
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t facts
/opt/puppetlabs/server/apps/postgresql/bin/pg_repack -d pe-puppetdb -t reports

Some of these pg_repack commands will take a long time. Invoke pg_repack in a tmux session or other interruption-proof execution context to avoid accidentally terminating or interrupting the command.