The PostgreSQL snapshots contain complete dumps of the AACT database that can be loaded into your local PostgreSQL installation. This option is ideal for users who need to perform complex queries, want the best performance, or need to integrate AACT data with other databases or tools.
The AACT system provides access to the 30 most recent daily snapshots along with permanent monthly archives that are created on the first day of each month. This ensures you have both current data and historical reference points.
PostgreSQL Installation: You must have PostgreSQL installed on your system to use these database dumps. If you haven't installed PostgreSQL yet, visit the official PostgreSQL website to download the appropriate version for your operating system.
Version Compatibility: We recommend using PostgreSQL version 14 or higher for working with current AACT database dumps. The pg_dump tool used to create these snapshots is from PostgreSQL 14, and the dumps may contain features that aren't compatible with earlier PostgreSQL versions.
System Requirements: We recommend at least 8GB of RAM and 10GB of available disk space for optimal performance when working with the full AACT database.
Technical Knowledge: Basic familiarity with command-line operations and SQL queries is helpful but not required. These instructions provide step-by-step guidance for all necessary commands.
Choose between the current snapshot on the Downloads page or explore past archives if you need older data.
Locate the downloaded ZIP file in your system's Downloads folder. Extract its contents to a folder of your choice using your operating system's extraction tool or the command line.
The primary file you'll need is postgres.dmp, which contains the PostgreSQL database dump. The archive may also include supplementary files to help you understand the database structure and field definitions.
To better understand the database structure, we recommend reviewing the schema diagram and documentation available on our website.
Open a terminal or command prompt and run the following command to create a new database called "aact":
createdb aact
If you want to use a different database name, replace "aact" with your preferred name in this command and the next one.
Use the pg_restore command to load the database dump into your newly created database:
pg_restore -e -v -O -x -d aact --no-owner path/to/postgres.dmp
Replace path/to/postgres.dmp with the actual path to where you extracted the dump file. This process typically takes 5-15 minutes depending on your system's performance.
If you already have an AACT database and want to replace it with the new data, use this command instead:
pg_restore -e -v -O -x -d aact --clean --no-owner path/to/postgres.dmp
Parameter explanation:
You can now connect to your local AACT database using any PostgreSQL client. To connect using the command-line client:
psql -d aact
Alternatively, you can use GUI tools like pgAdmin, DBeaver, Beekeeper Studio, or Postico to connect to and query your database with a visual interface.
For a quick test to verify your database is working, try running this simple query after connecting:
SELECT count(*) FROM studies;
Permission Errors: If you encounter permission issues during database creation or restoration, you may need to run the commands with administrator/sudo privileges or check that your PostgreSQL user has the appropriate permissions.
Database Already Exists: If you get an error saying the database already exists, you can either drop the existing database with dropdb aact before creating it again, or use the --clean option in pg_restore as shown in step 4.
Memory or Disk Space Issues: If restoration fails due to insufficient resources, try closing other applications to free up memory, or ensure you have adequate disk space available.