Thread: shell script to SQL statement: `pg_dump | psql -U`

I have a perl program that I use (also) to copy a database to a
perfect clone having a different name. To perform the operation I
execute from perl the following shell pipe

pg_dump -U postgres -h db_server database_name | psql -U postgres -h
db_server database_name_backup

I'd like to perform the same operation using SQL statement. Is it
possible?

The problem with the current implementation is that sometime pd_dump
and psql ask for password but I want to create the database copy
without any user typing (it is a cron script).

"filippo" <filippo2991@virgilio.it> writes:
> The problem with the current implementation is that sometime pd_dump
> and psql ask for password but I want to create the database copy
> without any user typing (it is a cron script).

Create a ~/.pgpass file for the user that runs the cron job.

            regards, tom lane

filippo wrote:
> The problem with the current implementation is that sometime pd_dump
> and psql ask for password but I want to create the database copy
> without any user typing (it is a cron script)

Consider using the PGPASSWORD environment variable or .pgpass file.

--
   Richard Huxton
   Archonet Ltd

On 20 Mar, 16:47, t...@sss.pgh.pa.us (Tom Lane) wrote:
> "filippo" <filippo2...@virgilio.it> writes:
> > The problem with the current implementation is that sometime pd_dump
> > and psql ask for password but I want to create the database copy
> > without any user typing (it is a cron script).
>
> Create a ~/.pgpass file for the user that runs the cron job.

thanks Tom and Richard, actually it works now. However I wonder if
there is a SQL way, I'm partially in a Windows enviroment and I don't
trust very much the microsoft security.

Thanks and regards,

Filippo