pgAdmin 1.6 online documentation

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:

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', '2005-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', '2005-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 24.4.1, “ Log Messages Associated with Log Shipping ”.

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

Options, chosen from the following:

A specified log shipper configuration file

This configuration file consists of the following specifications:

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:

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.