By CSharpened


2012-03-16 11:01:42 8 Comments

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.

11 comments

@GPrathap 2016-08-10 13:30:50

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

@ChickenWing24 2016-09-19 09:46:08

mind explaining what is -a and -q?

@zwacky 2016-11-02 23:25:56

@ChickenWing24 -a: all echo, -q: quiet, -f: file

@Florian 2018-02-13 13:25:17

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.

@pmverma 2012-08-23 05:57:23

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

@matthias krull 2012-10-05 17:29:14

Note that the provided username has to be a valid postgres role. The default is the currently logged in user.

@AlikElzin-kilaka 2018-02-06 09:19:06

Why the -a param?

@mjspier 2018-02-26 21:16:17

@AlikElzin-kilaka -a is not needed here. It's "Print all nonempty input lines to standard output as they are read"

@MDK 2017-07-28 21:43:09

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.

@Deepu Sahni 2017-01-12 21:49:20

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

@martin 2017-10-27 11:16:28

This works if you don't want to set environment variables or use strange pass files. =)

@Rachid Oussanaa 2014-10-04 12:00:54

You should do it like this:

\i path_to_sql_file

See:

Enter image description here

@Zac 2017-03-23 15:11:23

I get Permission denied

@Ulug'bek Ro'zimboyev 2017-04-10 12:39:12

after doing chmod 777 myfile error Permission denied was fixed

@pgsandstrom 2018-06-13 09:00:50

@Zac if your Permission denied happened on a windows machine, you might be using the wrong slashes.

@Waldemar Tsiamruk 2019-04-05 18:31:01

this should be an accepted answer

@abeginner 2013-03-08 18:04:09

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.

@amphetamachine 2014-08-22 14:21:37

I think you mean -d "postgres"

@Yaz 2016-06-30 12:40:56

@amphetamachine -d for database name , check psql --help

@Satish Sharma 2012-11-26 08:11:00

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

@AlexG 2014-12-14 15:25:59

this solution is more solid as for me; not the one marked as answe

@Eric Leschinski 2012-10-16 15:04:30

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)

@mKane 2018-05-03 01:00:10

How do you set up a user? It's my first time installing and running -f on a new .sql file. Always says wrong password

@a_horse_with_no_name 2012-03-16 11:13:10

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

@CSharpened 2012-03-16 11:17:07

Hi. I have now set the settings to trust but I notice that the software is trying to use my windows username in order to login. I want to use a role that I have setup in my Postgresql database. Is there a way of telling it which role to use to run the command?

@a_horse_with_no_name 2012-03-16 11:18:26

@CSharpened: use the -u parameter as documented in the manual

@Kriil 2016-06-17 14:23:47

#2 is extremely simple. Just add one line containing host:port:db:user:pass to a file and you're done. Nice work.

@vishu9219 2015-03-10 07:29:41

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

@Lu32 2016-02-08 19:42:03

how can I do that without output

@vishu9219 2016-02-10 07:43:36

psql -h <host> -d <database> -U <user_name> -p <port> -a -q -w -f <file>.sql

@Rauni Lillemets 2016-02-17 12:14:54

@Lu32 You could also leave out the flag -a (which means "Print all nonempty input lines to standard output as they are read"). EDIT tested, without -a it prints out less, but still too much information. So the -q flag is correct, as vishu9219 said.

Related Questions

Sponsored Content

17 Answered Questions

[SOLVED] How do I run two commands in one line in Windows CMD?

14 Answered Questions

[SOLVED] Which version of PostgreSQL am I running?

  • 2012-12-05 22:34:16
  • Highly Irregular
  • 608678 View
  • 824 Score
  • 14 Answer
  • Tags:   postgresql

41 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

31 Answered Questions

[SOLVED] How do I parse command line arguments in Bash?

16 Answered Questions

[SOLVED] How can I pass arguments to a batch file?

18 Answered Questions

[SOLVED] How to pass command line arguments to a rake task

9 Answered Questions

[SOLVED] List all environment variables from command line?

22 Answered Questions

[SOLVED] Is there an equivalent of 'which' on the Windows command line?

7 Answered Questions

[SOLVED] How to exit from PostgreSQL command line utility: psql

17 Answered Questions

[SOLVED] psql: FATAL: database "<user>" does not exist

  • 2013-07-13 19:18:24
  • Ryan Rich
  • 260720 View
  • 571 Score
  • 17 Answer
  • Tags:   postgresql psql

Sponsored Content