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
andschema-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
-
Set default schema -
set search_path=<schema-name>;
-
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";
- 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;