Skip to main content

Creating Publication

To create a publication for the dataset, run the following commands:

Replace the following variables:

  • publication-name, database-name, container-name with the parameters used in database setup.
  • pg-user with postgres user for replication role.
  • schema-1 and schema-2 with schemas for creating publication.
DATABASE_NAME=<database-name>
PUBLICATION=<publication-name>
CONTAINER_NAME=<host-name>
USER=<pg-user>
SCHEMA1=<schema-1>
SCHEMA2=<schema-2>
  • Create publication for dataset:
docker exec -it $CONTAINER_NAME psql -h localhost -U postgres -d $DATABASE_NAME -c "CREATE PUBLICATION $PUBLICATION FOR TABLES IN SCHEMA <replace-with-schema-1>, <replace-with-schema-2>;"
docker exec -it $CONTAINER_NAME psql -h localhost -U postgres -d $DATABASE_NAME -c "ALTER TABLE <replace-with-schema-1>.COHORT REPLICA IDENTITY FULL; ALTER TABLE <replace-with-schema-1>.COHORT_DEFINITION REPLICA IDENTITY FULL; ALTER TABLE <replace-with-schema-2>.COHORT REPLICA IDENTITY FULL; ALTER TABLE <replace-with-schema-2>.COHORT_DEFINITION REPLICA IDENTITY FULL;"
docker exec -it $CONTAINER_NAME psql -h localhost -U postgres -d $DATABASE_NAME -c "ALTER ROLE <replace-with-user> REPLICATION;"

(Example for synpuf1k setup)

GIT_BASE_DIR=$(pwd)
PROJECT_NAME=$(grep -E '^PROJECT_NAME=' .env 2>/dev/null | awk -F'=' '{print $2}' | tr -d '"')
PROJECT_NAME=${PROJECT_NAME:-"d2e"}
CONTAINER_NAME=$PROJECT_NAME-minerva-postgres-1
DATABASE_NAME=alpdev_pg
PUBLICATION=alpdev_pg_publication
docker exec -it $CONTAINER_NAME psql -h localhost -U postgres -d $DATABASE_NAME -c "CREATE PUBLICATION $PUBLICATION FOR TABLES IN SCHEMA cdmdefault, cdmvocab;"
docker exec -it $CONTAINER_NAME psql -h localhost -U postgres -d $DATABASE_NAME -c "ALTER ROLE postgres_tenant_admin_user REPLICATION;"

Troubleshooting

If you see the below error in logs

ERROR: replication client error: replica identity 'n' not supported

Do the following

  1. Set default schema - set search_path=<schema-name>;

  2. Run the following sql statements to drop the relevant views and sequences

drop view if exists "VIEW::GDM.CONSENT_BASE" cascade;
drop view if exists "VIEW::GDM.QUESTIONNAIRE_RESPONSE_BASE" cascade;
drop view if exists "VIEW::GDM.RESEARCH_SUBJECT_BASE" cascade;
drop view if exists "VIEW::OMOP.CONCEPT" cascade;
drop view if exists "VIEW::OMOP.COND" cascade;
drop view if exists "VIEW::OMOP.COND_ERA" cascade;
drop view if exists "VIEW::OMOP.COND_ICD10" cascade;
drop view if exists "VIEW::OMOP.DEATH" cascade;
drop view if exists "VIEW::OMOP.DEVICE_EXPOSURE" cascade;
drop view if exists "VIEW::OMOP.DOSE_ERA" cascade;
drop view if exists "VIEW::OMOP.DRUG_ERA" cascade;
drop view if exists "VIEW::OMOP.DRUG_EXP" cascade;
drop view if exists "VIEW::OMOP.EPISODE" cascade;
drop view if exists "VIEW::OMOP.EPISODE_EVENT" cascade;
drop view if exists "VIEW::OMOP.GDM.PATIENT" cascade;
drop view if exists "VIEW::OMOP.MEAS" cascade;
drop view if exists "VIEW::OMOP.OBS" cascade;
drop view if exists "VIEW::OMOP.OBS_PER" cascade;
drop view if exists "VIEW::OMOP.PARTICIPANT_TOKEN" cascade;
drop view if exists "VIEW::OMOP.PATIENT" cascade;
drop view if exists "VIEW::OMOP.PP_PER" cascade;
drop view if exists "VIEW::OMOP.PROC" cascade;
drop view if exists "VIEW::OMOP.SPEC" cascade;
drop view if exists "VIEW::OMOP.VISIT" cascade;
drop sequence if exists "SEQ::OBSERVATION";
  1. Confirm if there are not any specific objects with replica identity n in the CDM schema, by running this statement
SELECT relname, relreplident FROM pg_class WHERE relreplident = 'n' and relname not like 'pg_%' order by relname;