blocks|key|2918290|text|您可以将命令echo到psql输入：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2918291|for+dbname+in+foo+foofoo+foobar+barbar
do
++++echo+"""
CREATE+DATABASE+$dbname+TEMPLATE+mytemplate1
"""+%7C+psql
done|code-block|syntax|javascript|2918292|entityMap^0|6|4|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

you can <code>echo</code> your commands to the psql input:

<pre><code>for dbname in foo foofoo foobar barbar
do
 echo """
CREATE DATABASE $dbname TEMPLATE mytemplate1
""" | psql
done
</code></pre>

blocks|key|247038|text|如果你愿意走得更远，你可能会用sqlalchemy取得更大的成功。它将允许您使用python而不是bash来构建脚本，后者更容易，并且有更好的控制。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|247039|根据评论中的要求：https://github.com/srathbun/sqlCmd|247040|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlalchemy.org/|1|https://github.com/srathbun/sqlCmd^0|F|A|0|0|9|Y|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

If you're willing to go the extra mile, you'll probably have more success with <a href="http://www.sqlalchemy.org/" rel="nofollow">sqlalchemy</a>. It'll allow you to build scripts with python instead of bash, which is easier and has better control.

As requested in the comments: <a href="https://github.com/srathbun/sqlCmd" rel="nofollow">https://github.com/srathbun/sqlCmd</a>

blocks|key|242240|text|首先，不要将psql元命令和SQL命令混合在一起。这些是单独的命令集。有一些技巧可以将它们组合在一起(使用psql元命令psql和\\，并在\o中通过管道将字符串传递给psql)，但这很快就会让人感到困惑。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|242241|242242|使您的文件仅包含SQL命令。|unordered-list-item|242243|不在SQL文件中包含CREATE+DATABASE语句。单独创建数据库，因为您要在同一模板数据库中执行多个文件。|242244|242245|假设您以操作系统用户postgres身份操作，并以(默认)+Postgres超级用户身份使用DB角色postgres，则所有数据库都位于默认端口5432上的同一DB集群中，并且角色postgres具有无密码访问权限，这是由于pg_hba.conf中的IDENT设置(默认设置)。|BOLD|242246|psql+postgres+-c+"CREATE+DATABASE+mytemplate1+WITH+ENCODING+'UTF8'
++++++++++++++++++TEMPLATE+template0"|code-block|syntax|javascript|242247|我基于默认的系统模板数据库template0创建了新的模板数据库。Basics+in+the+manual+here.|242248|您的问题|242249|242250|+How+to+(...)从文件运行一组pgsql+cmd|blockquote|242251|242252|242253|尝试：|242254|psql+mytemplate1+-f+file|242255|目录中批量文件的示例脚本文件：|242256|#!+/bin/sh

for+file+in+/path/to/files/*;+do
++++psql+mytemplate1+-f+"$file"
done|242257|命令选项SQL让psql在文件中执行-f命令。|242258|242259|如何在命令行中基于现有模板创建数据库|242260|242261|242262|psql+-c+'CREATE+DATABASE+my_db+TEMPLATE+mytemplate1'|242263|命令选项SQL让psql执行单个-c命令字符串。可以是多个命令，由;终止-将在一个事务中执行，并且只返回最后一个命令的结果。|242264|阅读有关psql+command+options+in+the+manual的信息。|242265|如果您不提供要连接的数据库，psql将连接到名为"postgres“的默认维护数据库。在第二个答案中，我们连接到哪个数据库并不重要。|242266|entityMap|0|LINK|mutability|MUTABLE|url|https://www.postgresql.org/docs/current/sql-createdatabase.html|1|https://www.postgresql.org/docs/current/app-psql.html^0|6|4|E|3|1T|2|1Y|2|0|0|0|A|F|0|0|0|3V|A|8|1E|8|2I|8|34|B|3H|5|0|0|0|1N|D|9|X|Q|0|0|0|4|0|0|0|T|0|0|0|0|3|0|0|0|F|0|0|0|N|8|4|I|2|0|0|0|I|0|0|0|0|0|1Q|8|4|G|2|X|1|0|0|16|4|Y|1|0|0|1U|E|4|0^^$0|@$1|2|3|4|5|6|7|21|8|@$9|22|A|23|B|C]|$9|24|A|25|B|C]|$9|26|A|27|B|C]|$9|28|A|29|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|2A|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|2B|8|@]|D|@]|E|$]]|$1|J|3|K|5|I|7|2C|8|@$9|2D|A|2E|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|2F|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|2G|8|@$9|2H|A|2I|B|O]|$9|2J|A|2K|B|C]|$9|2L|A|2M|B|C]|$9|2N|A|2O|B|C]|$9|2P|A|2Q|B|C]|$9|2R|A|2S|B|C]]|D|@]|E|$]]|$1|P|3|Q|5|R|7|2T|8|@]|D|@]|E|$S|T]]|$1|U|3|V|5|6|7|2U|8|@$9|2V|A|2W|B|O]|$9|2X|A|2Y|B|C]]|D|@$9|2Z|A|30|1|31]]|E|$]]|$1|W|3|X|5|6|7|32|8|@$9|33|A|34|B|O]]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|35|8|@]|D|@]|E|$]]|$1|Z|3|10|5|11|7|36|8|@$9|37|A|38|B|O]]|D|@]|E|$]]|$1|12|3|-4|5|6|7|39|8|@]|D|@]|E|$]]|$1|13|3|-4|5|6|7|3A|8|@]|D|@]|E|$]]|$1|14|3|15|5|6|7|3B|8|@$9|3C|A|3D|B|O]]|D|@]|E|$]]|$1|16|3|17|5|R|7|3E|8|@]|D|@]|E|$S|T]]|$1|18|3|19|5|6|7|3F|8|@$9|3G|A|3H|B|O]]|D|@]|E|$]]|$1|1A|3|1B|5|R|7|3I|8|@]|D|@]|E|$S|T]]|$1|1C|3|1D|5|6|7|3J|8|@$9|3K|A|3L|B|O]|$9|3M|A|3N|B|C]|$9|3O|A|3P|B|C]]|D|@]|E|$]]|$1|1E|3|-4|5|6|7|3Q|8|@]|D|@]|E|$]]|$1|1F|3|1G|5|11|7|3R|8|@$9|3S|A|3T|B|O]]|D|@]|E|$]]|$1|1H|3|-4|5|6|7|3U|8|@]|D|@]|E|$]]|$1|1I|3|-4|5|6|7|3V|8|@]|D|@]|E|$]]|$1|1J|3|1K|5|R|7|3W|8|@]|D|@]|E|$S|T]]|$1|1L|3|1M|5|6|7|3X|8|@$9|3Y|A|3Z|B|O]|$9|40|A|41|B|C]|$9|42|A|43|B|C]|$9|44|A|45|B|C]]|D|@]|E|$]]|$1|1N|3|1O|5|6|7|46|8|@$9|47|A|48|B|O]]|D|@$9|49|A|4A|1|4B]]|E|$]]|$1|1P|3|1Q|5|6|7|4C|8|@$9|4D|A|4E|B|O]|$9|4F|A|4G|B|C]]|D|@]|E|$]]|$1|1R|3|-4|5|6|7|4H|8|@]|D|@]|E|$]]]|1S|$1T|$5|1U|1V|1W|E|$1X|1Y]]|1Z|$5|1U|1V|1W|E|$1X|20]]]]

First off, do not mix <code>psql</code> meta-commands and <code>SQL</code> commands. These are separate sets of commands. There are tricks to combine those (using the psql meta-commands <code>\o</code> and <code>\\</code> and piping strings to psql in the shell), but that gets confusing quickly.
<ul>
<li>Make your files contain only SQL commands.</li>
<li>Do not include the <code>CREATE DATABASE</code> statement in the SQL files. Create the db separately, you have multiple files you want to execute in the same template db.</li>
</ul>
Assuming you are operating as OS user <code>postgres</code> and use the DB role <code>postgres</code> as (default) Postgres superuser, all databases are in the same DB cluster on the default port 5432 and the role <code>postgres</code> has password-less access due to an <code>IDENT</code> setting in <code>pg_hba.conf</code> - a default setup.
<pre><code>psql postgres -c &quot;CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8'
 TEMPLATE template0&quot;
</code></pre>
I based the new template database on the default system template database <code>template0</code>. <a href="https://www.postgresql.org/docs/current/sql-createdatabase.html" rel="noreferrer">Basics in the manual here.</a>
<h3>Your questions</h3>
<blockquote>
How to (...) run a set of pgsql cmds from file
</blockquote>
Try:
<pre><code>psql mytemplate1 -f file
</code></pre>
Example script file for batch of files in a directory:
<pre class="lang-sh prettyprint-override"><code>#! /bin/sh

for file in /path/to/files/*; do
 psql mytemplate1 -f &quot;$file&quot;
done
</code></pre>
The command option <code>-f</code> makes <code>psql</code> execute SQL commands in a file.
<blockquote>
How to create a database based on an existing template at the command line
</blockquote>
<pre><code>psql -c 'CREATE DATABASE my_db TEMPLATE mytemplate1'
</code></pre>
The command option <code>-c</code> makes <code>psql</code> execute a single SQL command string. Can be multiple commands, terminated by <code>;</code> - will be executed in one transaction and only the result of the last command returned. 
Read about <a href="https://www.postgresql.org/docs/current/app-psql.html" rel="noreferrer">psql command options in the manual</a>.
If you don't provide a database to connect to, <code>psql</code> will connect to the default maintenance database named &quot;postgres&quot;. In the second answer it is irrelevant which database we connect to.

blocks|key|2918382|text|将sql脚本存储在根目录下使用dev、tst、prd参数化数据库使用find运行所有pgsql脚本，如下所示，here在出错时退出|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2918383|或者只是git从here克隆整个工具|2918384|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/YordanGeorgiev/pgsql-runner/blob/master/src/bash/pgsql-runner/funcs/run-pgsql-scripts.func.sh|1|https://github.com/YordanGeorgiev/pgsql-runner^0|1J|4|0|0|8|4|1|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@$A|Q|B|R|1|S]]|C|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|O]]]]

Store your sql scripts under a root dir
Use dev,tst,prd parametrized dbs
Use find to run all your pgsql scripts as shown <a href="https://github.com/YordanGeorgiev/pgsql-runner/blob/master/src/bash/pgsql-runner/funcs/run-pgsql-scripts.func.sh" rel="nofollow noreferrer">here</a>
Exit on errors

Or just git clone the whole tool from <a href="https://github.com/YordanGeorgiev/pgsql-runner" rel="nofollow noreferrer">here</a>

blocks|key|245994|text|对于您必须这样做的用例...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|245995|下面是我用来将JSON导入PostgreSQL+(WSL+Ubuntu)的脚本，它基本上要求您在同一命令行中混合使用psql+meta命令和SQL。注意有点晦涩的script命令的用法，它分配了一个伪tty：|245996|$+more+update.sh
#!/bin/bash
wget+<filename>.json
echo+'\set+content+`cat+$(ls+-t+<redacted>.json.*+%7C+head+-1)`+\\+delete+from+<rable>;+insert+into+<table>+values(:'"'content');+refresh+materialized+view+<view>;+"+%7C+PGPASSWORD=<passwd>+psql+-h+<host>+-U+<user>+-d+<database>
$+|code-block|syntax|javascript|245997|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

For that use case where you have to do it....
Here is a script I've used for importing JSON into PostgreSQL (WSL Ubuntu), which basically requires that you mix psql meta commands and SQL in the same command line. Note use of the somewhat obscure script command, which allocates a pseudo-tty:
<pre><code>$ more update.sh
#!/bin/bash
wget &lt;filename&gt;.json
echo '\set content `cat $(ls -t &lt;redacted&gt;.json.* | head -1)` \\ delete from &lt;rable&gt;; insert into &lt;table&gt; values(:'&quot;'content'); refresh materialized view &lt;view&gt;; &quot; | PGPASSWORD=&lt;passwd&gt; psql -h &lt;host&gt; -U &lt;user&gt; -d &lt;database&gt;
$ 
</code></pre>

I am trying to automate a set of procedures that create TEMPLATE databases.
I have a set of files (file1, file2, ... fileN), each of which contains a set of pgsql commands required for creating a TEMPLATE database.
The contents of the file (createdbtemplate1.sql) looks roughly like this:
<pre><code>CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8';

\c mytemplate1

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

-- Add C language extension + functions
\i db_funcs.sql
</code></pre>
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:
<pre><code># 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
</code></pre>
Any suggestions on how to do this? (As you may have guessed, I'm a shell scripting newbie.)
<h3>Edit</h3>
To clarify the question further, I want to know:
<ol>
<li>How to write groksqlcommands.sh (a bash script that will run a set of pgsql cmds from file)</li>
<li>How to create a database based on an existing template at the command line</li>
</ol>

Shell script to execute pgsql commands in files

PostgreSQL

数据库

Ubuntu

JSON

我正在尝试自动化一组创建模板数据库的过程。我有一组文件(file1、file2、...fileN)，每个文件都包含创建模板数据库所需的一组pgsql命令。文件(createdbtemplate1.sql)的内容大致如下：CREATE DATABASE mytemplate1 WITH ENCODING 'UTF8';\...

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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