Backup Question

Hey everyone,

This should be pretty straight-forward, but figured I'd pass it by anyway.

I have a revised backup process that's coming out inconsistent, and I'm not entirely sure why. I call
pg_start_backup(),tar.gz the contents elsewhere, then pg_stop_backup(). Nothing crazy. Upon restore, two of my tables
reportduplicate IDs upon executing my redaction scripts. The "duplicate" records ended up having different ctid's,
suggestingthe log replay was incomplete. However, nothing in the restore logs suggest this is the case, and either way,
thedatabase wouldn't have come up if it were. (right?) 

Now, the main difference, is that I'm doing the backup process on our streaming replication node. The backup process
callsthe pg_start_backup() function on the upstream provider, backs up the local content, then calls pg_stop_backup()
onthe upstream provider. In both cases, it captures the start/stop transaction log positions to grab all involved
archivedWAL files. I already know the start xlog position is insufficient, because those transaction logs may not have
replayedon the standby yet, so I also grab 3xcheckpoint_timeout extra older files (before backup start), just in case. 

So, I get no complaints of missing or damaged archive log files. Yet the restore is invalid. I checked the upstream,
andthose duplicate rows are not present; it's clearly the backup that's at fault. I remember having this problem a
coupleyears ago, but I "fixed" it by working filesystem snapshots into the backup script. I can do that again, but it
seemslike overkill, honestly. 

Why am I using my own backup system instead of pg_basebackup, or Barman or something? Because I use pigz for parallel
compressionand hard links to save space. I can back up a 800GB database in less than 20 minutes a night, or 45 minutes
fora non-incremental backup. Without disturbing the primary node. Like I said, I can enable filesystem snapshots to fix
this,but it feels like something more obvious is going on. 

Any ideas?

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com




______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email