Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Install Postgresql 9.2 on Ubuntu 12.04
#!/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
@ric03uec

This comment has been minimized.

Copy link

@ric03uec ric03uec commented Jun 3, 2013

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

@ric03uec

This comment has been minimized.

Copy link

@ric03uec ric03uec commented Jun 3, 2013

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

@makaroni4

This comment has been minimized.

Copy link

@makaroni4 makaroni4 commented Aug 7, 2013

👍

@natalyaportnov

This comment has been minimized.

Copy link

@natalyaportnov natalyaportnov commented Aug 22, 2013

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-0ppa1
precise_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-0ppa1
precise_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 (140
precise) ...
Setting up postgresql-client-9.2 (9.2.4-0ppa1precise) ...
Setting up postgresql-common (140
precise) ...
reenter password for pam_mount:

@tranhv

This comment has been minimized.

Copy link

@tranhv tranhv commented Jul 28, 2015

Thanks you for your guide 👍

@berlincarrie

This comment has been minimized.

Copy link

@berlincarrie berlincarrie commented Jul 26, 2016

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment