用于执行文件中 pgsql 命令的 Shell 脚本

Question

我试图自动化创建TEMPLATE数据库的一组过程。

我有一组文件（file1，file2，… fileN），每个文件都包含用于创建TEMPLATE数据库所需的一组pgsql命令。

文件的内容（createdbtemplate1.sql）大致如下：

CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8';

\c mytemplate1

CREATE TABLE first_table ( 
 --- fields here ..
);

-- Add C language extension + functions
\i db_funcs.sql

我希望能编写一个Shell脚本，以便执行文件中的命令，这样我就可以像这样编写脚本：

# run commands to create TEMPLATE db mytemplate1
# ./groksqlcommands.sh createdbtemplate1.sql

for dbname in foo foofoo foobar barbar
do
    # Need to simply create a database based on an existing template in this script
    psql CREATE DATABASE $dbname TEMPLATE mytemplate1
done

有没有关于如何做到这一点的建议？（正如你可能猜到的那样，我是一个shell脚本的新手。）

编辑

进一步澄清问题，我想知道：

1. 如何编写groksqlcommands.sh（一个将从文件运行一组pgsql命令的bash脚本）
2. 如何在命令行基于现有模板创建数据库

- Homunculus Reticulli

---

你的问题含糊不清。首先，你想使用多个文件创建一个模板数据库。但是，你的代码示例尝试基于模板数据库创建多个数据库。你到底想做什么？ - Erwin Brandstetter

@ErwinBrandstetter：不完全正确。我会尝试澄清一下。一个（SQL命令）文件包含一组文件，用于创建一个单独的模板数据库。 （SQL命令）文件与模板数据库之间存在1:1的映射关系。我想创建一个Bash脚本来自动化数据库的创建。创建的数据库是从早些时候在脚本中创建的模板数据库“派生”的。根据运行时脚本变量，Bash脚本创建不同的模板数据库（及其派生）。希望对你有所帮助。 - Homunculus Reticulli

更正：一个（SQL命令）文件包含一组SQL命令，用于创建一个单一的模板数据库。 - Homunculus Reticulli

Answer 1

首先，不要将psql元命令和SQL命令混合使用。这些是不同的命令集合。虽然可以通过使用 psql 元命令 \o 和 \\ 并在 shell 中将字符串传输到 psql 来合并它们，但容易混淆。

• 确保您的文件只包含 SQL 命令。
• 不要在 SQL 文件中包含CREATE DATABASE 语句。请单独创建数据库，如果有多个文件需要在同一个模板数据库中执行。

假设您以 OS 用户postgres运行，并使用 DB 角色postgres作为(默认)的 Postgres 超级用户，所有数据库都在默认端口 5432 上的同一 DB 集群中，并且由于pg_hba.conf中的 IDENT 设置 - 这是一个默认设置 - 角色postgres可以无密码访问。

psql postgres -c "CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8'
                  TEMPLATE template0"

我基于默认的系统模板数据库template0创建了新的模板数据库。这里是手册中的基础知识。

您的问题

如何从文件运行一组pgsql命令

尝试：

psql mytemplate1 -f file

批处理目录文件的示例脚本文件：

#! /bin/sh

for file in /path/to/files/*; do
    psql mytemplate1 -f "$file"
done

-f 命令选项可使 psql 从文件中执行 SQL 命令。

如何在命令行基于现有模板创建数据库？

psql -c 'CREATE DATABASE my_db TEMPLATE mytemplate1'

命令选项-c使psql执行单个SQL命令字符串。可以是多个命令，以;分隔 - 将在一个事务中执行，并且只返回最后一个命令的结果。
请阅读手册中关于psql命令选项的内容。 如果您没有提供要连接的数据库，则psql将连接到默认的维护数据库“postgres”。在第二个答案中，我们连接到哪个数据库是不相关的。

- Erwin Brandstetter

Answer 2

您可以使用echo命令将您的命令输出到psql输入界面：

for dbname in foo foofoo foobar barbar
do
    echo """
CREATE DATABASE $dbname TEMPLATE mytemplate1
""" | psql
done

- Elias Dorneles

---

我猜你的答案与我问题的第二部分有关（顺便说一句，psql不会像你所写的那样提示用户名和密码，是吗？）。从上面并不清楚如何执行文件中的单个命令（除非你建议读取文件中的行，迭代这些行并将其回显到psql中？） - Homunculus Reticulli

echo 中输入的内容与在 psql 提示符中正常输入的内容相同，因此您可以执行 \i file_with_commands.sql 来执行整个文件，或者使用 sed 创建过滤器，然后将其输出传递给 psql，例如 echo "CREATE DATABASE __dbname__" | sed "s/__db_name/$variable_with_db_name/g" | psql。至于 psql 是否要求密码取决于您的 PostgresSQL 配置，但如果您不想配置没有密码的用户，仍然可以手动输入密码。 - Elias Dorneles

Answer 3

将您的SQL脚本存储在根目录下

使用参数化的dev，tst，prd数据库

使用find命令运行所有pgsql脚本，如此处所示

出现错误时退出

或者从这里完整地克隆整个工具

- Yordan Georgiev

Answer 4

如果你愿意付出额外的努力，那么使用sqlalchemy可能会更加成功。它可以让你使用Python而不是Bash来构建脚本，这样更容易且控制更好。

根据评论中的要求：https://github.com/srathbun/sqlCmd

- Spencer Rathbun

---

嗯，你可能有点道理。事实上，我刚刚（今天！）开始使用SQLAlchemy……但是学习曲线陡峭，我认为使用Bash会更容易/更快一些。 - Homunculus Reticulli

@HomunculusReticulli 哦，使用bash绝对更容易。但是，任何有用的东西都会被重复使用，而你告诉自己不会出现在一次性脚本中的所有这些问题都会回来咬你一口。 - Spencer Rathbun

恰巧，我也创建了一个非常类似的东西，尽管数据库是mssql实例，并且我还有一些其他要求。这花费了我大部分时间，有一些细节我希望能够做得不同。但是大多数情况下，我希望我使用了sqlalchemy。如果你感兴趣，我可以将代码上传到github供您查看。 - Spencer Rathbun

我同意使用bash方法虽然（表面上）现在更快，但以后可能会带来麻烦。我更喜欢sqlAlchemy的方法 - 特别是如果我有一个起点可以工作。看到你之前所做的事情将非常有帮助。请将你的代码上传到github，这样我就可以看到它是否可以给我一些使用sqlalchemy的想法。谢谢。 - Homunculus Reticulli

谢谢。我已经克隆了代码库。我会尽快查看它。希望它能成为一个很好的起点！ - Homunculus Reticulli

Answer 5

对于必须执行此操作的用例...

这是我在将JSON导入PostgreSQL（WSL Ubuntu）时使用的脚本，基本上需要您在同一命令行中混合使用psql元命令和SQL。请注意使用有点模糊的脚本命令，它分配了一个伪终端：

$ more update.sh
#!/bin/bash
wget <filename>.json
echo '\set content `cat $(ls -t <redacted>.json.* | head -1)` \\ delete from <rable>; insert into <table> values(:'"'content'); refresh materialized view <view>; " | PGPASSWORD=<passwd> psql -h <host> -U <user> -d <database>
$ 

- Joseph H