Create Local Database from Static Copy of AACT

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 11.5 of PostgreSQL; it's recommended you install this version or higher. You many download PostgreSQL from the PostgreSQL Download Site or this Enterprise DB site.

We've provided a few screenshots and basic instructions here to help guide you through the installation process.

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.)

Once PostgreSQL is installed on your local machine and you have downloaded a static copy of the AACT database, 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.

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.html - Copy of the National Library of Medicine: ClinicalTrials.gov protocol-related documentation.
• nlm_result_definitions.html - Copy of the National Library of Medicine: ClinicalTrials.gov results-related documentation.

Create and populate your local database instance

Enter the following command(s) to create the AACT database on your local machine & populate it with clinical trials data.

If this is the first time you're creating a local copy of the AACT database, enter these commands from a terminal session:

        

          ->  createdb aact  
          ->  pg_restore -e -v -O -x -d aact --no-owner ~/Downloads/postgres_data.dmp
        
      

If the AACT database already exists on your local machine and you want to refresh it with another version, note that older dump files may not be compatible with your current database. This is because we make changes to schema structure from time to time as well as upgrade PostgreSQL.

To refresh your existing database use this command:

        

          ->  pg_restore -e -v -O -x -d aact --clean --no-owner ~/Downloads/postgres_data.dmp
        
      

This typically takes 5-15 minutes, depending on the speed of your machine.

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 commands. AACT tables are saved in a schema named ctgov. You need to add this schema to your pSQL search path:

  

   aact=#  alter role your-username in database aact set search_path = ctgov, public;
    ALTER ROLE 
    aact=# _
  

You will need to logout of psql (with command \q) and then back in for the search path to take effect. Then you can query the database. For example, to find the number of studies in the database:

  

   aact=#  select count(*) from studies;
     
      count
      --------
      387486
      (1 row)
     
    aact=# _                                                                                                                                              As of 08/24/21