Applying database migrations with ansible

user_12703797 0
Viewed 916

I do want to apply migrations when fetching build artifact with ansible.

Some facts:

  • Migrations are pure-sql
  • Postresql is in cluster mode (no sharding)
  • There are 2 hosts (api1 and api2)
  • No "down-mechanics" required

Possible problems:

  • How to choose which host should run migration (first ready or always primary)?
  • How to track already applied scripts and avoid double applience?

My thoughts about how to solve this:

Using fs-layer:

  • Every build artifact would contain sql-migrations
  • Once deployed, primary host should diff existing migration files with new ones and apply new ones, save state by replacement old files with new ones
  • Migrations are executed with community.postgresql.postgresql_query

This way looks weird - fs is not reliable in my opinion and primary host can change (be destroed and etc.)

Using database:

  • Every build artifact would contain sql-migrations
  • Once deployed, first-ready host should query some table for applied migrations and apply new ones, save state by community.postgresql.postgresql_query inserting new migration names

This way looks better - db is still reliable, but is it possible using ansible?

I'm not familiar with python, so i can't accept solutions like "Write your own plugin" - I won't be able to support it in the future. And any NPM packages is not preferred, i'm limited to compiled solutions and built-in linux tools like python, bash, etc.

btw, playbook looks like:

---
- hosts: api
  vars:
    api_apps:
      - name: "Api"
        description: "API service"
        exec: "/usr/local/bin/handler"
        identifier: "handler"
        env:
          - name: PORT
            value: "3000"
          - name: DB_TYPE
            value: 'postgres'
          - name: DB_PATH
            value: "postgres://{{hostvars['db01'].postgresql_users[0].name}}:{{hostvars['db01'].postgresql_users[0].pass}}@{{hostvars['db01'].private_ip}}:{{hostvars['db01'].postgresql_port}}/{{hostvars['db01'].postgresql_databases[0].name}}"
          - name: ACCESS_CONTROL_ALLOW_ORIGIN
            value: "\"*""
          - name: ACCESS_CONTROL_MAX_AGE
            value: 9600
  tasks:
    - block: # Pull and extract artifacts
      - name: "[ api ] Pull release info"
        uri:
          url: "https://api.github.com/repos/<my_profile>/{{ item }}/releases/latest"
          headers:
            Authorization: "Bearer {{ github_token }}"
          return_content: true
        register: release_response
        no_log: True
        with_items:
          - "<my_private_repo>"

      - name: "[ api ] Pull latest artifact"
        get_url:
          url: "{{ item.json.assets[0].url }}"
          headers:
            Accept: "application\/octet-stream"
            Authorization: "Bearer {{ github_token }}"
          force: true
          dest: "/tmp/{{ item.item }}.zip"
        no_log: True
        register: pulled_items
        with_items: "{{ release_response.results }}"

      - name: Extract archives
        unarchive:
          src: "/tmp/{{ item.item.item }}.zip"
          dest: "/usr/local/bin"
          remote_src: yes
        no_log: True
        with_items: "{{ pulled_items.results }}"

      - name: create service templates
        template:
          src: "files/api/service.j2"
          dest: "/etc/systemd/system/{{item.identifier}}.service"
        with_items: "{{ api_apps }}"
        no_log: False

      - name: Start services
        systemd:
          state: restarted
          daemon_reload: yes
          name: "{{item.identifier}}"
        with_items: "{{api_apps}}"
ansible postgresql linux bash
1 Answers

How to track already applied scripts and avoid double applience?

In database world there are two primary ways of automatic database migration.

  1. State-based migration
  • Source control all database objects in a consistent state
  • Deploying tool compares repository state against live DB and generate differential script
  • Script are object definition like: CREATE TABLE ...
  • Example: SQL Server Data Tools
  1. Migration-based approach
  • Source control: objects and changes that are evolving
  • Deploy the scripts
  • Scripts are mostly like: ALTER/CREATE OR REPLACE
  • Example: Redgate Flyway

Each approach has its own pros and cons. I am not aware of any existing tool that support state-migration for PosrgreSQL.

As for avoiding douple appliance the same script:

Ideally each SQL script should be idempotent and orthogonal.

For instance if you need to add a new column:

ALTER TABLE tab ADD COLUMN new_col INT;

This script is not idempotent, because the second run will fail. So we could safeguard it as:

ALTER TABLE tab ADD COLUMN IF NOT EXISTS new_col INT;

Another common example is adding dictionary data:

-- instead of 
INSERT INTO tab(col, ...) VALUES (...);

-- use upsert to handle existing data
INSERT INTO tab(col, ...)
VALUES(....) 
ON CONFLICT (...) 
DO

Another issue is correct script order. For instance when creating a view that uses a new column, ALTER table has to be first.

As we can see something that was going to be trivial is going trickier and trickier.

Fortunately we have tools for it. The main idea is a migration tracking table, that contains a full history which scripts were already run. And by ordering SQL scripts we have full control of what is deployed.

Related: Version control tools

user_50708790
Related