Mr Zesty's Brain Dump: PostgreSQL notes

PostgreSQL notes


./configure --with-perl --with-openssl [--enable-syslog]
make
make check (as non-root user)
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

vi ~postgres/postgresql.conf

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

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

Values to change in ~postgres/postgresql.conf

tcpip_socket = true
virtual_host = 'localhost'
shared_buffers = 15200       # (Number of buffers * 8k each)
sort_mem = 32768             # (kb)

Set table owner:

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