PostgreSQL Cheat Sheet

Starting/Stopping Server:
$set PGDATA=/postgres/data
$pg_ctl -w start/restart -D /var/lib/pgsql/data -l log -o --port=5432
$pg_ctl -D /var/lib/pgsql/data stop -m immediate/smart/fast
$pg_ctl -s
$pg_ctl reload

Server Administration (psql)
Some useful commands :
=>select VERSION()
=>select CURRENT_DATABASE()
=>select CURRENT_SCHEMA()
=>select USER
=>select INET_SERVER_PORT()
=>select INET_SERVER_ADDR()
=>vacum analyze verbose tablename
=>explain sql

#TCP/IP settings in var/lib/pgsql/data/pg_hba.conf
host all 127.0.0.1 255.255.255.0 password
To allow remote connections: modify /var/lib/pgsql/data/postgresql.conf
#change listen_addresses = 'localhost'
# to listen_addresses = '*'

Forgot root/postgres password?
#Modify pg_hba.conf to all trust connections
$pg_ctl reload
$psql -U postgres
=>alter user postgres with password 'newpassword' ;
=>\q
#Set pg_hba.conf back to orginal settings
$pg_ctl reload

$pg_dump -U user db > tmp/db_dump
Create a database:
$createdb test
or
$psql -U postgres -c "CREATE DATABASE test" -d template1
$psql -U postgres -d test
=>\r ; clear buffer
=>\e ; edit buffer
=>\w ; write buffer
=> \! ; run sub-shell
=>\g or ; ; rerun contents of buffer
=>copy table to file
=>copy table from file
=>set ; to view DBNAME, user, host and port
=>\connect or \c dbname ; no arguments gives current database name
=>\i file.sql ; execute script
=>\e ; to change encoding
=>\o filename ;
send query results to a file
=>\o
=>\d table or \d for all tables
=>\dt or \di or \dv or \dv or \dC
=>\dg ; list of groups
=>\dn ; schemas
=>\dn+ schema
=>\c db username

To view a stored procedure in psql:
=>select prosrc from pg_proc where procname='procedure_name' ;
Some other useful system tables
pg_trigger (triggers)
pg_class (tables)
=>\d tablename ;describes the table
=>select relname from pg_class ; lists all the user tables

To view the a schema with all its tables, try the following:
\o dbschema.txt
=>echo \d `select relname from pg_class`
\o

error messages
undefined symbol: pg_valid_server_encoding_id is often the result of incompatible libraries (usally when upgrading PostgreSQL) usually the libpq.so
$ldd -d path to psql
$export LD_LIBRARY_PATH=path to correct libpq.so
$sudo ldconfig