Deploying `structure.sql` in a Ruby on Rails application

I realized my last article was incomplete on incorporating structure.sql in a Ruby on Rails application. This article has the additional steps required to successfully integrate structure.sql into the remaining development lifecycle, especially configuring for continuous integration.

I will step through errors I encountered and how I solved them.

I learned the required changes the hard way and writing them up here will enable you to accelerate your solution (and avoid me making the same mistakes again!)

This article will take less than six minutes to read.

source and more information

Introduction

When I thought all that needed to change to use structure.sql was to add an application configuration, remove the schema.rb file, and commit changes.

I made the changes to our current project and found out the hard way I did not have everything needed for a full development lifecyle to incorporate the struture.sql file.

These are the additional changes required:

• use $ rake db:test:prepare for continuous integration configuration.
• comment out lines from the structure.sql file that require additional priviledges if using Postgres version before 10.0.
• add the postgres-client to the continuous integration server’s operating system.

Let’s dive into each in more detail.

Use rake db:test:prepare

In my testing, I only used $ rake db:schema:load to load the structure.sql file, which seemed to work, but looking at my changes, I believe I did not do full database $ rake db:drop; rake db:create;

Well, I did not test enough when pushing up the pull request with just structure.sql file on the continuous integration service as this error appears:

#!/bin/bash -eo pipefail
bundle exec rake db:schema:load
/home/circleci/circleci/ppc-segments-api/db/schema.rb doesn't exist yet. Run `rails db:migrate` to create it, then try again. If you do not intend to use a database, you should instead alter /home/circleci/circleci/ppc-segments-api/config/application.rb to limit the frameworks that will be loaded.
Exited with code 1

🤦

There are three different ways to set up a database in Ruby on Rails:

• $ rake db:schema:load - sets up the test AND development from the schema.rb file.
• $ rake db:structure:load - sets up the test AND development database from the structure.sql file.
• $ rake db:test:prepare - sets up only the test database, according to config/application.rb setting for config.active_record.schema_format. If set to :ruby, it will load schema.rb file or structure.sql file when set to :sql. more details

I used $ rake db:test:prepare for the continous integration server as it only requires the test database and can be controller by the config/application.rb setting instead of the file.

For local environments, use the appropriate load command to setup the database.

Lesson: Use the appropriate command to load the database schema for your use case.

Install plsql?!

When trying to load the structure.sql file, I saw the following error message:

db/structure.sql:23: ERROR: must be owner of extension plpgsql

Searching for answers says this means the database user does not have enough priviledges. The normal way to solve this would be to grant administrative priviledges to the database.

This is fine on a local development system. On a production system or even continuous integration server, this isn’t ideal, as granting additional access can be tricky or even dangerous!

Worst, this issue did not appear for schema.rb file, so why does structure.sql need it instead when these files are equivalent?!

Solution

The culprit are the following lines in the structure.sql file:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

The easiest way is to comment out these lines, and run: rake db:test:prepare.

Voila, it works!

As the solution to the above problem is to comment out those lines from the structure.sql file this creates another problem: for every change to the structure.sql file, comment out the above lines. Doing it manually is possible, but it can definitely be dangerous as now there is manual manipulation of an autogenerated file. (Which one is the machine and which is the human again?!)

Fortunately, rietta created a rake task that will comment out the above lines everytime you generate the structure.sql.

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
#
# PostgreSQL writes two optional commands to the database schema
# file, called db/structure.sql, that can only be run as a root
# database user.  These are not needed actually, so comment them
# out automatically
#
# CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
# COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
#
namespace :db do
  namespace :structure do
    desc 'Comment out the plpgsql lines from structure.sql so that a non-root user can create the test database'
    task :fix_plpgsql do

      lines_to_strike = [
          'CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;',
          "COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';",
      ]

      schema_file = File.join(File.dirname(__FILE__), '..', '..', 'db', 'structure.sql')
      if File.exist?(schema_file) and (input = File.open(schema_file))
        # Create a temp file, read through the original, commenting out the target lines.
        lines = Array.new
        line_count = 0
        input.each_line do |line|
          if line
            line_count += 1
            if lines_to_strike.include?(line.strip)
              lines << "-- The following was commented out by rake db:structure:fix_plpgsql\n"
              lines << '-- ' + line
            else
              lines << line
            end
          end
        end # each

        input.close

        if lines.count > line_count
          # Lines were commented out, so write the new content to the file
          File.write(schema_file, lines.join)
        else
          # No lines were commented out, so there is no need to rewrite the file
          STDERR.puts "No changes are needed to #{schema_file}, it's left unchanged."
        end


      end

    end # task
  end # namespace
end # namespace

# Inform Rake that this should be run every time rake db:structure:dump is run
Rake::Task['db:structure:dump'].enhance do
  Rake::Task['db:structure:fix_plpgsql'].invoke
end

source

Whew - this loads the structure.sql file locally, let’s get this on our continuous integration server.

This error only appears in postgres versions before 10.0. Postgres 10.0 fixes this issue and there is no error when loading an unmodified structure.sql file. (Guess what I configured my development system with…whoops!)

Operating System Errors

Well, even when things are working locally, when pushing the change onto the continuous integration server, this error message appears:

Please check the output above for any errors and make sure that `psql` is installed in your PATH and has proper permissions.

What the?! psql, I just spent energy debugging plpgsql, which turned out to be super weird. what’s going on here?!

I found the solution in this posting in the CircleCI forum. The issue arises from how the operating system interacts with postgres. The above error message indicates the operating system needs another client, the fix is to install the postgres-client. (Thanks levlaz!)

Installing the postgres client will depend on the operating system. In debian, $ sudo apt install -y postgres-client is sufficient.

Even with the above line and the following error appears:

E: Unable to fetch some archives, maybe run apt-get update or try with --fix-missing?
Exited with code 100      

The fix is to run: $ sudo apt-get update before $ sudo apt install -y postgres-client.

Conclusion

Even with changes working on my local development system to incorporate structure.sql into my Ruby on Rails application, there are changes outside of my local development environment:

• Use $ rake db:test:prepare instead of $ rake db:schema:load or even $ rake db:structure:load for continuous integration as it only needs the test database and config/application.rb controls which file to load.
• On system using postres 9 and lower, removing lines from the structure.sql file that requires additional permissions. I solved it using another rake task that automatically comments out the offending lines.
• Ensuring the operating system on the continuous integration system can load the structure.sql file. On the instance of circleci I encountered, adding $ sudo apt-get update and $ sudo apt install postgres-client, in that order, fixed this problem.

A system working with changes in development does not guarantee those changes will work in all environments, no matter how similar those environments are.