I was wondering if there is a better way to deploy changes to several Postgres databases on multiple servers. I have been using Ansible for this task, I have a playbook that basically copies the changes to the server, applies them to the databases, and collect the logs using psql:

psql -v ON_ERROR_STOP=1 -f my_changes.sql  > log.out 2> log.err

But I have some problems with this approach with the INDEX creations. Sometimes it takes several minutes or hours to create the index and I need to use background jobs with ansible using [async & poll](https://docs.ansible.com/ansible/latest/user_guide/playbooks_async.html) to allow Ansible run long tasks wihtout close the ssh connection. Also, I think I am using Ansible wrong since it is a configuration tool and this kind of task always reflect a change on the server... Hoping you guys can tell me better approaches, tools( I heard that [pgbarman](https://pgbarman.org/) can also deploy changes) or improvements on my current solution

I'm preparing to upgrade nodes from 3.11.17 to 4.1.7. I've done the upgrade successfully by hand with some dev nodes, but I am wanting to make this more repeatable with Ansible. I am running on Ubuntu servers, so I have to fight with apt auto starting the service. Wondering what would be the most kosher process of doing these upgrades without apt tossing default files at me and possibly messing stuff up. My thoughts are: 1. drain and stop one node 2. Template in my necessary cassandra.yaml and rack properties files 3. Do the install of cassandra 4.1.7 4. Cope with apt auto starting cassandra Does this spell disaster? My data isn't in the default path of the config that comes from the package maintainer (/var/lib/cassandra), and some of my config variables are no longer present in 4.1 so I would expect cassandra to crash on startup, seeing the unrecognized variables (which I came across with my dev tests). What I would REALLY like is to install cassandra and give it a good look over before starting, but I am not afforded that luxury. In numerous tests, stopping cassandra immediately after a successful install did not work, cassandra would persist.

I am running an ansible playbook. The machine running ansible is localhost. The playbook breaks during a task which loads data from a big csv (1.2 GB) into a postgres database table ( module community.postgresql.postgresql_copy ). The task (running on localhost) breaking the execution is: - name: Load data from CSV into table community.postgresql.postgresql_copy: login_host: '{{ db_host }}' login_user: '{{ db_username }}' login_password: '{{ db_password }}' db: '{{ db_database }}' port: '{{ db_database_port }}' copy_from: "{{ path }}/my_big_csv_file.csv" dst: "{{ my_table }}" options: format: csv delimiter: ';' header: yes The error raised was: msg: Cannot execute SQL 'COPY "my_table" FROM '/path/my_big_csv_file.csv' (format csv, delimiter ';', header True)': ERROR: Could not extend file "base/16385/45444.1": only 4096 bytes of 8192 written in block 165767 HINT: Check free disk space. CONTEXT: COPY my_table, line 9615264 I have did some research I have read in this thread that this error shows up when there is lack of space on the disk. However, I have run df -h --output=avail . on localhost, in the folder where the source csv is located, and got 84G as output. So what could be the problem?

I have provisioned my server with Ansible playbook. I've used the root/bedrock-Ansible playbook . One of the tasks was to set up mysql server together with mysql root user password. Now I urgently need to change this password. The steps I took: 1. I updated variables for Ansible roles 2. I executed the command ansible-playbook -i hosts/staging server.yml in order to reprovision the server All tasks were executed as expected (no changes), but the script failed at [mariadb | Set root user password] with this message: msg: unable to connect to database, check login_user and login_password are correct or ~/.my.cnf has the credentials My guess is that once MySQL root password has been set, reprovisioning the server cannot change this password. Is it possible at all to change MySQL root password by reprovisioning the server with Ansible? What are my options?

I sign-up on this site to ask an question. How can I pass those master status information especially **File** and **Position** to an **Ansible variable**, to automate the processess of replication with an Ansible playbook. I want to write this variables in my playbook here.

I had tried to change MariaDB root password using following Ansible script.

- name: Dump MySQL root Password
        debug:
          msg: "MySQL new Password : {{ mysql_pass }}"

      - name: Set MySQL root Password
        become: True
        mysql_user: 
          name=root
          host="localhost"
          password="{{ mysql_pass }}"
          check_implicit_admin=yes
          login_user="root"
          login_password=""
          state=present

In my /etc/ansible/hosts

[myhosts:vars]
mysql_pass=mynewpassword

As I was told password variable may not be taken, I added debug/msg step.

TASK [Dump MySQL root Password] ********************************************************************************************************************************
ok: [vizua@node1] => {
    "msg": "MySQL new Password : mynewpassword"
}

Similar issue discussed here . Thats where I got Password changing steps. This step runs without error, showing changed, but yet I can access without password or any password. MariaDB : Server version: 10.1.47-MariaDB-0ubuntu0.18.04.1 Ansible : ansible 2.5.1

I use the [ansible role](https://github.com/geerlingguy/ansible-role-postgresql) installed PostgreSQL. Its default version is 10.6. How to set another version to install? Such as 12.4.

$ psql --version
psql (PostgreSQL) 10.6

Use this task to create a database on CentOS by Ansible

- name: Create mydb database
  command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
  become_user: postgres

It will pending. Output:

TASK [db_primary : Create mydb database] *********************************************************************************************************
fatal: [stg-master]: FAILED! => {"changed": true, "cmd": ["createdb", "-E", "UNICODE", "-l", "en_US.UTF-8", "-T", "template0", "mydb", "-O", "mydbuser"], "delta": "0:00:44.323037", "end": "2020-09-17 18:44:48.873994", "msg": "non-zero return code", "rc": 1, "start": "2020-09-17 18:44:04.550957", "stderr": "WARNING:  canceling the wait for synchronous replication and terminating connection due to administrator command\nDETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.\ncreatedb: database creation failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.", "stderr_lines": ["WARNING:  canceling the wait for synchronous replication and terminating connection due to administrator command", "DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.", "createdb: database creation failed: server closed the connection unexpectedly", "\tThis probably means the server terminated abnormally", "\tbefore or while processing the request."], "stdout": "", "stdout_lines": []}

Sometimes run the command on the server directly will also got pending. If restart PostgreSQL service will fix the issue. What's the problem? ---

postgres=# SELECT * FROM pg_locks;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |       mode       | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
 virtualxid    |          |          |      |       | 7/10       |               |         |       |          | 7/10               | 30451 | ExclusiveLock    | t       | t
 virtualxid    |          |          |      |       | 6/3        |               |         |       |          | 6/3                | 30290 | ExclusiveLock    | t       | t
 relation      |    13808 |    11577 |      |       |            |               |         |       |          | 3/3                | 29004 | AccessShareLock  | t       | t
 virtualxid    |          |          |      |       | 3/3        |               |         |       |          | 3/3                | 29004 | ExclusiveLock    | t       | t
 relation      |        0 |     2676 |      |       |            |               |         |       |          | 7/10               | 30451 | RowExclusiveLock | t       | f
 transactionid |          |          |      |       |            |           569 |         |       |          | 7/10               | 30451 | ExclusiveLock    | t       | f
 relation      |        0 |     1260 |      |       |            |               |         |       |          | 7/10               | 30451 | RowExclusiveLock | t       | f
 transactionid |          |          |      |       |            |           568 |         |       |          | 7/10               | 30451 | ShareLock        | f       | f
 transactionid |          |          |      |       |            |           568 |         |       |          | 6/3                | 30290 | ExclusiveLock    | t       | f
 relation      |        0 |     2677 |      |       |            |               |         |       |          | 7/10               | 30451 | RowExclusiveLock | t       | f
 relation      |        0 |     1260 |      |       |            |               |         |       |          | 6/3                | 30290 | RowExclusiveLock | t       | f
(11 rows)