Working with PostgreSQL

Basic Configuration

Add PostgreSQL to the path if it isn't already:

Control Panel->System->Advanced->Environmental Variables
Add: C:\Program Files\PostgreSQL\9.0\bin

To use PLPerl on windows, you will need to be careful to mix and match the right versions of PostgreSQL and Perl.

Perl version 5.10.1 (a 64bit build is available from ActiveState) works with PostgreSQL 9.0.1 (64bit build 1500). To see your PostgreSQL version type:

psql -c "select version();" template1

To see your perl version type:

perl -v

With these two versions together you should be able to add plperl to template1 (which all new dbs will inherit) with the command:

createlang plperl template1

There is a list of commands/client applications, with links to documentation, which is useful.

You will almost surely want to 'performance tune' your postgresql database, as the default settings are near useless. In particular edit postgresql.conf (which is in the data directory of your install) to change:

shared_buffers = 512MB	#Use about 10-15% of available RAM, to a max of 512Mb on windows
effective_cache_size = 3GB #Use half to 3/4 of available RAM, depending on your pref
work_mem = 128MB # This is the memory allocated to each query sort
maintenance_work_mem = 256MB #This is for vacuum, and a max of 256 is recommended.

Note that with work_mem this is the allocation to each sort. Each query you run may do many sorts and you may have many users, so this can explode quickly. 128Mb is an aggresive setting that assumes only a single user. See the various documentation resources, especially the official performance optimization page for more information.

Restart the server (on windows use the 'services' control panel) for many changes to take effect:

pg_ctl restart

Create a user using pgAdmin or the createuser command:

createuser ed_egan

And then create a database again using pgAdmin or the createdb command:

createdb -O ed_egan DBName 

Dumping and Restoring a Database

This can be done in pgAdmin, but the commands are:

To dump a db:

pg_dump mydb > db.backup

To reload this database:

pg_restore -d DBName db.backup

Working with psql

To connect your psql client to a db type (for localhost don't specify the host):

psql -h host DBName

Useful commands are:

\q Quits

SQL Commands

There is a list of SQL commands that may help.