使用 Ansible 运行 PostgreSQL 脚本

Run a PostgreSQL script using Ansible

提问by ryekayo

I am looking for a way to run a Postgres script using Ansible. While I found a reasonably good example Here, I need to:

我正在寻找一种使用 Ansible 运行 Postgres 脚本的方法。虽然我在这里找到了一个相当不错的例子，但我需要：

• Run the script as user postgres
• I don't necessarily need to keep a copy of the script on the server so if I need to have a copy, it will only be for temp use.

• 以用户 postgres 运行脚本
• 我不一定需要在服务器上保留脚本的副本，所以如果我需要一个副本，它只会用于临时使用。

Can anyone tell me if this is possible and if so an example of running it. Here is what I tried so far using Ansible and it just hung at these points:

谁能告诉我这是否可行，如果可以，请告诉我运行它的示例。这是我到目前为止使用 Ansible 尝试过的，它只是挂在这些点上：

 - name: Testing DB to make sure it is available
   command: psql -U bob image
   register: b
 - debug: b

 - name: Verifying Tables exist in Image
   shell: \d image
   register: c
 - debug: c

 - name: Exiting Image DB
   shell: \q
   register: d
 - debug: d

 - name: Going to Agent DB
   command: psql -U bob agent
   register: e
 - debug: e

This always hangs at the first part of it when logging into the image DB.

当登录到图像数据库时，它总是挂在它的第一部分。

回答by Craig Ringer

Why it doesn't work

为什么它不起作用

This:

这个：

 - name: Testing DB to make sure it is available
   command: psql -U bob image
   register: b
 - debug: b

 - name: Verifying Tables exist in Image
   shell: \d image
   register: c
 - debug: c

doesn't do what you think it does.

不会做你认为它会做的事情。

The first command runs psql -U bob image. This starts a psqlsession. psqlwaits for input from stdin. Ansible will never send any, it is simply waiting for the command you specified to exit, so it can check the exit code.

第一个命令运行psql -U bob image。这将启动一个psql会话。psql等待来自标准输入的输入。Ansible 永远不会发送任何内容，它只是等待您指定的命令退出，因此它可以检查退出代码。

So Ansible waits for psqlto exit, and psqlwaits for Ansible to send some input.

所以 Ansible 等待psql退出，并psql等待 Ansible 发送一些输入。

Each task in Ansible is independent. The shellor commandmodules do not change the shell that subsequent commands run in. You simply can't do this the way you expect.

Ansible 中的每个任务都是独立的。该shell或command后续的命令在运行模块不更换的外壳，你根本就做不到这一点，你所期望的方式。

Even if psqlexited after the first task (or went to the background), you'd just get an error from the second task like:

即使psql在第一个任务之后退出（或进入后台），您也会从第二个任务中得到一个错误，例如：

bash: d: command not found

So the way you're trying to do this just isn't going to work.

所以你试图这样做的方式是行不通的。

How to do it

怎么做

You need to run each task as a separate psqlcommand, with a command string:

您需要将每个任务作为单独的psql命令运行，并带有命令字符串：

 - name: Testing DB to make sure it is available
   command: psql -U bob image -c 'SELECT 1;'

 - name: Verifying Tables exist in Image
   command: psql -U bob image -c '\d image'

... or with standard input, except that Ansible doesn't seem to support supplying a variable as stdin to a command.

...或使用标准输入，除了 Ansible 似乎不支持将变量作为 stdin 提供给命令。

... or with a (possibly templated) SQL script:

...或使用（可能是模板化的）SQL 脚本：

- name: Template sql script
  template:  src="my.sql.j2" dest="{{sometemplocation}}/my.sql"

- name: Execute sql script
  shell: "psql {{sometemplocation}}/my.sql"

- name: Delete sql script
  file: path="{{sometemplocation}}/my.sql" state=absent

Alternately you can use Ansible's built-in support for querying PostgreSQL to do it, but in that case you cannot use the psqlclient's backslash commands like \d, you'd have to use only SQL. Query information_schemafor table info, etc.

或者，您可以使用 Ansible 对查询 PostgreSQL 的内置支持来执行此操作，但在这种情况下，您不能使用psql客户端的反斜杠命令，例如\d，您必须仅使用 SQL。查询information_schema表信息等。

Here's how some of my code looks

这是我的一些代码的外观

Here's an example from an automation module I wrote that does a lot with PostgreSQL.

这是我编写的自动化模块中的一个示例，该模块对 PostgreSQL 做了很多工作。

Really, I should just suck it up and write a psqlAnsible task that runs commands via psql, rather than using shell, which is awful and clumsy. For now, though, it works. I use connection strings that're assigned from variables or generated using set_factto reduce the mess a bit and make connections more flexible.

真的，我应该把它psql搞定并编写一个Ansible 任务，通过 运行命令psql，而不是使用shell，这是可怕和笨拙的。不过，就目前而言，它有效。我使用从变量分配或生成的连接字符串set_fact来减少混乱并使连接更加灵活。

- name: Wait for the target node to be ready to be joined
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_join_target_dsn}}' -qAtw 'SELECT bdr.bdr_node_join_wait_for_ready();'"

- name: Template pre-BDR-join SQL script
  template:  src="{{bdr_pre_join_sql_template}}" dest="{{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql"

- name: Execute pre-BDR-join SQL script
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -f {{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql"

- name: Delete pre-BDR-join SQL script
  file: path="{{postgres_install_dir}}/bdr_pre_join_{{inventory_hostname}}.sql" state=absent

- name: bdr_group_join
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -c \"SELECT bdr.bdr_group_join(local_node_name := '{{inventory_hostname}}', node_external_dsn := '{{bdr_node_dsn}}', join_using_dsn := '{{bdr_join_target_dsn}}');\""

- name: Template post-BDR-join SQL script
  template:  src="{{bdr_post_join_sql_template}}" dest="{{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql"

- name: Execute post-BDR-join SQL script
  shell: "{{postgres_install_dir}}/bin/psql '{{bdr_node_dsn}}' -qAtw -f {{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql"

- name: Delete post-BDR-join SQL script
  file: path="{{postgres_install_dir}}/bdr_post_join_{{inventory_hostname}}.sql" state=absent

回答by MillerGeek

The answer that Craig gives is good, but fails to solve the problem of running the commands as a specific user. That can be done with my additions to his code:

Craig 给出的答案很好，但未能解决以特定用户身份运行命令的问题。这可以通过我添加到他的代码来完成：

- name: Testing DB to make sure it is available
  become: true
  become_user: postgres
  command: psql -U bob image -c 'SELECT 1;'

- name: Verifying Tables exist in Image
  become: true
  become_user: postgres
  command: psql -U bob image -c '\d image'

Note the "become" and "become_user" parameters. These will tell Ansible to change to the correct user before running the commands.

注意“become”和“become_user”参数。这些将告诉 Ansible 在运行命令之前更改为正确的用户。

IMPORTANT:Ansible Version 1.9 and earlier use sudo: yesand sudo_user: postgresinstead of become: trueand become_user: postgres

重要提示：Ansible 1.9 及更早版本使用sudo: yesandsudo_user: postgres而不是become: trueandbecome_user: postgres

回答by Roy Wood

Building on the excellent responses above, you can also specify environment variables in your Ansible task as shown below. Note that this assumes you have set up a .pgpass file with the password for the target db.

在上述出色响应的基础上，您还可以在 Ansible 任务中指定环境变量，如下所示。请注意，这假设您已使用目标数据库的密码设置了 .pgpass 文件。

-   name: Execute some sql via psql
    command: psql -f /path/to/your/sql
    environment:
        PGUSER: "{{ db_user }}"
        PGDATABASE: "{{ db_name }}"
        PGHOST: "{{ db_host }}"
        PGPASS: "{{ pgpass_filepath }}"