Instructional Application/Server Support - IC PostgreSQL Info
(for use mainly by CMPS180 and CMPS182)
- PostgreSQL is running on
db.ic.ucsc.edu (a Solaris system).
- To access the database server, users must have a UCSC account.
- Users do not need to log into
db.ic.ucsc.edu directly. All database operations can be performed remotely.
- The instructor and/or TA of the class will be given access to the DBA account in the database. Creating database users and databases will be the responsibility of the DBA.
- The PostgreSQL DBA account is
postgres.
- The password for the
postgres account can be given over the phone. Please call 459-2472.
- The
psql command is located in /usr/local/pgsql/bin. This directory should already be in the default path for all users.
- The JDBC driver can be found at
/usr/local/pgsql/share/jdbc/postgresql.jar on all IC unix systems.
- Shortly after finals, the database will be taken down and all data archived for one year.
Creating database accounts and database
- When creating database users and databases, the DBA should be logged into
unix.ic.ucsc.edu with their own username and password. The DBA should use the command /home/bin/createuser with the -h db.ic.ucsc.edu -U postgres arguments, and at least the -P argument. This will prompt for a password for the new database user.
- The database username and their respective database should have the same name.
- Here is an example of creating the database user 'bogus' and its associated database:
prompt$ /usr/local/pgsql/bin/createuser -P
prompt$ createuser -h db.ic.ucsc.edu -U postgres
Enter name of role to add: bogus
Enter password for new role: [enter new password]
Enter it again: [enter it again]
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
Password: [postgres password]
prompt$ /usr/local/pgsql/bin/createdb -h db.ic.ucsc.edu -U postgres -O bogus bogus
Password: [postgres password]
- Instructors who need to create and remove multiple accounts at one time, such as at the beginning of the quarter, may use the
pg_acct_create_batch and pg_acct_delete_batch commands. To use these, create a text file with a list of the users who need accounts and databases, with one username per line. You may then run the command as follows (where "filename" is the actual name of the file containing the list of users):
prompt$ pg_account_create_batch filename
The script will prompt for the postgres password and create postgres users and databases for each user listed in the files. It will generate a random password for each user and return a list of username:password pairs when it finishes running. It will not create accounts or databases for any users who do not have UCSC unix accounts.
- To remove these usernames and databases, the
pg_account_delete_batch script can be used the same way.
If you have a question about IC's PostgreSQL, please e-mail icunix@ucsc.edu.
