16. Log Shipping - Slony-I with Files

16.1. Usage Hints

• You don't want to blindly apply SYNC files because any given SYNC file may not be the right one. If it's wrong, then the result will be that the call to setsyncTracking_offline() will fail, and your psql session will ABORT, and then run through the remainder of that SYNC file looking for a COMMIT or ROLLBACK so that it can try to move on to the next transaction.

  But we know that the entire remainder of the file will fail! It is futile to go through the parsing effort of reading the remainder of the file.

  Better idea:

  • Read the first few lines of the file, up to and including the setsyncTracking_offline() call.

  • Try to apply it that far.

  • If that failed, then it is futile to continue; ROLLBACK the transaction, and perhaps consider trying the next file.

  • If the setsyncTracking_offline() call succeeds, then you have the right next SYNC file, and should apply it. You should probably ROLLBACK the transaction, and then use psql to apply the entire file full of updates.

  In order to support the approach of grabbing just the first few lines of the sync file, the format has been set up to have a line of dashes at the end of the "header" material:

  -- Slony-I log shipping archive
  -- Node 11, Event 656
  start transaction;
  
  select "_T1".setsyncTracking_offline(1, '655', '656', '2007-09-23 18:37:40.206342');
  -- end of log archiving header

• Note that the header includes a timestamp indicating SYNC time.

  -- Slony-I log shipping archive
  -- Node 11, Event 109
  start transaction;
  
  select "_T1".setsyncTracking_offline(1, '96', '109', '2007-09-23 19:01:31.267403');
  -- end of log archiving header

  This timestamp represents the time at which the SYNC was issued on the origin node.

  The value is stored in the log shipping configuration table sl_setsync_offline.

  If you are constructing a temporal database, this is likely to represent the time you will wish to have apply to all of the data in the given log shipping transaction log.

• You may find information on how relevant activity is logged in Section 26.6.1.

As of 1.2.11, there is an even better idea for application of logs, as the sequencing of their names becomes more predictable.

• The table, on the log shipped node, tracks which log it most recently applied in table sl_archive_tracking.

  Thus, you may predict the ID number of the next file by taking the latest counter from this table and adding 1.

• There is still variation as to the filename, depending on what the overall set of nodes in the cluster are. All nodes periodically generate SYNC events, even if they are not an origin node, and the log shipping system does generate logs for such events.

  As a result, when searching for the next file, it is necessary to search for files in a manner similar to the following:

  ARCHIVEDIR=/var/spool/slony/archivelogs/node4
  SLONYCLUSTER=mycluster
  PGDATABASE=logshipdb
  PGHOST=logshiphost
  NEXTQUERY="select at_counter+1 from \"_${SLONYCLUSTER}\".sl_archive_tracking;"
  nextseq=`psql -d ${PGDATABASE} -h ${PGHOST} -A -t -c "${NEXTQUERY}"
  filespec=`printf "slony1_log_*_%20d.sql"
  for file in `find $ARCHIVEDIR -name "${filespec}"; do
     psql -d ${PGDATABASE} -h ${PGHOST} -f ${file}
  done

16.2. find-triggers-to-deactivate.sh

It was once pointed out ( Bugzilla bug #19) that the dump of a schema may include triggers and rules that you may not wish to have running on the log shipped node.

The tool tools/find-triggers-to-deactivate.sh was created to assist with this task. It may be run against the node that is to be used as a schema source, and it will list the rules and triggers present on that node that may, in turn need to be deactivated.

It includes logtrigger and denyaccess triggers which will may be left out of the extracted schema, but it is still worth the Gentle Administrator verifying that such triggers are kept out of the log shipped replica.

16.3. slony_logshipper Tool

As of version 1.2.12, Slony-I has a tool designed to help apply logs, called slony_logshipper. It is run with three sorts of parameters:

• Options, chosen from the following:

  • h

    display this help text and exit

  • v

    display program version and exit

  • q

    quiet mode

  • l

    cause running daemon to reopen its logfile

  • r

    cause running daemon to resume after error

  • t

    cause running daemon to enter smart shutdown mode

  • T

    cause running daemon to enter immediate shutdown mode

  • c

    destroy existing semaphore set and message queue (use with caution)

  • f

    stay in foreground (don't daemonize)

  • w

    enter smart shutdown mode immediately

• A specified log shipper configuration file

  This configuration file consists of the following specifications:

  • logfile = './offline_logs/logshipper.log';

    Where the log shipper will leave messages.

  • cluster name = 'T1';

    Cluster name

  • destination database = 'dbname=slony_test3';

    Optional conninfo for the destination database. If given, the log shipper will connect to thisdatabase, and apply logs to it.

  • archive dir = './offline_logs';

    The archive directory is required when running in "database-connected" mode to have a place to scan for missing (unapplied) archives.

  • destination dir = './offline_result';

    If specified, the log shipper will write the results of data massaging into result logfiles in this directory.

  • max archives = 3600;

    This fights eventual resource leakage; the daemon will enter "smart shutdown" mode automatically after processing this many archives.

  • ignore table "public"."history";

    One may filter out single tables from log shipped replication

  • ignore namespace "public";

    One may filter out entire namespaces from log shipped replication

  • rename namespace "public"."history" to "site_001"."history";

    One may rename specific tables.

  • rename namespace "public" to "site_001";

    One may rename entire namespaces.

  • post processing command = 'gzip -9 $inarchive';

    Pre- and post-processign commands are executed via system(3).

  An "@" as the first character causes the exit code to be ignored. Otherwise, a nonzero exit code is treated as an error and causes processing to abort.

  Pre- and post-processing commands have two further special variables defined:

  • $inarchive - indicating incoming archive filename

  • $outnarchive - indicating outgoing archive filename

• error command = ' ( echo "archive=$inarchive" echo "error messages:" echo "$errortext" ) | mail -s "Slony log shipping failed" postgres@localhost ';

  The error command indicates a command to execute upon encountering an error. All logging since the last successful completion of an archive is available in the $errortext variable.

  In the example shown, this sends an email to the DBAs upon encountering an error.

• Archive File Names

  Each filename is added to the SystemV Message queue for processing by a slony_logshipper process.