From 4a28afdf7a6b193942f180862d443a7d818c5789 Mon Sep 17 00:00:00 2001
From: jsdelfino <jsdelfino@13f79535-47bb-0310-9956-ffa450edef68>
Date: Mon, 28 May 2012 04:39:28 +0000
Subject: Database support optimizations. Use PgBouncer to pool connections.
 Track and manage WAL archives to optimize space and speed up database
 replication setup.

git-svn-id: http://svn.us.apache.org/repos/asf/tuscany@1343139 13f79535-47bb-0310-9956-ffa450edef68
---
 sca-cpp/trunk/components/sqldb/pgsql-conf | 60 +++++++++++++++++++++++++++----
 1 file changed, 53 insertions(+), 7 deletions(-)

(limited to 'sca-cpp/trunk/components/sqldb/pgsql-conf')

diff --git a/sca-cpp/trunk/components/sqldb/pgsql-conf b/sca-cpp/trunk/components/sqldb/pgsql-conf
index 58d38412ca..482048a295 100755
--- a/sca-cpp/trunk/components/sqldb/pgsql-conf
+++ b/sca-cpp/trunk/components/sqldb/pgsql-conf
@@ -33,11 +33,15 @@ else
     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`
+
 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`
@@ -48,6 +52,7 @@ fi
 mkdir -p $root/sqldb
 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
@@ -77,7 +82,7 @@ port = $port
 
 # Setup archival
 archive_mode = on
-archive_command = 'cp %p $root/sqldb/archive/%f'
+archive_command = '$here/pgsql-archive $root localhost $bport %p %f'
 
 # Setup hot standby with streaming replication
 wal_level = hot_standby
@@ -97,7 +102,7 @@ host    replication     all             samenet                 trust
 
 EOF
 
-# Create the db
+# 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
@@ -110,15 +115,22 @@ while [ $sti -ne 30 ]; do
 done
 
 $pgsql_prefix/bin/createdb -h localhost -p $port 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 localhost $port
+fi
+
 $pgsql_prefix/bin/pg_ctl stop -w -D $root/sqldb/data 2>&1 | sh $root/sqldb/logger
 
-# Generate database backup script
+# Generate database restore script
 mkdir -p $root/sqldb/scripts
-cat >$root/sqldb/scripts/backup <<EOF
+cat >$root/sqldb/scripts/restore <<EOF
 #!/bin/sh
-$here/pgsql-backup $root localhost $port
+$here/pgsql-restore $root
 EOF
-chmod 700 $root/sqldb/scripts/backup
+chmod 700 $root/sqldb/scripts/restore
 
 # Configure HTTPD to serve backup and archive files
 if [ -f "$root/conf/httpd.conf" ]; then
@@ -126,10 +138,44 @@ if [ -f "$root/conf/httpd.conf" ]; then
 # Generated by: pgsql-conf $*
 
 # Serve PostgreSQL backup and WAL archive files
-ScriptAlias /pgsql-backup "$root/sqldb/scripts/backup"
+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=localhost dbname=db
+
+[pgbouncer]
+pool_mode = session
+listen_port = $bport
+listen_addr = $ip
+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
+
-- 
cgit v1.2.3