Finding documentation

Check the PostgreSQL page in this wiki and the web site postgresql.org.

• http://www.postgresql.org/docs/current/interactive/index.html

Setting up the database server

    $ dpkg -l "postgresql*"    
       ... (Find the exact names of latest packages)...
    $ sudo apt-get install postgresql-8.2 postgresql-client-8.2

In /etc/postgresql/8.2/main, modify postgresql.conf to listen for TCP requests on all interfaces,

    listen_addresses = '*'
    port = 5432

Add the following line to pg_hba.conf to accept MD5 hashes of passwords over the network from UW machines,

    host    all         all         129.97.0.0/16           md5

Setting up the OGSAS database

Create a database user ogsas by running the createuser command from a super-user account:

   sudo createuser -P ogsas

Assign the new database user a random password, such as the last group of characters generated by uuidgen. Copy the password into the workstation's ~/.pgpass file as follows:

    *:*:*:*:$PASSWORD

One may create a passwordless DB user having the same name as your system account $USER:

    sudo createuser $USER

This will let the DB server trust local (Unix socket) connections when running createdb and psql, since your pg_hba.conf allows local users whose account name coincides with the DB user name,

    # "local" is for Unix domain socket connections only
    local   all         all                               ident sameuser

To verify passwordless authentication of local connections and to see a list of DB users, run the "\du" command,

    $ psql -d postgres -c "\du"
                                   List of roles
     Role name | Superuser | Create role | Create DB | Connections | Member of 
    -----------+-----------+-------------+-----------+-------------+-----------
     $USER     | yes       | yes         | yes       | no limit    | 
     ogsas     | yes       | yes         | yes       | no limit    | 
     postgres  | yes       | yes         | yes       | no limit    | 
    (3 rows)

Create a new database named ogsas by running the createdb command. The load.sh script from the repository creates the database, as well as creates and populates the tables. The script should be run on the same machine were the database server is launched. The current directory should contain the t_gso_*.txt data files from the OGSAS repository's transform directory.

    cscfpc15:~/data$ svn co svn+ssh://odyssey@core.cs/u/odyssey/svn/ogsas/transform
    A    transform/t_gso_program.txt
    A    transform/t_gso_employment.txt
    A    transform/t_gso_program_status.txt
    A    transform/t_gso_applicant.txt
    A    transform/t_gso_education.txt
    A    transform/t_gso_program_subplan.txt
    A    transform/t_gso_testscore.txt
    A    transform/messages.txt
    A    transform/t_gso_program_condition.txt
    A    transform/t_gso_referee.txt
    A    transform/t_gso_finance.txt
    Checked out revision 456.
    
    cscfpc15:~/data$ cd transform/
    
    cscfpc15:~/data/transform$ ls
    messages.txt         t_gso_employment.txt         t_gso_program_status.txt   t_gso_referee.txt
    t_gso_applicant.txt  t_gso_finance.txt            t_gso_program_subplan.txt  t_gso_testscore.txt
    t_gso_education.txt  t_gso_program_condition.txt  t_gso_program.txt

    ogsas-dev1:~/data/transform$ ~/scripts/install/load.sh -d ~/scripts/install/ogsas.sql
    Drop database ogsas (y/n)? 

To connect to the database server from other machines, save the following script into an executable location such as /usr/local/bin/psql-ogsas.

    #! /bin/sh
    exec psql -d ogsas -U ogsas -h ogsas-dev1 -P pager=off "$@"

Try connecting to the database and looking at the list of databases and table descriptions,

    $ psql-ogsas -c "\l"
      
                    List of databases
               Name    |  Owner   | Encoding 
            -----------+----------+----------
             ogsas     | $USER    | UTF8
             postgres  | postgres | UTF8
             template0 | postgres | UTF8
             template1 | postgres | UTF8
            (4 rows)

    $ psql-ogsas -c "\d gso_finance"

                   Table "public.gso_finance"
             Column |       Type        | Modifiers 
            --------+-------------------+-----------
             appid  | id_type           | not null
             type   | character varying | not null
             status | character varying | not null
             amount | numeric(8,2)      | 
            Indexes:
                "gso_finance_pkey" PRIMARY KEY, btree (appid, "type", status)
            Foreign-key constraints:
                "gso_finance_appid_fkey" FOREIGN KEY (appid) REFERENCES gso_applicant(id) ON UPDATE CASCADE ON DELETE CASCADE

    $ psql-ogsas -c "select * from gso_finance" | less

Refreshing the database over the network

(a) Delete and create the database anew from local files

    cscfpc15:~/data/transform$ PGUSER=ogsas PGHOST=ogsas-dev1 PGDATABASE=ogsas \
        ~/scripts/install/load.sh -d ~/scripts/install/ogsas.sql
    Drop database ogsas (y/n)?
    CREATE DATABASE
    ...

(b) Drop the constraints, replace the data with local files, then restore the constraints

    cscfpc15:~/data/transform$ PGUSER=ogsas PGHOST=ogsas-dev1 PGDATABASE=ogsas  \
        ~/scripts/install/load.sh -a ~/scripts/install/ogsas-add-constraints.sql 
    ALTER TABLE ONLY gso_education DROP CONSTRAINT gso_education_appid_fkey;
    ...

(c) Update modified rows and insert new rows from the repository data files

    $ ssh odyssey@core
    odyssey@core $ cd /u/odyssey
    odyssey@core $ ./bin/ogsas-read-mail dbupdate

Modifying DDL

After modifying ogsas.sql in install/, don't forget to update the list of constraints in ogsas-add-constraints.sql:

    cscfpc15:~/scripts/install$ PGUSER=ogsas PGHOST=ogsas-dev1 PGDATABASE=ogsas \
        ./load.sh -a ./ogsas-add-constraints.sql -r

-- IlguizLatypov - 10 Jul 2007 -- 11 Jul 2007