PHP PostgreSQL database backup.

[javilla]

Is there anyway to dump a database from a SQL query on PostgreSQL?

I want to do that from a PHP script. So if there ins't any way to do it from a query, is there any PHP function to do it?

Thanks a lot,
Javier.

 

[garrettroyce]

You can get an array of all the tables using the query "select * from information_schema.tables where table_schema='public' and table_type='BASE TABLE'" Then, you can use "select * from $table" to get all the rows in the table. Then, you can dump it in whatever format you want. I can't think of another way to do it programatically.

 

[javilla]

Thank you garrettroyce,

At last I do it with pg_dump through the PHP function passthru() to get the file. The problem was setting the password. You can force pg_dump to prompt for a password by the -W option. But, what if you want to pass the password to the command to avoid the need to prompt?

I've found you can set an enviroment variable called "PGPASSWORD" (setenv() in PHP) which will be used by pg_dump. I don't know why that is not documented on postgresql.org but it works.

Regards,
Javier