Run a PostgreSQL .sql file using command line argu

问题:

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

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"

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:

# 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.

回答1:

You have four choices to supply a password:

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

回答2:

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

回答3:

You should do it like this:

\i path_to_sql_file

See:

回答4:

export PGPASSWORD=<password>
psql -h <host> -d <database> -U <user_name> -p <port> -a -w -f <file>.sql

回答5:

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

回答6:

Use this to execute *.sql files when the PostgreSQL server is located in a difference place:

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 
-f file

Then you are prompted to enter the password of the user.

EDIT: updated based on the comment provided by @zwacky

回答7:

If you are logged in into psql on the Linux shell the command is:

\i fileName.sql

for an absolute path and

\ir filename.sql

for the relative path from where you have called psql.

回答8:

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

回答9:

you could even do it in this way:

sudo -u postgres psql -d myDataBase -a -f myInsertFile

If you have sudo access on machine and it's not recommended for production scripts just for test on your own machine it's the easiest way.

回答10:

Walk through on how to run an SQL on the command line for PostgreSQL in Linux:

Open a terminal and make sure you can run the psql command:

psql --version
which psql

Mine is version 9.1.6 located in /bin/psql.

Create a plain textfile called 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):

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)

回答11:

You can open a command prompt and run as administrator. Then type

../bin>psql -f c:/...-h localhost -p 5432 -d databasename -U "postgres"

Password for user postgres: will show up.

Type your password and enter. I couldn't see the password what I was typing, but this time when I press enter it worked. Actually I was loading data into the database.