Take the backup of PostgreSQL database using php

General PHP
1

I am trying to take the backup of PostgreSQL database using php. I have written the below code to run a batch file which has the command to take the backup. I am using Wampserver for development and the below coed works perfectly. But the problem is that it is not working on the server which is Windows Server 2008 R2. Please help.

putenv("PGPASSWORD=postgres");

system("cmd /c D:\wamp\www\pentium\protected\data\backup\cron.bat");

Below is the content of cron.bat

pg_dump -i -U "postgres" -F c -b -v -f "D:\wamp\www\pentium\protected/data/backup/pentium.backup" "pentium"

2

Did you try to execute the command that is in cron.bat from the server command line?

One thing I noticed is that you have a mix of backslash (\) and forward slash (/) in that command

3

The code I posted is for the local Wamp server, and it is working correctly. The path is different on the server. I have not posted server path because of security issues. Anyway there is no mix of slashes in that file and it is creating the backup file if I double click and run the cron.bat file on the server. I have also tried with exec() also but with no success.

4

Try to redirect the pg_dump output to a file so you can check if there is any output or if there is any error reported.

5

I tried redirecting the output

pg_dump -i -U "postgres" -F c -b -v -f "D:\wamp\www\pentium\protected\data\backup\pentium.backup" "pentium" 2>D:\wamp\www\pentium\protected\data\backup\pg_dump.log

But log file is created only when I directly run the cron.bat file.Log file is not created when executed using PHP.

6

so it seems that PHP is not even running the command… system() returns false on failure so maybe check on that… also it’s possible that those functions like system(), exec() and similar are disabled on the server. Check in the server php.ini the disable_functions option.

7

php.ini disable_functions is empty.

If I do

echo system("cmd /c D:\wamp\www\pentium\protected\data\backup\cron.bat");

Then nothing gets printed.

If I use return variable

system("cmd /c D:\wamp\www\pentium\protected\data\backup\cron.bat",$return_var);

echo $return_var;

then ‘1’ is echoed.

8

the idea is to check if "false" is returned as this means there is some failure… "echo false;" does not print anything, you need to use var_dump()…

[size=2]also try to execute the script directly without the "CMD"[/size]

[size=2]

[/size]

9

var_dump(system("cmd /c D:\wamp\www\pentium\protected\data\backup\cron.bat"));

gives

string(0) ""

var_dump(system("/c D:\wamp\www\pentium\protected\data\backup\cron.bat"));

gives

string(0) ""

10

Seems there is no failure… btw. in the second example you need to remove "/c"…

The next thing I would test is to see if pg_dump is the problem… to test this you can comment the pg_dump line and put there something like dir > a.txt… so after running the PHP code if there is a file "a.txt" it means that calling the script works but the problem is in the pg_dump command.

11

No. The file is not created.

12

So it seems it’s about the system() call… try to check the php error log file if there is more info.

Btw, are you executing from the browser or from the command line? Executing the PHP script from the command line might give more info if there is some errors.

13

There is no error in log file. But if I execute the PHP file through command line then it successfully executes and the back up file is generated.

14

hmm…

for the error.log - it depends if it’s configured http://www.cyberciti.biz/tips/php-howto-turn-on-error-log-file.html, http://php.net/manual/en/errorfunc.configuration.php

as on CLI the script calling works I have 2 ideas now:

  1. the command line PHP and the web server PHP have different configurations files (php.ini)… for example on my system (linux) the web server configuration is in /etc/php5/apache2/php.ini while the CLI configuration is in /etc/php5/cli/php.ini… so maybe check them to see if there are some difference regarding running system commands

  2. maybe it’s something with permissions because if you run from the command line it runs as your user while when you run from the browser it runs as another user

15

Finally I wrote the command in php and executed the php file using windows task scheduler.

$path = Yii::app()->basePath . ‘/data/backup/’;

        $file=$path."pentium.backup";


        unlink($file);


       


       putenv("PGPASSWORD=postgres");


       $dumpcmd = array("pg_dump", "-i", "-U", escapeshellarg("postgres"), "-F", "c", "-b", "-v", "-f", escapeshellarg($file), escapeshellarg("pentium"));


       exec( join(' ', $dumpcmd), $cmdout, $cmdresult );


       putenv("PGPASSWORD");