PostgreSQL databases
This page describes some of the information held in the dCache postgreSQL databases.
$ 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.