Makla Makla - 2 months ago 23
PHP Question

Postgres backup data with PHP

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. enter image description here
1: Catch all for general errors from this page

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

Answer

Is there any SQL command to backup entire database to file?

No. It'd be nice, but there isn't one.

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

Don't use a batch file. Instead, either set environment variables in your local process and then CreateProcess pg_dump directly or use a .pgpass file, which is pgpass.conf on Windows.

Direct execution

Rather than using a batch file, execute pg_dump directly after setting the environment.

This is made harder because PHP is awful, it lacks anything sane like execv to exec with an array of arguments. You should be able to just:

# DOES NOT WORK, as exec(...) doesn't take an array argument:
putenv("PGPASSWORD=" . COF_DATABASE_PASSWORD);
$dumpcmd = array("pg_dump", "-i", "-U", COF_DATABASE_USERNAME, "-F", "c", "-b", "-v", "-f", COF_BACKUP_FILE, COF_DATABASE_NAME);
exec($dumpcmd);
putenv("PGPASSWORD");

but PHP doesn't take an array for exec and doesn't have execv. So you have to munge it into a shell command, dealing with the escaping yourself.

Not even proc_open supports this.

Instead you have to manually escape arguments, e.g.:

putenv("PGPASSWORD=" . COF_DATABASE_PASSWORD);
$dumpcmd = array("pg_dump", "-i", "-U", escapeshellarg(COF_DATABASE_USERNAME), "-F", "c", "-b", "-v", "-f", escapeshellarg(COF_BACKUP_FILE), escapeshellarg(COF_DATABASE_NAME));
exec( join(' ', $dumpcmd), $cmdout, $cmdresult );
putenv("PGPASSWORD");
if ($cmdresult != 0)
{
    # Handle error here...
}

If you were using a civilized language you could use one of the secure alternatives directly, e.g. execve/CreateProcessEx (C), subprocess.check_call (Python), Kernel.exec with an array argument (Ruby), etc.

Pgpass

You can append lines to the user's pgpass.conf file, or require it to be preconfigured. Then pg_dump will find the login automatically.

You must still check for errors.

Batch file

If you must use a batch file, you need to redirect stderr to stdout (since you're probably not capturing it) and also test %ERRORLEVEL% on exit so you detect a failure of pg_dump. e.g.

pg_dump -i -U myuser -F c -b -v -f outfile dbname 2>&1

IF %ERRORLEVEL% NEQ 0
  GOTO ERROR
exit 0

:ERROR
echo "pg_dump failed, see logs for details"
exit 1

then you need to check the return code. shell_exec doesn't give you access to that, it only produces the output text, as shown by the "Note" block. So you should use exec.

I don't speak Windows Batch File well, and would personally do this with direct execution of pg_dump. But then I don't use PHP given a choice either.