Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2017
    Posts
    12

    Answered: export of table causing authentication issue

    Hi,

    I am trying to take export dump of a table. But it's giving me authentication issue -

    C:\Temp>SET PGPASSWORD=MyPass123
    C:\Temp>"C:\Program Files\PostgreSQL\9.6\bin\psql.exe" -h localhost -U postgres -d postgres -p 5432
    psql (9.6.2)
    WARNING: Console code page (437) differs from Windows code page (1252)
    8-bit characters might not work correctly. See psql reference
    page "Notes for Windows users" for details.
    Type "help" for help.

    C:\Temp>pg_dump -t EMP postgres > emp.sql
    pg_dump: [archiver (db)] connection to database "postgres" failed: FATAL: password authentication failed for user "gondkarg"


    The same password works when I try to connect to postgres from command line. However, pg_dump says, password authentication failed for user "gondkarg" (that's my system login user name).

    Can someone help me to figure out what's wrong here.

    Thanks,
    -Anand

  2. Best Answer
    Posted by shammat

    "You are connecting as the postgres user with psql, but you do not specify a user for pg_dump (so it takes the current Windows user, not "postgres"). You need to use "-U postgres" for pg_dump as well."


  3. #2
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    You are connecting as the postgres user with psql, but you do not specify a user for pg_dump (so it takes the current Windows user, not "postgres"). You need to use "-U postgres" for pg_dump as well.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Mar 2017
    Posts
    12
    Hi Shammat,

    Thanks for the point. I corrected it now, but getting "no matching tables...." error -

    C:\Temp>pg_dump -t EMP -U postgres > emp.sql
    pg_dump: no matching tables were found

    However, if you look at my DB in below image, there's a table called EMP -

    Click image for larger version. 

Name:	POST.png 
Views:	1 
Size:	25.7 KB 
ID:	17313

    Thanks,
    -Anand

  5. #4
    Join Date
    Mar 2017
    Posts
    12
    For example, below works -

    C:\Temp>SET PGPASSWORD=Post123

    C:\Temp>"C:\Program Files\PostgreSQL\9.6\bin\psql.exe" -h localhost -U postgres -d postgres -p 5432
    psql (9.6.2)
    WARNING: Console code page (437) differs from Windows code page (1252)
    8-bit characters might not work correctly. See psql reference
    page "Notes for Windows users" for details.
    Type "help" for help.

    postgres=# select * from anand."EMP" limit 2;
    EMPID | EMPNAME | DEPTID
    -------+---------+--------
    1 | JHON | 1
    2 | LUCY | 1
    (2 rows)

  6. #5
    Join Date
    Nov 2003
    Posts
    2,988
    Provided Answers: 23
    Quote Originally Posted by anand_gp View Post
    However, if you look at my DB in below image, there's a table called EMP -
    The table is called "EMP", not emp. You created it using those dreaded double quotes.
    Now you need to use them always when referencing the table (e.g. select * from "EMP" instead of select * from EMP)

    See the manual for details: https://www.postgresql.org/docs/curr...AX-IDENTIFIERS

    The best thing to do is to never use double quotes in SQL.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #6
    Join Date
    Mar 2017
    Posts
    12
    Hi Shammat,

    Thanks for your clarification. I recreated my table and export worked fine. Thanks again!

    Regards,
    -Anand

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •