The PostgreSQL server currently installed is version 8.1.4. It is accessible at
See the below sections for information about performing common PostgreSQL tasks. Note that the command line instructions assume that you have logged into an OCF machine via SSH. The PostgreSQL commands on the OCF are configured to work with the OCF's PostgreSQL server by default, which is why many of the command lines are so short. If you are connecting using a PostgreSQL client from a non-OCF machine, you may need to add extra options to the command line that specify, for example, the username and server hostname.
Note: The rest of this page assumes that you are using the OCF's default shell runtime configuration (e.g., .cshrc or .bashrc), which sets the PGHOST environment variable appropriately. If you don't source OCF's shell runtime configuration files, or you are working from a non-OCF machine, then either set PGHOST to the PostgreSQL server hostname above, or else use the -h option to specify the server hostname.
Creating a PostgreSQL database
SSH into your OCF account and run
This will create a PostgreSQL database and assign it a randomly-generated password. Note that the OCF does not allow changing of PostgreSQL passwords from the randomly-generated default (see below for rationale on why we do this). The database name will be whatever your OCF username is. You can create as many tables as you want in your database, but you cannot create additional databases.
If you don't wish to use SSH, you may use the web interface to do this (select the "makepgsql" option); note, however, that most PostgreSQL administration tasks will require using the shell.
Connecting to a PostgreSQL database
To connect to the OCF's PostgreSQL server using the PostgreSQL client on an OCF machine, simply run the command
This command will prompt you for your PostgreSQL database password.
Backing up a PostgreSQL database
To backup your database (which you should probably do regularly), the basic command to use is
pg_dump [username] > backup
where [username] denotes your OCF username and backup is the name of the file you want to dump the contents of your database into. This command will prompt you for your PostgreSQL password.
To see more options, try running
Restoring a PostgreSQL database from backup
If you need to restore your database from a backup (dump file) you made previously, you simply need to connect to the database and run the SQL commands in the dump file. To do this from the command line, use
psql < backup
where backup is the name of the file that contains the dumped data. As usual, this command will prompt you for your PostgreSQL password.
Changing or resetting your database password
If you have lost or forgotten the randomly-generated password that you received when you created your database, you can easily reset your password. Simply SSH into an OCF machine and re-run
This will change your PostgreSQL password to a new random password and tell you what that password is.
Alternately, you may visit the web interface and select the "makepgsql" option to reset your database password.
Using .pgpass for passwordless access to PostgreSQL
If you are using PostgresSQL in a script or in another program (or if you have poor memory), you will probably want to set it up so that the psql call does not prompt for a password. You can do this by creating a .pgpass file in your home directory with proper permissions that contains your PostgreSQL password. To do this, enter the following commands; replace username with your username and dbpasswd with the actual PostgreSQL password given to you when you ran makepgsql.
touch ~/.pgpass chmod 600 ~/.pgpass cat >> ~/.pgpass << EOF postgresql.OCF.Berkeley.EDU:*:username:username:dbpasswd EOF
Make sure that your .pgpass has correct permissions by running
ls -l ~/.pgpass
Your screen output should be something like
-rw------- 1 staff ocf 54 Nov 15 17:37 /home/s/st/staff/.pgpass
where the important part is -rw-------. You should now be able to use PostgreSQL without memorizing or writing down your database password.
For more information on the format of the .pgpass file, see the PostgreSQL documentation on .pgpass.