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.
This question is related to
postgresql
command-line
psql
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)
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
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.
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.
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)
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
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
You should do it like this:
\i path_to_sql_file
See:
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
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.
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.
export PGPASSWORD=<password>
psql -h <host> -d <database> -U <user_name> -p <port> -a -w -f <file>.sql
Source: Stackoverflow.com