Search code examples
postgresqlcommand-linepsql

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.

Solution

  • 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