Postgresql – Postgres one-liner import & export SQL without login & without asked for password

postgresql

I'm looking for one-liner command for import & export SQL without login into postgres role or psql prompt.

Below is what I got for import SQL, but it asking password every time:

psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Below is equivalent command in MySQL (import & export):

mysql -u root -p password dbname < /var/www/db.sql

mysqldump -u root -p password --single-transaction --skip-extended-insert --quick dbname > /var/www/db.sql

Best Answer

psql doesn't provide a command line option for the password to avoid disclosing it to other local users.

However it accepts the PGPASSWORD environment variable, so your equivalent of mysql's -p password is, in shell syntax:

PGPASSWORD=password psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Alternatively, a client-side password file may be used.

Related Solutions

Postgresql – Export/Import Subset of data in postgres

Your problem is that the output (row structure) of your query does not match either of your tables.

A possible solution would be creating a temporary table to put in your randomly generated IDs and copying data from one table at a time, using the necessary joins.

BEGIN;

CREATE TEMPORARY TABLE doc_ids ON COMMIT DROP AS 
SELECT 1 + floor(random() * 1040000)::integer AS documentUUID
  FROM generate_series(1, 500) g;

COPY (
      SELECT d.*
        FROM Document d
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/document_data.sql;

COPY (
      SELECT c.*
        FROM Citation c
        JOIN Document d ON d.citation_id = c.citationUUID
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/citation_data.sql;

COMMIT;

Note that I removed the double quotes as they don't make any difference for the given table and column names. Also I don't see what is the merit of grouping when generating the random document IDs. Furthermore, I won't call the IDs ...UUID as UUID is a specific type used very often in very diverse applications.

Postgresql – postgres makes db dump without password

ops, how stupid I am...

i have used 1 time pgadmin and logged to my external postgress server it made in my home directory .pgpass file

if i delete it - pg_dump doesn't work without password

my error!

Best Answer

Related Solutions

Postgresql – Export/Import Subset of data in postgres

Postgresql – postgres makes db dump without password

Related Question