使用命令行参数运行 PostgreSQL .sql 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9736085/
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
Run a PostgreSQL .sql file using command line arguments
提问by CSharpened
I have some .sql files with thousands of INSERT statements in them and need to run these inserts on my PostgreSQL database in order to add them to a table. The files are that large that it is impossible to open them and copy the INSERT statements into an editor window and run them there. I found on the Internet that you can use the following by navigating to the bin folder of your PostgreSQL install:
我有一些 .sql 文件，其中包含数千条 INSERT 语句，需要在我的 PostgreSQL 数据库上运行这些插入，以便将它们添加到表中。这些文件太大了，无法打开它们并将 INSERT 语句复制到编辑器窗口中并在那里运行。我在 Internet 上发现您可以通过导航到 PostgreSQL 安装的 bin 文件夹来使用以下内容：
psql -d myDataBase -a -f myInsertFile
In my case:
就我而言：
psql -d HIGHWAYS -a -f CLUSTER_1000M.sql
I am then asked for a password for my user, but I cannot enter anything and when I hit enter I get this error:
然后我被要求为我的用户输入密码，但我无法输入任何内容，当我按回车键时，我收到此错误：
psql: FATAL: password authentication failed for user "myUsername"
psql：致命：用户“myUsername”的密码验证失败
Why won't it let me enter a password. Is there a way round this as it is critical that I can run these scripts?
为什么它不让我输入密码。有没有办法解决这个问题，因为我可以运行这些脚本至关重要？
I got around this issue by adding a new entry in my pg_hba.conf file with the following structure:
我通过在我的 pg_hba.conf 文件中添加一个具有以下结构的新条目来解决这个问题：
# IPv6 local connections:
host myDbName myUserName ::1/128 trust
The pg_hba.conf file can usually be found in the 'data' folder of your PostgreSQL install.
pg_hba.conf 文件通常可以在 PostgreSQL 安装的“data”文件夹中找到。
采纳答案by a_horse_with_no_name
You have four choices to supply a password:
您有四种选择来提供密码：
- Set the PGPASSWORD environment variable. For details see the manual:
http://www.postgresql.org/docs/current/static/libpq-envars.html - Use a .pgpass file to store the password. For details see the manual:
http://www.postgresql.org/docs/current/static/libpq-pgpass.html - Use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
- Since PostgreSQL 9.1 you can also use a connection string:
https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
- 设置 PGPASSWORD 环境变量。有关详细信息，请参阅手册：http:
//www.postgresql.org/docs/current/static/libpq-envars.html - 使用 .pgpass 文件来存储密码。有关详细信息，请参阅手册：http:
//www.postgresql.org/docs/current/static/libpq-pgpass.html - 对该特定用户使用“信任身份验证”：http: //www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
- 从 PostgreSQL 9.1 开始，您还可以使用连接字符串：https:
//www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING
回答by pmverma
Of course, you will get a fatal error for authenticating, because you do not include a user name...
当然，您会在进行身份验证时遇到致命错误，因为您没有包含用户名...
Try this one, it is OK for me :)
试试这个，对我来说没问题:)
psql -U username -d myDataBase -a -f myInsertFile
If the database is remote, use the same command with host
如果数据库是远程的，请使用与主机相同的命令
psql -h host -U username -d myDataBase -a -f myInsertFile
回答by Rachid O
You should do it like this:
你应该这样做：
\i path_to_sql_file
See:
看：
回答by GPrathap
Use this to execute *.sql files when the PostgreSQL server is located in a difference place:
当 PostgreSQL 服务器位于不同的地方时，使用它来执行 *.sql 文件：
psql -h localhost -d userstoreis -U admin -p 5432 -a -q -f /home/jobs/Desktop/resources/postgresql.sql
-h PostgreSQL server IP address
-d database name
-U user name
-p port which PostgreSQL server is listening on
-f path to SQL script
-a all echo
-q quiet
Then you are prompted to enter the password of the user.
然后系统会提示您输入用户的密码。
EDIT: updated based on the comment provided by @zwacky
编辑：根据@zwacky 提供的评论更新
回答by vishu9219
export PGPASSWORD=<password>
psql -h <host> -d <database> -U <user_name> -p <port> -a -w -f <file>.sql
回答by Florian
If you are logged in into psql on the Linux shell the command is:
如果您在 Linux shell 上登录到 psql，命令是：
\i fileName.sql
for an absolute path and
对于绝对路径和
\ir filename.sql
for the relative path from where you have called psql.
用于调用 psql 的相对路径。
回答by Satish Sharma
Via the terminal log on to your database and try this:
通过终端登录到您的数据库并尝试以下操作：
database-# >@pathof_mysqlfile.sql
or
或者
database-#>-i pathof_mysqlfile.sql
or
或者
database-#>-c pathof_mysqlfile.sql
回答by Deepu Sahni
You can give both user name and PASSSWORD on the command line itself.
您可以在命令行本身上同时提供用户名和密码。
psql "dbname='urDbName' user='yourUserName' password='yourPasswd' host='yourHost'" -f yourFileName.sql
回答by MDK
you could even do it in this way:
你甚至可以这样做：
sudo -u postgres psql -d myDataBase -a -f myInsertFile
If you have sudoaccess on machine and it's not recommended for production scripts just for test on your own machine it's the easiest way.
如果您sudo可以在机器上访问并且不建议仅用于在您自己的机器上测试的生产脚本，那么这是最简单的方法。
回答by Eric Leschinski
Walk through on how to run an SQL on the command line for PostgreSQL in Linux:
演练如何在 Linux 中的 PostgreSQL 命令行上运行 SQL：
Open a terminal and make sure you can run the psqlcommand:
打开终端并确保您可以运行以下psql命令：
psql --version
which psql
Mine is version 9.1.6 located in /bin/psql.
我的是 9.1.6 版，位于/bin/psql.
Create a plain textfile called mysqlfile.sql
创建一个名为的纯文本文件 mysqlfile.sql
Edit that file, put a single line in there:
编辑该文件，在其中放一行：
select * from mytable;
Run this command on commandline (substituting your username and the name of your database for pgadmin and kurz_prod):
在命令行上运行此命令（将您的用户名和数据库名称替换为 pgadmin 和 kurz_prod）：
psql -U pgadmin -d kurz_prod -a -f mysqlfile.sql
The following is the result I get on the terminal (I am not prompted for a password):
以下是我在终端上得到的结果（我没有被提示输入密码）：
select * from mytable;
test1
--------
hi
me too
(2 rows)
