Run a PostgreSQL .sql file using command line arguments
Solution 1
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
Solution 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
Solution 3
You should do it like this:
\i path_to_sql_file
See:
Solution 4
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
Solution 5
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.
CSharpened
Currently working as a Software Engineer for LJT Systems Ltd
Updated on July 21, 2021Comments
-
CSharpened almost 3 years
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.