PostgreSQL support

From OCF Help Wiki
(Redirected from PostgreSQL)
Jump to: navigation, search

The PostgreSQL server currently installed is version 8.1.4. It is accessible at

 postgresql.OCF.Berkeley.EDU

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.

Contents

Creating a PostgreSQL database

SSH into your OCF account and run

 makepgsql

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

 psql

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

 pg_dump --help

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

 makepgsql

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.

See Also

External Links

Personal tools
Namespaces

Variants
Actions
Navigation
Toolbox