Run a PostgreSQL .sql file using command line arguments

Question

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.

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 (http://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
Does this answer your question? [How do I specify a password to 'psql' non-interactively?](https://stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively) — rogerdpack, Mar 12 '21 at 18:30

score 701 · Answer 1 · edited Feb 03 '18 at 05:30

701

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

edited Feb 03 '18 at 05:30

Yash Sharma

1,674
2
16
23

answered Aug 23 '12 at 05:57

pmverma

7,011
2
13
2

4

Note that the provided username has to be a valid postgres role. The default is the currently logged in user. – matthias krull Oct 05 '12 at 17:29
25

Why the `-a` param? – AlikElzin-kilaka Feb 06 '18 at 09:19
16

@AlikElzin-kilaka `-a` is not needed here. It's "Print all nonempty input lines to standard output as they are read" – mjspier Feb 26 '18 at 21:16
Is it possible to specify an sql file that is stored at a url? For instance in an s3 bucket @ https://my-bucket.s3.eu-west-2.amazonaws.com/import.sql ? – Timothy Hawkins Jul 06 '23 at 09:24

score 440 · Answer 2 · edited Dec 27 '16 at 11:39

440

You should do it like this:

\i path_to_sql_file

See:

Enter image description here

edited Dec 27 '16 at 11:39

Peter Mortensen

30,738
21
105
131

answered Oct 04 '14 at 12:00

Rachid O

13,013
15
66
92

3

I get `Permission denied` – Zac Mar 23 '17 at 15:11
5

after doing `chmod 777 myfile` error `Permission denied` was fixed – Ulug'bek Apr 10 '17 at 12:39
15

@Zac if your Permission denied happened on a windows machine, you might be using the wrong slashes. – pgsandstrom Jun 13 '18 at 09:00
5

I had this error on windows and it turned out to be the slashes, as @pgsandstrom mentioned. Wrong slashes = use forward slashes instead, and no quote on path. \i c:/dev/script.sql – Dave Oct 28 '19 at 01:01
I am getting `No such file or directory` although the file path has been specified correctly.Any idea? – Sihat Afnan Jun 16 '23 at 05:29

score 193 · Accepted Answer · edited Dec 27 '16 at 11:30

193

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

edited Dec 27 '16 at 11:30

Peter Mortensen

30,738
21
105
131

answered Mar 16 '12 at 11:13

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
2

@CSharpened: use the `-u` parameter as documented in the manual – Mar 16 '12 at 11:18
1

#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

score 80 · Answer 4 · edited Jun 17 '19 at 13:35

80

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

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

EDIT: updated based on the comment provided by @zwacky

edited Jun 17 '19 at 13:35

cegas

2,823
3
16
16

answered Aug 10 '16 at 13:30

GPrathap

7,336
7
65
83

8

@ChickenWing24 `-a`: all echo, `-q`: quiet, `-f`: file – Simon Wicki Nov 02 '16 at 23:25
1

does this work without database? I'm trying to create new database using this tool into existing server. – Janne Harju Nov 10 '21 at 08:16

score 80 · Answer 5 · answered Feb 13 '18 at 13:25

80

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.

answered Feb 13 '18 at 13:25

Florian

980
7
9

As @Florian says, once logged in you can execute a file. Remember to mark any comment lines in your SQL as either of two ways ...-- comment to end of line a) -- one line comment OR b) /* multiple line comments */ – Sumit S Mar 19 '20 at 00:45
Wow, thank you very much, it ran very well. – Wellington Alves Jun 27 '22 at 23:25

score 54 · Answer 6 · edited Oct 09 '15 at 14:05

54

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

edited Oct 09 '15 at 14:05

Alban

3
2

answered Mar 10 '15 at 07:29

vishu9219

761
6
14

1

how can I do that without output – Lu32 Feb 08 '16 at 19:42
2

psql -h -d -U -p -a -q -w -f .sql – vishu9219 Feb 10 '16 at 07:43
3

@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 Lillemets Feb 17 '16 at 12:14

score 30 · Answer 7 · edited Dec 27 '16 at 11:33

30

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

edited Dec 27 '16 at 11:33

Peter Mortensen

30,738
21
105
131

answered Nov 26 '12 at 08:11

Satish Sharma

3,284
9
38
51

this solution is more solid as for me; not the one marked as answe – AlexHalkin Dec 14 '14 at 15:25
Is there any difference between `@`, `-i ` and `-c `? – Chris Dec 22 '22 at 05:22

score 26 · Answer 8 · edited Nov 01 '22 at 06:19

26

You can give both user name and PASSSWORD on the command line itself with the "-d" parameter

   psql -d "dbname='urDbName' user='yourUserName' password='yourPasswd' host='yourHost'" -f yourFileName.sql

edited Nov 01 '22 at 06:19

juanpa

3
2

answered Jan 12 '17 at 21:49

Deepu Sahni

479
5
9

This works if you don't want to set environment variables or use strange pass files. =) – martin Oct 27 '17 at 11:16
2

this should have been the accepted answer, thank you – grepit Jun 09 '20 at 20:58

score 16 · Answer 9 · edited Nov 02 '17 at 09:11

16

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.

edited Nov 02 '17 at 09:11

Ravinder Reddy

23,692
6
52
82

answered Jul 28 '17 at 21:43

MDK

680
1
9
20

score 14 · Answer 10 · answered Jan 09 '21 at 04:29

14

2021 Solution

if your PostgreSQL database is on your system locally.

psql dbname < sqldump.sql username

If its hosted online

psql -h hostname dbname < sqldump.sql username

If you have any doubts or questions, please ask them in the comments.

answered Jan 09 '21 at 04:29

wingman__7

679
13
17

is it OS dependent? – BingLi224 Feb 23 '21 at 07:11
1

@BingLi224 I use a Windows System and I'm pretty sure that it's not OS dependent. – wingman__7 Feb 24 '21 at 09:10
1

Best answer. Applicable to Ubuntu/Debian systems. If you run it from a user with the permission to alter the database, you can even omit the `username` part, so it's just `psql dbname < sqldump.sql`. – Neurotransmitter Dec 27 '21 at 13:04
@Neurotransmitter true, that will work as well. – wingman__7 Dec 27 '21 at 16:54

score 12 · Answer 11 · edited Mar 02 '22 at 17:57

12

psql -h localhost -d userstoreis -U admin -p 5432 -a -q -f /home/jobs/Desktop/resources/postgresql.sql

Parameter explanations:

-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

edited Mar 02 '22 at 17:57

blurfus

13,485
8
55
61

answered Jul 20 '21 at 14:52

Amazigh Coder

121
1
2

score 11 · Answer 12 · edited Dec 27 '16 at 11:32

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)

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 — mKane, May 03 '18 at 01:00

score 4 · Answer 13 · edited Dec 27 '16 at 11:34

4

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.

edited Dec 27 '16 at 11:34

Peter Mortensen

30,738
21
105
131

answered Mar 08 '13 at 18:04

abeginner

41
1

1

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

score 2 · Answer 14 · answered May 26 '21 at 15:42

2

A small improvement in @wingman__7 's 2021 answer: if your username contains certain characters (an underscore in my case), you need to pass it with the -U flag.
This worked for me:

$ psql -h db.host -d db_name -U my_user < query.sql

answered May 26 '21 at 15:42

jmm

1,044
2
12
38

score 1 · Answer 15 · edited Apr 13 '20 at 13:04

I achived that wrote (located in the directory where my script is)

::someguy@host::$sudo -u user psql -d my_database -a -f file.sql

where -u user is the role who owns the database where I want to execute the script then the psql connects to the psql console after that -d my_database loads me in mydatabase finally -a -f file.sql where -a echo all input from the script and -f execute commands from file.sql into mydatabase, then exit.

I'm using: psql (PostgreSQL) 10.12 on (Ubuntu 10.12-0ubuntu0.18.04.1)

score 1 · Answer 16 · edited May 27 '22 at 14:01

1

Try using the following command in the command line console:

psql -h localhost -U postgres -f restore.sql

edited May 27 '22 at 14:01

lemon

14,875
6
18
38

answered May 27 '22 at 06:54

p8ul

2,212
19
19

Alex · Answer 17 · 2023-06-10T18:14:36.697

1

sudo -u postgres -i .

postgres is username who you use to install postgres

psql -d demo -a -f demo.sql

demo -is name db

demo.sql file sql backup

edited Jun 10 '23 at 18:14

answered Jun 10 '23 at 18:13

Alex

11
2

Run a PostgreSQL .sql file using command line arguments

17 Answers17

Linked

Related