Announcement

  • Article published in BMJ: How to avoid common problems when using ClinicalTrials.gov in research: 10 issues to consider. https://www.bmj.com/content/361/bmj.k1452
  • Please email ctti-aact@duke.edu if you have questions about creating a database account.
  • The aact/aact database account was deactivated on May 31, 2018. Please use your individual database account to log into the live AACT database.

Create Local Database from Static Copy of AACT

If you would like to work with the complete set of clinical trials on your own computer... install PostgreSQL locally, download a static copy of the AACT database, and populate a local PostgreSQL AACT database instance with the data. This page provides the instructions.

1

Install PostgreSQL Database Platform

The PostgreSQL package (open source and freely available) includes a number of nice tools. If you intend to access the public AACT database hosted in the cloud, the only PostgreSQL tool you need is psql. If you would like to download a static copy of AACT to use on your local machine, you'll need the full postgreSQL package including the tools to create and populate the database (createdb & pg_restore). These tools are part of the standard PostgreSQL package, so once installed, you will be able to create your own AACT database.

AACT is currently running on version 9.6.6 of PostgreSQL; it's recommended you install this version or higher. Refer to PostgreSQL Downloads for free software and installation instructions.

2

Download Package Containing Static Copy of AACT Database

Download a zip file that contains the AACT database file. With a single command (see below), this file can be used to automatically configure the database & populate it with the clinical trials dataset. The zip file also contains documentation that describes the database at the time the static copy was created. (The download typically takes 3-7 minutes, depending on your network speed.)

Current Month's Daily Static Copies

[{:name=>"20180717_clinical_trials.zip", :date_created=>"07/17/2018", :size=>"798 MB", :url=>"/static/static_db_copies/daily/20180717_clinical_trials.zip"}, {:name=>"20180715_clinical_trials.zip", :date_created=>"07/15/2018", :size=>"798 MB", :url=>"/static/static_db_copies/daily/20180715_clinical_trials.zip"}, {:name=>"20180714_clinical_trials.zip", :date_created=>"07/14/2018", :size=>"798 MB", :url=>"/static/static_db_copies/daily/20180714_clinical_trials.zip"}, {:name=>"20180713_clinical_trials.zip", :date_created=>"07/13/2018", :size=>"797 MB", :url=>"/static/static_db_copies/daily/20180713_clinical_trials.zip"}, {:name=>"20180712_clinical_trials.zip", :date_created=>"07/12/2018", :size=>"797 MB", :url=>"/static/static_db_copies/daily/20180712_clinical_trials.zip"}, {:name=>"20180711_clinical_trials.zip", :date_created=>"07/11/2018", :size=>"796 MB", :url=>"/static/static_db_copies/daily/20180711_clinical_trials.zip"}, {:name=>"20180710_clinical_trials.zip", :date_created=>"07/10/2018", :size=>"795 MB", :url=>"/static/static_db_copies/daily/20180710_clinical_trials.zip"}, {:name=>"20180709_clinical_trials.zip", :date_created=>"07/09/2018", :size=>"795 MB", :url=>"/static/static_db_copies/daily/20180709_clinical_trials.zip"}, {:name=>"20180708_clinical_trials.zip", :date_created=>"07/08/2018", :size=>"794 MB", :url=>"/static/static_db_copies/daily/20180708_clinical_trials.zip"}, {:name=>"20180707_clinical_trials.zip", :date_created=>"07/07/2018", :size=>"794 MB", :url=>"/static/static_db_copies/daily/20180707_clinical_trials.zip"}, {:name=>"20180706_clinical_trials.zip", :date_created=>"07/06/2018", :size=>"794 MB", :url=>"/static/static_db_copies/daily/20180706_clinical_trials.zip"}, {:name=>"20180705_clinical_trials.zip", :date_created=>"07/05/2018", :size=>"793 MB", :url=>"/static/static_db_copies/daily/20180705_clinical_trials.zip"}, {:name=>"20180704_clinical_trials.zip", :date_created=>"07/04/2018", :size=>"793 MB", :url=>"/static/static_db_copies/daily/20180704_clinical_trials.zip"}, {:name=>"20180703_clinical_trials.zip", :date_created=>"07/03/2018", :size=>"793 MB", :url=>"/static/static_db_copies/daily/20180703_clinical_trials.zip"}, {:name=>"20180702_clinical_trials.zip", :date_created=>"07/02/2018", :size=>"792 MB", :url=>"/static/static_db_copies/daily/20180702_clinical_trials.zip"}, {:name=>"20180701_clinical_trials.zip", :date_created=>"07/01/2018", :size=>"791 MB", :url=>"/static/static_db_copies/daily/20180701_clinical_trials.zip"}]
Downloadable File Date Created Size
20180717_clinical_trials.zip 07/17/2018 798 MB
20180715_clinical_trials.zip 07/15/2018 798 MB
20180714_clinical_trials.zip 07/14/2018 798 MB
20180713_clinical_trials.zip 07/13/2018 797 MB
20180712_clinical_trials.zip 07/12/2018 797 MB
20180711_clinical_trials.zip 07/11/2018 796 MB
20180710_clinical_trials.zip 07/10/2018 795 MB
20180709_clinical_trials.zip 07/09/2018 795 MB
20180708_clinical_trials.zip 07/08/2018 794 MB
20180707_clinical_trials.zip 07/07/2018 794 MB
20180706_clinical_trials.zip 07/06/2018 794 MB
20180705_clinical_trials.zip 07/05/2018 793 MB
20180704_clinical_trials.zip 07/04/2018 793 MB
20180703_clinical_trials.zip 07/03/2018 793 MB
20180702_clinical_trials.zip 07/02/2018 792 MB
20180701_clinical_trials.zip 07/01/2018 791 MB

Monthly Archive of Static Copies

[{:name=>"20180701_clinical_trials.zip", :date_created=>"07/01/2018", :size=>"791 MB", :url=>"/static/static_db_copies/monthly/20180701_clinical_trials.zip"}, {:name=>"20180601_clinical_trials.zip", :date_created=>"06/01/2018", :size=>"780 MB", :url=>"/static/static_db_copies/monthly/20180601_clinical_trials.zip"}, {:name=>"20180501_clinical_trials.zip", :date_created=>"05/01/2018", :size=>"773 MB", :url=>"/static/static_db_copies/monthly/20180501_clinical_trials.zip"}, {:name=>"20180401_clinical_trials.zip", :date_created=>"04/01/2018", :size=>"765 MB", :url=>"/static/static_db_copies/monthly/20180401_clinical_trials.zip"}, {:name=>"20180301_clinical_trials.zip", :date_created=>"03/01/2018", :size=>"755 MB", :url=>"/static/static_db_copies/monthly/20180301_clinical_trials.zip"}, {:name=>"20180201_clinical_trials.zip", :date_created=>"02/01/2018", :size=>"746 MB", :url=>"/static/static_db_copies/monthly/20180201_clinical_trials.zip"}, {:name=>"20180111_clinical_trials.zip", :date_created=>"01/11/2018", :size=>"744 MB", :url=>"/static/static_db_copies/monthly/20180111_clinical_trials.zip"}, {:name=>"20171217_clinical_trials.zip", :date_created=>"12/17/2017", :size=>"737 MB", :url=>"/static/static_db_copies/monthly/20171217_clinical_trials.zip"}, {:name=>"20171102_clinical_trials.zip", :date_created=>"11/02/2017", :size=>"721 MB", :url=>"/static/static_db_copies/monthly/20171102_clinical_trials.zip"}, {:name=>"20171016_clinical_trials.zip", :date_created=>"10/16/2017", :size=>"717 MB", :url=>"/static/static_db_copies/monthly/20171016_clinical_trials.zip"}, {:name=>"20170906_clinical_trials.zip", :date_created=>"09/06/2017", :size=>"701 MB", :url=>"/static/static_db_copies/monthly/20170906_clinical_trials.zip"}, {:name=>"20170811_clinical_trials.zip", :date_created=>"08/11/2017", :size=>"684 MB", :url=>"/static/static_db_copies/monthly/20170811_clinical_trials.zip"}, {:name=>"20170703_clinical_trials.zip", :date_created=>"07/03/2017", :size=>"671 MB", :url=>"/static/static_db_copies/monthly/20170703_clinical_trials.zip"}, {:name=>"20170613_clinical_trials.zip", :date_created=>"06/13/2017", :size=>"661 MB", :url=>"/static/static_db_copies/monthly/20170613_clinical_trials.zip"}, {:name=>"20170502_clinical_trials.zip", :date_created=>"05/02/2017", :size=>"649 MB", :url=>"/static/static_db_copies/monthly/20170502_clinical_trials.zip"}, {:name=>"20170403_clinical_trials.zip", :date_created=>"04/03/2017", :size=>"632 MB", :url=>"/static/static_db_copies/monthly/20170403_clinical_trials.zip"}, {:name=>"20170309_clinical_trials.zip", :date_created=>"03/09/2017", :size=>"628 MB", :url=>"/static/static_db_copies/monthly/20170309_clinical_trials.zip"}, {:name=>"20170218_clinical_trials.zip", :date_created=>"02/18/2017", :size=>"625 MB", :url=>"/static/static_db_copies/monthly/20170218_clinical_trials.zip"}, {:name=>"20170105_clinical_trials.zip", :date_created=>"01/05/2017", :size=>"610 MB", :url=>"/static/static_db_copies/monthly/20170105_clinical_trials.zip"}]
Downloadable File Date Created Size
20180701_clinical_trials.zip 07/01/2018 791 MB
20180601_clinical_trials.zip 06/01/2018 780 MB
20180501_clinical_trials.zip 05/01/2018 773 MB
20180401_clinical_trials.zip 04/01/2018 765 MB
20180301_clinical_trials.zip 03/01/2018 755 MB
20180201_clinical_trials.zip 02/01/2018 746 MB
20180111_clinical_trials.zip 01/11/2018 744 MB
20171217_clinical_trials.zip 12/17/2017 737 MB
20171102_clinical_trials.zip 11/02/2017 721 MB
20171016_clinical_trials.zip 10/16/2017 717 MB
20170906_clinical_trials.zip 09/06/2017 701 MB
20170811_clinical_trials.zip 08/11/2017 684 MB
20170703_clinical_trials.zip 07/03/2017 671 MB
20170613_clinical_trials.zip 06/13/2017 661 MB
20170502_clinical_trials.zip 05/02/2017 649 MB
20170403_clinical_trials.zip 04/03/2017 632 MB
20170309_clinical_trials.zip 03/09/2017 628 MB
20170218_clinical_trials.zip 02/18/2017 625 MB
20170105_clinical_trials.zip 01/05/2017 610 MB

3

Populate Local PostgreSQL Database from the Static Copy

Once PostgreSQL is installed on your local machine and you have downloaded an AACT database package, you can create and populate your own database. You have two alternatives for installing it: 1) via a terminal session using command lines or 2) via a graphical user interface (GUI) such as pgAdmin. The command line option is somewhat simpler, so we've highlighted instructions for this below and provided guidance for using the GUI here.

Database Creation Via Command Line

Open a terminal session
  • On a Mac: press [Command] [Spacebar] and enter 'Terminal' in the 'Spotlight Search' dialog that pops up.
  • On a PC: press [Start] and enter 'CMD' in the command input box.

This should open a terminal session where you can enter the following commands.

Initialize empty postgres database: aact

After postgres is installed on your local machine, enter the following command to initialize the AACT database. (You might get the error message 'already exists' if the database was already created; no harm done.)

        

-> createdb aact createdb: database creation failed: ERROR: database "aact" already exists

Locate and unzip the database package/zipfile

Verify the location of the database package you downloaded in step 2 - both the path and the file name. You'll need to unzip this package and use the data dump file it contains to create and populate your own, local instance of the database.

  • On a Mac: The zip package is probably here: ~/Downloads/YYYYMMDD_clinical_trials.zip (where YYYYMMDD is the date the database was copied).

        

-> unzip ~/Downloads/YYYYMMDD_clinical_trials.zip

This will unpack the files contained in the zip package and save them to the directory where the zip file exists. These files include:

  • schema_diagram.png Database schema diagram that illustrates the relationships between tables.
  • postgres_data.dmp Postgres dump file containing all the instructions and data required to create a local copy of the AACT database.
  • data_dictionary.xlsx Spreadsheet that describes all tables and columns in the AACT database.
  • nlm_protocol_definitions.htmlCopy of the National Library of Medicine: ClinicalTrials.gov protocol-related documentation
  • nlm_result_definitions.htmlCopy of the National Library of Medicine: ClinicalTrials.gov results-related documentation
Create and populate your local database instance

Enter the following command to create all database tables in aact and then populate them with clinical trials data.

This typically takes 10-20 minutes, depending on the speed of your machine.

        

-> pg_restore -e -v -O -x --dbname=postgres --no-owner --clean --create ~/Downloads/postgres_data.dmp

Verify the database

Confirm the download was successful by logging into the database and perusing the data.

  

-> psql aact psql (9.6.1) Type "help" for help. aact=# _

The aact=> prompt indicates that you're in a pSQL console session and can enter SQL queries against the AACT database. A simple example:

  

aact=> select count(*) from studies; count -------- 277865 (1 row) aact=> _

PostgreSQL provides helpful instructions on their website. For Windows, the certified Interactive Installer by EnterpriseDB is a good option. It includes a graphical interface for PostgreSQL (pgAdmin). The following instructions are for Postgres Version 9.5.4, pgAdmin III, and SQL Shell (psql).

Using pgAdmin III

  • Connect to the local Postgres server.
  • Using pgAdmin, select a Server, right-click and select Connect, and enter the password to connect to the Postgres server. (The PostgresSQL selection no longer contains a red X.)
  • Create an empty Postgres database and create/populate it from the static copy of AACT.
  • Using pgAdmin, select Databases, right-click and select New Database.
    • Under the Properties tab, enter a name for the Database and click OK
    • Select the new Database, right-click and select Restore
    • In the File Options section, select the name of the AACT database package as the Filename and select postgres as the Rolename
    • In the Restore Options #1 section, under the Don’t save header, select Owner and Privilege and click Restore.

The restore is complete when the Restore database window shows “Process returned exit code 0.”

Execute a sample query Using pgAdmin, select the Database and select SQL from the top menu.

In the SQL window, type...

          

select count(*) from studies

Click the right-arrow in the top menu to execute the sql. The result shows in the results window.

Using psql

  • Connect to the local Postgres server.
  • Using psql, click Enter to confirm:
  • Server [localhost] : (Press [Enter]}
  • Database [postgres] :
  • Port [5432] : (Press [Enter])
  • Username [postgres] : aact
  • Enter the password you created for Password for user postgres:
  • psql (9.5.4)
  • Type "help" for help.
  • postgres=#

We are compiling a list of commonly encountered problems...

Error Possible Resolution

"pg_restore: [archiver (db)] could not execute query: ERROR: unrecognized configuration parameter "row_security"

Upgrade your version of PostgreSQL to 9.6.6.

pg_restore: connecting to database for restore

pg_restore: creating DATABASE "aact"

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 3543; 1262 1525913 DATABASE aact dbadmin

pg_restore: [archiver (db)] could not execute query: ERROR: database "aact" already exists

Make sure to include --clean in the command to drop the table before creating it.