Difference between revisions of "SUNScholar/SQL Tips"
Jump to navigation
Jump to search
m (→"pgadmin") |
m (→"pgadmin") |
||
| Line 11: | Line 11: | ||
You can connect to your PostgreSQL database using "pgadmin". | You can connect to your PostgreSQL database using "pgadmin". | ||
https://www.pgadmin.org | https://www.pgadmin.org | ||
| − | However you must allow connections to the database from other hosts besides "localhost". To do this | + | However you must allow connections to the database from other hosts besides "localhost". |
| − | + | ||
| − | * | + | To do this follow the steps below: |
| − | * | + | |
| + | ===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. | ||
| + | <pre> | ||
| + | listen_addresses = '*' # what IP address(es) to listen on; | ||
| + | # comma-separated list of addresses; | ||
| + | # defaults to 'localhost'; use '*' for all | ||
| + | # (change requires restart) | ||
| + | </pre> | ||
| + | ===Step 2=== | ||
| + | Modify the '''pg_hba.conf''' file to allow other hosts to connect. | ||
See example pg_hba.conf entries; | See example pg_hba.conf entries; | ||
| Line 27: | Line 41: | ||
host dspace dspace 127.0.0.1/32 md5 | host dspace dspace 127.0.0.1/32 md5 | ||
</pre> | </pre> | ||
| + | |||
| + | ===References=== | ||
| + | *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 | ||
==[[SUNScholar/SQL Tips/5.X|DSpace => 5.X]]== | ==[[SUNScholar/SQL Tips/5.X|DSpace => 5.X]]== | ||
Revision as of 12:58, 27 September 2016
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.
Contents
"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:
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;
# 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
References
- 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
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;
