PostgreSQL

Algemeen

Een database backuppen:

/usr/local/psa/postgresql/bin/pg_dump -cDx databasename > /home/dump.sql

Een database restoren:

/usr/local/psa/postgresql/psql -f backupfile databasename username

Het probleem is alleen dat je voor het dumpen een database de PGUSER en PGPASSWORD als environment variabelen moet opgeven. Ik weet hoe je dit doet in bash, maar niet in ssh:

export PGUSER=postgresql
export PGPASSWORD=bQ...

Dus maak ik zelf altijd een bash bestandje aan met de volgende regel aan het begin:

#!/usr/local/bin/bash

En dan de export PGUSER/PGPASSWORD en dan de dump/restore regel(s) die je wilt uitvoeren.

Na instalatie

Het wachtwoord aanpassen van een gebruiker.

ALTER USER davide WITH PASSWORD 'bQ';

SEC (Simple Event Correlator)

SEC (Simple Event Correlator) is an open source and platform independent event correlation tool that was designed to fill the gap between commercial event correlation systems and homegrown solutions that usually comprise a few simple shell scripts. SEC accepts input from regular files, named pipes, and standard input, and can thus be employed as an event correlator for any application that is able to write its output events to a file stream. The SEC configuration is stored in text files as rules, each rule specifying an event matching condition, an action list, and optionally a Boolean expression whose truth value decides whether the rule can be applied at a given moment. Regular expressions, Perl subroutines, etc. are used for defining event matching conditions. SEC can produce output events by executing user-specified shell scripts or programs (e.g., snmptrap or mail), by writing messages to pipes or files, and by various other means.

Installatie

# cd /usr/ports/sysutils/sec
# make install clean

Configureren van SEC

# ee /etc/sec.conf
# SEC - Simple Event Correlator Configuration File
#
# Description:
# SEC is being used for real-time notification of
# web server failure events. When HAProxy recognizes
# a server failure it writes information about the
# failure to its log file. SEC monitors the log file
# looking for certain events and sends notification
# e-mails to administrators
#
# match on a line like this:
# Server http_proxy/www0 is DOWN. 0 active and 2 backup servers left. 
# Running on backup. 0 sessions active, 0 requeued, 0 remaining in queue.
# Take the name of the instance (http_proxy/www0) and put it in $1
# Take the server status (DOWN or UP) and put it in $2
# Take the rest of the line and put it in $3
#
type=Single
ptype=RegExp
pattern=Server\s+(\S+)\s+\S+\s+(\S+)(.*)
desc=$0
action=pipe '%t server $1 went $2 $3' /usr/bin/mail -s 'HAProxy: $1 went $2' systeembeheer@markterweele.nl

De commando hieronder kun je gebruiken om de sec file in te lezen:

# sec -detach -pid=/var/run/sec.pid -conf=/etc/sec.conf -log=/var/log/sec.log -input=/var/log/messages

Met dit onderstaande script wordt

# ee /usr/bin/swatch2sec.pl
#!/usr/bin/perl -w
# swatch2sec (contributed by Johan Nilsson).
# Convert a swatch configuration file to a simple event correlator conf.
# Far from complete but might save time on long and simple swatch files.
#
# usage: swatch2sec < ~/.swatchrc > newconf.sec
#
use strict;

while ( my $line = <> ) {
    chomp $line;

    # keep comments and whitespace
    if ( $line =~ /(^#|^\s*$)/ ) {
        print "$line\n";
        next;
    }

    # log line
    if ( $line =~ /\s*watchfor\s*\/(.*)\/$/ ) {
        print "type=single
ptype=regexp
pattern=(.*$1.*)
desc=Log line
action=write - \$1\n\n";
        next; # should the pattern ($1) be enough?
    }

    # suppress line
    if ( $line =~ /\s*ignore\s*\/(.*)\/$/ ) {
        print "type=Suppress\nptype=RegExp\npattern=$1\n\n";
        next;
    }

    # echo color not supported by sec, try ccze instead
    if ( $line =~/\s*echo\s*\w/ ){
        next;
    }

    # not implemented
    print "## untranslated line from swatch: $line\n";
}

Opstart script maken:

# ee /etc/rc.d/rc.sec
#!/bin/bash

# description: Simple Event Correlator - SEC
#
# processname: /usr/local/sbin/sec.pl
# config: /etc/sec.conf

PATH=/sbin:/usr/sbin:/bin:/usr/bin

OPTIONS="-detach -conf=/etc/sec.conf -log=/var/log/sec.log -pid=/var/run/secd.pid"
OPTIONS2="-input=/var/log/messages"
RETVAL=0
prog="sec"

start() {
    echo -n "Starting $prog: "
        /usr/local/bin/sec $OPTIONS $OPTIONS2
    RETVAL=$?
    echo
    touch /usr/bin/swatch2sec.pl

    return $RETVAL
}

stop() {
    echo -n "Stopping $prog: "
    kill -9 `cat /var/run/secd.pid`
    RETVAL=$?
    echo
    rm -f var/run/secd.pid
    return $RETVAL
}

reload(){
    stop
    start
}

restart(){
    stop
    start
}


case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  restart)
    restart
        ;;
  reload)
    reload
        ;;
  *)
    echo "Usage: rc.$prog {start|stop|status|restart|reload}"
    RETVAL=1
esac

exit $RETVAL
# chmod 755 /etc/rc.d/rc.sec

Kopiëren van een logrotatie config file:

# cp /usr/ports/sysutils/logrotate/files/logrotate.conf.sample /etc/logrotate.conf
# ee /etc/logrotate.conf

Pas de logrotate file aan.

# rotate log files weekly
#weekly

# rotate log files daily
daily

# keep 4 weeks worth of backlogs
#rotate 4

# keep 7 days worth of backlogs
rotate 7

# create new (empty) log files after rotating old ones
create


/var/log/sec.log {
    missingok
    postrotate
        /etc/rc.d/rc.sec restart
    endscript
}

Starten

Handmatig sec starten:

# sh /etc/rc.d/rc.sec start

Om het script sec.conf automatisch te laten opstarten als FreeBSD opstart:
Hiervoor moet je inloggen als root:

# ee /etc/rc.local
sh /etc/rc.d/rc.sec start

Links

Newsyslog

Om automatische logfiles te roteren, edit dan het bestand

/etc/newsyslog

/var/log/httpd_access_log             644  7     *  24    B     /var/run/httpd.pid 30
/var/log/httpd_error_log             644  7     *  24    B     /var/run/httpd.pid 30

SCP

Met SCP kan je bestanden kopiëren tussen verschillende machines via SSH.

Syntax

scp user@host:file TARGET

Een voorbeeld

scp user@hostname.com:/usr/ports/distfiles/apache1.3.tar.gz .