Migration from Oracle to Postgres

Hi guys, I hope you're doing well. Simple question What are the best methods to migrate a huge table from Oracle to Postgres ? Cheers

Re: Migration from Oracle to Postgres

Hi Jean Claude, > On 05. May, 2020, at 09:07, Jean Claude wrote: > > Hi guys, > > I hope you're doing well. > > Simple question > What are the best methods to migrate a huge table from Oracle to Postgres ? > > Cheers my colleague uses ora2pg. Though I've never tried it myself, he says, it wo

Re: How to know if initial sync of logical subscription is finished

Am 26.02.2020 um 17:48 schrieb Klaus Darilion: Hello all! When subscribing a slave I need to know when the initial sync is finished. Is there some table I can query to know if the initial sync is finished? No solution to this? At least the replication worker need to be aware if it has finishe

Re: How to know if initial sync of logical subscription is finished

On Tue, 2020-05-05 at 09:33 +0200, Klaus Darilion wrote: > > When subscribing a slave I need to know when the initial sync is > > finished. Is there some table I can query to know if the initial sync is > > finished? > > No solution to this? At least the replication worker need to be aware if > i

Re: Installing Postgis25_11

On Mon, 2020-05-04 at 16:40 -0700, Clifford Snow wrote: > When I try to install PostGIS version 2.5 to a Postgresql-11 installation on > Fedora 32 I get warning that nothing provides for these packages: > - nothing provides libproj.so.19()(64bit) needed by > postgis25_11-2.5.4-1.f32.x86_64 > -

Re: Migration from Oracle to Postgres

Jean Claude wrote > Hi guys, > > I hope you're doing well. > > Simple question > What are the best methods to migrate a huge table from Oracle to Postgres > ? > > Cheers Hello, Depending on the datatype, and if the databases are co-located on the same server, you may try oracle_fdw (except for

Re: 12.2: Howto check memory-leak in worker?

On Tue, May 05, 2020 at 10:57:04AM +1200, Thomas Munro wrote: ! On Tue, May 5, 2020 at 10:13 AM Peter wrote: ! > BTW, I would greatly appreciate if we would reconsider the need for ! > the server to read the postmaster.pid file every few seconds (probably ! > needed for something, I don't know). !

Lock Postgres account after X number of failed logins?

Hi, everyone. Wondering if there's a way in PostgreSQL to automatically lock accounts after a number of failed logins (a security requirement for my organization). I've been investigating this for a while and the only reference I've found is to write a hook: https://wiki.postgresql.org/image

Re: Lock Postgres account after X number of failed logins?

On 5/5/20 7:13 AM, Wolff, Ken L wrote: Hi, everyone.  Wondering if there’s a way in PostgreSQL to automatically lock accounts after a number of failed logins (a security requirement for my organization).  I’ve been investigating this for a while and the only reference I’ve found is to write a h

Re: Lock Postgres account after X number of failed logins?

On Tue, May 5, 2020 at 5:28 PM Adrian Klaver wrote: > On 5/5/20 7:13 AM, Wolff, Ken L wrote: > > Hi, everyone. Wondering if there’s a way in PostgreSQL to automatically > > lock accounts after a number of failed logins (a security requirement > > for my organization). I’ve been investigating th

Re: Installing Postgis25_11

Laurenz Albe (laurenz.a...@cybertec.at), pgsql-gene...@postgresql.org Draft saved Make sure this is someone you trust. laurenz.a...@cybertec.at does not belong to your organization and is not in your contacts. On Tue, May 5, 2020 at 1:23 AM Laurenz Albe wrote: > > There has been a change in the

jsonb unique constraints

Can you have unique constraints on jsonb columns keys? I've looked for examples but haven't found any what is the proper syntax? Here's what I tried: CREATE TABLE report_json ( recnum int, id integer, report jsonb, PRIMARY KEY (recnum), CONSTRAINT report_json_unique_constraint

Re: Installing Postgis25_11

On 5/5/20 8:09 AM, Clifford Snow wrote: Laurenz Albe (laurenz.a...@cybertec.at ), pgsql-gene...@postgresql.org Draft saved Make sure this is someone you trust. laurenz.a...@cybertec.at

Re: jsonb unique constraints

On Tue, May 5, 2020 at 8:33 AM Ted Toth wrote: > Can you have unique constraints on jsonb columns keys? I've looked for > examples but haven't found any what is the proper syntax? Here's what I > tried: > > CREATE TABLE report_json ( > recnum int, > id integer, > report jsonb, > P

Re: Installing Postgis25_11

On Tue, May 5, 2020 at 8:37 AM Adrian Klaver wrote: > So to be clear you did: > > dnf update pgdg-fedora-repo > > Have you manually edited the repo file? > > > Adrian, Thanks - I did actually do the update, but nothing changed. What I noticed when looking at the /etc/yum.repo folder, there was a

Re: 12.2: Howto check memory-leak in worker?

Peter writes: > On Tue, May 05, 2020 at 10:57:04AM +1200, Thomas Munro wrote: > ! * Once a minute, verify that postmaster.pid hasn't been removed or > ! * overwritten. If it has, we force a shutdown. > It is really hard to comment on this in a way that might not be > considered

Re: jsonb unique constraints

I was able to create a unique index, thanks. On Tue, May 5, 2020 at 10:38 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, May 5, 2020 at 8:33 AM Ted Toth wrote: > >> Can you have unique constraints on jsonb columns keys? I've looked for >> examples but haven't found any what

Re: Lock Postgres account after X number of failed logins?

Hi, Allan. Thanks very much for taking the time to respond. Definitely appreciate all your ideas & suggestions. Some may help but for various reasons, at the end of the day, I’m still probably going to be looking for a way to automatically lock accounts after a number of failed logins. I pa

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día lunes, abril 27, 2020 a las 09:40:39a. m. -0400, Tom Lane escribió: > Matthias Apitz writes: > > El día Montag, April 27, 2020 a las 08:40:04 -0400, Tom Lane escribió: > >> Can you get a stack trace from the connected backend? > > > (gdb) bt > > #0 0x7fd567776000 in epoll_pwait () fr

Re: 12.2: Howto check memory-leak in worker?

On Tue, May 05, 2020 at 11:59:27AM -0400, Tom Lane wrote: ! Well, the choice we face is preventing somebody's disk from spinning ! down, versus preventing somebody else from completely corrupting their ! database. From where I sit that's not a difficult choice, nor one ! that I feel a need to let

Re: Lock Postgres account after X number of failed logins?

"Wolff, Ken L" writes: > I do understand what you described about locking down access through > pg_hba.conf, so only authorized users/applications can connect. That makes a > lot of sense and I’m going to take it forward to our Information Security > organization. However, in case they won’t

Re: Thoughts on how to avoid a massive integer update.

On 2020-05-04 21:32:56 +, Fehrle, Brian wrote: > I have a database with hundreds of terabytes of data, where every table has an > integer column referencing a small table. For reasons out of my control and > cannot change, I NEED to update every single row in all these tables, changing > the in

RETURNING to_jsonb(...)

How can the new record returned from RETURNING to converted to jsonb? For example something like: INSERT INTO my_table (a, b, c) VALUES ('a', 'b', 'c') RETURNING to_jsonb(*); or UPDATE my_table SET a = 'a' RETURNING to_jsonb(*); or INSERT INTO my_table (a, b, c) VALUES ('a', 'b', 'c') ON C

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

Matthias Apitz writes: > El día lunes, abril 27, 2020 a las 09:40:39a. m. -0400, Tom Lane escribió: >> If you're in a position to run a modified server, you could try >> inserting a debug log message: > I've added the printout of the length in this case and another one, and > see this in the serv

Re: RETURNING to_jsonb(...)

On 5/5/20 1:11 PM, Miles Elam wrote: How can the new record returned from RETURNING to converted to jsonb? For example something like: INSERT INTO my_table (a, b, c) VALUES ('a', 'b', 'c') RETURNING to_jsonb(*); or UPDATE my_table SET a = 'a' RETURNING to_jsonb(*); or

Re: Lock Postgres account after X number of failed logins?

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > "Wolff, Ken L" writes: > > I do understand what you described about locking down access through > > pg_hba.conf, so only authorized users/applications can connect. That makes > > a lot of sense and I’m going to take it forward to our Informat

Re: PostgreSQL client hangs sometimes on 'EXEC SQL PREPARE sid_sisisinst FROM :select_anw;'

El día martes, mayo 05, 2020 a las 04:18:02p. m. -0400, Tom Lane escribió: > Matthias Apitz writes: > > El día lunes, abril 27, 2020 a las 09:40:39a. m. -0400, Tom Lane escribió: > >> If you're in a position to run a modified server, you could try > >> inserting a debug log message: > > > I've a

Re: Lock Postgres account after X number of failed logins?

Stephen & Tom, Want to thank you both for taking the time to respond. Sometimes tone gets lost in email so please understand I'm saying this with all respect: my company is starting an effort to move away from "commercial" databases (you can probably guess which ones) and Postgres has been id

Encoding conversion: Use replacement character instead of failing query with "ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8" has no equivalent in encoding LATIN1" ?

Hello list I'm slowly converting all of our databases from Postgres 9 and LATIN1 to Postgres 12 and UTF8, one by one. I was wondering if there is a solution for this issue: if a database that is still latin1 has a postgres_fdw foreign table onto a converted utf8 database, and somehow a character

Re: Lock Postgres account after X number of failed logins?

Wolff, Ken L writes: > As Stephen states, even some basic functionality in this regard would go a > long way. Perhaps something could be built into the postgresql-contrib RPM? > Right now the only way I see is to write a hook, which involves changing > source code, which then puts us into

Re: Encoding conversion: Use replacement character instead of failing query with "ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8" has no equivalent in encoding LATIN1" ?

Christian Ramseyer writes: > Can I somehow influence the client:UTF8->server:LATIN1 character set > conversion so that instead of failing, it inserts an invalid codepoint > character, the utf8 hex bytes as string, drops the character or > something like that? There's nothing built-in for that, bu

Wrong PostgreSQL Plan

Hi Everyone, PG Version - RDS PostgreSQL 10.11 We have very simple query of 3 table joins and a few filter clause. Optimizer is behaving little weird in the sense that for change in one filter it is using NESTED LOOP JOIN and running for over 30 seconds whereas for other filter is working fine u

Re: Wrong PostgreSQL Plan

On Tue, May 5, 2020 at 6:15 PM Virendra Kumar wrote: > Optimizer is behaving little weird in the sense that for change in one > filter > You mean when you change the date equality filter to a more recent date? > it is using NESTED LOOP JOIN and running for over 30 seconds whereas for > other f

Re: Temporary table has become problematically persistent

We couldn't drop the table in the normal setup because of the wraparound protection measures. Specifically: "ERROR: database is not accepting commands to avoid wraparound data loss in database" We also tried vacuuming the table. The vacuum didn't generate an error (the wraparound warning was prin

Re: Wrong PostgreSQL Plan

Hi David, Thank you for your reply! You mean when you change the date equality filter to a more recent date? - This is correct we have faced this when we change equality filter to more recent date. Will be sending explain analyze on the thread when I get that. We ran analyze many time to see if

Re: Lock Postgres account after X number of failed logins?

On 05.05.20 16:13, Wolff, Ken L wrote: > Hi, everyone.  Wondering if there’s a way in PostgreSQL to automatically > lock accounts after a number of failed logins (a security requirement > for my organization).  > > Locking accounts after X number of failed logins is an excellent way to > defea