Re: pg_dump shell script with ~/.pgpass

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).
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