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

User · Answer

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

User · Answer

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

User · Answer

Via the terminal log on to your database and try this   database-   gt  pathof mysqlfile sql   or  database-  gt -i pathof mysqlfile sql   or  database-  gt -c pathof mysqlfile sql

User · Answer

export PGPASSWORD  lt password gt  psql -h  lt host gt  -d  lt database gt  -U  lt user name gt  -p  lt port gt  -a -w -f  lt file gt  sql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

2021 Solution if your PostgreSQL database is on your system locally  psql dbname  lt  sqldump sql username  If its hosted online psql -h hostname dbname  lt  sqldump sql username  If you have any doubts or questions  please ask them in the comments

User · Answer

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

User · Answer

You can open a command prompt and run as administrator  Then type      bin gt 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

User · Answer

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

User · Answer

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

User · Answer

You should do it like this    i path to sql file   See

[postgresql] Run a PostgreSQL .sql file using command line arguments

Examples related to postgresql

Examples related to command-line

Examples related to psql