The following instructions are for PostgreSQL 9.1, supported by shelob and marsh. Postgres 9.1 listens on port 5433 instead of 5432, as an artifact of when we supported 8.1 and 9.1 simultaneously. You will therefore need to supply the port when connecting to the database.
Create an Account
To create a PostgreSQL user account, enter the following command on ssh.hcoop.net:
dbtool postgres-9 adduser
You will not be prompted for a password since PostgreSQL utilizes a security model that is quite different from MySQL. The user created will be the same as your log-in username.
Create a Database
Additionally, dbtool is used to create new Postgres databases. Replace DATABASE with your desired database name:
dbtool postgres-9 createdb DATABASE
The database created will be called USER_DATABASE (where USER is your username and DATABASE is the name you provided dbtool).
If you would like to specify an alternate text encoding for your database (e.g., UTF8), you can run dbtool like this:
dbtool postgres-9 createdb DATABASE UTF8
Delete a Database
Delete a database with the following command:
dbtool postgres-9 dropdb DATABASE
For security reasons, you cannot drop a database using the psql shell.
Accessing Databases
For web applications, set the PostgreSQL host/server to postgres. Currently version 8.1 is running on port 5432 and version 9.1 is running on port 5433. Use your HCoop username. A password is not required. Remember that your database is really named USER_DATABASE, where DATABASE is the name you originally gave dbtool. Follow your software package's instructions.
It is very easy to access your database using a PostgreSQL shell:
psql -h postgres -p PORT USER_DATABASE
To learn more about the psql shell, take a look at the PostgreSQL manual.
Transferring Databases
If you're using PostgreSQL, then you probably already know how to do this. Let us know if that's not the case and you need instructions.