SUNScholar/SQL Tips

Back to System Admin

 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 follow the steps below: (PLEASE NOTE: Doing the following creates a security risk. You have been warned.)

Step 1
Enable access from other hosts.

Modify the postgresql.conf file and change the listen_addresses parameter to allow "wildcard" hosts to connect.

See example below. listen_addresses = '*' 		# what IP address(es) to listen on; # comma-separated list of addresses; # defaults to 'localhost'; use '*' for all # (change requires restart)

Step 2
Modify the pg_hba.conf file to allow other hosts to connect.

See example pg_hba.conf entries; 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
 * 1) Added by H Gibson for BIB IT access

host   dspace       dspace      127.0.0.1/32         md5
 * 1) DSpace DB user access

General

 * Number of database connections
 * SELECT count(*) FROM pg_stat_activity;


 * Display current query
 * SELECT current_query FROM pg_stat_activity;

SELECT * FROM resourcepolicy WHERE resource_type_id = '0' SELECT handle.handle, resourcepolicy.* FROM resourcepolicy, handle WHERE resourcepolicy.resource_type_id = '2' AND resourcepolicy.resource_id = handle.resource_id
 * Permissions on bitstreams
 * Permissions on items
 * Find all hidden items
 * select * from item where discoverable =false;