copy_database.yml

---
# This dumps the data from a production server
# This will dump the data into a new database, initialize db-migrator and migrate the data/schema.

- name: get database details
  hosts:
    - database
    - publishing
  vars_prompt:
    - name: "source_db_host"
      prompt: "Where is the database hosted?"
      private: no
      default: prod09.cnx.org
    - name: "source_db_port"
      prompt: "What port is the database listening on?"
      private: no
      default: 5432
    - name: "db_name"
      prompt: "What database are we pull from to dump onto this machine?"
      private: no
      default: repository
  tasks:
    - set_fact:
        source_db_host: "{{ source_db_host }}"
        source_db_port: "{{ source_db_port }}"
        source_db_name: "{{ db_name }}"
        db_name: "{{ db_name }}_new"
  tags:
    - db-details

- name: disable replicant
  hosts: replicant
  tasks:
    - name: stop replicant service
      become: yes
      service:
        name: postgresql
        state: stopped
    - name: remove data directory
      become: yes
      file:
        path: /var/lib/postgresql/{{ postgres_version }}/main/
        state: absent
  tags:
    - db-disable-repl

- name: transfer database
  hosts: database
  vars:
    # If working with authoring, set venv_environment to 'authoring' at
    # and set dbmigrate_context_args value to `--content cnx-authoring`
    # at runtime.
    dbmigrate_context_args: "{{ migration_contexts|default('--context cnx-publishing --context cnx-archive') }}"
    db_connection_string: "dbname={{ db_name }} user=postgres host=localhost port=5432"
  tasks:
    - import_tasks: tasks/create_db.yml
      vars:
        db_owner: "postgres"
    - name: pipe database in
      become: yes
      become_user: postgres
      shell: "set -o pipefail && pg_dump -U postgres -h {{ source_db_host }} -p {{ source_db_port }} {{ source_db_name }} | psql -U postgres {{ db_name }} -f -"
      args:
        executable: "/bin/bash"
    - name: change ownership of database
      become: yes
      become_user: postgres
      shell: "psql -U postgres -d {{ db_name }} -c 'ALTER DATABASE {{ db_name }} OWNER TO {{ archive_db_user }};'"
    - name: remove venv schema
      become: yes
      become_user: postgres
      shell: "psql -U postgres -d {{ db_name }} -c 'DROP SCHEMA IF EXISTS venv CASCADE;'"
  tags:
    - db-transfer

- name: set up venv and migrate database
  hosts: publishing
  tasks:
    - name: rerun cnx-db venv
      become: yes
      become_user: www-data
      environment:
        DB_URL: "postgresql://{{ archive_db_user }}:{{ archive_db_password }}@{{ archive_db_host }}:{{ archive_db_port }}/{{ db_name }}"
        DB_SUPER_URL: "postgresql://postgres:{{ archive_db_password }}@{{ archive_db_host }}:{{ archive_db_port }}/{{ db_name }}"
      delegate_to: "{{ groups.database|first }}"
      command: "/var/cnx/venvs/python2/archive/bin/cnx-db venv"

    - name: change ownership of venv schema
      become: yes
      become_user: postgres
      delegate_to: "{{ groups.database|first }}"
      command: "psql -U postgres -d {{ db_name }} -c 'ALTER SCHEMA venv OWNER TO {{ archive_db_user }};'"
    - name: migrate database
      become: yes
      become_user: www-data
      environment:
        # If PGHOST is not set, psql tries to look for cluster "main" which doesn't
        # work:
        #     $ psql -U postgres -l
        #     Error: Invalid data directory
        # This appears to be a problem after a postgresql-common update
        PGHOST: "{{ archive_db_host }}"
      command: "/var/cnx/venvs/python2/publishing/bin/dbmigrator --db-connection-string='postgresql://{{ archive_db_user }}:{{ archive_db_password }}@{{ archive_db_host }}:{{ archive_db_port }}/{{ db_name }}' --config /etc/cnx/publishing/app.ini --context cnx-db migrate"
  tags:
    - db-migrate