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 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
"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
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
Date: 21 Mar 2007 00:29:33 -0700
From: "filippo" <filippo2991@virgilio.it>
To: pgsql-general@postgresql.org
Subject: Re: shell script to SQL statement: `pg_dump | psql -U`
Message-ID: <1174462173.105601.197370@b75g2000hsg.googlegroups.com>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
You may already know some of this so I'm sorry if it's a repeat. This is a
work around not a solution from inside Postgresql.
Create a Dos/Windows batch file. Ends with extension .bat. Like a cron
file it contains commands that will be executed. Go into a text editor
(notepad or wordpad) and make a file called whatever you want ending with
the extension .bat, for example PGBackup.bat Make sure that when you save
the file you save it as a type Text Document. Put the PG command into the
file and save the .bat file in the directory that you are running the
command from manually. Go into the Windows Scheduler on the SQLServer and
schedule a time for the .bat file to run. The scheduler can be accessed
from the control panel or the Help file. You must have administrator
rights to create a scheduled job.
One issue, if the command has problems and doesn't complete it may popup a
dialogue box and wait for a response. It would be wise to check your
server the first few days to make sure there are no dialogue boxes waiting
for you.
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
*** ***
Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
Import Notes
Resolved by subject fallback
On 23 Mar, 19:32, MargaretGil...@chromalloy.com wrote:
thanks Margaret, I didn't know window scheduler so far.
By the way I have choosen to do all management stuff on database via
command line programs, providing passowd file for administrator user.
Thanks
Filippo