====== 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 ======

~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';