Postgres Pro
Downloads
Home   >   mailing lists

Thread: Automated pg_Dump issue

Automated pg_Dump issue

From
Chris Campbell
Date:

Hello,  (using Windows 7 Professional & a local install of PostgreSQL 9.0.4)

 

I’m trying to write a call to perform an automated backup using pg_Dump where there is no password prompt.

 

The error I keep getting is that it failed because I didn’t supply a password.

 

The database name I’m trying to backup is called: dd3_000

 

The call string is:

"c:\program files (x86)\PostgreSQL\9.0\bin\PG_dump.exe" --create --no-password --format="c" --superuser="postgres" --username="postgres" --file="c:\temp\pg.backup" "dd3_000"

 

I read in an earlier post where there were two ways to do this.  One way discussed was to use the pgpass.conf file, which I’ve created and looks like this:

 

localhost:5432:dd3_000:postgres:postgres  (These are the out-of-the-box user name and password)

 

The contents of my pg_hba.conf are:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD

# IPv4 local connections:

host       all           all           127.0.0.1/32      md5

# IPv6 local connections:

host       all           all           ::1/128 md5

 

I’ve rebooted after every little config change but still no luck on getting this to work.

 

Any suggestions would be greatly appreciated…

 

Regards,

 

Chris Campbell

Cascade Data Solutions, Inc.

ccampbell@CascadeDS.com

(800) 280-2090

 

Re: Automated pg_Dump issue

From
Carlo Ascani
Date:
On Wed, 31 Aug 2011 14:13:07 -0700
Chris Campbell <ccampbell@cascadeds.com> wrote:

> The call string is:
> "c:\program files (x86)\PostgreSQL\9.0\bin\PG_dump.exe" --create
> --no-password --format="c" --superuser="postgres"
> --username="postgres" --file="c:\temp\pg.backup" "dd3_000"
>

I'm not so strong in Windows, but specifying the -h 127.0.0.1
option should be sufficient for you.



--
 Carlo Ascani - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 carlo.ascani@2ndQuadrant.it - www.2ndQuadrant.it

Re: Automated pg_Dump issue

From
Chris Campbell
Date:
-----Original Message-----
From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Carlo Ascani
Sent: Friday, September 02, 2011 1:42 AM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Automated pg_Dump issue

On Wed, 31 Aug 2011 14:13:07 -0700
Chris Campbell <ccampbell@cascadeds.com> wrote:

>> The call string is:
>> "c:\program files (x86)\PostgreSQL\9.0\bin\PG_dump.exe" --create
>> --no-password --format="c" --superuser="postgres"
>> --username="postgres" --file="c:\temp\pg.backup" "dd3_000"
>>

>I'm not so strong in Windows, but specifying the -h 127.0.0.1
>option should be sufficient for you.


Thank you for your reply.  Unfortunately it made no difference.  I've revised the call somewhat based on some other
finetuning but the password prompt remains.  And when I include the no-password parameter if fails with a no password
suppliederror 

"c:\program files (x86)\PostgreSQL\9.0\bin\PG_dump.exe" --host="27.0.0.1" --username="postgres" --no-password
--file="c:\temp\pg.backup"--disable-dollar-quoting --format=plain --inserts --column-inserts "dd3_000" 

What dark magic is involved in setting the pgpass.conf file to allow pg_dump to work without a password prompt?  I set
itbased on the suggestion given in the older post but it doesn't seem to matter what I put in there:
(localhost:5432:dd3_000:postgres:postgres)
The pgpass.conf file exists in the root of my database cluster.  Is that correct?  Thanks for any suggestions...

Re: Automated pg_Dump issue

From
Carlo Ascani
Date:
On Fri, 2 Sep 2011 06:39:57 -0700
Chris Campbell <ccampbell@cascadeds.com> wrote:

>
> What dark magic is involved in setting the pgpass.conf file to allow
> pg_dump to work without a password prompt?  I set it based on the
> suggestion given in the older post but it doesn't seem to matter what
> I put in there: (localhost:5432:dd3_000:postgres:postgres) The
> pgpass.conf file exists in the root of my database cluster.  Is that
> correct?  Thanks for any suggestions...
>

Reading from postgresql documentation [1]:
"On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf
(where %APPDATA% refers to the Application Data subdirectory in the
user's profile)."

Anyway, you could use pgAdmin III to store the password (in the server's properties).
That operation automatically creates a correct pgpass.conf file, so pg_dump will use that file.

[1] http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html

Waiting for feedback
--
 Carlo Ascani - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 carlo.ascani@2ndQuadrant.it - www.2ndQuadrant.it

Re: Automated pg_Dump issue

From
Chris Campbell
Date:
>Reading from postgresql documentation [1]:
>"On Microsoft Windows the file is named %APPDATA%\postgresql\pgpass.conf
>(where %APPDATA% refers to the Application Data subdirectory in the
>user's profile)."

Yeah I'm an idiot.  Right settings, wrong location.  I had this preconceived notion that pgpass file went in the same
folderas all the other config files. 

Works now.  Grazie tanto!

Chris