database administrator

If you're starting out with postgres or have maybe been using it for a while now you'll know there's no automated backup routine. You need to create your own. This tutorial shows how you can create a simple PHP task that runs at night to do your backups.

This tutorial is designed around a windows based server. It triggers a PHP web page using a scheduled task running at midnight, which dumps the database to a folder and then compresses it. For it to work you need:

  • A BAT script to run the PHP file - windows scheduled tasks wont run PHP scripts natively.
  • The PHP script that will export the database for backup
  • A DOS based ZIP tool that can compress the file after (I use a tool that creates 7zip called PowerArchiver).
  • A scheduled task to trigger all this (which means you need access to this on the server, or admins will need to assist).

Create a new PHP file in a folder of your choice. You will need a "backups" folder inside that folder. Using the script below you can prepare your backup script:

if (isset($_REQUEST['debug'])) {

 $debug = true;
} else {
 $debug = false;
}


$strFileName = date('Y-m-d').'-Data.7z';
$strZipFile = 'backups/'.date('Y-m-d').'/'.$strFileName;
$strTmpDir = 'backups/tmp';

// Check the backup folder exists
if (!file_exists('backups/'.date('Y-m-d').'/')) {
 mkdir('backups/'.date('Y-m-d').'/', 0777, true);
}

// Check the temp folder exists
if (!file_exists('backups/tmp')) {
 mkdir('backups/tmp', 0777, true);
}

// Prevent timeouts on large exports (non-safe mode only)
if (!ini_get('safe_mode')) set_time_limit(0);

The problem is passing the username and password to DOS applications to perform the backup. To do this use the environment variables as below.

// Set environmental variables that pg_dump uses
putenv('PGPASSWORD=yourpassword');
putenv('PGUSER=postgres');
putenv('PGHOST=localhost');
putenv('PGPORT=5432');
putenv('PGDATABASE=yourdatabase');

Clearly you need to replace yourpassword and yourdatabase above with your information. Next is the database dump. The code below needs to be modified if you are running this on a Linux server OR if you postgres path is different.

if ($debug == true) {

 $str = '"C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe" --host localhost --port 5432 --username postgres --format plain --encoding SQL_ASCII --file backups\\tmp\\'.date('Y-m-d').'.sql';
echo $str;
} else {
 $str = '"C:\Program Files\PostgreSQL\9.0\bin\pg_dump.exe" --host localhost --port 5432 --username postgres --format plain --encoding SQL_ASCII --file backups\\tmp\\'.date('Y-m-d').'.sql';
 exec($str);
 //echo $str;
}

You'll notice that you cannot pass the username or password to pg_dump.exe, which is why you need to set the environment variables ahead of time. I also included some debug code here. Now you can compress the temporary file in tmp folder. You may also want to change the date format used for the resulting file.

if (file_exists('backups/tmp/'.date('Y-m-d').'.sql')) {
 exec('pacomp -a -cz -g -q '.$strZipFile.' backups\\tmp\\'.date('Y-m-d').'.sql');
 unlink('backups\\tmp\\'.date('Y-m-d').'.sql');
}

That's the PHP file. Now create a BAT file named backup.bat which needs to say the following:

start /B /WAIT http://localhost/yourwebsitefolder/db_backup.php

Obviously you'll replace "yourwebsitefolder" with your actual website folder location. Change the name of the PHP file also if yours is different. The "start" command will open an application on a windows machine. The /B will NOT create a visible window running the application. /WAIT will make the script wait until its finished before proceeding. You will now need to create a schedule task to run this BAT file at a time that suits you.