PostgreSQL databases

This page describes some of the information held in the dCache postgreSQL databases.

• postgreSQL optimisation tricks

$ su - postgres
$ psql
Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

Some useful commands are:

• \l will list the available databases.
• \c <db-name> connects to that database.
• \d lists the tables within that database.
• select * from <tablename> where <column-name>~'seach string'

The ~ operator allows for regex pattern matching. You can also use =, among other commands. See the posgres tutorials for a full description of commands.

On a standard dCache installation, the available databases should be:

postgres=# \l
         List of databases
   Name    |   Owner    | Encoding
-----------+------------+-----------
 admin     | pnfsserver | UTF8
 billing   | srmdcache  | UTF8
 companion | srmdcache  | UTF8
 data1     | pnfsserver | UTF8
 dcache    | postgres   | SQL_ASCII
 postgres  | postgres   | UTF8
 replicas  | srmdcache  | SQL_ASCII
 template0 | postgres   | UTF8
 template1 | postgres   | UTF8
(9 rows)

There will also be VO specific databases if you have created them. Here, all of the information is held in data1.

As a start, you should note the following (these statements are true on the Edinburgh dCache):

• template0 does not accpet connections.
• templete1 and postgres databases are empty
• data1 (or any of the VO specific databases) and admin are in binary, so it is no use trying to list the contents of their tables.

The remaining DBs have the contents and it is possible to get useful information from them:

data1=# \c dcache
You are now connected to database "dcache".
dcache=# \d
                   List of relations
 Schema |            Name             | Type  |   Owner
--------+-----------------------------+-------+-----------
 public | copyfilerequests            | table | srmdcache
 public | copyfilerequests_b          | table | srmdcache
 public | copyrequests                | table | srmdcache
 public | copyrequests_b              | table | srmdcache
 public | getfilerequests             | table | srmdcache
 public | getfilerequests_b           | table | srmdcache
 public | getrequests                 | table | srmdcache
 public | getrequests_b               | table | srmdcache
 public | getrequests_protocols       | table | srmdcache
 public | getrequests_protocols_b     | table | srmdcache
 public | nextpinrequestid            | table | srmdcache
 public | pinrequestsv1               | table | srmdcache
 public | putfilerequests             | table | srmdcache
 public | putfilerequests_b           | table | srmdcache
 public | putrequests                 | table | srmdcache
 public | putrequests_b               | table | srmdcache
 public | putrequests_protocols       | table | srmdcache
 public | putrequests_protocols_b     | table | srmdcache
 public | spacemanagernextid          | table | srmdcache
 public | spacemanagerpoolreservation | table | srmdcache
 public | spacemanagerpoolspace       | table | srmdcache
 public | srmnextrequestid            | table | srmdcache
 public | srmrequestcredentials       | table | srmdcache
(23 rows)

This database contains all of the SURLS and TURLS that are used during file transfers into and out of the dCache.

srmrequestcredentials holds the grid certificates of users who have copied files into the dCache. However, from this information it is not easy to map a file to the user who put it in the dCache.

dcache=# \c replicas
You are now connected to database "replicas".
replicas=# \d
           List of relations
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+-----------
 public | action    | table | srmdcache
 public | heartbeat | table | srmdcache
 public | pools     | table | srmdcache
 public | replicas  | table | srmdcache
(4 rows)

All tables are empty on our dCache, but we do not have resilient dCache operating, so this is to be expected.

admin=# \c billing
You are now connected to database "billing".
billing=# \d
            List of relations 
 Schema |    Name     | Type  |   Owner
--------+-------------+-------+-----------
 public | billinginfo | table | srmdcache
 public | costinfo    | table | srmdcache
 public | doorinfo    | table | srmdcache
 public | hitinfo     | table | srmdcache
 public | storageinfo | table | srmdcache
(5 rows)

The billing database contains information about GridFTP transfers into and out of the dCache, telling information like the hostname of the source of the file, filesize, timestamp, DN of the user who initiated the transfer. If a file is srmCopied into the dCache from another SRM then you can find out the pnfsID of the file from the billinginfo table. It is unclear at the moment how to map this to a user. Further information can be found in the Billing_Database page.

billing=# \c companion
You are now connected to database "companion".
companion=# \d
           List of relations  
 Schema |   Name    | Type  |   Owner
--------+-----------+-------+-----------
 public | cacheinfo | table | srmdcache
(1 row)

The companion database lists all of the pnfsIDs that are in use, the name of the pool that the file belongs to and the time it was created.