How does Meltano handle referential integrity/dependencies between tables? I’m getting errors when running FULL_TABLE replication from tap-postgres to target-postgres: 2022-08-05T194324.488559Z [info ] psycopg2.errors.ForeignKeyViolation: insert or update on table “enrollments” violates foreign key constraint “fk_enrollment_bpi” cmd_type=loader name=target-postgres run_id=fd613f0b-ac5b-433a-928d-8f4c4ffe000f state_id=tap-postgres-to-tgt-postgres stdio=stderr 2022-08-05T194324.489048Z [info ] DETAIL: Key (benefit_plan_id)=(<redacted>) is not present in table “benefit_plans”. cmd_type=loader name=target-postgres run_id=fd613f0b-ac5b-433a-928d-8f4c4ffe000f state_id=tap-postgres-to-tgt-postgres stdio=stderr How can I fix this?

Typically constraints are utilized in OLTP database design whereas OLAP doesn't typically use constraints. Thats not a hard and fast rule ofc but the idea is you want to analyze all the data, quarantine records which dont look like what you want, but you 100% dont need a FK constraint for the purpose that an OLTP use-case would need it. So you should replicate data to a table without a constraint Then move the data afterwards within the database however you need

This use case is a bit different. I’m trying to use Meltano to make a copy of an OLTP database. I had also planned to try OLTP replication use cases. I have been considering using Meltano for all (or most) data movement use cases. Perhaps Meltano is not designed for these types of use cases?

Is this a simple ordering problem? i.e. are you using a wildcard in your

select

rule that's causing the child table to be replicated before the parent?

Yes. I’m trying to get all the tables: select: - ‘*.*’

As annoying as it might be, one possible fix is to specify the tables in a foreign key-friendly order. 😉 That aside, I'll second what @alexander_butler said though - Meltano's role is to move data from A to B, where A and B can be basically anything, not just databases. If your destination is an RDBMS and you want referential integrity in the destination, you're going to have to move the data first, then transform it into an RI-friendly state separately.

Ok. Thanks for the feedback. Further clarification on the use case: making a copy of a prod db into a lower level RDS instance [e.g. dev, qa, stage]. We typically copy the entire RDS instance, but sometimes we need to make a copy of a single Postgres db. We can go through the backup/restore process, but it’d be nice to use something more streamlined and automated — and less painful, like Meltano.

Thanks for the link.