I’ve been wanting to play around more with pre-scripting in PowerProtect Data Manager. The proof-of-concept style example I gave last time after all pretty much existed just to give the general concept. So I thought — what about giving an example of using pre-scripts to execute PostgreSQL dumps ahead of a backup? And then I thought … well, why not do it on Windows?
The first step towards doing this was to get PostgreSQL installed on a Windows server and a couple of databases created on it:
On the system, I setup a G:\ drive to hold my PostgreSQL dumps. The goal: configure G:\ as an asset into a policy where the pre-script populates G:\ with the database backups before running the actual backup to Data Domain.
I wanted to test this out manually first, so I started setting up for the testing by creating a pgpass.conf file into %AppData%\postgresql. By having this file present, you don’t have to supply a password to your various pg_ commands, essential for non-interactive backup operations. This uses the format host:port:database:username:password. On my system, that looked like this:
localhost:5432:*:postgres:superSecretPassword
That meant that by running my script and invoking the connection as the postgres user, I’d automatically supply the correct password to any database I connected to. (Obviously, you want to store such a file in a secure location that unauthorised users can’t access.)
Next, the backup batch file — just for a single database for the time being, which looked like the following:
C:\Users\Administrator>type backup.bat
@SET PATH=C:\Program Files\PostgreSQL\16\bin;%PATH%
@SET PGDATA=F:\PostgreSQL\16\data
@SET PGDATABASE=postgres
@SET PGUSER=postgres
@SET PGPORT=5432
@SET PGLOCALEDIR=C:\Program Files\PostgreSQL\16\share\locale
pg_dump -d PMdG -U postgres > G:\PMdG\Backup.sql
That’s my starting point. Now, note I’m not going any compression here of the dump file; since my intention is to send this to Data Domain, I don’t want to interfere with storage efficiency, after all.
That script worked for me, so with the basic concept in place I was ready to set this up as a policy with pre-script in Data Manager.
So in Data Manager, I went to Protection > Scripts and created a new backup script that did pretty much what my script above did:
There are minimum differences between the script entered for a Data Manager policy, and the batch script I created on the system. You’ll note that I’ve thrown in an “exit 0” at the end of the script to make sure I signal completion of the backup. I’ve also added an extra environment variable to explicitly set the PGPASSFILE location, as that needs be explicitly set up when running as a pre- or post-script.
Within the script, the key settings included — asset selection:
And of course, the actual pre-script configuration itself:
The only other modification was to make sure the backed-up data was indexed:
When the policy ran, my database dump for the ‘PMdG’ database was successfully generated into G:\PMdG and the file was then backed up by Data Manager. Success!
Now, my batch scripting is a little basic and I wanted to do something a bit more involved — I wanted a backup script that detected all the databases on the system (minus the template databases/postgres database) so that if I added more databases to the system, they’d be automatically backed up. I got cracking and installed Strawberry Perl on my Windows server, and got to work on a more advanced backup script. After a little bit of testing, I ended up with the following script:
#!/usr/bin/perl -w
###########
### Modules
###########
use strict;
use File::Basename;
use Sys::Hostname;
############
### Env Vars
############
$ENV{PATH}="C:\\Program Files\\PostgreSQL\\16\\bin;%PATH%";
$ENV{PGDATA}="F:\\PostgreSQL\\16\\data";
$ENV{PGDATABASE}="postgres";
$ENV{PGUSER}="postgres";
$ENV{PGPORT}=5432;
$ENV{PGLOCALEDIR}="C:\\Program Files\\PostgreSQL\\16\\share\\locale";
$ENV{PGPASSFILE}="C:\\Users\\Administrator\\AppData\\Roaming\\postgresql\\pgpass.conf";
###############
### Global Vars
###############
my @excludeDBs = ("postgres","template1","template0");
my @databasesToProtect = ();
my $dbUser = "postgres";
my $backupBase = "G:\\";
my $keepPrevious = 1;
#############
### Functions
#############
# in_list($expr,@list) returns true iff $expr is in list.
sub in_list {
return 0 if (@_+0 < 2);
my $element = $_[0];
return 0 if (!defined($element));
shift @_;
my @list = @_;
return 0 if (!@_ || @_+0 == 0);
my $foundCount = 0;
my $e = quotemeta($element);
foreach my $item (@list) {
my $i = quotemeta($item);
$foundCount++ if ($e eq $i);
}
return $foundCount;
}
# excluded($dbName) returns true if the database name is in the excluded database list
sub excluded {
return 0 if (@_+0 != 1);
# else
my $database = $_[0];
if (in_list($database,@excludeDBs)) {
return 1;
} else {
return 0;
}
}
# keep_previous (database,path) retains the previous copy if we need to.
sub keep_previous {
return 0 if (@_+0 != 2);
my $dbName = $_[0];
my $backupPath = $_[1];
return 1 if (!$keepPrevious); # Nothing special to do here.
my $backupFile = $backupPath . "\\Backup-" . $dbName . ".sql";
my $prevBackupFile = $backupPath . "\\Backup-" . $dbName . "-prev.sql";
if (-f $prevBackupFile) {
if (!unlink($prevBackupFile)) {
print "Could not remove $prevBackupFile, unsafe to continue.\n";
return 0;
}
}
if (!rename($backupFile,$prevBackupFile)) {
print "Could not rename $backupFile to $prevBackupFile, unsafe to continue.\n";
return 0;
}
# If everything is OK.
return 1;
}
########
### MAIN
########
if (open(DBS,"echo select datname from pg_database | psql -U $dbUser -A -F, -t 2>&1 |")) {
while (<DBS>) {
my $line = $_;
chomp $line;
my $dbName = (split(/,/,$line))[0];
if (!excluded($dbName)) {
print "Found database: $dbName\n";
push(@databasesToProtect,$dbName);
}
}
close(DBS);
}
foreach my $db (@databasesToProtect) {
my $backupPath = $backupBase . "\\" . $db;
print "Backup $db into $backupPath\n";
if (! -d $backupPath) {
print "Backup $db to $backupPath -- create directory\n";
if (mkdir($backupPath)) {
print "...successfully created!\n";
} else {
print "...whoopsie!\n";
}
}
my $safe = keep_previous($db,$backupPath);
if (!$safe) {
print "Unsafe to continue for $db/$backupPath, keep_previous was not happy.\n";
}
# OK, we can back this baby up.
my $backupFile = $backupPath . "\\" . "Backup-$db.sql";
print "--> Starting pg_dump as of " . localtime(time) . "\n";
system("pg_dump -d $db -U $dbUser > $backupFile");
print "--> pg_dump finished as of " . localtime(time) . "\n\n\n";
}Now, pulling this script into the policy is more a case of writing a basic one-line script to invoke it. I saved it to the Windows database server in C:\Users\Administrator as pg_backup.pl, and configured the Data Manager script as follows:
I then just needed to change the pre-script in the policy to this new one:
That was it! And the proof is in the file-search pudding:
And there you have it! The beauty for me in pre-scripts is that they truly do massively increase your horizon for options within your protection product.
Hey, I’m still running a book giveaway competition! I’d love it if you won.