diff options
Diffstat (limited to '')
13 files changed, 1201 insertions, 0 deletions
diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql new file mode 100755 index 0000000000..8a60068ab7 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql @@ -0,0 +1,39 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Run SQL command +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +pgsql_prefix=`cat $here/pgsql.prefix` + +if [ "$2" = "" ]; then + host="localhost" + port="6432" + cmd="$1" +else + host="$1" + port="$2" + cmd="$3" +fi + +if [ "$cmd" = "" ]; then + $pgsql_prefix/bin/psql -h $host -p $port db +else + $pgsql_prefix/bin/psql -h $host -p $port -c "$cmd" db +fi + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-archive b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-archive new file mode 100755 index 0000000000..128e6eb539 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-archive @@ -0,0 +1,59 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# PostgreSQL archive command +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +host=$2 +port=$3 +walp=$4 +walf=$5 + +# Copy WAL to archive directory +if [ ! -f $root/sqldb/archive/$walf ]; then + cp $walp $root/sqldb/archive/$walf + rc=$? + if [ "$rc" != "0" ]; then + exit $rc + fi +fi + +# Trigger a backup if we have 10 WAL files archived since the last backup +lastbak=`ls $root/sqldb/archive | sort -r | grep "\.backup$" | head -1` +if [ "$lastbak" = "" ]; then + exit 0 +fi +wals=`ls $root/sqldb/archive | sort -r | grep -v "\.backup\.tar\.gz$"` + +w=0 +for f in $wals; do + if [ "$f" = "$lastbak" ]; then + break + fi + w=$((w+1)) + + if [ "$w" = "10" ]; then + nohup /bin/sh -c "$here/pgsql-backup $root $host $port" 1>/dev/null 2>/dev/null & + break + fi +done + +exit 0 + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-backup b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-backup new file mode 100755 index 0000000000..ff660ad20d --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-backup @@ -0,0 +1,79 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Backup postgresql data directory +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +if [ "$2" = "" ]; then + host="localhost" + port="6432" +else + host="$2" + port="$3" +fi + +pgsql_prefix=`cat $here/pgsql.prefix` + +if [ -f "$root/sqldb/log.conf" ]; then + pgsql_log=`cat $root/sqldb/log.conf` +else + mkdir -p $root/logs + pgsql_log="cat >>$root/logs/postgresql" +fi +mkdir -p $root/sqldb +echo $pgsql_log >$root/sqldb/logger + +mkdir -p $root/sqldb/backup +mkdir -p $root/sqldb/archive + +# Make sure that only one backup is in progress at a time +if [ -f $root/sqldb/backup/inprogress ]; then + exit 0 +fi +touch $root/sqldb/backup/inprogress + +# Backup +stamp=`date +%Y%m%d%H%M%S` +$pgsql_prefix/bin/psql -h $host -p $port -c "SELECT pg_start_backup('$stamp', true)" db 2>&1 | sh $root/sqldb/logger + +uname=`uname -s` +if [ $uname = "Darwin" ]; then + tar=gnutar +else + tar=tar +fi +$tar -C $root/sqldb --exclude data/postmaster.pid --exclude data/postmaster.opts --exclude data/pg_xlog --ignore-failed-read -czf $root/sqldb/backup/$stamp.backup.tar.gz data +rc=$? +if [ "$rc" = "0" ]; then + mv $root/sqldb/backup/$stamp.backup.tar.gz $root/sqldb/archive/$stamp.backup.tar.gz +fi + +$pgsql_prefix/bin/psql -h $host -p $port -c "SELECT pg_stop_backup()" db 2>&1 | sh $root/sqldb/logger + +if [ "$rc" != "0" ]; then + rm -f $root/sqldb/backup/inprogress + exit $rc +fi + +# Clean obsolete backup and WAL files +$here/pgsql-clean-archive $root + +rm -f $root/sqldb/backup/inprogress + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-clean-archive b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-clean-archive new file mode 100755 index 0000000000..0e52fade89 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-clean-archive @@ -0,0 +1,58 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Cleanup database archive. +# Keep the last two backups and corresponding WAL files. + +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +baks=`ls $root/sqldb/archive | sort -r | grep "\.backup$" | head -2` +wals=`ls $root/sqldb/archive | sort -r | grep -v "\.backup\.tar\.gz$"` +tars=`ls $root/sqldb/archive | sort -r | grep "\.backup\.tar\.gz$"` + +for f in $baks; do + if [ "$lastbak" = "" ]; then + lastbak=$f + else + prevbak=$f + fi +done + +w=0 +for f in $wals; do + if [ "$w" = "2" ]; then + rm $root/sqldb/archive/$f + fi + if [ "$w" = "1" ]; then + w=2 + fi + if [ "$f" = "$prevbak" ]; then + w=1 + fi +done + +t=0 +for f in $tars; do + if [ "$t" = "2" ]; then + rm $root/sqldb/archive/$f + fi + t=$((t+1)) +done + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-conf b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-conf new file mode 100755 index 0000000000..8adbb902c9 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-conf @@ -0,0 +1,194 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Configure a postgresql master server +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +mkdir -p $1 +root=`echo "import os; print os.path.realpath('$1')" | python` + +addr=$2 +if [ "$addr" = "" ]; then + host="localhost" + listen="*" + port="5432" +else + host=`$here/../../modules/http/httpd-addr ip $addr` + if [ "$host" = "" ]; then + host="localhost" + listen="*" + else + listen=$host + fi + port=`$here/../../modules/http/httpd-addr port $addr` +fi +bport=`expr $port + 1000` + +pgsql_prefix=`cat $here/pgsql.prefix` +pgbouncer_prefix=`cat $here/pgbouncer.prefix` + +user=`id -un` + +mkdir -p $root/sqldb/data +chmod 700 $root/sqldb/data +mkdir -p $root/sqldb/archive +mkdir -p $root/sqldb/backup + +if [ -f "$root/sqldb/log.conf" ]; then + pgsql_log=`cat $root/sqldb/log.conf` +else + mkdir -p $root/logs + pgsql_log="cat >>$root/logs/postgresql" +fi +mkdir -p $root/sqldb +mkdir -p $root/sqldb/tmp +echo $pgsql_log >$root/sqldb/logger + +# Initialize PostgreSQL +if [ ! -f $root/sqldb/data/postgresql.conf ]; then + $pgsql_prefix/bin/pg_ctl init -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger + cp $root/sqldb/data/postgresql.conf $root/sqldb/data/postgresql-init.conf + cp $root/sqldb/data/pg_hba.conf $root/sqldb/data/pg_hba-init.conf +fi + +# Generate server configuration +cp $root/sqldb/data/postgresql-init.conf $root/sqldb/data/postgresql.conf +cat >>$root/sqldb/data/postgresql.conf <<EOF + +# Generated by: pgsql-conf $* + +# Setup logging +log_min_messages = NOTICE +log_min_error_statement = NOTICE +log_min_duration_statement = -1 +log_checkpoints = on +log_connections = off +log_disconnections = off +log_duration = off +log_lock_waits = on +log_statement = none + +# Listen +listen_addresses = '$listen' +port = $port +unix_socket_directory = '$root/sqldb/tmp' + +# Setup archival +archive_mode = on +archive_command = '$here/pgsql-archive $root $host $bport %p %f' + +# Setup hot standby with streaming replication +wal_level = hot_standby +max_wal_senders = 5 +wal_keep_segments = 32 + +EOF + +# Generate client auth configuration +cp $root/sqldb/data/pg_hba-init.conf $root/sqldb/data/pg_hba.conf +cat >>$root/sqldb/data/pg_hba.conf <<EOF + +# Generated by: pgsql-conf $* +# TYPE DATABASE USER CIDR-ADDRESS METHOD +host all all samenet trust +host replication all samenet trust + +EOF + +# Create the db if it's not created yet +nohup /bin/sh -c "($pgsql_prefix/bin/pg_ctl start -W -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger)" 1>/dev/null 2>/dev/null & +sti=0 +while [ $sti -ne 30 ]; do + st=`$pgsql_prefix/bin/pg_ctl status -D $root/sqldb/data | grep 'server is running'` + if [ "$st" != "" ]; then + break + fi + sleep 1 + sti=$((sti+1)) +done + +$pgsql_prefix/bin/createdb -h $host -p $port db 2>&1 | sh $root/sqldb/logger + +# Create default user roles +$pgsql_prefix/bin/psql -h $host -p $port -c "create role standby with login replication" db 2>&1 | sh $root/sqldb/logger +$pgsql_prefix/bin/psql -h $host -p $port -c "create role bouncer with login" db 2>&1 | sh $root/sqldb/logger + +# Backup the db if there's no backup for it yet +baks=`ls $root/sqldb/archive | sort -r | grep "\.backup\.tar\.gz$"` +if [ "$baks" = "" ]; then + $here/pgsql-backup $root $host $port +fi + +$pgsql_prefix/bin/pg_ctl stop -w -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger + +# Generate database restore script +mkdir -p $root/sqldb/scripts +cat >$root/sqldb/scripts/restore <<EOF +#!/bin/sh +$here/pgsql-restore $root +EOF +chmod 700 $root/sqldb/scripts/restore + +# Configure HTTPD to serve backup and archive files +if [ -f "$root/conf/httpd.conf" ]; then + cat >>$root/conf/httpd.conf <<EOF +# Generated by: pgsql-conf $* + +# Serve PostgreSQL backup and WAL archive files +ScriptAlias /pgsql-restore "$root/sqldb/scripts/restore" +Alias /pgsql-archive "$root/sqldb/archive" + +EOF + +fi + +# Configure PgBouncer +mkdir -p $root/logs +id=`id -un` +cat >$root/sqldb/data/pgbouncer.conf <<EOF + +[databases] +db = host=$host port=$port dbname=db user=bouncer + +[pgbouncer] +pool_mode = session +listen_addr = $listen +listen_port = $bport +unix_socket_dir = +auth_type = trust +auth_file=$root/sqldb/data/pgbouncer-auth.conf +logfile = $root/logs/pgbouncer +pidfile = $root/logs/pgbouncer.pid +max_client_conn = 1000 +pool_mode = transaction +server_reset_query = +default_pool_size = 500 +min_pool_size = 5 +reserve_pool_size = 50 +log_connections = 0 +log_disconnections = 0 +stats_period = 3600 +admin_users = $id + +EOF + +cat >$root/sqldb/data/pgbouncer-auth.conf <<EOF +"$id" "password" + +EOF + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-log-conf b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-log-conf new file mode 100755 index 0000000000..c68b4b718d --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-log-conf @@ -0,0 +1,37 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Configure postgresql logging +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +mkdir -p $1 +root=`echo "import os; print os.path.realpath('$1')" | python` + +mkdir -p $root/sqldb +if [ "$2" = "" ]; then + cat >$root/sqldb/log.conf << EOF +cat >>$root/logs/postgresql +EOF + +else + cat >$root/sqldb/log.conf << EOF +$2 +EOF + +fi + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-restore b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-restore new file mode 100755 index 0000000000..e91eba2a6b --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-restore @@ -0,0 +1,30 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Backup postgresql data directory +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +tar=`ls $root/sqldb/archive | sort -r | grep "\.backup\.tar\.gz$" | head -1` + +echo "Content-type: application/x-compressed" +echo "" + +cat $root/sqldb/archive/$tar + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-conf b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-conf new file mode 100755 index 0000000000..5f76b5b332 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-conf @@ -0,0 +1,193 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Configure a postgresql hot standby server +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +mkdir -p $1 +root=`echo "import os; print os.path.realpath('$1')" | python` + +# Server address +addr=$2 +if [ "$addr" = "" ]; then + host="localhost" + listen="*" + port="5432" +else + host=`$here/../../modules/http/httpd-addr ip $addr` + if [ "$host" = "" ]; then + host="localhost" + listen="*" + else + listen=$host + fi + port=`$here/../../modules/http/httpd-addr port $addr` +fi +bport=`expr $port + 1000` + +# Master server address +if [ "$3" = "" ]; then + mhost="localhost" + mport="5432" + mhttpport="80" +else + mhost="$3" + mport="$4" + mhttpport="$5" +fi + +pgsql_prefix=`cat $here/pgsql.prefix` +pgbouncer_prefix=`cat $here/pgbouncer.prefix` + +user=`id -un` + +mkdir -p $root/sqldb/data +chmod 700 $root/sqldb/data +mkdir -p $root/sqldb/archive +mkdir -p $root/sqldb/backup + +if [ -f "$root/sqldb/log.conf" ]; then + pgsql_log=`cat $root/sqldb/log.conf` +else + mkdir -p $root/logs + pgsql_log="cat >>$root/logs/postgresql" +fi +mkdir -p $root/sqldb +mkdir -p $root/sqldb/tmp +echo $pgsql_log >$root/sqldb/logger + +# Initialize from a backup of the master +if [ ! -f $root/sqldb/data/postgresql.conf ]; then + uname=`uname -s` + if [ $uname = "Darwin" ]; then + tar=gnutar + else + tar=tar + fi + (curl -L -# http://$mhost:$mhttpport/pgsql-restore | $tar -C $root/sqldb -xz) 2>&1 | grep -v "100.0%" | sh $root/sqldb/logger + mkdir -p $root/sqldb/data/pg_xlog/archive_status + chmod 700 $root/sqldb/data/pg_xlog/archive_status +fi + +# Generate server configuration +cp $root/sqldb/data/postgresql-init.conf $root/sqldb/data/postgresql.conf +cat >>$root/sqldb/data/postgresql.conf <<EOF + +# Generated by: pgsql-standby-conf $* + +# Setup logging +log_min_messages = NOTICE +log_min_error_statement = NOTICE +log_min_duration_statement = -1 +log_checkpoints = on +log_connections = off +log_disconnections = off +log_duration = off +log_lock_waits = on +log_statement = none + +# Listen +listen_addresses = '$listen' +port = $port +unix_socket_directory = '$root/sqldb/tmp' + +# Setup archival +archive_mode = on +archive_command = '$here/pgsql-archive $root $host $bport %p %f' + +# Setup hot standby with streaming replication +wal_level = hot_standby +max_wal_senders = 5 +wal_keep_segments = 32 + +# Enable hot standby +hot_standby = on + +EOF + +# Generate recovery configuration +cat >$root/sqldb/data/recovery.conf << EOF +# Generated by: pgsql-standby-conf $* + +# Start in standby mode +standby_mode = 'on' +primary_conninfo = 'host=$mhost port=$mport user=standby' + +# Failover +trigger_file = '$root/sqldb/failover' + +restore_command = 'curl -L -# http://$mhost:$mhttpport/pgsql-archive/%f -o "%p" 2>&1 | grep -v "100.0%"' + +EOF + +# Generate database restore script +mkdir -p $root/sqldb/scripts +cat >$root/sqldb/scripts/restore <<EOF +#!/bin/sh +$here/pgsql-restore $root +EOF +chmod 700 $root/sqldb/scripts/restore + +# Configure HTTPD to serve backup and archive files +if [ -f "$root/conf/httpd.conf" ]; then + cat >>$root/conf/httpd.conf <<EOF +# Generated by: pgsql-standby-conf $* + +# Serve PostgreSQL backup and WAL archive files +ScriptAlias /pgsql-restore "$root/sqldb/scripts/restore" +Alias /pgsql-archive "$root/sqldb/archive" + +EOF + +fi + +# Configure PgBouncer +mkdir -p $root/logs +id=`id -un` +cat >$root/sqldb/data/pgbouncer.conf <<EOF + +[databases] +db = host=$host port=$port dbname=db user=bouncer + +[pgbouncer] +pool_mode = session +listen_addr = $listen +listen_port = $bport +unix_socket_dir = +auth_type = trust +auth_file=$root/sqldb/data/pgbouncer-auth.conf +logfile = $root/logs/pgbouncer +pidfile = $root/logs/pgbouncer.pid +max_client_conn = 1000 +pool_mode = transaction +server_reset_query = +default_pool_size = 500 +min_pool_size = 5 +reserve_pool_size = 50 +log_connections = 0 +log_disconnections = 0 +stats_period = 3600 +admin_users = $id + +EOF + +cat >$root/sqldb/data/pgbouncer-auth.conf <<EOF +"$id" "password" + +EOF + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-test.cpp b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-test.cpp new file mode 100644 index 0000000000..2cd25f874a --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-standby-test.cpp @@ -0,0 +1,88 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* $Rev$ $Date$ */ + +/** + * Test PostgreSQL hot standby support. + */ + +#include <assert.h> +#include "stream.hpp" +#include "string.hpp" +#include "perf.hpp" +#include "pgsql.hpp" + +namespace tuscany { +namespace pgsql { + +bool testPGSql() { + PGSql wpg("host=localhost port=6432 dbname=db", "test"); + PGSql rpg("host=localhost port=6433 dbname=db", "test"); + const value k = mklist<value>("a"); + + assert(hasContent(post(k, string("AAA"), wpg))); + sleep(1); + assert((get(k, rpg)) == value(string("AAA"))); + assert(hasContent(put(k, string("aaa"), wpg))); + sleep(1); + assert((get(k, rpg)) == value(string("aaa"))); + assert(hasContent(del(k, wpg))); + sleep(1); + assert(!hasContent(get(k, rpg))); + + return true; +} + +struct getLoop { + const value k; + PGSql& pg; + getLoop(const value& k, PGSql& pg) : k(k), pg(pg) { + } + const bool operator()() const { + assert((get(k, pg)) == value(string("CCC"))); + return true; + } +}; + +bool testGetPerf() { + const value k = mklist<value>("c"); + PGSql wpg("host=localhost port=6432 dbname=db", "test"); + PGSql rpg("host=localhost port=6433 dbname=db", "test"); + assert(hasContent(post(k, string("CCC"), wpg))); + sleep(1); + + const lambda<bool()> gl = getLoop(k, rpg); + cout << "PGSql get test " << time(gl, 5, 200) << " ms" << endl; + return true; +} + +} +} + +int main() { + tuscany::cout << "Testing..." << tuscany::endl; + + tuscany::pgsql::testPGSql(); + tuscany::pgsql::testGetPerf(); + + tuscany::cout << "OK" << tuscany::endl; + + return 0; +} diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-start b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-start new file mode 100755 index 0000000000..6b388d29f0 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-start @@ -0,0 +1,50 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Start postgresql +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +pgsql_prefix=`cat $here/pgsql.prefix` +pgbouncer_prefix=`cat $here/pgbouncer.prefix` + +if [ -f "$root/sqldb/log.conf" ]; then + pgsql_log=`cat $root/sqldb/log.conf` +else + mkdir -p $root/logs + pgsql_log="cat >>$root/logs/postgresql" +fi +mkdir -p $root/sqldb +echo $pgsql_log >$root/sqldb/logger + +nohup /bin/sh -c "($pgsql_prefix/bin/pg_ctl start -W -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger)" 1>/dev/null 2>/dev/null & +sti=0 +while [ $sti -ne 30 ]; do + st=`$pgsql_prefix/bin/pg_ctl status -D $root/sqldb/data | grep 'server is running'` + if [ "$st" != "" ]; then + break + fi + sleep 1 + sti=$((sti+1)) +done + +# Start PgBouncer +mkdir -p $root/logs +$pgbouncer_prefix/bin/pgbouncer -q -d $root/sqldb/data/pgbouncer.conf + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-stop b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-stop new file mode 100755 index 0000000000..16b6506838 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-stop @@ -0,0 +1,43 @@ +#!/bin/sh + +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. + +# Stop postgresql +here=`echo "import os; print os.path.realpath('$0')" | python`; here=`dirname $here` +root=`echo "import os; print os.path.realpath('$1')" | python` + +pgsql_prefix=`cat $here/pgsql.prefix` +pgbouncer_prefix=`cat $here/pgbouncer.prefix` + +if [ -f "$root/sqldb/log.conf" ]; then + pgsql_log=`cat $root/sqldb/log.conf` +else + mkdir -p $root/logs + pgsql_log="cat >>$root/logs/postgresql" +fi +mkdir -p $root/sqldb +echo $pgsql_log >$root/sqldb/logger + +pgb="$pgbouncer_prefix/bin/pgbouncer -q -d $root/sqldb/data/pgbouncer.conf" +k=`ps -ef | grep -v grep | grep "${pgb}" | awk '{ print $2 }'` +if [ "$k" != "" ]; then + kill $k +fi + +$pgsql_prefix/bin/pg_ctl stop -w -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger + diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-test.cpp b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-test.cpp new file mode 100644 index 0000000000..d10ab5f4c2 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql-test.cpp @@ -0,0 +1,82 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* $Rev$ $Date$ */ + +/** + * Test PostgreSQL access functions. + */ + +#include <assert.h> +#include "stream.hpp" +#include "string.hpp" +#include "perf.hpp" +#include "pgsql.hpp" + +namespace tuscany { +namespace pgsql { + +bool testPGSql() { + PGSql pg("host=localhost port=6432 dbname=db", "test"); + const value k = mklist<value>("a"); + + assert(hasContent(post(k, string("AAA"), pg))); + assert((get(k, pg)) == value(string("AAA"))); + assert(hasContent(put(k, string("aaa"), pg))); + assert((get(k, pg)) == value(string("aaa"))); + assert(hasContent(del(k, pg))); + assert(!hasContent(get(k, pg))); + + return true; +} + +struct getLoop { + const value k; + PGSql& pg; + getLoop(const value& k, PGSql& pg) : k(k), pg(pg) { + } + const bool operator()() const { + assert((get(k, pg)) == value(string("CCC"))); + return true; + } +}; + +bool testGetPerf() { + const value k = mklist<value>("c"); + PGSql pg("host=localhost port=6432 dbname=db", "test"); + assert(hasContent(post(k, string("CCC"), pg))); + + const lambda<bool()> gl = getLoop(k, pg); + cout << "PGSql get test " << time(gl, 5, 200) << " ms" << endl; + return true; +} + +} +} + +int main() { + tuscany::cout << "Testing..." << tuscany::endl; + + tuscany::pgsql::testPGSql(); + tuscany::pgsql::testGetPerf(); + + tuscany::cout << "OK" << tuscany::endl; + + return 0; +} diff --git a/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql.hpp b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql.hpp new file mode 100644 index 0000000000..581cd943e6 --- /dev/null +++ b/sca-cpp/branches/lightweight-sca/components/sqldb/pgsql.hpp @@ -0,0 +1,249 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +/* $Rev$ $Date$ */ + +#ifndef tuscany_pgsql_hpp +#define tuscany_pgsql_hpp + +/** + * PostgreSQL access functions. + */ + +#include <libpq-fe.h> + +#include "string.hpp" +#include "list.hpp" +#include "value.hpp" +#include "monad.hpp" +#include "../../modules/scheme/eval.hpp" + +namespace tuscany { +namespace pgsql { + +/** + * Return and clear a Postgres result failure. + */ +const string pgfailure(PGresult* r, PGconn* conn) { + const string re = PQresultErrorMessage(r); + PQclear(r); + if (length(re) != 0) + return re; + const string ce = PQerrorMessage(conn); + return ce; +} + +/** + * Represents a PGSql connection. + */ +class PGSql { +public: + PGSql() : owner(false) { + debug("pgsql::pgsql"); + } + + PGSql(const string& conninfo, const string& table) : owner(true), conn(NULL), conninfo(conninfo), table(table) { + debug(conninfo, "pgsql::pgsql::conninfo"); + debug(table, "pgsql::pgsql::table"); + + // Connect to the database + conn = PQconnectdb(c_str(conninfo)); + if (PQstatus(conn) != CONNECTION_OK) { + mkfailure<bool>(string("Couldn't connect to postgresql database: ") + PQerrorMessage(conn)); + return; + } + + // Find the name of the first column in the target table + // Assume that's the key we need to use + string ks = string("select a.attname from pg_attribute a, pg_class c where a.attrelid = c.relfilenode and c.relname = '") + table + string("' and a.attnum in (1, 2) order by a.attnum;"); + PGresult* kr = PQexec(conn, c_str(ks)); + if (PQresultStatus(kr) != PGRES_TUPLES_OK) { + mkfailure<bool>(string("Couldn't execute postgresql column select statement: ") + pgfailure(kr, conn)); + return; + } + if (PQntuples(kr) != 2) { + PQclear(kr); + mkfailure<bool>(string("Couldn't find postgresql table key and value column names")); + return; + } + kname = PQgetvalue(kr, 0, 0); + vname = PQgetvalue(kr, 1, 0); + PQclear(kr); + } + + PGSql(const PGSql& c) : owner(false), conn(c.conn), conninfo(c.conninfo), table(c.table) { + debug("pgsql::pgsql::copy"); + } + + const PGSql& operator=(const PGSql& c) { + debug("pgsql::pgsql::operator="); + if(this == &c) + return *this; + owner = false; + conn = c.conn; + conninfo = c.conninfo; + table = c.table; + return *this; + } + + ~PGSql() { + debug("pgsql::~pgsql"); + if (!owner) + return; + if (conn == NULL) + return; + PQfinish(conn); + } + +private: + bool owner; + PGconn *conn; + string conninfo; + string table; + string kname; + string vname; + + friend const failable<bool> setup(const PGSql& pgsql); + friend const failable<bool> post(const value& key, const value& val, const PGSql& pgsql); + friend const failable<bool> put(const value& key, const value& val, const PGSql& pgsql); + friend const failable<value> get(const value& key, const PGSql& pgsql); + friend const failable<bool> del(const value& key, const PGSql& pgsql); +}; + +/** + * Setup the database connection if necessary. + */ +const failable<bool> setup(const PGSql& pgsql) { + debug("pgsql::setup"); + if (PQstatus(pgsql.conn) == CONNECTION_OK) + return true; + debug("pgsql::setup::reset"); + PQreset(pgsql.conn); + if (PQstatus(pgsql.conn) != CONNECTION_OK) + return mkfailure<bool>(string("Couldn't reconnect to postgresql database: ") + PQerrorMessage(pgsql.conn)); + return true; +} + +/** + * Post a new item to the database. + */ +const failable<bool> post(const value& key, const value& val, const PGSql& pgsql) { + debug(key, "pgsql::post::key"); + debug(val, "pgsql::post::value"); + debug(pgsql.conninfo, "pgsql::post::conninfo"); + debug(pgsql.table, "pgsql::post::table"); + setup(pgsql); + + const string ks(scheme::writeValue(key)); + const string vs(scheme::writeValue(val)); + const char* params[2] = { c_str(ks), c_str(vs) }; + PGresult* r = PQexecParams(pgsql.conn, c_str(string("insert into ") + pgsql.table + string(" values($1, $2);")), 2, NULL, params, NULL, NULL, 0); + if (PQresultStatus(r) != PGRES_COMMAND_OK) + return mkfailure<bool>(string("Couldn't execute insert postgresql SQL statement: ") + pgfailure(r, pgsql.conn)); + PQclear(r); + + debug(true, "pgsql::post::result"); + return true; +} + +/** + * Update an item in the database. If the item doesn't exist it is added. + */ +const failable<bool> put(const value& key, const value& val, const PGSql& pgsql) { + debug(key, "pgsql::put::key"); + debug(val, "pgsql::put::value"); + debug(pgsql.conninfo, "pgsql::put::conninfo"); + debug(pgsql.table, "pgsql::put::table"); + setup(pgsql); + + const string ks(scheme::writeValue(key)); + const string vs(scheme::writeValue(val)); + const char* params[2] = { c_str(ks), c_str(vs) }; + PGresult* r = PQexecParams(pgsql.conn, c_str(string("update ") + pgsql.table + string(" set ") + pgsql.vname + string(" = $2 where ") + pgsql.kname + string(" = $1;")), 2, NULL, params, NULL, NULL, 0); + if (PQresultStatus(r) != PGRES_COMMAND_OK) + return mkfailure<bool>(string("Couldn't execute update postgresql SQL statement: ") + pgfailure(r, pgsql.conn)); + const string t = PQcmdTuples(r); + if (t != "0") { + PQclear(r); + debug(true, "pgsql::put::result"); + return true; + } + PQclear(r); + + PGresult* pr = PQexecParams(pgsql.conn, c_str(string("insert into ") + pgsql.table + string(" values($1, $2);")), 2, NULL, params, NULL, NULL, 0); + if (PQresultStatus(pr) != PGRES_COMMAND_OK) + return mkfailure<bool>(string("Couldn't execute insert postgresql SQL statement: ") + pgfailure(pr, pgsql.conn)); + PQclear(pr); + + debug(true, "pgsql::put::result"); + return true; +} + +/** + * Get an item from the database. + */ +const failable<value> get(const value& key, const PGSql& pgsql) { + debug(key, "pgsql::get::key"); + debug(pgsql.conninfo, "pgsql::get::conninfo"); + debug(pgsql.table, "pgsql::get::table"); + setup(pgsql); + + const string ks(scheme::writeValue(key)); + const char* params[1] = { c_str(ks) }; + PGresult* r = PQexecParams(pgsql.conn, c_str(string("select * from ") + pgsql.table + string(" where ") + pgsql.kname + string(" = $1;")), 1, NULL, params, NULL, NULL, 0); + if (PQresultStatus(r) != PGRES_TUPLES_OK) + return mkfailure<value>(string("Couldn't execute select postgresql SQL statement: ") + pgfailure(r, pgsql.conn)); + if (PQntuples(r) < 1) { + PQclear(r); + ostringstream os; + os << "Couldn't get postgresql entry: " << key; + return mkfailure<value>(str(os), 404, false); + } + const char* data = PQgetvalue(r, 0, 1); + const value val(scheme::readValue(string(data))); + PQclear(r); + + debug(val, "pgsql::get::result"); + return val; +} + +/** + * Delete an item from the database + */ +const failable<bool> del(const value& key, const PGSql& pgsql) { + debug(key, "pgsql::delete::key"); + debug(pgsql.conninfo, "pgsql::delete::conninfo"); + debug(pgsql.table, "pgsql::delete::table"); + setup(pgsql); + + const string ks(scheme::writeValue(key)); + const char* params[1] = { c_str(ks) }; + PGresult* r = PQexecParams(pgsql.conn, c_str(string("delete from ") + pgsql.table + string(" where ") + pgsql.kname + string(" = $1;")), 1, NULL, params, NULL, NULL, 0); + if (PQresultStatus(r) != PGRES_COMMAND_OK) + return mkfailure<bool>(string("Couldn't execute delete postgresql SQL statement: ") + pgfailure(r, pgsql.conn)); + PQclear(r); + + debug(true, "pgsql::delete::result"); + return true; +} + +} +} + +#endif /* tuscany_pgsql_hpp */ |