bash 用于在文件中执行 pgsql 命令的 Shell 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8594717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Shell script to execute pgsql commands in files
提问by Homunculus Reticulli
I am trying to automate a set of procedures that create TEMPLATE databases.
我正在尝试自动化一组创建 TEMPLATE 数据库的过程。
I have a set of files (file1, file2, ... fileN), each of which contains a set of pgsql commands required for creating a TEMPLATE database.
我有一组文件（file1、file2、...fileN），每个文件都包含一组创建 TEMPLATE 数据库所需的 pgsql 命令。
The contents of the file (createdbtemplate1.sql) looks roughly like this:
文件 (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
I want to be able to write a shell script that will execute the commands in the file, so that I can write a script like this:
我希望能够编写一个 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
Any suggestions on how to do this? (As you may have guessed, I'm a shell scripting newbie.)
关于如何做到这一点的任何建议？（您可能已经猜到了，我是一个 shell 脚本新手。）
Edit
编辑
To clarify the question further, I want to know:
为了进一步澄清这个问题，我想知道：
- How to write groksqlcommands.sh (a bash script that will run a set of pgsql cmds from file)
- How to create a database based on an existing template at the command line
- 如何编写 groksqlcommands.sh（一个 bash 脚本，它将从文件运行一组 pgsql cmds）
- 如何在命令行基于现有模板创建数据库
回答by Erwin Brandstetter
First off, do notmix psqlmeta-commands and SQLcommands. These are separate sets of commands. There are tricks to combine those (using the psql meta-commands \oand \\and piping strings to psql in the shell), but stay away from that if you can.
首先，千万不能混psql元命令和SQL命令。这些是单独的命令集。有一些技巧可以将它们组合起来（在 shell 中使用 psql 元命令\o和\\管道字符串到 psql），但如果可以，请远离它。
- Make your files contain only SQL commands.
- Do not include the CREATE DATABASE statement in the SQL files. Create the db separately, you have multiplefiles you want to execute in the same template db.
- 使您的文件仅包含 SQL 命令。
- 不要在 SQL 文件中包含 CREATE DATABASE 语句。单独创建数据库，您有多个文件要在同一个模板数据库中执行。
I assume you are operating as system user postgresand you have postgresas Postgres superuser. All databases in the same db cluster on the default port 5432 and the user postgreshas password-less access due to an IDENTsetting in pg_hba.conf(default settings).
我假设您以系统用户身份运行，postgres并且您postgres以 Postgres 超级用户身份运行。默认端口 5432 上同一数据库集群中的所有数据库，并且postgres由于（默认设置）中的IDENT设置，用户具有无密码访问权限pg_hba.conf。
psql postgres -c "CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8'
TEMPLATE template0"
I base the new template db on the system template db template0. Read more about that here.
Your question:
我将新模板 db 建立在系统模板 db 上template0。在此处阅读更多相关信息。
你的问题：
How to (...) run a set of pgsql cmds from file
如何（...）从文件运行一组 pgsql cmds
Try:
尝试：
psql mytemplate1 -f file
Example for batch:
批处理示例：
#! /bin/sh
for file in /path/to/files/*; do
psql mytemplate1 -f "$file"
done
The command option -fmakes psqlexecute SQL commands in a file.
命令选项-f使psql在文件中执行 SQL 命令。
How to create a database based on an existing template at the command line
如何在命令行基于现有模板创建数据库
psql -c 'CREATE DATABASE myDB TEMPLATE mytemplate1'
The command option -cmakes psqlexecute a single SQL command string. Can be multiple commands, terminated by ;- will be executed in onetransaction and only the result of the last command returned.
Read about psql command options in the manual.
命令选项-c使psql执行单个 SQL 命令字符串。可以是多个命令，以;-终止，将在一个事务中执行并且只返回最后一个命令的结果。
阅读手册中的 psql 命令选项。
If you don't provide a database to connect to, psqlwill use the default maintenance database named postgres. In the second answer it is irrelevant which database we connect to.
如果您不提供要连接的数据库，psql则将使用名为 的默认维护数据库postgres。在第二个答案中，我们连接到哪个数据库无关紧要。
回答by Elias Dorneles
you can echoyour commands to the psql input:
您可以echo将命令发送到 psql 输入：
for dbname in foo foofoo foobar barbar
do
echo """
CREATE DATABASE $dbname TEMPLATE mytemplate1
""" | psql
done
回答by Spencer Rathbun
If you're willing to go the extra mile, you'll probably have more success with sqlalchemy. It'll allow you to build scripts with python instead of bash, which is easier and has better control.
如果您愿意加倍努力，那么使用sqlalchemy可能会取得更大的成功。它将允许您使用 python 而不是 bash 构建脚本，这更容易并且具有更好的控制。
As requested in the comments: https://github.com/srathbun/sqlCmd
根据评论中的要求：https: //github.com/srathbun/sqlCmd
