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.

Windows Users: Click here for step-by-step instructions to install PostgreSQL & create/load a local copy of AACT on a Windows machine.


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 11.1 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 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

Downloadable File Date File Was Created Size 06/29/2022 1.38 GB 06/28/2022 1.38 GB 06/27/2022 1.37 GB 06/26/2022 1.38 GB 06/25/2022 1.38 GB 06/24/2022 1.38 GB 06/23/2022 1.38 GB 06/22/2022 1.38 GB 06/21/2022 1.38 GB 06/20/2022 1.38 GB 06/18/2022 1.38 GB 06/16/2022 1.37 GB 06/15/2022 1.37 GB 06/14/2022 1.37 GB 06/13/2022 1.37 GB 06/12/2022 1.37 GB 06/11/2022 1.37 GB 06/10/2022 1.37 GB 06/09/2022 1.37 GB 06/08/2022 1.37 GB 06/07/2022 1.37 GB 06/06/2022 1.36 GB 06/05/2022 1.36 GB 06/04/2022 1.36 GB 06/03/2022 1.36 GB 06/02/2022 1.36 GB 06/01/2022 1.36 GB

Monthly Archive of Static Copies

Downloadable File Date File Was Created Size 05/01/2022 1.34 GB 04/01/2022 1.32 GB 03/01/2022 1.29 GB 02/01/2022 1.27 GB 01/04/2022 1.25 GB 12/01/2021 1.28 GB 11/01/2021 1.23 GB 10/04/2021 1.22 GB 08/01/2021 1.28 GB 06/01/2021 1.26 GB 05/01/2021 1.24 GB 04/01/2021 1.16 GB 03/01/2021 1.14 GB 02/01/2021 1.12 GB 01/01/2021 1.11 GB 12/01/2020 1.1 GB 11/01/2020 1.09 GB 10/01/2020 1.07 GB 09/01/2020 1.06 GB 08/01/2020 1.05 GB 07/01/2020 1.04 GB 06/01/2020 1.02 GB 05/01/2020 1.01 GB 04/01/2020 1 GB 03/01/2020 1020 MB 02/01/2020 1000 MB 01/01/2020 991 MB 12/01/2019 980 MB 11/01/2019 971 MB 10/01/2019 959 MB 09/01/2019 948 MB 08/01/2019 936 MB 07/01/2019 924 MB 06/01/2019 913 MB 05/01/2019 901 MB 04/01/2019 888 MB 03/01/2019 877 MB 02/01/2019 866 MB 01/01/2019 854 MB 12/01/2018 844 MB 11/01/2018 834 MB 10/01/2018 822 MB 09/01/2018 813 MB 08/01/2018 800 MB 07/01/2018 791 MB 06/01/2018 780 MB 05/01/2018 773 MB 04/01/2018 765 MB 03/01/2018 755 MB 02/01/2018 746 MB 01/11/2018 744 MB 12/17/2017 737 MB 11/02/2017 721 MB 10/16/2017 717 MB 09/06/2017 701 MB 08/11/2017 684 MB 07/03/2017 671 MB 06/13/2017 661 MB 05/02/2017 649 MB 04/03/2017 632 MB 03/09/2017 628 MB 02/18/2017 625 MB 01/05/2017 610 MB

On Novermber 15, 2021 our database structured changed slightly. Monthly files generated after November 15, 2021 are not backwards compatible. They can still be loaded into an SQL server or used.

Click here to see a list of these archived files


Populate Local PostgreSQL Database from the Static Copy

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

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.


-> unzip ~/Downloads/

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

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

Current Month's Daily Static Copies

Monthly Archive of Static Copies

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=#