I'm going to assume this install takes place on a stock install of Ubuntu 13.04 without
the PostgreSQL packages installed.. This particular
example shows a custom-compile of PostgreSQL 9.3, with perl enabled so that the perl procedural
language can be used.
Add the postgres user
Please note that the postgres user will already exist if you had previously installed
PostgreSQL in your system. Note we do this as root:
root@mycomputer:/etc/init.d# adduser postgres
Adding user `postgres' ...
Adding new group `postgres' (1001) ...
Adding new user `postgres' (1001) with group `postgres' ...
Creating home directory `/home/postgres' ...
Copying files from `/etc/skel' ...
Enter new UNIX password:
Retype new UNIX password:
passwd: password updated successfully
Changing the user information for postgres
Enter the new value, or press ENTER for the default
Full Name []: PostgreSQL
Room Number []: none
Work Phone []: none
Home Phone []: none
Other []: none
Is the information correct? [Y/n]
Install prerequisites
To compile, PostgreSQL requires a few packages to be installed. Note we are doing
everything as root:
This can be found at www.postgresql.org. In our example here, we will assume
we have downloaded the source code into /usr/home/myuser/Downloads. Note we are
doing everything as root:
root@mycomputer:/usr/local/src# cp /home/myuser/Downloads/postgresql-9.3.0.tar.gz .
root@mycomputer:/usr/local/src# tar -xzvf postgresql-9.3.0.tar.gz
root@mycomputer:/usr/local/src# cd postgresql-9.3.0/
Create the following wrapper script for configure. It just makes life easier:
root@mycomputer:/usr/local/src/postgresql-9.3.0# vi runconfigure.sh
Write a config wrapper script
This just helps document what configuration settings you used when you configured
the build. The contents of runconfigure.sh are:
root@mycomputer:/usr/local/src/postgresql-9.3.0# chmod +x runconfigure.sh
root@mycomputer:/usr/local/src/postgresql-9.3.0# ./runconfigure.sh
root@mycomputer:/usr/local/src/postgresql-9.3.0# make
root@mycomputer:/usr/local/src/postgresql-9.3.0# make install
root@mycomputer:/usr/local/src/postgresql-9.3.0# cd ./contrib/uuid-ossp
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# make
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# make install
root@mycomputer:/usr/local/src/postgresql-9.3.0/contrib/uuid-ossp# chown -R postgres:postgres /usr/local/pgsql-9.3
Configure
Let's create a start/stop script for PostgreSQL:
root@mycomputer:/usr/local/src/postgresql-9.3.0# cd /etc/init.d/
root@mycomputer:/etc/init.d# vi postgresql
The contents of postgresql are:
#!/bin/bash
# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS
# where to find commands like su, echo, etc...
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
DB_ENCODING=UTF8
DB_LOCALE=C
PG_INSTALL_DIR=/usr/local/pgsql-9.3
PG_DATA_DIR="${PG_INSTALL_DIR}/data"
PG_SERVER_LOG="$PG_DATA_DIR/serverlog"
PG_UNIX_USER=postgres
POSTGRES="$PG_INSTALL_DIR/bin/postgres"
PG_CTL="$PG_INSTALL_DIR/bin/pg_ctl"
INITDB="$PG_INSTALL_DIR/bin/initdb"
# die on first failure; do not keep trucking
set -e
if [ $# -ne 1 ]; then
echo "please enter start/stop/restart etc..." 1>&2
exit 1
fi
# Only start if we can find postgres and pg_ctl.
if [ ! -x $PG_CTL ]; then
echo "$PG_CTL not found" 1>&2
exit 1
fi
if [ ! -x $POSTGRES ]; then
echo "$POSTGRES not found" 1>%amp;2
exit 1
fi
case $1 in
init)
su - $PG_UNIX_USER -c "$INITDB --pgdata='$PG_DATA_DIR' --encoding=$DB_ENCODING --locale=$DB_LOCALE"
;;
start)
echo -n "Starting PostgreSQL: "
su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
echo "ok"
;;
stop)
echo -n "Stopping PostgreSQL: "
su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast"
echo "ok"
;;
restart)
echo -n "Restarting PostgreSQL: "
su - $PG_UNIX_USER -c "$PG_CTL stop -D '$PG_DATA_DIR' -s -m fast -w"
su - $PG_UNIX_USER -c "$PG_CTL start -D '$PG_DATA_DIR' -l $PG_SERVER_LOG &"
echo "ok"
;;
reload)
echo -n "Reload PostgreSQL: "
su - $PG_UNIX_USER -c "$PG_CTL reload -D '$PG_DATA_DIR' -s"
echo "ok"
;;
status)
su - $PG_UNIX_USER -c "$PG_CTL status -D '$PG_DATA_DIR'"
;;
*)
# Print help
echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
exit 1
;;
esac
exit 0
Get the script ready to run, but do not run it yet:
Configure the kernel params to allow postgres to use as much memory as it needs:
Note!I copied this from my PostgreSQL 9.1 install, but I think as of PostgreSQL 9.2,
it is no longer necessary to configure your system's shared memory parameters. I'll re-visit this later.
root@mycomputer:/etc/init.d# cd
root@mycomputer:~# vi postgresql-kernel-params.sh
The contents of postgresql-kernel-params.sh are:
#!/bin/bash
SYSCTL=/sbin/sysctl
echo "# add the output of this script to /etc/sysctl.conf,"
echo "# and then, as root, run"
echo
echo "# sysctl -p /etc/sysctl.conf"
echo
echo "# to load change the kernel settings for these parameters."
echo
PAGE_SIZE=`getconf PAGE_SIZE`
echo "# page size is: $PAGE_SIZE"
NUM_PHYS_PAGES=`getconf _PHYS_PAGES`
echo "# number of physical pages on this box: $NUM_PHYS_PAGES"
CURR_SHMALL=`$SYSCTL -n kernel.shmall`
PREF_SHMALL=`expr $NUM_PHYS_PAGES / 2`
echo "# kernel.shmall should be half of the number of pages. Current kernel.shmall, in pages, is: $CURR_SHMALL"
echo "# kernel.shmall should be:"
echo
echo "kernel.shmall = $PREF_SHMALL"
echo
CURR_SHMMAX=`$SYSCTL -n kernel.shmmax`
PREF_SHMMAX=`expr $PREF_SHMALL \* $PAGE_SIZE`
echo "# kernel.shmmax should be half of available RAM, in kB. Current kernel.shmmax, in kB, is: $CURR_SHMMAX"
echo "# kernel.shmmax should be:"
echo
echo "kernel.shmmax = $PREF_SHMMAX"
echo
# CURR_SHMMIN=`$SYSCTL -n kernel.shmmin` # XXX: does not exist on linux
# CURR_SHMSEG=`$SYSCTL -n kernel.shmseg` # XXX: does not exist on linux
CURR_SHMMNI=`$SYSCTL -n kernel.shmmni`
echo "# kernel.shmmni is usually set to a sane amount on Linux. Currently, it is: $CURR_SHMMNI"
# CURR_SEMMNI=`$SYSCTL -n kernel.semmni` # XXX: does not exist on linux
# CURR_SHMMNI=`$SYSCTL -n kernel.semmns` # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmsl` # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmap` # XXX: does not exist on linux
# CURR_SHMMSL=`$SYSCTL -n kernel.semmvx` # XXX: does not exist on linux
CURR_SEM=`$SYSCTL -n kernel.sem`
echo "# kernel.sem usually has sane defauls. They are currently: $CURR_SEM"
# add the output of this script to /etc/sysctl.conf,
# and then, as root, run
# sysctl -p /etc/sysctl.conf
# to load change the kernel settings for these parameters.
# page size is: 4096
# number of physical pages on this box: 1782938
# kernel.shmall should be half of the number of pages. Current kernel.shmall, in pages, is: 2097152
# kernel.shmall should be:
kernel.shmall = 891469
# kernel.shmmax should be half of available RAM, in kB. Current kernel.shmmax, in kB, is: 33554432
# kernel.shmmax should be:
kernel.shmmax = 3651457024
# kernel.shmmni is usually set to a sane amount on Linux. Currently, it is: 4096
# kernel.sem usually has sane defauls. They are currently: 250 32000 32 128
And so I follow the advice and edit sysctl.conf:
root@mycomputer:~# vi /etc/sysctl.conf
And so I follow the advice and edit sysctl.conf:
and at the bottom of sysct.conf, I add:
kernel.shmall = 891469
kernel.shmmax = 3651457024
And now I make the changes take effect:
root@mycomputer:~# sysctl -p /etc/sysctl.conf
Initialise the database
root@mycomputer:~# cd /usr/local/
root@mycomputer:/usr/local# chown -R postgres:postgres pgsql-9.3 # this should have already been done earlier
root@mycomputer:/usr/local# su - postgres
postgres@mycomputer:~$ cd /usr/local/pgsql-9.3/bin
postgres@mycomputer:/usr/local/pgsql-9.3/bin$ ./initdb --pgdata=/usr/local/pgsql-9.3/data --encoding=UTF8 --no-locale
postgres@mycomputer:/usr/local/pgsql-9.3/bin$ cd ../data/
postgres@mycomputer:/usr/local/pgsql-9.3/data$ vi postgresql.conf
Configure the database
Now that we have run initdb, we have a data directory, and inside that data directory,
we have a postgresql.conf file that needs to be edited.
If you run a postgresql database with few connections but large jobs, here are some recommended settings for postgrsql.conf. Assume a sever with 75GB of RAM.
listen_address='localhost' ==> listen_address='*'
# according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html,
# shared_buffers should be 25% of RAM, but no higher than 8GB.
# However, see http://www.keithf4.com/a-small-database-does-not-mean-small-shared_buffers/
# for a point of view on when shared_buffers might legitimately be set above 8GB
shared_buffers = 32MB ==> shared_buffers = 8GB
temp_buffers = 8MB ==> temp_buffers = 80MB
work_mem = 1MB ==> work_mem = 1GB # set higher on a per-session basis
maintenance_work_mem = 16MB ==> maintenance_work_mem = 20GB # set quite high; used to build indexes and do data loads
# according to http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html,
# wal_buffers should be 3% of shared_buffers up to a maximum of 16MB, the size of a single WAL segment.
wal_buffers = 64kB ==> wal_buffers = 16MB
# according to https://www.packtpub.com/article/server-configuration-tuning-postgresql,
# checkpoint segments should be much higher than the default of 3:
checkpoint_segments = 3 ==> checkpoint_segments = 64
# if you are on RAID 10, the cost of accessing a random page should be much closer to the cost
# of sequential IO. (In fact, if you are on SSDs, seq_page_cost and random_page_cost should equal each other.)
random_page_cost = 4.0 ==> random_page_cost = 2.0
# according to http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server,
# "Setting effective_cache_size to 1/2 of total memory would be a normal conservative setting,
# and 3/4 of memory is a more aggressive but still reasonable amount."
effective_cache_size = 128MB ==> effective_cache_size = 37GB
# settings for logging:
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_min_messages = debug1 # obviously rather verbose; drop to warning if your logs fill up
log_line_prefix = '%t'
# According to http://www.databasesoup.com/2014/05/why-you-should-always-set-tempfilelimit.html,
# you should set temp_file_limit if you are concerned that any process (or set of processes)
# could start over-expanding all of the temporary files that get used for sorts, hashes, and similar operations.
# If you wet temp_file_limit, when a user goes over the limit, their query gets cancelled and they see an error.
Next, edit pg_hba.conf if you need to, though the defaults allow logins to all databases from
all users from the local machine.