PostgreSQL Notes

• Compile from source
• Client-only installation:
• Create user and database
• Create a certificate and install
• Values to change in ~postgres/postgresql.conf
• Set table owners:

Compile from source

./configure --with-perl --with-openssl
make -j6
su nobody -c "make check"
make install

/usr/sbin/useradd -u 400 -d /usr/local/pgsql/data -c "PostgreSQL" -m -k /dev/null postgres

su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

• Edit ~postgres/postgresql.conf, then add to startup

echo "Starting pgSQL"
su - postgres -c "/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start >>logfile 2>&1"

• crontab -e -u postgres

0 12 * * * /usr/local/pgsql/bin/vacuumdb --all --analyze > /dev/null
0 0 * * * LOGFILE=/usr/local/pgsql/data/serverlog; bzip2 -c $LOGFILE > $LOGFILE.old.bz2 && cat /dev/null > $LOGFILE

Client-only installation:

./configure --with-perl --with-openssl

make -C src/bin install
make -C src/include install
make -C src/interfaces install
make -C doc install

Create user and database

../bin/createuser -D -A -P -E -U postgres username
../bin/createdb -U postgres -O username dbname

Create a certificate and install

• OpenSSL Certificates
• ~postgres/server.key and ~postgres/server.crt

Values to change in ~postgres/postgresql.conf

tcpip_socket = true
virtual_host = 'localhost'
shared_buffers = 48000       # (Number of buffers * 8k each)  48000 * 8k = 384 MB ram for postgres
sort_mem = 32768             # (kb)
ssl = true

Set table owners:

UPDATE pg_database SET datdba = (SELECT usesysid FROM pg_user WHERE usename='newownername') WHERE datname='dbname';