playbook_db.yml

- hosts: db_host
  vars_files:
    - ./vars.yml
  tasks:
    - name: Update and upgrade packages
      become: yes
      apt:
        update_cache: yes
        upgrade: dist

    - name: dowload fail2ban
      become: yes
      apt:
        name: fail2ban
        state: present
        update_cache: yes

    - name: turn on Fail2ban
      systemd:
        name: fail2ban
        enabled: yes

    - name: run Fail2ban
      become: yes
      systemd:
        name: fail2ban
        state: restarted

    - name: Install curl and ca-certificates
      become: yes
      apt:
        name:
          - curl
          - ca-certificates
        state: present
        update_cache: yes

    - name: Delete the conflicting PostgreSQL source, if it exists
      become: yes
      file:
        path: /etc/apt/sources.list.d/pgdg.list
        state: absent

    - name: Create directory for PostgreSQL repository key
      become: yes
      file:
        path: /usr/share/postgresql-common/pgdg
        state: directory
        mode: '0755'

    - name: Download PostgreSQL repository key
      become: yes
      get_url:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        dest: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    - name: Add PostgreSQL APT repository
      become: yes
      apt_repository:
        repo: "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt {{ ansible_lsb.codename }}-pgdg main"
        state: present
        
    - name: Update APT cache after adding PostgreSQL repo
      become: yes
      apt:
        update_cache: yes

    - name: Install PostgreSQL 16
      become: yes
      apt:
        name: postgresql-16
        state: present


    - name: Restart PostgreSQL
      become: yes
      service:
        name: postgresql
        state: restarted

    - name: Enable PostgreSQL service
      become: true
      systemd:
        name: postgresql
        state: started
        enabled: yes
        daemon-reload: yes
    
    - name: key Webmin
      ansible.builtin.get_url:
        url: http://www.webmin.com/jcameron-key.asc
        dest: /tmp/jcameron-key.asc

    - name: set key Webmin
      become: yes
      ansible.builtin.command:
        cmd: apt-key add /tmp/jcameron-key.asc
        creates: /etc/apt/trusted.gpg.d/jcameron.gpg

    - name: rep Webmin
      become: yes
      ansible.builtin.apt_repository:
        repo: "deb http://download.webmin.com/download/repository sarge contrib"
        state: present

    - name: Update
      become: yes
      ansible.builtin.apt:
        update_cache: yes

    - name: dowload Webmin
      become: yes
      ansible.builtin.apt:
        name: webmin
        state: present

    - name: Run Webmin
      systemd:
        name: webmin
        state: started

    - name: Set password for "{{ DB_USER }}"
      become: yes
      become_user: postgres
      shell: "psql -c \"ALTER USER {{ DB_USER }} WITH PASSWORD '{{ DB_PASSWORD }}';\""

    - name: Check if exists
      command: "psql -U {{ DB_USER }} -lqt | cut -d \\| -f 1 | grep -qw \"{{ DB_DATABASE }}\""
      ignore_errors: true
      register: db_check_result
      failed_when: false

    - name: Create PostgreSQL db
      become: yes
      become_user: postgres
      command: "createdb -U {{ DB_USER }} {{ DB_DATABASE }}"
      when: db_check_result.rc != 0
      failed_when: false
      register: db_created

    - name: Edit pg_hba
      become: yes
      become_user: postgres
      postgresql_pg_hba:
        dest: /etc/postgresql/16/main/pg_hba.conf
        contype: "host"
        databases: "all"
        users: "all"
        source: "0.0.0.0/0"
        method: "md5"
        create: true

    - name: Edit pg_hba
      become: yes
      become_user: postgres
      postgresql_pg_hba:
        dest: /etc/postgresql/16/main/pg_hba.conf
        contype: "host"
        databases: "replication"
        users: "all"
        source: "0.0.0.0/0"
        method: "md5"
        create: true

    - name: Install psycopg2 for PostgreSQL
      become: yes
      apt: 
        name: python3-psycopg2
        state: present
      when: ansible_distribution == "Ubuntu" or ansible_distribution == "Debian"

    - name: Create arch dir
      become: yes
      file:
        path: /oracle/pg_data/archive/
        state: directory

    - name: chmod 777 /oracle/pg_data/archive/
      become: yes
      file:
        path: /oracle/pg_data/archive/
        mode: '0777'
        state: directory

    - name: Edit postgresql.conf
      become: yes
      become_user: postgres
      community.postgresql.postgresql_set:
        name: "{{ item.key }}"
        value: "{{ item.value }}"
      with_dict:
        max_connections: '100'
        listen_addresses: '*'
        log_replication_commands: 'on'
        unix_socket_directories: '/var/run/postgresql'
        logging_collector: 'on'
        log_connections: 'on'
        log_disconnections: 'on'
        log_directory: '/var/log/postgresql'
        log_filename: 'postgresql.log'
        log_file_mode: '0664'
        wal_level: 'replica'
        max_wal_senders: '10'
        wal_log_hints: 'on'
        archive_mode: 'on'
        archive_command: 'cp %p /oracle/pg_data/archive/%f'
        password_encryption: 'scram-sha-256'

    - name: Create replication user '{{ DB_REPL_USER }}' with password '{{ DB_REPL_PASSWORD }}'
      become: yes
      become_user: postgres
      command: "psql -c \"CREATE USER {{ DB_REPL_USER }} REPLICATION LOGIN ENCRYPTED PASSWORD '{{ DB_REPL_PASSWORD }}';\""
      ignore_errors: yes

    - name: Copy init.sql
      copy:
        src: "init.sql"
        dest: "~/init.sql"

    - name: Read init.sql
      slurp:
        src: init.sql
      register: init

    - name: initialization DB
      become: yes
      become_user: postgres
      ignore_errors: yes
      postgresql_query:
        db: int-4
        query: "{{ init.content | b64decode }}"
        login_user: "{{ DB_USER }}"
        login_password: "{{ DB_PASSWORD }}"
      when: db_created.changed


- hosts: db_repl_host
  vars_files:
    - ./vars.yml
  tasks:
  
    - name: Install curl and ca-certificates
      become: yes
      apt:
        name:
          - curl
          - ca-certificates
        state: present
        update_cache: yes

    - name: Create directory for PostgreSQL repository key
      become: yes
      file:
        path: /usr/share/postgresql-common/pgdg
        state: directory
        mode: '0755'

    - name: Download PostgreSQL repository key
      become: yes
      get_url:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        dest: /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc

    - name: Add PostgreSQL APT repository
      become: yes
      apt_repository:
        repo: "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt {{ ansible_lsb.codename }}-pgdg main"
        state: present
    
    - name: Update APT cache after adding PostgreSQL repo
      become: yes
      apt:
        update_cache: yes

    - name: Install PostgreSQL 16
      become: yes
      apt:
        name: postgresql-16
        state: present

    - name: Restart PostgreSQL
      become: yes
      service:
        name: postgresql
        state: restarted

    - name: Enable PostgreSQL service
      become: true
      systemd:
        name: postgresql
        state: started
        enabled: yes
        daemon-reload: yes

    - name: Clear directory
      become: yes
      ansible.builtin.file:
        state: absent
        path: /var/lib/postgresql/data

    - name: RUN Replication
      become: yes
      become_user: postgres
      environment:
        PGUSER: "{{ DB_USER }}"
        PGPASSWORD: "{{ DB_PASSWORD }}"
      shell: pg_basebackup --pgdata=/var/lib/postgresql/data -R --host="{{ DB_HOST }}" --port="{{ DB_PORT }}"