Postgres backup data with PHP

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.