Postgres backup data with PHP

Question

I try to backup database with PHP script. My idea was to run the pg_dump with PHP as a batch file. (Yes I am using PHP for Windows).

I write this code:

$content = "set BACKUP_FILE=database.backup \r\n" .
            "SET PGPASSWORD=" . COF_DATABASE_PASSWORD . "\r\n" . 
            "pg_dump -i -U " . COF_DATABASE_USERNAME . " -F c -b -v -f %BACKUP_FILE% " . COF_DATABASE_NAME . "\r\n" .
            "echo done";

    file_put_contents($root . "/Files/Updates/Backup.bat", $content);
    $system = exec ($root . "/Files/Updates/Backup.bat", $result);
    pclose(popen("start /B $root/Files/Updates/Backup.bat", "r"));
    $result = shell_exec($root . "/Files/Updates/Backup.bat");

The Backup.dat file is created. If I run it manually from windows, the database.backup file is created, but not when I try to run the batch from PHP. For both command I get in $result:

(string:241) D:\Data\WWW...\Process>set BACKUP_FILE=database.backup

D:\Data\WWW...\Process>SET PGPASSWORD=dom123

D:\Data\WWW...\Process>pg_dump -i -U dom -F c -b -v -f database.backup dom

Once as array (exec) and once as string (shell_exec).

Then I was thinking that running bat file (even if I created in code and then delete) is not the brightest idea (security). So I way trying to backup database with SQL-s (PDO - execute ...)

But I can not find any documentation if this is even possible. All solutions propose pg_dump. Is there any SQL command to backup entire database to file?

I am looking for a working solution. SQL or .bat, doesn't matters at this point.

Edited:

Also try:

$string = "export PGPASSWORD=" . COF_DATABASE_PASSWORD . " && export PGUSER=" . COF_DATABASE_USERNAME . " && pg_dump -h localhost db_name " . COF_DATABASE_NAME . " > " . $root . "Files/Updates/Backup.sql && unset PGPASSWORD && unset PGUSER"; 
    exec($string);
    shell_exec($string);

Edited (answer on Craig Ringer):

Try your code, I get error. 1: Catch all for general errors from this page

For image open link in new tab to get full size.

Answer 1

You can try with the following command. I also have the same problem. I fixed that by using attached code:

<?php
    exec('pg_dump --dbname=postgresql://username:[email protected]:5432/mydatabase > dbbackup.sql',$output);
    print_r($output);
?>