gistfile1.sh

#!/bin/bash
#
# Install Postgres 9.2 on a clean Ubuntu 12.04

"""
LC_ALL issue
comment out the AcceptEnv LANG LC_* line in the remote /etc/ssh/sshd_config file.
sudo apt-get install language-pack-en-base
sudo dpkg-reconfigure locales
comment out the SendEnv LANG LC_* line in the local /etc/ssh/ssh_config file.
"""
sudo apt-get update
sudo apt-get -y install python-software-properties
sudo add-apt-repository ppa:pitti/postgresql
sudo apt-get update

sudo apt-get -y install postgresql-9.2 postgresql-client-9.2 postgresql-contrib-9.2
sudo apt-get -y install postgresql-server-dev-9.2 libpq-dev

# pg_createcluster 9.2 main --start

sudo passwd postgres
su - postgres
psql -c"alter user postgres with password 'postgres';"

sudo vi /etc/sysctl.conf
"""
kernel.shmmax=8589934592   (8G * 1024 * 1024 * 1024)
"""

/sbin/sysctl -p

sudo vi /etc/postgresql/9.2/main/postgresql.conf
"""
#data_directory = '/var/lib/postgresql/9.2/main'
data_directory = '/mnt/postgresql/9.2/main'
listen_addresses = '*'
unix_socket_directory = '/var/run/postgresql'
shared_buffers = 4096MB    # < kernel.shmmax
"""

sudo vi /etc/postgresql/9.2/main/pg_hba.conf
'''
local all postgres              peer
host  all all 127.0.0.1/32      md5
host  all all 192.168.1.0/24    md5
host  all all 10.200.13.221/32  md5
host  all all 10.200.0.117/32   md5
host  all all ::1/128           md5
'''

# pgbouncer
'''
sudo apt-get install build-essential
sudo apt-get install libevent-dev
wget http://pgfoundry.org/frs/download.php/3369/pgbouncer-1.5.3.tar.gz
tar xvfz pgbouncer-1.5.3.tar.gz
cd pgbouncer-1.5.3
./configure --prefix=/usr/local
make
sudo make install
sudo cp -r /usr/local/share/doc/pgbouncer /etc/
su postgres -c"pgbouncer -d /etc/pgbouncer/pgbouncer.ini"
'''
sudo apt-get install pgbouncer
# edit /etc/pgbouncer/pgbouncer.ini file
# edit /etc/default/pgbouncer file and set START=1
sudo service pgbouncer start

sudo vi /etc/pgbouncer/userlist.txt
'''
"postgres" "postgres"
'''
sudo vi /etc/pgbouncer/pgbouncer.ini
"""
[databases]
* = port=5432
[pgbouncer]
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/log/postgresql/pgbouncer.pid
listen_addr = *
listen_port = 6432
unix_socket_dir = /var/run/postgresql
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = postgres
stats_users = postgres
pool_mode = transaction
server_reset_query = DISCARD ALL;
server_check_query = select 1
server_check_delay = 10
max_client_conn = 1000
default_pool_size = 20
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
"""

# pgbench
su - postgres
createdb bench
/usr/lib/postgresql/9.2/bin/pgbench -i -s 10 bench
/usr/lib/postgresql/9.2/bin/pgbench -c 10 -C -T 60 bench
/usr/lib/postgresql/9.2/bin/pgbench -c 10 -C -T 60 -p 6432 bench