Postgres utilities

On this page we have collected some postgres-utilities, or just commands and sql-statements that we've used ourselves in working with OpenClinica. The topics are:

- creating an e-mail-list
- a list of all databases
- a list of all tables of your OpenClinica database
- vacuuming your database for better performance
- reindexing your database for better performance
- locking accounts
- when template1 is not UTF8
- and one little thing about pg_hba.conf

creating an e-mail-list

If you want to send out a mailing to all users of your OpenClinica-instance, it is easy if you can get the mail-addresses from the UserAccounts. In order to get this, start psql and connect to the OpenClincia database
cd /usr/bin
sudo -u postgres ./psql
\c openclinica
Now you can issue the select statement and write the result to a file:
copy (select email||', ' from user_account) to '/tmp/mailaddresses.txt';
If you would like to have only the enabled, non-locked accounts filter on status_id:
copy (select email||', ' from user_account where status_id=1) to '/tmp/mailaddresses.txt';

fig. 1: writing mail-addresses to a file

If you're working with pgAdmin, the command should be
copy (select email||', ' from user_account) to 'c:/temp/mailaddresses.txt';

fig. 2: with pgAdmin

a list of all databases

To list all databases, for example if you want to check your OpenClinica database was created, login with putty to your server and type:
cd /usr/bin
sudo -u postgres ./psql -l
Your listing should look something like:

a list of all tables of your OpenClinica database

Connect to your server with putty and start a psql session:
sudo -u postgres ./psql
Now connect to the openclinica database:
\c openclinica.
The prompt changes to openclinica=#. You can now issue the command \dt and your listing will be something like:

vacuuming your database for better performance

Connect to your server with putty and start a psql session:
sudo -u postgres ./psql
. Now connect to the openclinica database: \c openclinica.
The prompt changes to openclinica=#. You can now issue the command
vacuum verbose analyse;
This will free disk space and improve your queries. The last bit of your ouput will be:

INFO: index "measurement_unit_name_key" now contains 13 row versions in 2 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "measurement_unit": found 0 removable, 13 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.measurement_unit"
INFO: "measurement_unit": scanned 1 of 1 pages, containing 13 live rows and 0 dead rows; 13 rows in sample, 13 estimated total rows
INFO: free space map contains 213 pages in 213 relations
DETAIL: A total of 3456 page slots are in use (including overhead).
3456 page slots are required to track all free space.
Current limits are: 153600 page slots, 1000 relations, using 965 kB.
VACUUM

reindexing your database for better performance

A more drastic measure to improve performance is reindexing your database. Before you do this, make a backup (you can find a page about this here).

Again, you must login to the server on which your postgres is running and start a psql-session.
sudo -u postgres ./psql
and then connect to the openclinica database:
\c openclinica.
Now you can issue the command to rebuild the indexes:
reindex database openclinica;
And quit psql with \q.

what if I want to use pgAdmin?

You're lucky if you can use pgAdmin, because it's even easier. Choose your database and right-click on it.

fig. 3: pgAdmin

Choose option "Maintenance" and there you have in one window both vacuum and reindex. Choose "FULL" if you can lock your database, and not when other users are accessing it. With option "ANALYZE" postgres uses new data to optimise query-execution-plans.

fig. 4: Vacuum and Reindex

locking accounts

You may want to lock accounts automatically if users have not logged in for 100 days. To do this you can use the following script:

\c openclinica
select 'the following accounts will be set to Locked: ' as title ;
SELECT user_id, user_name, first_name, last_name, status_id, account_non_locked, date_lastvisit
FROM user_account
WHERE date_lastvisit < current_date - 100 and status_id=1
ORDER by date_lastvisit;

Update user_account set status_id=6, account_non_locked=false WHERE
date_lastvisit < current_date - 100 and status_id=1;

select 'the following accounts are still unlocked: ' as title ;
SELECT user_id, user_name, first_name, last_name, status_id, account_non_locked, date_lastvisit
FROM user_account
WHERE date_lastvisit < current_date - 100 and status_id=1
ORDER by date_lastvisit;
select 'end of script' as title ;

If you save this script you can schedule it as a cron-job.

when template1 is not UTF8

On rare occassions you may receive an error message when creating your openclinica database, stating you should Change Default Encoding of New Databases To UTF-8

UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
DROP DATABASE template1;
CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
\c template1
VACUUM FREEZE;
\c postgres

and one little thing about pg_hba.conf

On some Debian-distro's postgresql is set up in such a way that a user can only access databases that have the same name as their OS-name. So user clinica is not allowed to connect to database openclinica. To correct this go to pg_hba.conf and edit the line

# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident

to:

local all all password

Other how-to-pages can be found here.

this page was last reviewed November 2013

trial data solutions

datamanagement-expertise for academic & non-profit trials