current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 178 down vote favorite
52

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.

share|improve this question
3  
You've already had your answer but just in case... "I cannot enter anything", might you be talking about the fact that typing your password doesn't show anything? That's normal in this case, normally typing the password and hitting Enter should work... – Évelyne Lachance Aug 24 '14 at 20:12
    
I had a similar problem installing a copy of ITIS (itis.gov). The database didn't exist, so I couldn't use its name. Because of the way PostgreSQL works, I could do this: psql --port=5554 --username=root --file=ITIS.sql template1 – Cyberknight Jan 16 '15 at 15:40
up vote 59 down vote accepted

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
share|improve this answer
    
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? – CSharpened Mar 16 '12 at 11:17
1  
@CSharpened: use the -u parameter as documented in the manual – a_horse_with_no_name Mar 16 '12 at 11:18
    
#2 is extremely simple. Just add one line containing host:port:db:user:pass to a file and you're done. Nice work. – Kriil Jun 17 '16 at 14:23
up vote 195 down vote

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
share|improve this answer
3  
Note that the provided username has to be a valid postgres role. The default is the currently logged in user. – matthias krull Oct 5 '12 at 17:29
    
It has worked for me aswell ^ ^ Thankyou! – Bilal Hasan Jan 26 '15 at 15:24
    
This should be the accepted answer. – wswld Jul 24 '15 at 8:28
up vote 115 down vote

You should do it like this:

\i path_to_sql_file

See:

Enter image description here

share|improve this answer
up vote 19 down vote 
export PGPASSWORD=<password>
psql -h <host> -d <database> -U <user_name> -p <port> -a -w -f <file>.sql
share|improve this answer
1  
how can I do that without output – Lu32 Feb 8 '16 at 19:42
    
psql -h <host> -d <database> -U <user_name> -p <port> -a -q -w -f <file>.sql – vishu9219 Feb 10 '16 at 7:43
2  
@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. – Rauni Feb 17 '16 at 12:14
up vote 13 down vote

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
share|improve this answer
    
this solution is more solid as for me; not the one marked as answe – AlexG Dec 14 '14 at 15:25
up vote 8 down vote

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

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

share|improve this answer
1  
mind explaining what is -a and -q? – ChickenWing24 Sep 19 '16 at 9:46
    
@ChickenWing24 -a: all echo, -q: quiet, -f: file – zwacky Nov 2 '16 at 23:25
up vote 5 down vote

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)
share|improve this answer
up vote 3 down vote

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.

share|improve this answer
    
I think you mean -d "postgres" – amphetamachine Aug 22 '14 at 14:21
    
@amphetamachine -d for database name , check psql --help – Yaz Jun 30 '16 at 12:40
up vote 2 down vote

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.