使用命令行参数运行PostgreSQL .sql文件 [英] Run a PostgreSQL .sql file using command line arguments

问题描述

我有一些.sql文件，其中包含成千上万条INSERT语句，并且需要在我的PostgreSQL数据库上运行这些插入内容，以便将它们添加到表中.这些文件太大，以至于无法打开它们并将INSERT语句复制到编辑器窗口中并在其中运行它们.我在互联网上发现可以导航到PostgreSQL安装的bin文件夹来使用以下内容:

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:

psql -d myDataBase -a -f myInsertFile

就我而言:

psql -d HIGHWAYS -a -f CLUSTER_1000M.sql

然后要求我为用户提供密码，但是我无法输入任何内容，并且当我按Enter键时出现此错误:

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:致命:用户"myUsername"的密码身份验证失败

psql: FATAL: password authentication failed for user "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?

我通过在pg_hba.conf文件中添加具有以下结构的新条目来解决此问题:

I got around this issue by adding a new entry in my pg_hba.conf file with the following structure:

# IPv6 local connections:
host    myDbName    myUserName ::1/128    trust

通常可以在PostgreSQL安装的数据"文件夹中找到pg_hba.conf文件.

The pg_hba.conf file can usually be found in the 'data' folder of your PostgreSQL install.

推荐答案

您有四个选择来提供密码:

You have four choices to supply a password:

1. 设置PGPASSWORD环境变量.有关详细信息，请参见手册:
   http://www.postgresql. org/docs/current/static/libpq-envars.html
2. 使用.pgpass文件存储密码.有关详细信息，请参见手册:
   http://www.postgresql. org/docs/current/static/libpq-pgpass.html
3. 对该特定用户使用信任身份验证": http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
4. 自PostgreSQL 9.1起，您还可以使用连接字符串:
   https ://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING

1. Set the PGPASSWORD environment variable. For details see the manual:
   http://www.postgresql.org/docs/current/static/libpq-envars.html
2. Use a .pgpass file to store the password. For details see the manual:
   http://www.postgresql.org/docs/current/static/libpq-pgpass.html
3. Use "trust authentication" for that specific user: http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
4. Since PostgreSQL 9.1 you can also use a connection string:
   https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-CONNSTRING

这篇关于使用命令行参数运行PostgreSQL .sql文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！