ansaurus

Question

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

Answer 1

+1 A:

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

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

depesz 2009-08-27 14:38:11

# -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 2009-08-27 14:39:14

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 2009-08-27 14:40:45

Proper way to handle it is to use .pgpass file ( http://www.postgresql.org/docs/current/interactive/libpq-pgpass.html )

depesz 2009-08-28 06:23:09

Answer 2

+3 A:

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

Kyle Kochis 2009-08-27 14:59:22

ansaurus

tags:

views:

answers:

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

related questions