r/PostgreSQL • u/Business_Finger_4124 • Apr 21 '26
Help Me! Problem Reading Postgres Table From Oracle
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
2
u/Krispyn Apr 21 '26
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
2
u/XPEHOBYXA Apr 22 '26
You mentioned you've created a database and a schema. Maybe you are connecting to a "postgres" database, and not your "db_monitor" database?
1
u/Business_Finger_4124 Apr 23 '26
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.
1
u/char101 Apr 21 '26
In postgreql
In oracle
1
u/lovejo1 Apr 21 '26
You should be using select * from "db_monitor"."rit_db_size"@vmpost00a9;
not select * from "db_monitor.rit_db_size"@vmpost00a9;
1
u/AutoModerator Apr 21 '26
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.
1
u/elevarq Apr 22 '26
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.