How to create a backup of a postgres database to a sql file using a PHP script?

Question

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

Accepted Answer

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_dump_+_cronjob.html and http://forum.soft32.com/linux/Backup-Postgressql-ftopict460054.html

How to create a backup of a postgres database to a sql file using a PHP script?

Solution

Problem