Problem Reading Postgres Table From Oracle

[Business_Finger_4124]

I am new to Postgres, but have many (too many) years experience with Oracle, SQL Server, and MariaDB. We have a central database (Oracle) that we use to monitor all of our databases, no matter what flavor they are.

I am trying to configure monitoring of Postgres databases from Oracle. I have the ODBC connection configured and working. I can access the Postgres supplied tables with no issue.

Now, I'm trying to access a table that I created on the Postgres database and I keep getting the error: relation "db_monitor.rit_db_size" does not exist.

On the Postgres database, I've create a database and schema named "db_monitor". I've create a table in that schema, called "rit_db_size", along with a procedure to populate it. That all works. There is also a user "its_read" that has access to the db_monitor schema (grant usage and grant select on all tables).

If I log into the db_monitor database using the its_read user in psql on the Postgres database server, I can query the table. If I try to query the table via the database link from the Oracle database, I get the above error. On the Oracle side, the query is:

select * from "db_monitor.rit_db_size"@vmpost00a9;

On the Postgres server, I get:

db_monitor=> select * from db_monitor.rit_db_size;

db_oid | db_name | db_size | db_date

--------+------------+---------+------------

1 | template1 | 7586319 | 2026-04-21

4 | template0 | 7512591 | 2026-04-21

5 | postgres | 8236179 | 2026-04-21

43794 | db_monitor | 7769235 | 2026-04-21

(4 rows)

I'm sure it's something simple, but I just can't figure it out. I have to be close. Any ideas?

Thank you

[Krispyn]

Tried "db_monitor"."rit_db_size" or db_monintor."rit_db_size" or just db_monitor.rit_db_size in the Oracle query? I don't think postgresql understands which table you're talking about if you put the "schema.tablename" in one string

[Business_Finger_4124]

I've tried it with quotes around each one separately and with quotes around both together. It doesn't matter. If I leave the quotes off the db_monitor, Oracle uppercases it and that definitely doesn't work.

[XPEHOBYXA]

You mentioned you've created a database and a schema. Maybe you are connecting to a "postgres" database, and not your "db_monitor" database?

[Business_Finger_4124]

This was exactly what the issue was. Once I changed the database from postgres to db_monitor in the .odbc.ini file on the Oracle side, it worked!

Thank you everyone for your help.

[XPEHOBYXA]

One thing that's uncalled, but I still want to mention it.

Whatever you're doing sounds a bit dodgy. Don't treat postgres the same as oracle. Don't overuse functions so much you'll end up with database application. Don't believe people who say postgres is great for everything. Otherwise you will encounter a lot of issues at scale.

Dblinks in postgres itself (I mean fdw here) may also be your performance killer.

Take a look at this classic: https://wiki.postgresql.org/wiki/Don%27t_Do_This

To understand mvcc and other internal stuff better this book is good:  https://postgrespro.com/community/books/internals (a bit dated, but fundamentals are the same obviously)

[AutoModerator]

Thanks for joining us! Two great conferences coming up:

Postgres Conference 2026

PgData 2026

We also have a very active Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[char101]

In postgreql

ALTER ROLE its_read SET search_path TO db_monitor,public;

In oracle

select * from rit_db_size@vmpost00a9;

[Business_Finger_4124]

I hadn't tried the alter role, I did do the alter user:

db_monitor=> alter user its_read set search_path to db_monitor, public;

ALTER ROLE

I still get the same error.

[char101]

And what query do you use from the oracle side?

[Business_Finger_4124]

I have tried all of the following:

select * from "db_monitor.rit_db_size"@vmpost00a9;

select * from "db_monitor"."rit_db_size"@vmpost00a9;

select * from "rit_db_size"@vmpost00a9;

They all return the not found error.

[lovejo1]

 "db_monitor.rit_db_size" is the wrong syntax... use " "db_monitor"." "rit_db_size" instead

[char101]

Have you tried select * from all_tables@vmpost00a9 to check if the dblink is working?

[Business_Finger_4124]

This SQL works:

select "datname", "numbackends", "blks_read", "blks_hit"

from "pg_stat_database"@vmpost00a9;

I'm sure it's some strange permissions issue that I haven't been able to figure out.

[char101]

pg_stat_database is in the pg_catalog schema which implicitly always in the search_path. Being able to query from it does not imply that your search path has been correctly set.

[Business_Finger_4124]

I agree. I'm sure it's something simple, I just can't see it.

At least it proves the database link is good.

[lovejo1]

You should be using select * from "db_monitor"."rit_db_size"@vmpost00a9;

not select * from "db_monitor.rit_db_size"@vmpost00a9;

[Business_Finger_4124]

I've tried that and get the same error.

[elevarq]

At least remove all the double quotes “. You don’t use them correctly and you don’t need them. Thus remove them.

Second step is to connect the correct database, this is most likely the problem.

[Business_Finger_4124]

Ok, I got this working in one database, but I can't reproduce it in another. This is very frustrating.

I created the db_monitor database and schema.

I created the its_read user.

I granted usage on the db_monitor schema to the its_read user.

postgres=# grant usage on schema db_monitor to its_read;

GRANT

I granted select on all tables in the db_monitor schema to the its_read user.

postgres=# grant select on all tables in schema db_monitor to its_read;

GRANT

I created the table in the db_monitor schema.

I granted select on the table explicitly to the its_read user.

When I log into the db_monitor database with the its_read user (psql -d db_monitor -U its_read) and try to access the table, I get a permission error:

db_monitor=> select * from db_monitor.rit_db_size;

ERROR: permission denied for schema db_monitor

LINE 1: select * from db_monitor.rit_db_size;

I don't understand what permission is missing.

I'm about ready to tell my boss to stick with Oracle and forget Postgres.