Skip to content

Instantly share code, notes, and snippets.

@cassioeskelsen
Forked from chen206/gist:4030441
Created September 1, 2013 04:02
Show Gist options
  • Save cassioeskelsen/6402246 to your computer and use it in GitHub Desktop.
Save cassioeskelsen/6402246 to your computer and use it in GitHub Desktop.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment