https://linen.dev logo
Join Slack
Powered by
Has anyone gotten `tap-postgres` working in meltan...
# announcements
j
Has anyone gotten 
tap-postgres
working in meltano? I've tried several variations of the 
select:
configuration including leaving it out altogether but every time I get
Copy code
Running extract & load...
No state was found, complete import.
INFO No properties were selected
Extract & load complete!
Transformation skipped.
d
Can you confirm discovery mode works? 
meltano invoke tap-postgres --discover
, and 
meltano select --list --all tap-postgres
I assume you've included the 
discover
and 
catalog
capabilities
in your custom plugin definition?
j
I'm using github.com/singer-io/tap-postgres is the pipelinewise one better supported?
ah. I ran 
meltano select --list --all tap-postgres
and I think my select format was wrong
It wasn't clear to me that select is at the property/column level, I assumed it was at the stream/table level
d
That'll do it! Still, listing no 
select
at all should fall back on 
*.*
and select everything, so it's odd you weren't seeing that
It wasn't clear to me that select is at the property/column level, I assumed it was at the stream/table level
How do you mean exactly? Selection requires both a stream/table/entity and property/column/attribute identifier, so it's at both levels 😛
j
I hadn't really dug into how 
select
works. I was trying
Copy code
select:
- users
instead of
Copy code
select:
- users.*
(although thanks to list I now know the db name and schema are in there too, like 
dbname-schemaname-tablename.columnname
Although running it explicitly with 
*.*
is doing the same thing
d
Doing the same thing, as in "No properties were selected"?
j
right
here's my config:
Copy code
- name: tap-postgres
    namespace: tap_postgres
    pip_url: tap-postgres
    executable: tap-postgres
    capabilities:
    - catalog
    - discover
    - state
    settings:
    - name: host
      env: TAP_POSTGRES_HOST
    - name: user
      env: TAP_POSTGRES_USER
    - name: password
      env: TAP_POSTGRES_PASSWORD
    - name: port
      env: TAP_POSTGRES_PORT
    - name: dbname
      env: TAP_POSTGRES_DBNAME
    - name: ssl
      env: TAP_POSTGRES_SSL
    config:
      ssl: true
    select:
      - '*.*'
d
Interesting, that definitely looks like a bug. I assume 
meltano select --list tap-postgres
(without 
--all
) doesn't list any selected entities/attributes either, then?
j
it lists them, and they are all selected
maybe it's a problem on the tap side then?
d
Maybe? Are you seeing 
selected: true
where you'd expect it in 
.meltano/run/tap-postgres/tap.properties.json
?
j
yep that file looks right
I'm looking at this line in the source code: https://github.com/singer-io/tap-postgres/blob/f8cd6b57edc2e751f5b85020ee4165201f22b1d1/tap_postgres/__init__.py#L700 maybe I should try properties instead of catalog?
d
And if you run 
meltano --log-level=debug elt ...
, the 
Invoke:
command looks right?
Yes, that'll be it!
Good catch 🙂
j
yep that did it! The tap said it supported catalog though /shrug
d
Oh well! Always good to double-check capabilities when a tap isn't behaving as expected 🙂
j
out of curiosity, does 
*
in select support partial matches, e.g. 
mydatabase-public-*.tableprefix_*
?
d
That's also mentioned under https://meltano.com/#meltano-select, but not https://meltano.com/docs/command-line-interface.html#extractor-extra-select, which just refers to the 
meltano select
docs
a
just jumping onto this thread - I followed the steps at https://meltano.com/tutorials/postgres-with-postgres.html#adding-a-custom-extractor and as mentioned in those docs, pinned the tap-postgres version to 0.0.61. Is that still necessary?
also, when I run 
meltano invoke tap-postgres --discover
it looks like Meltano is trying to connect to various different databases which aren't mine:
Copy code
$ meltano invoke tap-postgres --discover
INFO Running DB discovery: SELECT datname
            FROM pg_database
            WHERE datistemplate = false
              AND datname != 'rdsadmin' with itersize 20000
INFO (d6mtc0onm2a1qf) Testing connectivity...
WARNING Unable to connect to d6mtc0onm2a1qf. This maybe harmless if you have not desire to replicate from this database: "FATAL:  permission denied for database "d6mtc0onm2a1qf"
DETAIL:  User does not have CONNECT privilege.
"
INFO (d78t7injvja444) Testing connectivity...
WARNING Unable to connect to d78t7injvja444. This maybe harmless if you have not desire to replicate from this database: "FATAL:  permission denied for database "d78t7injvja444"
DETAIL:  User does not have CONNECT privilege.
"
INFO (db88dnpgtn22uc) Testing connectivity...
WARNING Unable to connect to db88dnpgtn22uc. This maybe harmless if you have not desire to replicate from this database: "FATAL:  permission denied for database "db88dnpgtn22uc"
DETAIL:  User does not have CONNECT privilege.
"
INFO (d8jhl1v17cdp2j) Testing connectivity...
WARNING Unable to connect to d8jhl1v17cdp2j. This maybe harmless if you have not desire to replicate from this database: "FATAL:  permission denied for database "d8jhl1v17cdp2j"
DETAIL:  User does not have CONNECT privilege.
"
INFO (dbn7lb7qvmt4t4) Testing connectivity...
WARNING Unable to connect to dbn7lb7qvmt4t4. This maybe harmless if you have not desire to replicate from this database: "FATAL:  permission denied for database "dbn7lb7qvmt4t4"
DETAIL:  User does not have CONNECT privilege.
(I'm trying to use a Heroku Postgres database as the tap source)
No worries, think I've found the solution - there was a setting called 
filter_dbs
which I had to set - I didn't know about it but read through the source for tap_postgres and found it there
d
as mentioned in those docs, pinned the tap-postgres version to 0.0.61. Is that still necessary?
No, there's no need to use that specific version, although pinning versions in 
meltano.yml
has some advantages, just like pinning dependencies does in general
2 Views
Open in Slack
PreviousNext
Powered by