up vote 2 down vote favorite

I have the following situation.

I have a PHP script that imports a CSV file and then updates the postgres database

Now I need to create a backup of the database before the importing occurs

The PHP files are running on one server and the postgres database on another server

I tried exec(pg_dump db_name -CdiOv > /tmp/db_name_backup.sql) but don't think this will work since the db is on another server.

I'm not sure how to do this, I can right code in PHP performing a backup but that takles ages to run.

Any adwise will be appreciated

link|flag
93% accept rate

2 Answers

up vote 3 down vote accepted

As depesz said you need to use the -h option to define the remote host but it will still prompt for a password which is problem. Try:

exec("export PGPASSWORD=mypassword && export PGUSER=myuser && pg_dump -h yourremotehost db_name -CdiOv > /tmp/db_name_backup.sql && unset PGPASSWORD && unset PGUSER");

Alternatively you can use a ~/.pgpass file but I've never tried this. Check out http://www.issociate.de/board/post/43225/pg%5Fdump%5F+%5Fcronjob.html and http://forum.soft32.com/linux/Backup-Postgressql-ftopict460054.html

link|flag
up vote 1 down vote

pg_dump can easily connect to remote host - just check -h option.

Also - what do you want to achieve by the "-CdiOv" thing?

link|flag
# -C = Begin the output with a command to create the database itself and reconnect to the created database. # -d = Dump the data as INSERT commands # -i = Ignores version mismatch between the old database server and the new database server. # -O = No owner, the database does not belong to any specific owner that was specified in the database # -v = Verbose, will output a detailed report of pg_dump if ran in command line. – Roland Aug 27 '09 at 14:39
So It will also work if I do something like this: pg_dump domains -CdiOv -h server -u > pg_dump.sql , the only problem here is it prompts for username and password, can I include that in file to since this is an internal system in can be save to send usernames and passwords – Roland Aug 27 '09 at 14:40
Proper way to handle it is to use .pgpass file ( postgresql.org/docs/current/interactive/libpq-pgpass.html ) – depesz Aug 28 '09 at 6:23

Your Answer

 
get an OpenID
or
never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.