Cron Jobs 2

[kizmar2]

I need to do the equivalent of the code below but with PostgreSQL. I haven't been able to find documentation on the pg_dump command that allows you to use a login. This is a script for a Cron Job by the way, on a web host.

mysqldump --user='username' --password='password' --extended-insert bd_name > /path/to/file/backup.sql

What I have with pgsql is this:

pg_dump -u --superuser='username' db_name > /home/macker/backup/db/macker2002.sql

I get permission denied. Which I'm assuming is because I don't have a password there.

KizMar
------------

http://www.kizmar.net

 

[flugh]

I use:
pg_dump -U <user> -h <hostname> -F t <database> > file.tar

the -F does the format, t is the 'tar' format, p is plain, forget what the third is (on windows box at this second).

----
JBR

 

[ceco]

you can set the environment variables in the backup script (PGPASSWORD for example), see here

http://www.postgresql.org/docs/8.0/static/libpq-envars.html

or you can use the .pgpass file for the user running the cron job

http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html

and you can tweek the pg_hba.conf file so it doesn't need password for the user running the cron job

http://www.postgresql.org/docs/8.0/interactive/client-authentication.html

 

[kizmar2]

I've tried setting the PGPASSWORD variable and it's not recognizing anything that I use... maybe I'm missing something. What's the syntax to set that in a script?

I don't have access to the .pgpass or pg_hba.conf files, I'm in a jailshell on my web host... so the variable may be my only hope of getting past the password verification...

KizMar
------------

http://www.kizmar.net

 

[ceco]

the syntax depends on the script language used for the script (for bash it is
PGPASSWORD=mypass
)


by the way, despite being in a jail, I am quite sure that you can modify .pgpass, try finding where is your home directory and put it there

 

[kizmar2]

OK... creating the .pgpass file in my account's home directory worked!

I have another issue though... now when it goes to backup the DB, I get this:

pg_dump: attempt to lock table "colors" failed: ERROR: permission denied for relation colors

Keep in mind that we acquired this DB from someone else and there are a lot of triggers and constraints in there that I'm not familiar with.

That specific table is pretty basic so I'm not sure why it's not allowing the lock. Is there a command to stop pg_dump from trying to lock the tables?

KizMar
------------

http://www.kizmar.net

 

[kizmar2]

FYI - I did the'ol "GRANT ALL PRIVILEGES ON <table> TO <user>;" to fix this...

I'm all set, thanks again!

KizMar
------------

http://www.kizmar.net