67 %
33 %
Information about pgWALSync

Published on February 18, 2014

Author: RummanIftekhar



pgWALSync - a tool fo Postgresql to decouple master from slaves in log shipping replication

PGWALSYNC DeCouple Postgresql Master from Slaves Ahmad Iftekhar Senior Postgresql Database Adminstrator Asurion Mobile Applications

Present Status (Master) • Check for /opt/pgdata/archive/ARCHIVE_ON • If exists, – reads slaves.conf and sends WAL files to slaves • If it fails to send in any of the salves, it copies that WAL file to /opt/pgdata/outboundwal and update “$slave.failure” • Next time starts sending files including file names at “$slave.failure”

Present Status (Slave) • Slave receives WAL files at the given directory at Master’s slave.conf usually it is /opt/pgdata/inboundwal • pgStandby applies WAL files • pgStandby removes WAL files from /opt/pgdata/inboundwal which are no longer required

Present Status : Problems • sends WAL file sequentially, so if there is any network delay for any of the slaves, WAL files pile up at pg_xlog • If fails to send WAL file in any of the slaves, it copies files to /opt/pgdata/outboundwal and hence /opt/pgdata may get out of space • does not support to copy WAL files in more than one local directory, however this may be achieved by using ssh to local machine

Hacking • New Version 1.4 • Added to support sending WAL files in more than one local directories; if it finds “localhost” in slaves.conf, it will use local copy instead of remote copy • Instead of “scp” as remote sending command, I used “scp –q” to keep the log clean and less size • New is capable of supporting the existing system

pgWalSync • New tool which copy WAL files from Master and acknowledge it and Master has the capability of intelligently remove unnecessary archive WAL files • • • Requires password less access between master and slaves

pgWalSync – directory structure • • • • • • • bin - all binary files build - carrier specific conf files cfg – configuration files doc lib – library to run the program log – log files process – process cache directory

pgWalSync: walCopier • • Runs in each slave • Copy files from master outboundwal using “masterHost”, “masterOutboundWal”, “localInboundWal” • Copy “maxNumberOfFilesToCopy” files in one process • Cron job to run

pgWalSync: walRemover • • • • • • • • Runs in master db host Cron job to call Remove file from master outboundWAL based on config parameters If number of files in outbound wal is less than “removerInitFileCount”, then do nothing, go to sleep; else starts removing files Before removing a file, check if all slaves receive that or not If it does not receive slave acknowledgement for “maxWaitTimeForAck”, then discard that slave and remove files If number of files in outboundwal crosses “maxFileAllowedInOutboundwal”, force NO SLAVE mode by discarding all slaves and remove files

pgWalSync: config parameters : walRemover related • slaveConf – list of slaves • masterOutboundWal – where walRemover • worksmaxNumberFileToRemove – maximum number of files to remove in an process • removerInitFileCount– If that many files exits in outboundwal dir, walRemover starts working • maxWaitTimeForAck – If master does not receive acknowledgement from a slave for this number of minutes, it discards that slave • maxFileAllowedInOutboudwal – If more than this number of files exist in outboundwal, force file removing

pgWalSync: config parameters : walCopier related • masterHost - host IP address of the • mastermasterOutboundWal – where WAL files are archived at masterlocalInboundWal - where to copy WAL files from • mastermaxNumberFileToCopy - maximum number of files to copy in one process • compressedCopy - whether the copy should use compression; suitable for distance slave

pgWalSync: walCopier : Compressed File • compressedCopy – default false • If it is true, then walCopier uses gzip to copy files from master and unzip it at slaves • Less network traffic • For example, gzip compressed one 16 MB of WAL files to 6.4 MB in my test scenario

Slave PgCluster: recovery.conf • In Recovery.conf we uses pg_standby • It waits for WAL files to be available • After applied, it removes WAL files from slave inboundwal

Source is available at: QUESTIONS?

Thank You

Add a comment

Related presentations