Thread: pg_dump shell script with ~/.pgpass
I need to create a shell script for cron.daily to run that will do pg_dump for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when I run the dump manually the command I use is #pg_dump -u -C -D -f /tmp/owl.sql owl What should the command look like using ~/.pgpass ? My other question... Is cron.daily is being run by root? If so would I need to put the .pgpass file in root's home directory? I only have two postgresql users right now, myself and postgres. On the redhat server I have only two users, myself and root, there is no user postgres. TIA. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
I posted the message below on 1/15/05 and haven't received a reply yet. Should I be asking this question on a different list? Thank you. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297 ----- Forwarded by Margaret Gillon/CLA/Chromalloy on 01/27/2005 08:38 AM ----- Margaret Gillon/CLA/Chroma lloy To pgsql-general@postgresql.org 01/15/2005 12:14 cc PM Subject pg_dump shell script with ~/.pgpass I need to create a shell script for cron.daily to run that will do pg_dump for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when I run the dump manually the command I use is #pg_dump -u -C -D -f /tmp/owl.sql owl What should the command look like using ~/.pgpass ? My other question... Is cron.daily is being run by root? If so would I need to put the .pgpass file in root's home directory? I only have two postgresql users right now, myself and postgres. On the redhat server I have only two users, myself and root, there is no user postgres. TIA. *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** Margaret Gillon, IS Dept., Chromalloy Los Angeles, ext. 297
On Thursday 27 January 2005 11:47 am, MargaretGillon@chromalloy.com saith: > > My other question... Is cron.daily is being run by root? If so would I need > to put the .pgpass file in root's home directory? > -- I don't know about .pgpass, so I can't help you with that. Root does run cron.daily. You can setup your own personal crontab entries by issuing the following command: crontab -e Do this with your personal account, not root. You will get a vi session when you execute this command and then you can setup you backup to occur as you desire. Here is what mine looks like: # min hour dom mon doy command # --- ---- ---- ---- ----- --------------- 30 7,12,22 * * * /esc/source.bkp 0 22 * * * cd /esc/pgrnd/prog; ./dbDump.sh rnd Word wrap has screwed up the alignment, but you get the idea.
On Thu, Jan 27, 2005 at 08:47:40AM -0800, MargaretGillon@chromalloy.com wrote: > I need to create a shell script for cron.daily to run that will do pg_dump > for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when > I run the dump manually the command I use is > > #pg_dump -u -C -D -f /tmp/owl.sql owl > > What should the command look like using ~/.pgpass ? The same. The file is automatically used if found and has the right permissions. > My other question... Is cron.daily is being run by root? If so would I need > to put the .pgpass file in root's home directory? Don't use root to do the backup, use postgres. crontab -e -u postgres -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "El día que dejes de cambiar dejarás de vivir"
MargaretGillon@chromalloy.com wrote: > I posted the message below on 1/15/05 and haven't received a reply yet. > Should I be asking this question on a different list? This is probably the right list - your message may have been missed though, there can be a lot of traffic. > > I need to create a shell script for cron.daily to run that will do pg_dump > for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when > I run the dump manually the command I use is > > #pg_dump -u -C -D -f /tmp/owl.sql owl > > What should the command look like using ~/.pgpass ? Well, from cron, I'd add an explicit username "-U username" and then in your .pgpass add the host/port/db/user/password as described in the manuals (client interfaces/libc/files). > My other question... Is cron.daily is being run by root? If so would I need > to put the .pgpass file in root's home directory? Yes and yes. Also, make sure you chmod the file as described in the manual. The alternative would be to set up a crontab for another user, and place the .pgpass in their home-dir. > I only have two postgresql users right now, myself and postgres. On the > redhat server I have only two users, myself and root, there is no user > postgres. Are you sure? There are almost certainly dozens of users in /etc/passwd and if you installed by RPM then there *will* be a user postgres. That's not to say you can just login as them though - they might have their shell disabled. -- Richard Huxton Archonet Ltd
On Thu, Jan 27, 2005 at 08:47:40 -0800, MargaretGillon@chromalloy.com wrote: > > What should the command look like using ~/.pgpass ? You might also consider using using ident as the authentication method if it is supported by your os.
On Thu, 27 Jan 2005, Richard Huxton wrote: > MargaretGillon@chromalloy.com wrote: [snip] >> I need to create a shell script for cron.daily to run that will do pg_dump >> for my database. I am using Redhat 9 and Postgresql v7.3.4. Currently when >> I run the dump manually the command I use is >> >> #pg_dump -u -C -D -f /tmp/owl.sql owl >> >> What should the command look like using ~/.pgpass ? > > Well, from cron, I'd add an explicit username "-U username" and then in your > .pgpass add the host/port/db/user/password as described in the manuals > (client interfaces/libc/files). Personally I _hate_ having passwords in files, and avoid it whenever possible. [1] It's another instance that I have to change when the password expires; and [B] it's a possible avenue for a security failure (or, at least requires real thought and attention to avoid turning into a security problem). What I do instead is have the cron listing run a simple script which uses su : su --command="INSERT YOUR PG COMMAND HERE" $PGNAME where PGNAME has previously been set to, for example, 'postgres'. This script can only be run by root (or the su will fail); and if run by root, and the PGNAME is postgres, no additional password will be needed for DB admin. [how to accomplish this in WinXX I have no idea] HTH, -frank