SUNScholar/SQL Tips
Back to System Admin
WE CANNOT ACCEPT RESPONSIBILITY FOR ANY DATA LOSS OR CORRUPTION
BEFORE PROCEEDING, DO EXTENSIVE TESTING ON SPARE INFRASTRUCTURE
*** YOU PROCEED AT YOUR OWN RISK ***
Please note:
No DSpace version control is exercised with these SQL queries. They are only meant to be starting guides.
"pgadmin"
You can connect to your PostgreSQL database using "pgadmin".
https://www.pgadmin.org
However you must allow connections to the database from other hosts besides "localhost". To do this view the links below:
- http://dba.stackexchange.com/questions/48152/connecting-to-an-external-database-with-pgadmin-iii
- http://www.mad-hacking.net/documentation/linux/applications/postgres/connection-permissions.xml
- http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/c15679_002.htm
See example pg_hba.conf entries;
# Added by H Gibson for BIB IT access host all all 146.232.125.245/24 md5 # Hilton host all all 146.232.212.49/24 md5 # S Kroon host all all 146.232.125.151/24 md5 # Wouter host all all 146.232.129.78/24 md5 # libweb server ## DSpace DB user access host dspace dspace 127.0.0.1/32 md5
DSpace => 5.X
DSpace =< 4.X
General
- Number of database connections
- SELECT count(*) FROM pg_stat_activity;
- Display current query
- SELECT current_query FROM pg_stat_activity;
- Permissions on bitstreams
SELECT * FROM resourcepolicy WHERE resource_type_id = '0'
- Permissions on items
SELECT handle.handle, resourcepolicy.* FROM resourcepolicy, handle WHERE resourcepolicy.resource_type_id = '2' AND resourcepolicy.resource_id = handle.resource_id
- Find all hidden items
- select * from item where discoverable =false;
