Archive

See also: Postgresql | Shell

Archive Script:

postgres@kb0odu:~/bin$ cat archive_sync.ksh 
#!/usr/bin/ksh

WAL_PATH=$1  # %p
WAL_FILE=$2  # %f
ARCHIVE_DIR="/var/lib/postgresql/archived_logs"

if [  ! -d "${ARCHIVE_DIR}" ] ; then
        echo $0 " Archive directory ${ARCHIVE_DIR} does not exist" >&2
        exit 1
fi

echo "ARCHIVE_DIR is: $ARCHIVE_DIR" >> /tmp/postgres_archive.txt
echo "WAL_PATH (%p / Param 1) is: $WAL_PATH" >> /tmp/postgres_archive.txt
echo "WAL_FILE (%f / Param 2) is: $WAL_FILE" >> /tmp/postgres_archive.txt
echo "Command is 'rsync -a {WAL_PATH} {ARCHIVE_DIR}'" >> /tmp/postgres_archive.txt
rsync -a  ${WAL_PATH} ${ARCHIVE_DIR}
#echo "Command is 'rsync -av {WAL_PATH} {ARCHIVE_DIR}'" >> /tmp/postgres_archive.txt
#rsync -av ${WAL_PATH} ${ARCHIVE_DIR}
RETVAL=$?
echo "RETVAL is: $REVAL"

if [ $RETVAL -ne 0 ] ; then
        echo $0 "Local archive rsync failed for $WAL_FILE - Error $RETVAL" >&2
        exit 1
fi
exit 0

Script:

#! /usr/bin/python

import datetime
import psycopg2
gbol_debug = True

pg = psycopg2.connect("dbname='assessment' user='timothy' host='localhost' password='tim'")
run_time = datetime.datetime.now().strftime ( "%b %d, %Y %H:%M:%S")
print "Staring now:", run_time

for i in range(100):
    lstr_sql = "insert into d values ( " + str(i) + " );"
    print ( lstr_sql  );
    cur = pg.cursor()
    cur.execute( lstr_sql )
    pg.commit()

run_time = datetime.datetime.now().strftime ( "%b %d, %Y %H:%M:%S")
print "Ending now:", run_time

Log File:

timothy@kb0odu:~$ sudo cat /tmp/postgres_archive.txt 
ARCHIVE_DIR is: /var/lib/postgresql/archived_logs
WAL_PATH (%p / Param 1) is: pg_xlog/00000001000000000000000E
WAL_FILE (%f / Param 2) is: 00000001000000000000000E
Command is 'rsync -a {WAL_PATH} {ARCHIVE_DIR}'
ARCHIVE_DIR is: /var/lib/postgresql/archived_logs
WAL_PATH (%p / Param 1) is: pg_xlog/00000001000000000000000F
WAL_FILE (%f / Param 2) is: 00000001000000000000000F
Command is 'rsync -a {WAL_PATH} {ARCHIVE_DIR}'

SQL:

# select count(*) from d;
 count
-------
   506
(1 row)

timothy@[local]) 12:25:49 [assessment]
# select * from pg_switch_xlog();
 pg_switch_xlog
----------------
 0/F011EB8
(1 row)

Archive.conf test config

# -----------------------------
# Archive configuration file by Tim
# -----------------------------
#

#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

wal_level = archive                     # minimal, archive, or hot_standby
#wal_level = minimal                    # minimal, archive, or hot_standby
                                        # (change requires restart)
# - Checkpoints -

checkpoint_segments = 10                # in logfile segments, min 1, 16MB each
#checkpoint_segments = 3                # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1min               # range 30s-1h
#checkpoint_timeout = 5min              # range 30s-1h
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0
checkpoint_warning = 30s                # 0 disables
#checkpoint_warning = 30s               # 0 disables

# - Archiving -

archive_mode = on               # allows archiving to be done
#archive_mode = off             # allows archiving to be done
                                # (change requires restart)
#archive_command = '/var/lib/postgresql/bin/archive_sync.ksh >> /tmp/postgres_archive.txt'
archive_command = '/var/lib/postgresql/bin/archive_sync.ksh %p %f'
#archive_command = ''           # command to use to archive a logfile segment
                                # placeholders: %p = path of file to archive
                                #               %f = file name only
                                # e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0            # force a logfile segment switch after this
                                # number of seconds; 0 disables