Advanced WAL File Management With OmniPITR

0

No comments posted yet

Comments

Slide 1

Advanced WAL File Management With OmniPITR Robert Treat, PGDay Denver, 2011

Slide 2

Robert Treat OmniTI Database Management and Consulting We’re Hiring! Postgres Major Contributor, Web, Advocacy, Random xzilla.net @robtreat2 +RobertTreat Who Am I?

Slide 3

Postgres emits WAL files What is PITR?

Slide 4

WAL Logs? Write Ahead Log Logs? OTOH, they are stored in pg_xlog... Digression

Slide 5

Postgres emits WAL files Send the WAL file to another server The other server can replay the WAL What is PITR?

Slide 6

Postgres emits WAL files Send the WAL file to another server The other server can replay the WAL What is PITR? Use combination of data files and WAL to make more postgrezes

Slide 7

8.1 (really) 8.2 (warm standby) 9.0 (hot standby) 9.0 (streaming replication) 9.1 (synchronous replication) A Brief History of PITR

Slide 8

Simple, Better Than You’d Think Ghetto Style archive_command = 'rsync %p sdb2:/data/pgsql/sdb1/84/walarchive/% f'

Slide 9

Kind of Hacky Early Version Of A PITR Script archive_command = ‘cp -i %p /data/pgsql/82_walarchives/%f </dev/null’

Slide 10

Kind of Hacky Early Version Of A PITR Script archive_command = ‘cp -i %p /data/pgsql/82_walarchives/%f </dev/null’ opendir(DIR, ".");my @wals2move = grep { -f $_ && (stat(_))[9] + $AGE < time() && ( # 000000010000007200000031 ( /^[0-9A-F]{24}$/ && (stat(_))[7] == $WALSIZE ) || # 000000010000007200000031.0012C968.backup ( /^[0-9A-F]{24}\.[0-9A-F]{8}\.backup$/ ) ) && ( $mtime{$_} = (stat(_))[9] ) # Always true (for later) } readdir(DIR);closedir(DIR);

Slide 11

Kind of Hacky Early Version Of A PITR Script archive_command = ‘cp -i %p /data/pgsql/82_walarchives/%f </dev/null’ @wals2move = sort { $mtime{$a} <=> $mtime{$b} } @wals2move;if($TARGET) { foreach (@wals2move) { if($NOOP) { print "$RSYNC -essh --rsync-path=$REMOTE_RSYNC -a $_ $TARGET/$_\n"; print "mv $_ $_.slaved\n"; } else { if(system("$RSYNC -essh --rsync-path=$REMOTE_RSYNC -a $_ $TARGET/$_") != 0) { print STDERR "Error moving wal to target: $?\n"; last; } if(!rename("$_", "$_.slaved")) { print STDERR "Error renaming $_ to $_.slaved\n"; last; } } }}

Slide 12

Multiple Destinations WAL File Delay Archive Storage (gzip & friends) Better Backups Customizations

Slide 13

primary

Slide 14

primary feed hot standby for failover write xlogs to shared storage failover

Slide 15

primary backups from hot standby feed to shared storage failover

Slide 16

primary feed warm standby in separate data centre (running on separate OS!) failover DC2

Slide 17

primary feed reporting server, rebuilt every 12 hours failover DC2 reports

Slide 18

primary feed sanitizing db, rebuilt daily deliveries transformed copies to clients failover DC2 reports sanitize

Slide 19

primary use backups and xlogs to spin up read only slaves failover slaves DC2 reports sanitize

Slide 20

Consolidate various scripts Deploy across ‘Nixes Reusable Complete Solution ENTER: OmniPITR

Slide 21

Lots of options compression custom log file special paths multiple destinations OmniPITR-Archive

Slide 22

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"'

Slide 23

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' Location of OmniPITR

Slide 24

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' Custom Log File Name/Location

Slide 25

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' state-directory to handle errors when sending wal to multiple destinations

Slide 26

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' Where To Create Temp Files

Slide 27

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' send to remote server (dbx) compressed (gzip)

Slide 28

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' send to remote server (db4) uncompressed

Slide 29

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' Where to store xlogs when building a backup

Slide 30

OmniPITR-Archive Example archive_command = '/opt/OMNIpitr/bin/omnipitr-archive -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -s /var/lib/pgsql/omnipitr/state/ -t /var/tmp/ -dr gzip=dbx:/mnt/db/prod/walstorage/ -dr db4:/mnt/db/prod/db4-walarchive/ -db /var/lib/pgsql/omnipitr/backup.xlogs "%p"' The xlog file :-)

Slide 31

OmniPITR-Restore Lots of options (though not as many) compressed files? custom log file special paths

Slide 32

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' location of omnipitr-restore program

Slide 33

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' custom log file format

Slide 34

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' Source of WAL Files to Use For Restore

Slide 35

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' pause xlog removal if this file exists (foreshadow: used for making backups)

Slide 36

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' verbose

Slide 37

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' Remove Xlogs Files Which Are No Longer Needed

Slide 38

OmniPITR-Restore Example restore_command = ' /opt/OMNIpitr/bin/omnipitr-restore -l /var/lib/pgsql/data/pg_log/omnipitr-^Y-^m-^d.log -s /var/lib/pgsql/wal_archive/ -p /var/lib/pgsql/wal_archives.pause -v -r %f %p' standard macros for “basename of segment” and “destination of recovery”

Slide 39

Goal simple “tarballs” for backup one for $PGDATA and xlogs simple to blow open and use OmniPITR-Master-Backup

Slide 40

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data location of OmniPITR-backup-master

Slide 41

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data custom log file

Slide 42

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data directory to store xlogs during backup creation

Slide 43

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data copy backup file to remote server, compressed

Slide 44

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data location for temporary files whilst working

Slide 45

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data path to psql

Slide 46

OmniPITR-Master-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-master -l "/var/log/omnipitr/omnipitr-^Y-^m-^d.log" -x /var/lib/pgsql/omnipitr/backup.xlogs -dr gzip=db4:/mnt/db/prod/backups/ -t /var/tmp/ -pp /usr/pgsql-9.0/bin/psql -D /pg_data/90data $PGDATA

Slide 47

Programmers Say: “I love it when people use my software to do cool things that I didn’t expect”

Slide 48

Treat’s Maxim? “Try not to let the way software was designed get in the way of how you want to use it”

Slide 49

On MySQL, you could dump on slave for a long time On ZFS, we use snapshots to make slaves on the backups While “we” prefer to use Solaris/ZFS, some of us prefer to use Linux But, you still don’t want to pay the price for backups on master OmniPITR-Slave-Backup

Slide 50

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata Location of omni-pitr-slave command

Slide 51

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata custom log location / format

Slide 52

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata source of wal files

Slide 53

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata file to pause removal of xlogs no longer needed for restore but still needed by backup

Slide 54

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata $PGDATA

Slide 55

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata location for temp files whilst working

Slide 56

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata location of our “gzip”

Slide 57

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata local storage, compressed

Slide 58

OmniPITR-Slave-Backup Example /opt/OMNIpitr/bin/omnipitr-backup-slave -l "/var/log/omnipitr/omnipitr-slave-backup-^Y-^m-^d.log" -s /mnt/db/prod/db4-walarchive -p /var/lib/pgsql/omnipitr/pause.removal -D /pg_data/90data/ -t /var/tmp/ -gp /usr/bin/pigz -dl gzip=/mnt/db/prod/backups/ -pp /usr/pgsql-9.0/bin/pg_controldata path to pg_controldata

Slide 59

Versions 8.2, 8.3, 8.4, 9.0, (9.1?) Linux, Solaris TB+ Databases, with multiple tablespaces Thousands of txn/sec Cross Datacenter Production Use?

Slide 60

Now Available on GitHub! Git Clone “BSD” Licensed Where’s The Code? https://github.com/omniti-labs/omnipitr git clone git://github.com/omniti-labs/omnipitr.git also on pgxn!

Slide 61

BUGS Failover of a streaming replication based slave created from a slave based backup file against a stream replication based slave doesn’t work

Slide 62

BUGS Failover of a streaming replication based slave created from a slave based backup file against a stream replication based slave doesn’t work, sometimes,

Slide 63

BUGS Failover of a streaming replication based slave created from a slave based backup file against a stream replication based slave doesn’t work, sometimes, when under load

Slide 64

BUGS Failover of a streaming replication based slave created from a slave based backup file against a stream replication based slave doesn’t work, sometimes, when under load Due to a “flaw” in Postgres

Slide 65

BUGS Failover of a streaming replication based slave created from a slave based backup file against a stream replication based slave doesn’t work, sometimes, when under load Due to a “flaw” in Postgres We have a fix/work-around available, which coordinates calls to pg_{start|stop}_backup on master

Slide 66

Better Monitoring Parallel Multi-Destination PITR Restores from Multiple Sources Config Files TODO

Slide 67

Trying to Convince PG Devs to Make These Things “Built In” Working on slaves from backups Getting easier to manage streaming servers with no WAL Some day, OmniPITR may be obsolete. But, probably not today. OF NOTE

Slide 68

THE END Thanks! pgday denver depesz aweber, gilt, etsy, omniti

Slide 69

THE END Slides http://www.xzilla.net/ @robtreat2

Summary: OmniPITR is a set of tools for doing advanced WAL file management with Postgres

Tags: postgres postgresql omniti pitr wal pgday denver omnipitr replication

URL:
More by this User
Most Viewed
Previous Page Next Page
Previous Page Next Page