Re: [EXTERNAL] Oracle FDW version
The original poster was building against Greenplum 6, it’s expected to fail. Try this https://github.com/adam8157/oracle_fdw_greenplum, it’s not based on the lasts oracle_fdw, but likely easy to rebase. From: Adrian Klaver Date: Thursday, August 24, 2023 at 15:19 To: Jethro Elmer Sanidad , Ian Lawrence Barwick Cc: Christophe Pettus , umair.sha...@gmail.com , pgsql-general@lists.postgresql.org Subject: Re: [EXTERNAL] Oracle FDW version !! External Email On 8/22/23 23:10, Jethro Elmer Sanidad wrote: > Hello, > > Can you confirm in this email that our current version of PostgreSQL > (9.2.24) is not compatible with any of oracle_fdw versions released? And > you are recommending an upgrade? Thanks! In your first post you said: "Can you provide us download links for oracle_fdw for psql (PostgreSQL) 9.4.24. ..." 1) Are you on 9.2 or 9.4? 2) Where did you get Postgres from? -- Adrian Klaver adrian.kla...@aklaver.com !! External Email: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender.
Re: Read only user permission
On Wed, Aug 23, 2023 at 7:46 PM Tom Lane wrote: > Hellen Jiang writes: > > I have created readonly and readwrite roles with the following grants: > however, readonly user does not have access to the new tables created by > readwrite user. (readonly user has the access to new tables created by > admin). > > Any idea how I can grant the access to readonly to make sure it has the > read access to NEW tables created by readwrite user? > > The readwrite user (not the admin) would need to issue ALTER DEFAULT > PRIVILEGES granting that. > Hi. I'm confused Tom. Why should it matter who created the new tables? I'm also using DEFAULT PRIVILEGES, for the very reason they dynamically extend to newly created objects in the schema. And I don't remember reading anything like what you imply. Could you please pinpoint in the doc where this would be explained? DEFAULT PRIVILEGES only apply to DDLs made by the role who did the GRANT'ing? In my case, the schema owner does both the granting and DDLs, so I guess I'll be OK. I just want to make sure I understand the situation better. Thanks, --DD
Re: Read only user permission
> On 24/08/2023 10:11 CEST Dominique Devienne wrote: > > On Wed, Aug 23, 2023 at 7:46 PM Tom Lane wrote: > > > > The readwrite user (not the admin) would need to issue ALTER DEFAULT > > PRIVILEGES granting that. > > Hi. I'm confused Tom. Why should it matter who created the new tables? > > I'm also using DEFAULT PRIVILEGES, for the very reason they dynamically > extend to newly created objects in the schema. > And I don't remember reading anything like what you imply. Could you please > pinpoint in the doc where this would be explained? > > DEFAULT PRIVILEGES only apply to DDLs made by the role who did the GRANT'ing? > > In my case, the schema owner does both the granting and DDLs, so I guess I'll > be OK. > I just want to make sure I understand the situation better. Thanks, --DD This is implied by: "You can change default privileges only for objects that will be created by yourself or by roles that you are a member of." https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION -- Erik
Re: Read only user permission
On Thu, Aug 24, 2023 at 11:34 AM Erik Wienhold wrote: > > On 24/08/2023 10:11 CEST Dominique Devienne wrote: > > On Wed, Aug 23, 2023 at 7:46 PM Tom Lane wrote: > > > The readwrite user (not the admin) would need to issue ALTER DEFAULT > > > PRIVILEGES granting that. > > > And I don't remember reading anything like what you imply. Could you > please > > pinpoint in the doc where this would be explained? > > This is implied by: > > "You can change default privileges only for objects that will be > created by yourself or by roles that you are a member of." > > > https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html#SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION Thanks Erik (and Tom). I either missed that, or it didn't "sink-in" properly. I'll be careful to keep that in mind from now on. I appreciate the doc pointer. --DD
ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
Hello Friends, i have an very strange issue that i have tried to fix in hours but couldn't solve it. Could you please help me this case: - I have installed ora2pg to migrate oracle table to postgres, everything was working fine for several projects before - recently i started a new project to mingrate tables from oracle to postgres again in the same computer, and i did the same process as the previous projects. But when i copy the data from oracle table to postgres tabe by following command, it doesn't work properly: ora2pg -c ora2pg.conf -t COPY -a tablename no data have been transfered to postgres. it shows: 0/0 tables(100%) end of scanning. - connection to both database is correct. - The tables are existing on source and on target( structure table )... - i can create table structure from this config file I have tried to fix it, but it didn't work, here are what i did: 1. try to install ora2pg again -> not work 2. try to transfer others tables -> have the same issue 3. try to install ora2pg on other computer -> everything works fine 4. Only one case when i transfered data to this table, it works but very only some rows not all, not exactly what i want to transfer. Maybe this data is stored on the Cachmemory not really from the source table... Here ist my config file: ORACLE_HOME C:\Oracle\product\12.2.0\client_1 ORACLE_DSN dbi:Oracle:host=xxx;sid=xx;port=1521 ORACLE_USERsystem ORACLE_PWD xxx SCHEMA Schemaname ALLOW TZV_DATA TYPETABLE PG_DSN dbi:Pg:dbname=;host=xxx;port=5432 PG_USER schemaname PG_PWD xxx LOG_ON_ERROR1 STOP_ON_ERROR 0 could you please recommend me the possible causes of this? it is possible, that perl does't work properly or in this computer has some other software running, which prevent this transfer?. or something else that I really dont understand what happended Thank you for you help in advance
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
On Thu, Aug 24, 2023 at 1:51 PM duc hiep ha wrote: > > no data have been transfered to postgres. it shows: 0/0 tables(100%) end of > scanning. > Even if asking only for the table structure? Can you perform an offline migration (i.e., saving to file)? I would suggest to ask on ora2pg support. Luca
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
On Thu, Aug 24, 2023 at 3:49 PM duc hiep ha wrote: > > do i have to completely delete the Ora2pg and install it again!? or some > other softaware running on the computer have prevented this transfer's > process. I don't think so. Does the -d flag helps in finding out the problem? Luca
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
Further updated to the below Post. Shortly question: - Saving data to file using the following Command is fine: ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/ - Direct copy data from Oracle to Postgres doesn't work(command: ora2pg -c ora2pg.conf -t COPY -a tablename). Everything Else works fine. Connection to Oracle and Postgres using DBD::Pg, DBD::Oracle are set correctly. do i have to completely delete the Ora2pg and install it again!? or some other softaware running on the computer have prevented this transfer's process. or Do you know the root cause of that? Thanks Hans Vào Th 5, 24 thg 8, 2023 vào lúc 19:01 Luca Ferrari đã viết: > On Thu, Aug 24, 2023 at 1:51 PM duc hiep ha > wrote: > > > > no data have been transfered to postgres. it shows: 0/0 tables(100%) end > of scanning. > > > > Even if asking only for the table structure? > Can you perform an offline migration (i.e., saving to file)? > > I would suggest to ask on ora2pg support. > > Luca >
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
> On 24/08/2023 15:49 CEST duc hiep ha wrote: > > Further updated to the below Post. Shortly question: > * Saving data to file using the following Command is fine: > ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/ > * Direct copy data from Oracle to Postgres doesn't work > (command: ora2pg -c ora2pg.conf -t COPY -a tablename). > Everything Else works fine. Connection to Oracle and Postgres using > DBD::Pg, DBD::Oracle are set correctly. Can you access table "tablename" when connecting with Postgres via psql and the connection settings from ora2pg.conf? Maybe privileges are missing or the table is not on the search path. -- Erik
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
Thanks for you idea, here is the log file when i used -d flag: D:\ora2pg\hans>ora2pg -c ora2pg.conf -t COPY -a codes1 -d WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [2023-08-24 18:07:13] Ora2Pg version: 24.0 [2023-08-24 18:07:13] Export type: COPY [2023-08-24 18:07:13] Geometry export type: INTERNAL [2023-08-24 18:07:13] ORACLE_HOME = C:\Oracle\product\12.2.0\client_1 [2023-08-24 18:07:13] NLS_LANG = AMERICAN_AMERICA.AL32UTF8 [2023-08-24 18:07:13] NLS_NCHAR = AL32UTF8 [2023-08-24 18:07:13] Trying to connect to database: dbi:Oracle:host=xx.xx ;sid=xx.xx;port=1521 [2023-08-24 18:07:14] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED [2023-08-24 18:07:14] Force Oracle to compile schema HANS before code extraction [2023-08-24 18:07:14] Retrieving table information... [2023-08-24 18:07:14] Collecting 0 tables in DBA_OBJECTS took: 0 wallclock secs ( 0.00 usr + 0.02 sys = 0.02 CPU) [2023-08-24 18:07:14] Collecting 0 tables information in DBA_TABLES took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) [2023-08-24 18:07:14] ORACLE_HOME = C:\Oracle\product\12.2.0\client_1 [2023-08-24 18:07:14] NLS_LANG = AMERICAN_AMERICA.AL32UTF8 [2023-08-24 18:07:14] NLS_NCHAR = AL32UTF8 [2023-08-24 18:07:14] Trying to connect to database: dbi:Oracle:host=xx.xx;sid=xx.xx;port=1521 [2023-08-24 18:07:14] Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED [2023-08-24 18:07:14] Retrieving partitions information... [2023-08-24 18:07:14] Collecting 0 indexes in DBA_INDEXES took: 0 wallclock secs ( 0.00 usr + 0.00 sys = 0.00 CPU) [2023-08-24 18:07:15] Total time to export data from 0 tables (0 partitions, 0 sub-partitions) and 0 total rows: 1 wallclock secs ( 0.00 usr + 0.02 sys = 0.02 CPU) [2023-08-24 18:07:15] Fixing function calls in output files... it was collecting 0 tables, i don't see the connection part to Posgrest database maybe do you see some thing strange here? the output without -d flag: D:\ora2pg\hans>ora2pg -c ora2pg.conf -t COPY -a codes1 WARNING: target PostgreSQL version must be set in PG_VERSION configuration directive. Using default: 11 [>] 0/0 tables (100.0%) end of scanning. Fixing function calls in output files... Thanks Hans Vào Th 5, 24 thg 8, 2023 vào lúc 22:55 Luca Ferrari đã viết: > On Thu, Aug 24, 2023 at 3:49 PM duc hiep ha > wrote: > > > > do i have to completely delete the Ora2pg and install it again!? or some > other softaware running on the computer have prevented this transfer's > process. > > I don't think so. > Does the -d flag helps in finding out the problem? > > Luca >
Re: ora2pg -c ora2pg.conf -t COPY -a tablename not working properly
Yes i can access the target table on Postgres, because i have to create it as strukture on the posgres before transfer data. Actually in the past i have done it successfully many time. The Problem here is some thing happened in between in my computer, or there is a bug on Ora2pg. Vào Th 5, 24 thg 8, 2023 vào lúc 23:34 Erik Wienhold đã viết: > > On 24/08/2023 15:49 CEST duc hiep ha wrote: > > > > Further updated to the below Post. Shortly question: > > * Saving data to file using the following Command is fine: > > ora2pg -c ora2pg.conf -t COPY -o data.sql schema/data/ > > * Direct copy data from Oracle to Postgres doesn't work > > (command: ora2pg -c ora2pg.conf -t COPY -a tablename). > > Everything Else works fine. Connection to Oracle and Postgres using > > DBD::Pg, DBD::Oracle are set correctly. > > Can you access table "tablename" when connecting with Postgres via psql and > the connection settings from ora2pg.conf? Maybe privileges are missing or > the table is not on the search path. > > -- > Erik >
Materialized view refreshing problem
Hi All, We have one business critical materialized view( let’s called materialized_view_1) which contains around 16M records. This materialized view is refreshed CONCURRENTLY every 1 minute, and got refreshed normally (without CONCURRENTLY) every 6 hours. But from time and time, we found that it took forever to refresh materialized view( normally or concurrently), and there is no response to query from this materialized view. In this case, we have to create new materialized view(materialized_view_1_new) with exactly the same definition, and the new materialized view(materialized_view_1_new) works well and the refresh finishes in 15 seconds. And after about 1 months, we have to do this again: I mean use freshly new materialized view to replace the old one. It looks like there are too much garbage in the old materialized view as time going. Any idea how we can fix this materialized view refreshing problem? Thanks Hellen Jiang
Re: Materialized view refreshing problem
Hellen Jiang writes: > But from time and time, we found that it took forever to refresh materialized > view( normally or concurrently), and there is no response to query from this > materialized view. That sounds like a locking problem. Have you looked into pg_locks while this is happening, to see what may be waiting on what? regards, tom lane
Re: LDAP Authentication
Greetings, * Emile Amewoto (emil...@yahoo.com) wrote: > Here is the high level process: > 1- Create the user x without password in Postgres. > 2- Assign role or roles to the user x > 3- Update pg_hba.conf with the ldap connection link. > > You might need cert for the ldap to connect to AD, assuming you are using AD. If you're using AD, you should *really* be using Kerberos/gssapi for your authentication and *not* LDAP. LDAP is insecure as it involves passing around the user's credentials which is extremely bad practice and is strongly discouraged. LDAP auth also involves in-line round trips to the LDAP server which can delay or even fail database connections in the event that the LDAP server is even temporarily unavailable. Thanks, Stephen signature.asc Description: PGP signature
Re: Will PostgreSQL 16 supports native transparent data encryption ?
Greetings, * Ron (ronljohnso...@gmail.com) wrote: > On 8/21/23 18:49, Bruce Momjian wrote: > > On Mon, Aug 21, 2023 at 07:02:46PM +0300, Mostafa Fathy wrote: > > > It is mentioned here https://www.postgresql.org/about/press/faq/#:~:text= > > > Q%3A%20What%20features%20will%20PostgreSQL%2016%20have%3F that native > > > transparent data encryption is being worked on and it may be delivered > > > with > > > PostgreSQL 16. > > > > > > Is PostgreSQL 16 beta version includes native transparent data encryption > > > or > > > not ? because I checked the docs > > > https://www.postgresql.org/docs/16/index.html > > > and couldn't find anything related to transparent data encryption. > > > > > > If not supported yet in the beta version I would like to know if > > > PostgreSQL 16 > > > final version will support native transparent data encryption or not? > > Not, PG 16 will not support it, and I am unclear if later major versions > > will either. > > That's disappointing, since TDE makes PCI audits that much simpler. There's ongoing work happening for TDE support and we'd love to hear from folks who would like to see it included. You can expect an updated patch set for the September commitfest. Getting more folks to test it and use it and review it would certainly help move it forward. Thanks, Stephen signature.asc Description: PGP signature
Re: Materialized view refreshing problem
Hellen Jiang writes: > When the materialized view was refreshing concurrently, I saw the pg_lock > like this: there was no response within 30 seconds when the api calls this > query, and we got api time out. Well, that seems to confirm my idea that the REFRESH is blocked behind somebody else's lock. But you need to look to see which session is holding a lock on mvw_cbsd_status_grants_lessinfo_active_softmarker and what it's doing. regards, tom lane
Re: Materialized view refreshing problem
When the materialized view was refreshing concurrently, I saw the pg_lock like this: there was no response within 30 seconds when the api calls this query, and we got api time out. sasanalytics=> select relation::regclass, * from pg_locks where not granted; relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ---+--+--++--+---++---+-+---+--++---+---+-+-- mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |16401 | 1886512426 | | || | | | | 236/858| 17332 | ExclusiveLock | f | f (1 row) When the materialized view was refreshing normally( without concurrently), I saw the following pg_locks: there was no response within 30 seconds when the api calls this query, and we got api time out. sasanalytics=> select relation::regclass, * from pg_locks where not granted; relation | locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid |mode | granted | fastpath ---+--+--++--+---++---+-+---+--++---+-+-+-- mvw_cbsd_status_grants_lessinfo_active_softmarker | relation |16401 | 1886512426 | | || | | | | 245/1163 | 15932 | AccessExclusiveLock | f | f (1 row) From: Tom Lane Date: Thursday, August 24, 2023 at 3:02 PM To: Hellen Jiang Cc: pgsql-general@lists.postgresql.org Subject: Re: Materialized view refreshing problem Hellen Jiang writes: > But from time and time, we found that it took forever to refresh materialized > view( normally or concurrently), and there is no response to query from this > materialized view. That sounds like a locking problem. Have you looked into pg_locks while this is happening, to see what may be waiting on what? regards, tom lane
