Postgresql

Links

• Homepage
• Postgresql on freebsd
• Backups for postgresql

Documentation

• Postgresql Administration

Cheatsheet

Describe table

\d table

Installation

Postgresql on freebsd

• http://www.freebsddiary.org/postgresql.php (doesn't seem entirely right)

(pg_ctl: could not read file "/usr/local/pgsql/data/postmaster.opts") : After installing and creating db: As user pgsql

/usr/local/bin/postmaster -D /usr/local/pgsql/data
pg_ctl stop

Ater which it should be possible to run ( as root again)

/usr/local/etc/rc.d/postgresql start

HOWTO

Back up postgresql databases

• Backup and Restore

pg_restore --disable-triggers --clean -d davical  /tmp/davical

FAQ

Create user

su - postgres
createuser

or in psql:

CREATE DATABASE yourdbname
CREATE USER youruser WITH ENCRYPTED PASSWORD 'yourpass'
GRANT ALL PRIVILEGES ON DATABASE yourdbname TO youruser

ERROR: character 'xxx' of encoding "UTF8" has no equivalent in "LATIN9" locale

Check

show server_encoding
show client_encoding
\encoding

List users

su to postgres, psql and then

\du

Delete user

drop user