Restore of the static copy of the AACT database fails

[cosbicentre]

Restoring the static copy of the database fails immediately with this error:

postgres@lnx$ pg_restore -e -v -O -x -d aact --no-owner /tmp/aact/postgres_data.dmp
pg_restore: connecting to database for restore
pg_restore: creating DATABASE PROPERTIES "aact"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3686; 0 0 DATABASE PROPERTIES aact ctti
pg_restore: [archiver (db)] could not execute query: ERROR:  role "ctti" does not exist
    Command was: ALTER ROLE ctti IN DATABASE aact SET search_path TO 'ctgov', 'support', 'public';

update: If I add the --clean argument as stated in the docs, I get a different error:

postgres@lnx$ pg_restore -e -v -O -x -d aact --clean --no-owner /tmp/aact/postgres_data.dmp
pg_restore: connecting to database for restore
pg_restore: dropping FK CONSTRAINT study_references study_references_nct_id_fkey
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3436; 2606 37207856 FK CONSTRAINT study_references study_references_nct_id_fkey ctti
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "ctgov" does not exist
    Command was: ALTER TABLE ONLY ctgov.study_references DROP CONSTRAINT study_references_nct_id_fkey;

I used this static copy: https://aact.ctti-clinicaltrials.org/static/static_db_copies/monthly/20200101_clinical_trials.zip

Any idea of why it fails?

Thanks

[micronix]

Hello,

you need to create a postgres user with the name ctti.

You can try the command "createuser --interactive" you will have to give the user the correct permissions or also create a superuser if this is your own database.

On Tue, Jan 7, 2020 at 10:31 AM cosbicentre notifications@github.com wrote:

Restoring the static copy of the database fails immediately with this error:

postgres@lnx$ pg_restore -e -v -O -x -d aact --no-owner /tmp/aact/postgres_data.dmp pg_restore: connecting to database for restore pg_restore: creating DATABASE PROPERTIES "aact" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3686; 0 0 DATABASE PROPERTIES aact ctti pg_restore: [archiver (db)] could not execute query: ERROR: role "ctti" does not exist Command was: ALTER ROLE ctti IN DATABASE aact SET search_path TO 'ctgov', 'support', 'public';

I used this static copy: https://aact.ctti-clinicaltrials.org/static/static_db_copies/monthly/20200101_clinical_trials.zip

Any idea why?

Thanks

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ctti-clinicaltrials/aact/issues/786?email_source=notifications&email_token=AAAHH4R3MPLZGXLJIYLGD3TQ4SN3NA5CNFSM4KD25XP2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4IEQTUOA, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAHH4Q7EQR6JQ6TOP5BH73Q4SN3NANCNFSM4KD25XPQ .

[Omer-N]

I've tried on few static copies of the last months as well. I got the following error:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3449; 2606 39641554 FK CONSTRAINT baseline_measurements baseline_measurements_result_group_id_fkey ctti
pg_restore: [archiver (db)] could not execute query: server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
    Command was: ALTER TABLE ONLY ctgov.baseline_measurements
    ADD CONSTRAINT baseline_measurements_result_group_id_fkey FOREIGN KEY (result_group_id) REFERENCES ctgov.result_groups(id);

Seems like I fail for another reason, any idea why? I work with the online version in the meantime but I would prefer to have it locally for more demanding computations.

[ZhangAngus]

@Omer-N Please check your postgresql version. Version 12 or above is required.

[micronix]

closing this issue since it looks like it has been resolved