CODEWITHSUNDEEP
postgresqlcommand-linepsql
CSharpened
CSharpened

Reputation: 12574

Run a PostgreSQL .sql file using command line arguments

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.

Upvotes: 807

Views: 1371304

Answers (18)

Florian
Florian

Reputation: 1130

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

for an absolute path:

\i fileName.sql

for the relative path:

\ir filename.sql

both from where you have called psql.

Upvotes: 95

GPrathap
GPrathap

Reputation: 7820

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

Upvotes: 94

jadki
jadki

Reputation: 492

The following is my preferred method as well as how to save the results to a txt file:

export PGPASSWORD='your_password'
psql -U postgres -h localhost -d mydatabase -a -f /path/to/your/sqlfile.sql -o /path/to/your/output.txt

Upvotes: 0

Alex
Alex

Reputation: 11

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

Upvotes: 1

Deepu Sahni
Deepu Sahni

Reputation: 519

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

Upvotes: 29

p8ul
p8ul

Reputation: 2320

Try using the following command in the command line console:

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

Upvotes: 2

Amazigh Coder
Amazigh Coder

Reputation: 141

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

Upvotes: 14

jmm
jmm

Reputation: 1064

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

Upvotes: 2

wingman__7
wingman__7

Reputation: 749

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.

Upvotes: 17

Chars
Chars

Reputation: 11

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)

Upvotes: 1

pmverma
pmverma

Reputation: 7659

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

Upvotes: 765

MDK
MDK

Reputation: 690

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.

Upvotes: 16

Rachid O
Rachid O

Reputation: 14032

You should do it like this:

\i path_to_sql_file

See:

Enter image description here

Upvotes: 489

abeginner
abeginner

Reputation: 41

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.

Upvotes: 4

Satish Sharma
Satish Sharma

Reputation: 3294

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

Upvotes: 30

Eric Leschinski
Eric Leschinski

Reputation: 154083

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)

Upvotes: 11

user330315
user330315

Reputation:

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

Upvotes: 198

vishu9219
vishu9219

Reputation: 791

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

Upvotes: 57

Related Questions