David Fetter expanded upon a
blog entry written by Joshua Drake by demonstrating how to create a PostgreSQL backup system based on the Model-View-Controller (MVC) design pattern. The simplicity of implementing such a reliable backup system inspired me to add a bit more functionality to make it fit a bit better within my organization since our backup requirements are a bit more complex than just a daily backup of each database. We, for example, make an hourly backup of a fairly large database and a weekly backup of another, very large database.
I chose to create a MVC backup system that would provide the administrator the ability to create backup profiles called by psql from a single script and store this backup configuration data within the database. Accordingly, I chose to implement a few rules to make it a safe system:
- All databases are backed-up unless they are expressly ignored. This means that an administrator can create databases until the cows come home and they will automatically be included in the backup. Unless...
- Any number of databases can be excluded by adding them to the backup_ignored table for the profile.
- All databases are backed-up if the backup_included table for the profile contains zero entries.
- Finally, only those databases listed in the backup_included table are backed-up, regardless of the contents of the backup_ignored table.
With the rules defined, let's put it all together.
The Schema
It's a simple, three-table schema (look Josh, no surrogate keys!):
-- create pg-backups schema
--
drop table backup_profiles cascade;
create table backup_profiles (
profile_name text constraint pk_backup_profiles primary key,
dest_directory text not null default '/var/tmp/',
bin_directory text default null,
backup_globals boolean not null default false
);
-- Create the default (full backup) profile
insert into backup_profiles(profile_name, backup_globals) values ('default', true);
drop table backup_ignored cascade;
create table backup_ignored (
profile_name text constraint fk_backup_ignored_profile_name references backup_profiles
on delete cascade,
db_name text not null,
constraint pk_backup_ignored primary key (profile_name, db_name)
);
drop table backup_included cascade;
create table backup_included (
profile_name text constraint fk_backup_included_profile_name references backup_profiles
on delete cascade
-- The default profile is used for full backups unless
-- the database is expressly ignored.
constraint ck_backup_included_profile_name
check (trim(profile_name) <> 'default'),
db_name text not null,
constraint pk_backup_included primary key (profile_name, db_name)
);
The Support FunctionsThese are the support functions:
-- Pg backups functions
--
create or replace function get_all_dbs() returns setof text as
$$
select quote_ident(datname) as d
from pg_database
where not datistemplate;
$$ language sql;
create or replace function ignore_db(v_pname text, v_db text) returns boolean as
$$
begin
insert into backup_ignored(profile_name, db_name) values (v_pname, v_db);
return found;
end;
$$ language plpgsql volatile;
create or replace function include_db(v_pname text, v_db text) returns boolean as
$$
begin
insert into backup_included(profile_name, db_name) values (v_pname, v_db);
return found;
end;
$$ language plpgsql volatile;
create or replace function add_trailing_slash(text) returns text as
$$
select case when substr($1, length($1)) = E'/' then $1 else $1 || E'/' end;
$$ language sql immutable;
create or replace function add_profile(
v_pname text, v_destdir text, v_bindir text,
v_bkglobals boolean default false) returns boolean as
$$
begin
insert into backup_profiles(profile_name, dest_directory, bin_directory, backup_globals)
values(v_pname, v_destdir, v_bindir, v_bkglobals);
return found;
end;
$$ language plpgsql volatile;
create or replace function fixup_profiles() returns trigger as
$$
begin
new.profile_name := lower(new.profile_name);
new.dest_directory := add_trailing_slash(new.dest_directory);
new.bin_directory := add_trailing_slash(new.bin_directory);
return new;
end;
$$ language plpgsql;
create or replace function fixup_profile_name() returns trigger as
$$
begin
new.profile_name := lower(new.profile_name);
return new;
end;
$$ language plpgsql;
create trigger trg_excluded_fixup_profile_name before insert or update on backup_ignored
for each row execute procedure fixup_profile_name();
create trigger trg_included_fixup_profile_name before insert or update on backup_included
for each row execute procedure fixup_profile_name();
create trigger trg_profile_fixups before insert or update on backup_profiles
for each row execute procedure fixup_profiles();
The Workhorse Function
This is the workhorse function. I apologize for the pathetic word-wrapping. It will be easier if you view the source directly.
create or replace function pg_backups(v_pname text default 'default') returns setof text as
$$
declare
p backup_profiles%rowtype;
i backup_included%rowtype;
included_count integer;
query_str text;
begin
-- Determine if the profile exists
select * into p from backup_profiles where profile_name = v_pname;
if not found then
raise exception 'Invalid profile: %', v_pname;
end if;
-- Determine if the profile specifies individual databases
select count(*) into included_count from backup_included where profile_name = v_pname;
-- If the default profile is specified or if the profile doesn't specify any databases
-- include all the databases except those explicitly ignored.
-- This is the model part of MVC
if (v_pname = 'default' or included_count = 0) then
query_str := 'with dbs as (
select * from get_all_dbs() as d
except
select db_name from backup_ignored where profile_name = ' ||
quote_literal(v_pname) || ') ';
else
-- Otherwise grab only those databases specified in the profile
query_str :=
'with dbs as (
select db_name as d from backup_included where profile_name = ' ||
quote_literal(v_pname) || ') ';
end if;
-- Do this here to avoid the headache of an additional quote_literal below
p.bin_directory := coalesce(p.bin_directory, '');
-- Append the view part of the MVC equation
query_str := query_str ||
'select ' || quote_literal(p.bin_directory || 'pg_dump -U postgres -Fc --file=' ||
p.dest_directory) || ' || d || ' ||
'to_char(now(), ' || quote_literal('-YYYYMMDD-HH24MISS') || ') || ' ||
quote_literal('.pgbackup ') || ' || d ' ||
'from dbs
union
select ' || quote_literal(p.bin_directory || 'pg_dumpall -g -U postgres --file=' || p.dest_directory ||
'globals') || ' || ' ||
'to_char(now(), ' || quote_literal('-YYYYMMDD-HH24MISS') || ') || ' || quote_literal('.pgbackup ') || ' ' ||
'where ' || quote_literal(p.backup_globals) || ' = true';
return query execute query_str;
end;
$$ language plpgsql volatile;
Ancillary FunctionalityI wanted to add some error checking to each database as it was backed-up. To accomplish that without modifying anything in the configuration, I wrote a very simple filter in Perl:
#!/usr/bin/perl
# Simple example of a filter to provide more feedback
# on the status of Postgres backups using the MVC method.
my $prepend = undef;
my $append_template = <<apt;
2> /dev/null || echo "Postgres database backup of %D FAILED (rc = ${?})"
apt
while (<>) {
chomp(my $append = $append_template);
# This depends on having "globals" in the pg_dump output
($dbname) = /globals/ ? "globals" : $_ =~ /\s+(\w+)$/;
$append =~ s/%D/$dbname/;
s/^/$prepend / if defined($prepend);
s/$/ $append/ if defined($append_template);
print;
}
The Controller PortionFinally, this is what actually executes the backup process. This is an example of how to call it from cron in a recent Linux distribution (please be aware of the possible odd word-wrapping):
# /etc/cron.d/postgres: crontab fragment for Postgres backups
# Backup PostgreSQL databases
MAILTO=postgres
50 23 * * * postgres /usr/bin/psql --variable=profile="'default'" -Aqtf /home/admin/bin/pg-backups.sql 2> /dev/null | /home/admin/bin/pg-backups-filter | sh
The psql utility is one of the most capable and useful database administration utilities with which I've ever had the pleasure of working. The key to making this system work is the ability to pass a parameter to the function that is called by psql (listed below).
The Simple SQL ScriptThis is, for the purposes of this discussion, the contents of /home/admin/bin/pg-backups.sql:
select * from pg_backups(:profile);
That's all folks!
with my previous entry on PostgreSQL MVC backups, I was constantly thinking that there had to be a better way to do this.