{"id":11754,"date":"2024-07-07T06:51:37","date_gmt":"2024-07-06T20:51:37","guid":{"rendered":"https:\/\/nsrd.info\/blog\/?p=11754"},"modified":"2024-07-07T06:51:39","modified_gmt":"2024-07-06T20:51:39","slug":"postgresql-backups-with-pre-scripts","status":"publish","type":"post","link":"https:\/\/nsrd.info\/blog\/2024\/07\/07\/postgresql-backups-with-pre-scripts\/","title":{"rendered":"PostgreSQL Backups with Pre-Scripts"},"content":{"rendered":"\n

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<\/a><\/strong> 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?<\/p>\n\n\n\n

The first step towards doing this was to get PostgreSQL installed on a Windows server and a couple of databases created on it:<\/p>\n\n\n\n

\"Windows<\/a>
pgAdmin 4 on Windows<\/strong> (RDP client: remmina)<\/figcaption><\/figure>\n\n\n\n

On the system, I setup a G:\\<\/strong> 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.<\/p>\n\n\n\n

I wanted to test this out manually first, so I started setting up for the testing by creating a pgpass.conf<\/strong> file into %AppData%\\postgresql<\/strong>. By having this file present, you don’t have to supply a password to your various pg_<\/em> commands, essential for non-interactive backup operations. This uses the format host:port:database:username:password<\/em>. On my system, that looked like this:<\/p>\n\n\n\n

localhost:5432:*:postgres:superSecretPassword<\/pre>\n\n\n\n
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.)<\/p>\n\n\n\n
Next, the backup batch file — just for a single database for the time being, which looked like the following:<\/p>\n\n\n\n
C:\\Users\\Administrator>type backup.bat<\/strong>
@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<\/pre>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
So in Data Manager, I went to Protection > Scripts<\/strong> and created a new backup script that did pretty much what my script above did:<\/p>\n\n\n\n
\"PowerProtect<\/a>
Entered script<\/strong><\/figcaption><\/figure>\n\n\n\n
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<\/strong> location, as that needs be explicitly set up when running as a pre- or post-script.<\/p>\n\n\n\n
Within the script, the key settings included — asset selection:<\/p>\n\n\n\n
\"Data<\/a>
Assigning the G:\\ asset to the policy<\/strong><\/figcaption><\/figure>\n\n\n\n
And of course, the actual pre-script configuration itself:<\/p>\n\n\n\n
\"Configuration<\/a>
Pre-script configuration for policy.<\/strong><\/figcaption><\/figure>\n\n\n\n
The only other modification was to make sure the backed-up data was indexed:<\/p>\n\n\n\n
\"Policy<\/a>
Indexing Enabled for Policy<\/strong><\/figcaption><\/figure>\n\n\n\n
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!<\/p>\n\n\n\n
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<\/a><\/strong> 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:<\/p>\n\n\n\n
#!\/usr\/bin\/perl -w\n\n###########\n### Modules\n###########\nuse strict;\nuse File::Basename;\nuse Sys::Hostname;\n\n\n############\n### Env Vars\n############\n$ENV{PATH}=\"C:\\\\Program Files\\\\PostgreSQL\\\\16\\\\bin;%PATH%\";\n$ENV{PGDATA}=\"F:\\\\PostgreSQL\\\\16\\\\data\";\n$ENV{PGDATABASE}=\"postgres\";\n$ENV{PGUSER}=\"postgres\";\n$ENV{PGPORT}=5432;\n$ENV{PGLOCALEDIR}=\"C:\\\\Program Files\\\\PostgreSQL\\\\16\\\\share\\\\locale\";\n$ENV{PGPASSFILE}=\"C:\\\\Users\\\\Administrator\\\\AppData\\\\Roaming\\\\postgresql\\\\pgpass.conf\";\n\n###############\n### Global Vars\n###############\nmy @excludeDBs = (\"postgres\",\"template1\",\"template0\");\nmy @databasesToProtect = ();\nmy $dbUser = \"postgres\";\nmy $backupBase = \"G:\\\\\";\nmy $keepPrevious = 1;\n\n\n#############\n### Functions\n#############\n\n# in_list($expr,@list) returns true iff $expr is in list.\nsub in_list {\n\treturn 0 if (@_+0 < 2);\n\n\tmy $element = $_[0];\n\treturn 0 if (!defined($element));\n\tshift @_;\n\n\tmy @list = @_;\n\treturn 0 if (!@_ || @_+0 == 0);\n\n\tmy $foundCount = 0;\n\tmy $e = quotemeta($element);\n\tforeach my $item (@list) {\n\t\tmy $i = quotemeta($item);\n\t\t$foundCount++ if ($e eq $i);\n\t}\n\n\treturn $foundCount;\n}\n\n# excluded($dbName) returns true if the database name is in the excluded database list\nsub excluded {\n\treturn 0 if (@_+0 != 1);\n\t\n\t# else\n\tmy $database = $_[0];\n\tif (in_list($database,@excludeDBs)) {\n\t\treturn 1;\n\t} else {\n\t\treturn 0;\n\t}\n}\n\n# keep_previous (database,path) retains the previous copy if we need to.\nsub keep_previous {\n\treturn 0 if (@_+0 != 2);\n\t\n\tmy $dbName = $_[0];\n\tmy $backupPath = $_[1];\n\t\n\treturn 1 if (!$keepPrevious);\t# Nothing special to do here.\n\t\n\tmy $backupFile = $backupPath . \"\\\\Backup-\" . $dbName . \".sql\";\n\tmy $prevBackupFile = $backupPath . \"\\\\Backup-\" . $dbName . \"-prev.sql\";\n\t\n\tif (-f $prevBackupFile) {\n\t\tif (!unlink($prevBackupFile)) {\n\t\t\tprint \"Could not remove $prevBackupFile, unsafe to continue.\\n\";\n\t\t\treturn 0;\n\t\t}\n\t}\n\tif (!rename($backupFile,$prevBackupFile)) {\n\t\tprint \"Could not rename $backupFile to $prevBackupFile, unsafe to continue.\\n\";\n\t\treturn 0;\n\t}\n\t\n\t# If everything is OK.\n\treturn 1;\n}\n\n\n########\n### MAIN\n########\nif (open(DBS,\"echo select datname from pg_database | psql -U $dbUser -A -F, -t 2>&1 |\")) {\n\twhile (<DBS>) {\n\t\tmy $line = $_;\n\t\tchomp $line;\n\t\tmy $dbName = (split(\/,\/,$line))[0];\n\t\tif (!excluded($dbName)) {\n\t\t\tprint \"Found database: $dbName\\n\";\n\t\t\tpush(@databasesToProtect,$dbName);\n\t\t}\n\t}\n\tclose(DBS);\n}\n\nforeach my $db (@databasesToProtect) {\n\tmy $backupPath = $backupBase . \"\\\\\" . $db;\n\tprint \"Backup $db into $backupPath\\n\";\n\tif (! -d $backupPath) {\n\t\tprint \"Backup $db to $backupPath -- create directory\\n\";\n\t\tif (mkdir($backupPath)) {\n\t\t\tprint \"...successfully created!\\n\";\n\t\t} else {\n\t\t\tprint \"...whoopsie!\\n\";\n\t\t}\n\t}\n\t\n\tmy $safe = keep_previous($db,$backupPath);\n\tif (!$safe) {\n\t\tprint \"Unsafe to continue for $db\/$backupPath, keep_previous was not happy.\\n\";\n\t}\n\t\n\t# OK, we can back this baby up.\n\tmy $backupFile = $backupPath . \"\\\\\" . \"Backup-$db.sql\";\n\tprint \"--> Starting pg_dump as of \" . localtime(time) . \"\\n\";\n\tsystem(\"pg_dump -d $db -U $dbUser > $backupFile\");\n\tprint \"--> pg_dump finished as of \" . localtime(time) . \"\\n\\n\\n\";\n}<\/code><\/pre>\n\n\n\n
Now, pulling this script into the policy is more a case of writing a basic one-line script to invoke<\/em> it. I saved it to the Windows database server in C:\\Users\\Administrator<\/strong> as pg_backup.pl<\/strong>, and configured the Data Manager script as follows:<\/p>\n\n\n\n
\"A<\/a>
Invoking the Perl script for the backup pre-script<\/strong><\/figcaption><\/figure>\n\n\n\n
I then just needed to change the pre-script in the policy to this new one:<\/p>\n\n\n\n
\"Amended<\/a>
Amended Data Manager Pre-Script settings in Policy<\/strong><\/figcaption><\/figure>\n\n\n\n
That was it! And the proof is in the file-search pudding:<\/p>\n\n\n\n
\"Data<\/a>
File Restore Search Results<\/strong><\/figcaption><\/figure>\n\n\n\n
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. <\/p>\n\n\n\n
\n\n\n\n
Hey, I’m still running a book giveaway competition! I’d love it if you<\/em> won.<\/p>\n\n\n\n
[CP_POLLS id=”12″]<\/p>\n","protected":false},"excerpt":{"rendered":"
I’ve been wanting to play around more with pre-scripting in PowerProtect Data Manager. The proof-of-concept style example I gave last…<\/p>\n","protected":false},"author":1,"featured_media":11756,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1638,1520,20],"tags":[752,1558,1258],"class_list":["post-11754","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-automation","category-powerprotect","category-scripting","tag-postgresql","tag-ppdm","tag-windows"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/nsrd.info\/blog\/wp-content\/uploads\/2024\/07\/bigStock-Databases.jpg","jetpack_shortlink":"https:\/\/wp.me\/pKpIN-33A","jetpack_sharing_enabled":true,"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/11754","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/comments?post=11754"}],"version-history":[{"count":5,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/11754\/revisions"}],"predecessor-version":[{"id":11772,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/posts\/11754\/revisions\/11772"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/media\/11756"}],"wp:attachment":[{"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/media?parent=11754"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/categories?post=11754"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nsrd.info\/blog\/wp-json\/wp\/v2\/tags?post=11754"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}