pgAgent sql command for backup db

Hello,

i installed pgAgent on my postgresql agency server but i have no idea about how make a scheduled backup of my db.

I search in internet and i found only one sql example script but it don't run....

Here my first question.
1) which is a simple sql script for make a backup of specific database ?
....and could i do this also with a batch script ?

2) I think my sql script tested for now wna't run because i have not configured pgpass.conf file....
...i could see exist more of this file... which file i have to configure, the administrator pgpass file, or postgres pgpass ?
How have i to set this file ???
i found this example......

localhost:5432:postgres:postgres:postgres...

what mean exactly ?

You can take the backup of the single database by using the pg_dump utility and command as follows.

./pg_dump -f <out_put_filename> -Fc -v -O -h <host_name> -P <database_port_number> -U <database_username> DATABASENAME

The file .pgpass in a user's home directory or the file referenced by PGPASSFILE can contain passwords to be used if the connection requires a password (and no password has been specified otherwise).

On Unix systems, the permissions on .pgpass must disallow any access to world or group; achieve this by the command chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored.

This file should contain lines of the following format:

hostname:port:database:username:password

But have i to copy this line as is in SQL statement of pgAgent scheduling job ??

My connection require password.....so, have i to set which pgpass.conf file ? the file in Admin User directory or the file in Postgres User directory ?

The .pgpass file located under the Postgres user home directory.

You can copy the same line as is in SQL statement of pgAgent scheduling job and database dump operation performed with respect to schedule time.

i.e. /usr/local/pgsql/pg_dump -f <out_put_filename> -Fc -v -O -h <host_name> -P <database_port_number> -U <database_username> DATABASENAME

(or)

You can schedule the crontab to take the backup of the database. crontab preparation of backup the database as shown below

1. Create .sh file:

#vi /bin/backup-postgres-script.sh

2. Write in .sh file

#!/bin/bash
BACKUP_DIR="/var/lib/pgsql/backups"
PGHOST="localhost"
PGUSER="postgres"
time=`date '+%d'-'%m'-'%y'`
/usr/local/pgsql/bin/./pg_dump -h $PGHOST -p 5444 -U $PGUSER -Fc -b -v -f $BACKUP_DIR/dbname-$time.backup dbname

3. Permission in .sh file

#chmod 755 /bin/backup-postgres-script.sh

4. Add in crontab

#crontab -e
Add line
0 4 * * * /bin/backup-postgres-script.sh > /dev/null 2>&1

5. Save changes

--
Thanks & Regards
Raghu

Ok thanks for now.....i'll try your suggest.....

Hello again...

my pgAgent run now...but the backup are maked only one time...after that they don't update the old backup file...

i used this code for the command

c:\programmi\postgresql\8.4\bin\pg_dump -i -h localhost -p 5432 -U eliosuser -w -E sql_ascii -F c -b -v -f "c:\BaKelios.backup" elios
c:\programmi\postgresql\8.4\bin\pg_dump -U eliosuser elios > C:\BakElios.sql
c:\programmi\postgresql\8.4\bin\pg_dumpall -U postgres > C:\BakEliosAll.sql

The password are stored in pgpass.conf file...

They run if are not file backup in the destination directory, but after that nothing happen...

Have i write other code for overwrite permission ???