Project

General

Profile

Direct Database Access

GUI-based access


Most PostgreSQL distributions come with a GUI, called pgAdminIII, that allows one to conveniently access any database. The interface is very intuitive. Note that you should only use the pgAdminIII that comes with your installation, as this should support GSSAPI authentication. We have found some stand-alone versions of pgAdminIII do not have GSSAPI support built-in, and so connections via kerberos authentication fails (the "nova_reader" account should still work, however).

To get started, go to the File menu of pgAdminIII and select "Add Server...". Fill in the "Name" (eg, "nova_prod", "nova_hardware", etc.), the "Host" (eg, "ifdbprod.fnal.gov"), the "Port" ("5432" or "5433") and "Username" (either your FNAL username or "nova_reader") entries. If you are using the "nova_reader" account, you will also need to enter the "Password". After the server is created it will appear on the left-hand side of the pgAdminIII browser, as in the screen capture shown here. Double click on the server; if you are prompted for a password but are using kerberos authentication, do not enter a password, simply click "Ok" (you must have a valid krb5 ticket first). Assuming you successfully connect to the database server, you may now begin browsing by drilling down on the left-hand frame of the pgAdminIII browser. The right-hand frames display the details of whatever you click on in the left-hand frame.

To "view" data, right-click on a table and select one of the options under the "View Data" menu option. A new window will open that display the table in a (row,column) format. If you have the appropriate permissions, you may even modify the data shown here.

Terminal-based "psql" command

PostgreSQL offers a powerful tool, called "psql", for direct access to a Postgres database. Assuming the NOvA environment has been properly set up, the command is:

>psql -h $NOVADBHOST -d $NOVADBNAME -p $NOVADBPORT -U nova_reader --password

for read-only access.

If you need direct access with whatever dB write privileges you have been granted, then:

> setenv NOVADBHOST ifdbprod.fnal.gov
> setenv NOVADBNAME nova_prod
> setenv NOVADBPORT 5433
> setenv NOVADBUSER $USER

and

>psql -h $NOVADBHOST -d $NOVADBNAME -p $NOVADBPORT -U $NOVADBUSER

for kerberos-authenticated access.

The scope of this documentation does not cover PostgreSQL commands in detail (eg, there is already excellent PostgreSQL documentation online), however, here are some quick examples:

  • to see a list of tables:
    nova_prod=> \dt 
                          List of relations
     Schema |              Name              | Type  |   Owner    
    --------+--------------------------------+-------+------------
     public | apd_high_voltages              | table | nova_admin
     public | asic_register_settings         | table | nova_admin
     public | daq_resources_by_run           | table | nova_admin
     public | dcm_application_parameters     | table | nova_admin
     public | dcm_data_dev_parameters        | table | nova_admin
     public | dcm_fpga_firmware_locations    | table | nova_admin
     public | dcm_fpga_parameters            | table | nova_admin
     public | dcm_system_parameters          | table | nova_admin
     public | dcs_fault                      | table | nova_admin
     public | dcs_hardlimit                  | table | nova_admin
     public | dcs_report                     | table | nova_admin
     public | dso_data_regulator_settings    | table | nova_admin
     public | feb_enable_masks               | table | nova_admin
     public | feb_fpga_firmware_locations    | table | nova_admin
     public | feb_pulser_parameters          | table | nova_admin
     public | named_global_configurations    | table | nova_admin
     public | named_subsystem_configurations | table | nova_admin
     public | pixel_enable_masks             | table | nova_admin
     public | pixel_offsets                  | table | nova_admin
     public | pixel_thresholds               | table | nova_admin
     public | pot_by_run                     | table | nova_admin
     public | runs                           | table | nova_admin
     public | subruns                        | table | nova_admin
     public | timing_system_settings         | table | nova_admin
     public | trigger_by_run                 | table | nova_admin
    (25 rows)
    
    
  • to see the details of a table:
    nova_prod=> \d runs;
                       Table "public.runs" 
        Column     |            Type             | Modifiers 
    ---------------+-----------------------------+-----------
     run           | integer                     | not null
     nsubruns      | integer                     | 
     nevents       | integer                     | 
     partition     | integer                     | 
     runtype       | text                        | 
     config        | text                        | 
     configid      | integer                     | not null
     detector      | text                        | not null
     tstart        | timestamp without time zone | not null
     tstop         | timestamp without time zone | 
     shifter       | text                        | 
     begin_comment | text                        | 
     end_comment   | text                        | 
     inserttime    | timestamp without time zone | not null
     insertuser    | text                        | not null
     npot          | real                        | 
     nactivechan   | integer                     | 
    Indexes:
        "runs_pkey" PRIMARY KEY, btree (run, detector)
    
    
  • to execute a "simple" query:
    nova_prod=> select run,nsubruns,config,tstart,nactivechan from runs order by run desc;
    
      run  | nsubruns |       config        |       tstart        | nactivechan 
    -------+----------+---------------------+---------------------+-------------
     11841 |          | InitialGlobalConfig | 2011-03-28 17:46:05 |        8360
     11840 |          | InitialGlobalConfig | 2011-03-28 16:23:24 |        8360
     11839 |        1 | InitialGlobalConfig | 2011-03-28 15:24:48 |        8360
     11838 |       19 | InitialGlobalConfig | 2011-03-27 19:48:27 |        8360
     11837 |       25 | InitialGlobalConfig | 2011-03-26 19:48:03 |        8360
     11836 |        1 | InitialGlobalConfig | 2011-03-26 19:43:51 |        8360
     11835 |        1 | InitialGlobalConfig | 2011-03-26 19:23:18 |        8360
     11834 |          | InitialGlobalConfig | 2011-03-26 17:13:10 |        8360
     11833 |        1 | InitialGlobalConfig | 2011-03-26 16:23:04 |        8372
     11832 |        2 | InitialGlobalConfig | 2011-03-26 15:14:19 |        8372
     11831 |        1 | InitialGlobalConfig | 2011-03-26 15:07:07 |        8372
     11830 |        1 | InitialGlobalConfig | 2011-03-26 14:05:31 |        8372
     11829 |        1 | InitialGlobalConfig | 2011-03-26 13:20:57 |        8372
     11828 |        9 | InitialGlobalConfig | 2011-03-26 04:42:27 |        8210
     11827 |        1 | InitialGlobalConfig | 2011-03-26 04:21:42 |        8210
     11826 |        4 | InitialGlobalConfig | 2011-03-26 01:00:08 |        8210
     11825 |        2 | InitialGlobalConfig | 2011-03-25 23:18:34 |        8210
     11824 |        1 | InitialGlobalConfig | 2011-03-25 22:57:09 |        8210
     11823 |          | InitialGlobalConfig | 2011-03-25 21:47:39 |        8382
     11822 |        1 | InitialGlobalConfig | 2011-03-25 21:15:29 |        8382
     11821 |        1 | InitialGlobalConfig | 2011-03-25 20:31:44 |        8382
     11820 |        3 | InitialGlobalConfig | 2011-03-25 17:26:55 |        8382
     11819 |        1 | InitialGlobalConfig | 2011-03-25 16:27:23 |        7590
     11818 |        1 | InitialGlobalConfig | 2011-03-25 14:54:47 |        7590
     11817 |        1 | InitialGlobalConfig | 2011-03-25 14:31:09 |        8382
     11816 |       15 | InitialGlobalConfig | 2011-03-24 23:17:15 |        8382
     11815 |        1 | InitialGlobalConfig | 2011-03-24 22:54:18 |        8382
     11814 |       28 | InitialGlobalConfig | 2011-03-24 22:13:34 |        8382
     11813 |       18 | InitialGlobalConfig | 2011-03-24 21:59:21 |        8382
     11812 |        2 | InitialGlobalConfig | 2011-03-24 21:50:25 |        8382
     11811 |        2 | InitialGlobalConfig | 2011-03-24 20:14:25 |        8382
     11810 |       23 | InitialGlobalConfig | 2011-03-24 20:01:24 |        8382
     11809 |       30 | InitialGlobalConfig | 2011-03-24 19:42:44 |        8382
     11808 |        6 | InitialGlobalConfig | 2011-03-24 14:05:46 |        8382
     11807 |        9 | InitialGlobalConfig | 2011-03-24 05:02:03 |        8141
     11806 |        1 | InitialGlobalConfig | 2011-03-24 04:27:52 |        8026
     11805 |        1 | InitialGlobalConfig | 2011-03-24 04:22:06 |        8026
    --More--