Using pg_partman with Rails and HerokuCI

Question

In my Rails 6 app, I'm creating a table that I know will get large. So I'm partitioning it by month using pg_partman. It's hosted on Heroku, so I've followed their instructions. The migration looks something like this:

class CreateReceipts < ActiveRecord::Migration[6.0]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          create extension pg_partman;
        SQL
      end
      dir.down do
        execute <<-SQL
          drop extension pg_partman;
        SQL
      end
    end

    create_table(
      :receipts,
      # Partitioning requires the primary key includes the column we're partitioning by.
      primary_key: [:id, :created_at],
      options: 'partition by range (created_at)'
    ) do |t|
      # When using the primary key option, it ignores id: true. Make the ID column manually.
      t.column :id, :bigserial, null: false

      t.references :customer, null: false, foreign_key: true
      t.integer :thing, null: false
      t.text :stuff, null: false

      t.timestamps
    end

    reversible do |dir|
      dir.up do
        execute <<-SQL
          select create_parent('public.receipts', 'created_at', 'native', 'monthly');
        SQL
      end
      dir.down do
        # Dropping receipts undoes all the partitioning, except the template table.
        drop_table(:template_public_receipts)
      end
    end
  end
end

class Receipt < ApplicationRecord
  # The composite primary key is only for partitioning.
  self.primary_key = 'id'

  # Unfortunately, partitioning gets confused if we add another unique index.
  # So we must enforce ID uniqueness in the model.
  validates :id, uniqueness: true
end

A little weird with the primary key, but it works fine locally. Heroku Postgres has the pg_partman extension, so production is fine.

The problem is HerokuCI. I'm using the recommended in-dyno database add on. It does not have pg_partman.

-----> Preparing test database
       Running: rake db:schema:load_if_ruby
       db:schema:load_if_ruby completed (6.17s)
       Running: rake db:structure:load_if_sql
        set_config 
       ------------

       (1 row)

       psql:/app/db/structure.sql:16: ERROR:  could not open extension control file "/app/.indyno/vendor/postgresql/share/extension/pg_partman.control": No such file or directory

       rake aborted!
       failed to execute:

I'd rather not having to attach a full database to CI just for this one thing. And it feels strange to hard code the pg_partman partitions into the schema, though it is good to have the tests as close to production as possible.

Is there an alternative approach?

score 3 · Answer 1 · answered Apr 21 '20 at 01:32

I worked around this by installing pg_partman as part of the db:structure:load_if_sql task when it detects its on HerokuCI with In-Dyno Postgres.

Rake::Task["db:structure:load_if_sql"].enhance [:install_pg_partman]

private def heroku_ci?
  ENV["CI"]
end

private def in_dyno_postgres?
  File.exist?("/app/.indyno/vendor/postgresql")
end

private def install_pg_partman
  system './bin/install-pg-partman'
end

task :install_pg_partman do
  # Heroku In-Dyno Postgres does not have pg_partman.
  if heroku_ci? && in_dyno_postgres?
    puts 'installing pg_partman'
    install_pg_partman
  end
end

And bin/install-pg-partman.

#!/bin/bash

REPO_URL=${REPO_URL='https://github.com/pgpartman/pg_partman.git'}
BUILD_DIR=${BUILD_DIR=tmp}
PG_CONFIG=${PG_CONFIG='/app/.indyno/vendor/postgresql/bin/pg_config'}

if [ ! -f "$PG_CONFIG" ]; then
  echo "Cannot find ${PG_CONFIG}"
  exit 1;
fi

cd tmp
rm -rf pg_partman
git clone ${REPO_URL}
cd pg_partman
make install PG_CONFIG=${PG_CONFIG} NO_BGW=1

With tests.

require 'rails_helper'

RSpec.describe 'rake install_pg_partman' do
  let(:task) { Rake::Task['install_pg_partman'] }

  before do
    # Otherwise if you call the same task twice it will think
    # it's already been done and skip it or re-raise the same exception.
    task.reenable
  end

  shared_context "with CI", temp_env: true do
    before { ENV["CI"] = "true" }
  end

  shared_context "with in-dyno postgres" do
    before {
      allow(File).to receive(:exist?)
        .with("/app/.indyno/vendor/postgresql")
        .and_return(true)
    }
  end

  shared_examples "it does not install" do
    it 'does not install' do
      expect(task).not_to receive(:install_pg_partman)

      task.invoke
    end
  end

  it 'is a prerequisite of db:structure:load_if_sql' do
    expect(
      Rake::Task["db:structure:load_if_sql"].prerequisite_tasks
    ).to include(task)
  end

  context 'no CI, no in-dyno postgres' do
    it_behaves_like 'it does not install'
  end

  context 'when in CI, but no in-dyno postgres' do
    include_context "with CI"

    it_behaves_like 'it does not install'
  end

  context 'with in-dyno postgres, but not in CI' do
    include_context "with in-dyno postgres"

    it_behaves_like 'it does not install'
  end

  context 'when in CI and with in-dyno postgres', temp_env: true do
    include_context "with CI"
    include_context "with in-dyno postgres"

    let(:pg_config_path) { "/does/not/exist/pg_config" }
    before {
      ENV["PG_CONFIG"] = pg_config_path
    }

    it 'tries to install' do
      expect(task.send(:heroku_ci?)).to be_truthy
      expect(task.send(:in_dyno_postgres?)).to be_truthy

      expect {
        task.invoke
      }.to output(/Cannot find #{pg_config_path}/).to_stdout_from_any_process
    end
  end
end

Using pg_partman with Rails and HerokuCI

1 Answers1