https://linen.dev logo
Join Slack
Powered by
Can I use a pattern for dbname in target-postgres?...
# troubleshooting
c
Can I use a pattern for dbname in target-postgres? I have a common set of data (geo lookup information) that I need to load into up to 50 databases, one for each US state. The databases are all named like 
vs_ny_main
where 'ny' is the abbreviation for New York. I would really rather not configure 
loaders:
in my meltano.yml for each state as they will be slowly added over time, and some servers may have some states and other servers may have different ones. Is there any way to configure the target-postgres plugin to dynamically support multiple databases? Or, is there a way to cause meltano to get list of matching databases on the server and then for each database run the pipeline?
a
@chrish - How are you extracting the data? Are you using inheritance to create multiple tap instances, or does the same tap get data from all dbs?
If the latter, do you have to invoke the tap multiple times, or does it get all dbs in one go?
c
The data source is a couple of csv files and I'm using tap-csv.
While I would really like to loop through a list of databases that I obtain from a query, in the interim since I have a small number is there a way to configure target-postgres to connect to multiple databases that are manually configured? I can have a list of 
files
which are really file paths in tap-csv. Can I create a list of 
databases
with target-postgres? Hacking around, I haven't figured it out yet - but I'm going to crack open the code and look deeper.
after reading through the 3 different postgres targets, it doesn't appear that any support a list. and after looking more closely at meltano.yml, it seems that I might need to create an environment for each combination of dev|prod and state. so in my case, with 2 state databases currently, I would need dev-state1, dev-state2, prod-state1, prod-state2. Does this sound right? Then I would need to run the 
meltano run
command 4 times, one for each environment. Am I understanding the current capabilities correctly?
with the transferwise target-postgres, I can specify a list under loaders like below, but it is only sending data to the first one.
Copy code
loaders:
      - name: target-postgres
        config:
          user: centurion
          dbname: vs_id_main
          default_target_schema: public
          primary_key_required: false
      - name: target-postgres
        config:
          user: centurion
          dbname: vs_oh_main
          default_target_schema: public
          primary_key_required: false
d
@chrish Did you see plugin inheritance? https://docs.meltano.com/guide/configuration#multiple-plugin-configurations. That’s the more appropriate 
meltano.yml
feature to use than environments
You can also dynamically run 
meltano run
with the same 
target-postgres
but different settings by injecting the proper values using environment variables: https://docs.meltano.com/guide/configuration#configuring-settings. You could have a script that loops over the target databases, and runs 
meltano run
for each with the appropriate env.
c
@douwe_maan - No, I had not seen plugin inheritance yet! But I was envisioning it as a was doing a lot of copy/paste. Nice! I haven't tested this yet, but am I on the right track, being able to use inheritance on loaders as well as extractors/taps as mentioned in the doc?
Copy code
loaders:
      - name: target-postgres-id
        inherit_from: target-postgres
        config:
          user: postgres
          dbname: id
          default_target_schema: public
          primary_key_required: false
      - name: target-postgres-oh
        config:
          dbname: oh
I had thought about having a script set env vars for each database, and then running 
meltano run
, but I'm trying to avoid having scripts to run things where I can - fewer layers to maintain. Though I may end up scripting it out if I end up with a number of different postgres servers with different sets of databases. Right now, with the inheritance feature, I can get down to having just two different commands I run, one for dev and one for prod, and I want those two split anyway. So I'm happy!
3 Views
Open in Slack
PreviousNext
Powered by