Thread: pg_dump/psql: Select a server and automate password

pg_dump/psql: Select a server and automate password

From
Radcon Entec
Date:
Greetings!
 
On my computer, I have a copy of a customer's database for which a full backup file would be about 300 megabytes long.  There are several history tables that are not needed for day-to-day operation of our system.  I came up with a DOS batch file that consists of a sequence of pg_dump commands.  The first dumps the database's schema, and all the rest dump the required tables into individual files.  A companion batch file issues psql repeatedly to recreate the database and the tables. 
 
On my computer, the batch files work fine.  I run them and they happily call pg_dump or psql as many times as they need to, and I don't need to do anything. 
 
I just uploaded the batch files to our customer's machine, and a couple of questions occured to me.  First, pg_dump asks me for a password every time it is invoked.  That doesn't happen on my computer.  What is likely to be set up differently on my computer so that I don't need a password?
 
Second, I see no way to select a server in pg_dump or psql.  I am connected to a computer that was set up for my use specifically for testing.  In PGAdmin on that machine, I have a server talking to localhost and a server talking to the machine that hosts the production database.  I would like to run my batch file on the testing machine, but how do I tell pg_dump to use the server that talks to the production machine?
 
Thank you very much!
 
RobR

Re: pg_dump/psql: Select a server and automate password

From
Adrian Klaver
Date:
On Wednesday 08 April 2009 6:51:44 am Radcon Entec wrote:
> Greetings!
>
> On my computer, I have a copy of a customer's database for which a full
> backup file would be about 300 megabytes long.  There are several history
> tables that are not needed for day-to-day operation of our system.  I came
> up with a DOS batch file that consists of a sequence of pg_dump commands. 
> The first dumps the database's schema, and all the rest dump the required
> tables into individual files.  A companion batch file issues psql
> repeatedly to recreate the database and the tables. 
>
> On my computer, the batch files work fine.  I run them and they happily
> call pg_dump or psql as many times as they need to, and I don't need to do
> anything. 
>
> I just uploaded the batch files to our customer's machine, and a couple of
> questions occured to me.  First, pg_dump asks me for a password every time
> it is invoked.  That doesn't happen on my computer.  What is likely to be
> set up differently on my computer so that I don't need a password?

See pgpass
http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html

>
> Second, I see no way to select a server in pg_dump or psql.  I am connected
> to a computer that was set up for my use specifically for testing.  In
> PGAdmin on that machine, I have a server talking to localhost and a server
> talking to the machine that hosts the production database.  I would like to
> run my batch file on the testing machine, but how do I tell pg_dump to use
> the server that talks to the production machine?

Use the -p switch to point pg_dump at the appropriate server.
http://www.postgresql.org/docs/8.3/interactive/app-pgdump.html

>
> Thank you very much!
>
> RobR



--
Adrian Klaver
aklaver@comcast.net