Install Postgresql 9.2 on Ubuntu 12.04

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

thanks for the script.
i followed all the steps but got an error while starting pgbouncer.

the following are the logs from pgbouncer log file.

LOG C-0xa4b850: (nodb)/(nouser)@unix:6432 closing because: client unexpected eof (age=0)
FATAL @src/main.c:742 in function main(): unix socket is in use, cannot continue 

i did a 'lsof -i' to see which process is holding the socket and i get following output

COMMAND     PID      USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
pgbouncer   744  postgres    8u  IPv4 18643707      0t0  TCP *:6432 (LISTEN)

can you please suggest a solution
thanks

hi, there were some permission issues. the steps work just fine.
sorry to bother :)

👍

Followed the steps but script hangs on "reenter password for pam_mount:". Why? The partial output below:

postgresql-client-common postgresql-common
Suggested packages:
oidentd ident-server locales-all postgresql-doc-9.2 libdbd-pg-perl
The following NEW packages will be installed:
postgresql-9.2 postgresql-client-9.2 postgresql-client-common postgresql-common postgresql-contrib-9.2
0 upgraded, 5 newly installed, 0 to remove and 2 not upgraded.
Need to get 0 B/6,151 kB of archives.
After this operation, 25.2 MB of additional disk space will be used.
Preconfiguring packages ...
Selecting previously unselected package postgresql-client-common.
(Reading database ... 598171 files and directories currently installed.)
Unpacking postgresql-client-common (from .../postgresql-client-common_140precise_all.deb) ...
Selecting previously unselected package postgresql-client-9.2.
Unpacking postgresql-client-9.2 (from .../postgresql-client-9.2_9.2.4-0ppa1precise_amd64.deb) ...
Selecting previously unselected package postgresql-common.
Unpacking postgresql-common (from .../postgresql-common_140precise_all.deb) ...
Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common'
Selecting previously unselected package postgresql-9.2.
Unpacking postgresql-9.2 (from .../postgresql-9.2_9.2.4-0ppa1precise_amd64.deb) ...
Selecting previously unselected package postgresql-contrib-9.2.
Unpacking postgresql-contrib-9.2 (from .../postgresql-contrib-9.2_9.2.4-0ppa1precise_amd64.deb) ...
Processing triggers for man-db ...
Processing triggers for ureadahead ...
Setting up postgresql-client-common (140precise) ...
Setting up postgresql-client-9.2 (9.2.4-0ppa1precise) ...
Setting up postgresql-common (140precise) ...
reenter password for pam_mount:

Thanks you for your guide 👍

Thank you for this guide!! Just changed to postgresql 9.5 and everything worked !!