Becoming a log-shipping receiver for a Slony cluster

This wiki page is intended to instruct remote DRMS users of our exported Slony data clusters. If you're reading this, we assume you want to get a dynamic set of our data and regularly pull updates from us. We assume you have system resources set aside, a postgres database set up, and a copy of the JSOC DRMS code in place. A basic understanding of SSH/SCP will be useful. Pulling Slony logs requires you to have certain trusted privileges on our system via ssh keys, so we want to know who you are. If we don't know you yet, or this is your first time setting up a remote DRMS, please contact either Rick Bogart or Jennifer Spencer at Stanford (see the ["Contacts"] page). BRBR For more information, please read: ["SlonyI"], and [http://jsoc.stanford.edu/netdrms/]. Please note there is more to becoming a remote DRMS than pulling Slony logs. Please read the NetDRMS documentation (link above) for more information if needed. For more on dealing with SSH keys and setting up a remote SUMS, please see: [http://jsoc.stanford.edu/netdrms/rmtsums.html].

The following document assumes you have already set up scp/ssh to and from solarport.stanford.edu. If you cannot do so, you will need to contact us before proceeding. To regularly pull Slony logs will require that you have a key on our system and we prefer you use HPN-SSH, as it is more efficient than regular SSH/SCP (it can be configured to encrypts the password and command exchange and not the data stream). TableOfContents

Subscribe Series

The subscribe series component, run with a single configuration file, allows for a subscriber to create or modify its subscription with the webdb host.

Architecture

Subscribe_series goes through the following steps: 1. Extracts variables from the configuration file. 2. Check to see if the schema and database exists on the subscribers database 3. Stops the ingestion script 4. Creates a trigger file to alert webdb box of a subscription request. 5. Checks to see if the schema and tables exist on the subscriber. 1. Appends result to trigger file 6. Copies trigger file to the webdb machine and awaits a response 7. When a return trigger file on the webdb box is found, it secure copies over the sql file generated by the webdb box. 8. Executes the sql file and get a success or failure return value 9. Create trigger file on webdb box containing a success or failure on sql application and wait for final notification of subscription completion. 10. Finds subscription completion trigger file on webdb and restarts ingestion script

Pre-requisites

The subscriber needs the following:

Installation

  1. Check out $JSOC_ROOT/base from cvs
  2. Edit the subscribe_series config file:
    • a) cd to $JSOC_ROOT/base/drms/scripts/subscribe_series/etc

      b) copy subscribe_series.cfg.sample to <site_name>.subscribe_series.cfg c) Edit the new <site_name>.subscribe_series.cfg file as follows:

  3. set pg_host to either localhost or the I.P. Address of your local database server
  4. set pg_port to the port of your local db (default = 5432)
  5. set pg_user to slony
  6. set pg_dbname to the local database name
  7. set pg_schema to public
  8. set ingestion_path to the full path/directory where the get_slony_logs.pl script lives
    • (i.e. $JSOC_ROOT/base/drms/scripts)
  9. set node to the site_name (the same site_name used in re-naming the

sample.subscribe_series.cfg.sample file e.g. nso)

parts. First will be <schema>.<series> and second will be either “subscribe” or “unsubscribe”. NOTE: you can add multiple series names (one per line)

the node parameter in the <site_name>.subscribe_series.cfg file

  1. Execute a subscribe series (as a new subscriber)
  2. cd $JSOC_ROOT/base/drms/scripts/subscribe_series

  3. ./subscribe_series ../etc/<site_name>.subscribe_series.cfg

../etc/<site_name>.subscribe_list.cfg <ssh_agent_rs file> (Note: on most systems the ssh_agent_rs file is likely ~/.ssh-agent_rs)

of the script):

(using the full path)

the name of your local database

alerted in case of errors (note: this can be set to empty single quotes if no alerts are desired)

you should get an int value back (e.g. 756)

where <ID> = the at_counter value from the above query (i.e. 756) and the same site name as in the node parameter in the <site_name>.subscribe_series.cfg file

Initialization / Configuration

1) The subscriber requires a configuration file, and a subscribe list :

# Subscribe series file name <Subscription_node_id>.subscribe_series.cfg # Required connection information for communicatoin between localhost and the subscription server # webdb_ip # target IP address or hostname (e.g. solarport) webdb_ip 192.168.0.88 # webdb_dir # target dir webdb_dir /stage/dev/triggers/ # webdb_user # user to connect to the target ip webdb_user postgres # pg_host # host of the subsribers database pg_host localhost # pg_port # port of the subscribers database pg_port 5432 # pg_user # user of the subscribers database pg_user slony # pg_dbname # name of the subscribers database pg_dbname postgres # pg_schema # name of the schema the data will live in pg_schema public # archive (archive to tape when retention is up? 0=no, 1=yes)# archive 0 # retention (time in days your SUMS data files live on your server)# retention 21 # tapegroup (unless you have a SUMS tape system, leave as is)# tapegroup 1 # attempts # number of attemps the script checks webdb_ip for notifications # each attempt is 5 seconds apart attempts 50 # ingestion_path # the path to put the ingestion die file to stop the ingestion script. ingestion_path /home/production/subscribe_series/bin/ #node # node name or id node node1 ◦ Create subscribe list file: ../etc/<node name>.subscription_list.cfg #schemaname.seriesname subscribe/unsubscribe (if this is a new site, assumes these are "subscribe") public.admin subscribe

Subscription_manager

Subscription_manager is a daemon that runs on webdb and constantly checks for certain trigger files, and executes other scripts depending on those files. It will execute three other scripts in the subscription process, subscription_update, sql_gen, and subscription_cleanup.

Architecture

Subscription_manager loops continuously looking for two types of trigger files.

  1. Finds a <node name>.subscription_series.tgr file

  2. Creates new configuration file ($subupdatefile) after extracting the following variables:
  3. $node
  4. $archive
  5. $retention
  6. $tapegroup
  7. Checks to see if the $node already exists or is a new subscriber.
  8. Sets $new_site to either true or false
  9. Writes “new_node (true/false)” to the trigger file for future extraction.
  10. Executes ./subscription_update $node $new_site $archive $retention $tapegroup

$subupdatefile &

  1. Find a <node name>.subscription_series.sqlapplied file

  2. Creates new configuration file ($appliedfile) after extracting the following variables:
  3. $node
  4. $success (reffers to whether or not the subscriber applied sql files successfully)
  5. $new_site

  6. Executes ./subscription_cleanup $node $new_site $success $appliedfile &

Subscription_update

Subscription_update is executed by subscription_manager when an initial subscription request is found

  1. Executed by ./subscription_update $node $new_site $archive $retention $tapegroup

$subupdatefile &

  1. Creates a directory named <node name>.new in the subscribers directory.

  2. If new_site = true, it copies the current directory <node> to <node>.new

  3. If new_site = false, just creates a new, empty directory <node>.new

  4. If new_site is true, adds all subscriptions in the $subupdate file to the new usertables file
  5. If new_site is false, does two things
  6. Removes subscriptions with the tag “unsubscribe” from the user table list
  7. Adds all subscriptions with the tag “subscribe” to the end of the user tables list
  8. Checks to see if the subscription already exists, if so do nothing.
  9. Add the $node.new directory entry to the slony_parser.cfg file

  10. Executes ./sql_gen $node $new_site $archive $retention $tapegroup $input_file &

sql_gen Sql_gen is executed by subscription_update after it has completed updating the subscription tables.

  1. Executed by ./sql_gen $node $new_site $archive $retention $tapegroup $input_file &

  2. Removes any “unsubscribe” requests from the subscription list file.
  3. If there are no NEW subscriptions, then skips steps 4, 5, and 6.

  4. For each <schema> requested, executes ./createns ns=<schema> nsgroup=user dbusr=<pg user>

>> <trigger dir>/$<node>.createns.sql

  1. For each <schema>.<series> executes ./createtabstructure in=$<schema>.<table>

out=$<schema>.<table> archive=<archive> retention=<retention> tapegroup=<tapegroup> owner=slony >> <trigger dir>/<node>.subscribe_series.sql

  1. Executes ./sdo_slony1_dump.sh <pg dbname> jsoc <pg port> <new site (1=true, 0=false)> <all

tables requested> >> <triggerdir/$node.subscribe_series.sql 2> /dev/null

  1. Creates trigger file for subscribers to retrieve sql file

Subscription_cleanup

Subscription_cleanup is executed by subscription_update

  1. Executed by ./subscription_cleanup $node $new_site $success $appliedfile &

  2. Removes the $node.new list from the slony_parser.cfg file
  3. Depending on success of sql application on the subscriber:
  4. If $success is true and $new_site is true
  5. Adds new subscription listing to slony_parser.cfg
  6. Moves directory and usertable list file from $node.new to just $node
  7. If $success is true and $new_site is false
  8. Replaces old directory and usertable list with new ones
  9. If $success is false and $new_site is true
  10. Removes new directory and usertable list file, subscription completely removed.
  11. If $success is false and $new_site is false
  12. Removes new Directory and usertable list file, previous subscription state remains.

Pre-requisites

Requires slony installed and shipping logs. Installation

  1. Place scripts in the desired bin directory
  2. Create the following directories up one level:
    • a) etc b) log c) triggers
  3. Determine locations of the subscribers and tables directories for input into config files

Initialization / Configuration

All scripts on webdb requires this single configuration file:

  1. ../etc/subscription_manager.cfg

# Directory the trigger files appear in from the subscriber triggerdir=/solarport/pgsql/slon_logs/triggers/ # Directory the subscribers directories exist subscribers_dir=/solarport/pgsql/slon_logs/site_logs/ # File the slon parser uses as input parser_config=/solarport/pgsql/slon_logs/etc/slon_parser.cfg # Directory the table files exist tables_dir=/solarport/pgsql/slon_logs/etc/ # port of the database pg_port=5432 # host of the database pg_host=localhost # user of the database pg_user=slony # name of the database pg_dbname=jsoc

Each Slony cluster represents a discrete set of JSOC data. Pulling logs and data must be done by cluster. It is our intention to have several clusters, each tailored to suit as many users as possible. A Slony cluster is not divisible; we can only replicate our data per cluster, not per user, so you may expect to subscribe to more than one cluster at a time. These instructions are for working with one cluster, and are meant to be extrapolated for use with many clusters.

The official available cluster list:

Cluster name

Namespace.Seriesname

mdi_mag

mdi.fd_m_96m_lev18

(more will be added over time)BR

Our Slony logs are presently kept at /scr21/jennifer/slony_logs/ under subdirectories by cluster name. The initial JSD and slony1_dump.sh output needed to setup your cluster are found in those subdirectories as well.

Get a Cluster

scp jsocexp@solarport.stanford.edu:/scr21/jennifer/slony_logs/mdi_mag/mdi.fd_M_96m_lev18.jsd .

Make a database user 'slony'

Then if you do not have a database user "slony", you'll need to create one. For Postgres version 8.3 this appears quite differently than version 8.4. Below are the instructions for v 8.3, with v8.4 instructions following.

Version 8.3

Check and see if you have one in postgres. {{{psql mydatabase... Welcome to psql 8.x.x, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

jsoc=# \du slony

+

+

+

+

+

Will appear as above if you have a user slony in your system. Otherwise, you'll need to add one.

\q}}}

To add the 'slony' user, go into your postgres database as user 'postgres' (this document assumes you have the required password). Then create your slony admin-level user. This is the user that will handle running all your slon processes should you ever change from Slony log-shipping mode to "classic" mode, and you do not need to have a Linux/Unix login for this user. Note that if you are planning on running replication on the same machine as the database server, you may have authentication issues logging in as user 'slony' on the localhost. You may have to tell postgres the hostname (add -h myhost) for all psql commands running on that system if your pg_hba.conf file (in your postgres data directory) is configured with 'ident sameuser' for localhost. 

create user slony with admin postgres password 'slonypassword';

where 'slonypassword' is a unique password for your system.

Version 8.4

Check and see if you have one in postgres. {{{psql mydatabase... Welcome to psql 8.x.x, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

jsoc=# \du slony

+

+

Will appear as above if you have a user slony in your system. Otherwise, you'll need to add one.

\q}}}

To add the 'slony' user, go into your postgres database as user 'postgres' (this document assumes you have the required password). Then create your slony admin-level user. This is the user that will handle running all your slon processes should you ever change from Slony log-shipping mode to "classic" mode, and you do not need to have a Linux/Unix login for this user. Note that if you are planning on running replication on the same machine as the database server, you may have authentication issues logging in as user 'slony' on the localhost. You may have to tell postgres the hostname (add -h myhost) for all psql commands running on that system if your pg_hba.conf file (in your postgres data directory) is configured with 'ident sameuser' for localhost. 

create user slony with password 'slonypassword';

where 'slonypassword' is a unique password for your system.

Now grant administrative permissions to database user slony: 

grant postgres to slony;

Then make sure that the password for slony is in user postgres' .pgpass file if you are using md5 security. If user postgres does not have a .pgpass file, you'll need to make one in the postgres home directory. For example: 

cat ~/.pgpass
postgreshostname:*:*:slony:slonypassword

Don't forget to make sure the permissions are correct for this file: chmod 600 ~/.pgpass

Setup a default session namespace

We recommend that the default namespace for slony be 'mdi' if you are importing a cluster with mdi but you should use the name of which ever namespace you know is in your cluster if you are not importing mdi. 

psql -U slony myjsoc_db

If this does not work, and you are on the same server as is running your database, you may try: 

psql -U slony -h myhostserver myjsoc_db

Further issues are likely due to your pg_hba.conf file settings, which do require a 'reload' of postgres to take effect. See [http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html the Postgres web site on this topic for more]. BR BR Once you can correctly log in to your database as user slony, proceed to the next step below. 

echo $JSOC_DBHOST
echo $JSOC_DBNAME
echo $JSOC_DBUSER

You should get the correct hostname for the database machine, your local JSOC database name, and the user 'slony'.  If not, set these variables for this session:

For Bash users:
$ export JSOC_DBHOST=hostname.stanford.edu
$ export JSOC_DBNAME=myjsoc_db
$ export JSOC_DBUSER=slony

For C shell users:
$ setenv JSOC_DBHOST hostname.stanford.edu
$ setenv JSOC_DBNAME myjsoc_db
$ setenv JSOC_DBUSER slony

If you need a namespace similar to an existing reserved namespace, for example to identify analysis or repackaged products based on data from various sources, we recommend that you create one from your institutional identifier and the source identifier, e.g. sha_gong, cu_mdi, mps_hmi, etc. BR Provenance of data is critical to a successful science mission. We must all work together to maintain the integrity of our data namespaces so that data provenance remains unequivocally clear. The JSOC teams reserves the following namespaces, and explicitly forbids remote DRMS users adding any new data to these spaces; only replicated data received from the JSOC is allowed in a JSOC-reserved namespace. To put it in a more general way, any data series in a DRMS with one of these reserved namespaces must have been generated at or replicated from the owning site. We expect that remote DRMS sites will have a similar policy about their own namespace designations, and we encourage them to document accordingly.

Reserved Namespaces

Please see [:DrmsSeriesNames#namespaces] for a complete list of reserved namespaces.

masterlists dbuser=slony namespace=mdi nsgrp=user 

BR You will only need to change the header, called "General Series Information", if even that requires a change for your system (it may not). BRDo not change anything beyond the first line of #===Links===== BR If you change any links, keywords or segments, your replication may fail. Or, it may not fail but it may cause mystery display problems. So please leave those alone. That said, here are some of the header fields you may need to change for your system. 

#=====General Series Information=====
Seriesname:             mdi.fd_M_96m_lev18
Author:                 "phil"
Owner:                  slony
Unitsize:               15
Archive:                0
Retention:              100
Tapegroup:              1
PrimeKeys:              T_REC
DBIndex:                T_REC
Description:            "MDI"

#=====Links=====
(stop here!)

Owner

May be set to any individual Postgres user on your system, but should be user slony if mirror updates are to be handled by slony processing. N.B.: This field in the JSD file is ignored by the create_series program; the owner of the series will be the user running the program, unless changed by the Postgres administrator.

Retention

Default retention time in days for data segments on SUMS disk cache for records in the series; data segments will be flushed no sooner than this many days after having been staged. Note: this value, and the others relating to SUMS segments and archiving, is irrelevant if there are no data segments associated with the series, i.e. if all the data are in the DRMS database.

Archive

Set to 1 only if you have a tape archiving system for SUMS and wish to archive the data segments in the series

Tapegroup

only applicable for SUMS with tape archive capability; controls which tapes data segments from the series are archived to. Remove this line if you don't have a tape group set up.

Use the DRMS create_series module. {{{create_series mdi.fd_M_96m_lev18.jsd JSOC_DBUSER=slony

Creating new series 'mdi.fd_M_96m_lev18'... NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fd_m_96m_lev18_pkey" for table "fd_m_96m_lev18" }}}

BRGo get the cluster setup data. BR scp jsocexp@solarport.stanford.edu:/scr21/jennifer/slony_logs/mdi_mag/mdi_mag_slony_setup.sql .

psql -Uslony myjsoc_db -f mdi_mag_slony_setup.sql You should some output from this, but not a whole lot. This script will install two tables and three functions as detailed in SlonyDumpFuncs, and it will copy over the data in the cluster into your new series.

To see if it worked, you can enter postgres and check for new tables and functions: {{{psql myjsoc_db -U slony \dt _mdi_mag. (should return two tables, sl_archive_tracking and sl_sequence_offline)

select count(*) from mdi.fd_m_96m_lev18; should return over 65,000 records. }}}

Make sure you have the get_slony_logs.pl file in your DRMS code scripts directory. If not, you may need to update your code. 

#### CONFIGURATION variables
$Net::SCP::scp="/usr/bin/scp";                           ## path to fast scp instance (HPN-SCP).
my $rmt_hostname="solarport.stanford.edu";               ## remote machine name
my $rmt_port="55000";                                    ## port to fast scp server in remote machine
my $user="igor";                                         ## user login in remote machine
my $rmt_slony_dir="/scr21/jennifer/slony_logs";          ## directory in remote machine where slony logs get stage
my $slony_logs="/home/slony/logs";                       ## local slony cluster directory area
my $PSQL="/usr/bin/psql -Uslony (-h myhostserver if needed) myjsoc_db";               ## psql path
my $email_list='igor@igorhost.edu';                          ## list of people to email in case some problem occurs.
#### END of CONFIGURATION variables
###########################################################################

designate a directory where your acquired Slony logs will be stored, with a cluster subdirectory, e.g. /home/slony/slony_logs/mdi_mag (where mdi_mag is the cluster). These logs are not huge files, but there will be a lot of them over time. Slony generates logs every two minutes at least; a log file that documents no change in the system is typically ~700 bytes. But even that reported every two minutes for weeks will add up, so choose a good spot for these logs and keep an eye on the growth. Another script, slony_logs_housekeeping.pl, is available to tar.gz your log files in a given directory, one tar.gz file per 5000 logs (and the file interval is settable). You may wish to cron the housekeeping script to ensure the logs aren't eating up space too quickly, and delete or archive eventually.

This script assumes you've sourced your .ssh-agent file and will not work properly without doing that first. Typical errors include 'permission denied' or request of a passphrase or password.

The first time you run get_slony_logs.pl, it will create a file called slony_counter.txt (e.g. /home/slony/logs/mdi_mag/slony_counter.txt ). The file contains a number, and that number is the current slony counter for your data set. If you want to double-check the value of the counter.txt file, you can run: 

psql -Uslony myjsoc_db -c 'select at_counter from _mdi_mag.sl_archive_tracking;'
This should return a number, e.g. 872 or 100043.  This number is the current log number for this cluster, and should be the number in slony_counter.txt.

We do not recommend editing the slony_counter.txt file. The get_slony_logs.pl script will manage the value in the counter file, and if there is an error, it will double-check with the database for the correct number.

Edit the crontab and add 

*/1 * * * * (. ~/.ssh-agent_rs;/home/slony/Scripts/get_slony_logs.pl mdi_mag >> /home/slony/Scripts/get_slony_logs.mdi_mag.log 2>&1 )

Please enter any additional tips and help to other users here in this wiki.