Re: hide data from admins

On Tue, Mar 11, 2025 at 9:48 PM Siraj G wrote: > Hello Experts! > > What are the features available in Postgresql to hide PII (personal > identifiable information) from the Admin team? Like in Oracle we have data > vault and data redaction, I am looking for similar features in > PostgreSQL.We do

hide data from admins

Hello Experts! What are the features available in Postgresql to hide PII (personal identifiable information) from the Admin team? Like in Oracle we have data vault and data redaction, I am looking for similar features in PostgreSQL.We do not want to do code level changes. Regards Siraj

Re: [EXTERNAL] Re: Asking for OK for a nasty trick to resolve PG CVE-2025-1094 i

On Thu, 2025-03-06 at 09:33 +, Abraham, Danny wrote: > We have hundreds of pg servers (mainly linux). > App is 7×24. > We think that patching the server to 15.12.will cost about 30 times > more compared to patching the pg client ( mainly qa effort). I don't think so. Don't do any QA when in

Re: Moving from Linux to Linux?

Le mar. 11 mars 2025 à 20:35, Ron Johnson a écrit : > On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster > wrote: > >> Hi Devrim, Thomas, Adrian, Ron, Joe, >> >> answering to myself as answering to five postings in one go is >> impossible. 🤣 >> >> > Are there any obstacles that definitely make that a

Re: Duplicate Key Values

On 3/11/25 12:55, mark bradley wrote: It happened again.  Now there are no sequences (although there once was). Read my previous post and provide the information requested. Also: 1) Postgres version. 2) Where did you get Postgres from? Sequence of data reentry: 1. I reentered the /n

Re: Duplicate Key Values

It happened again. Now there are no sequences (although there once was). Sequence of data reentry: 1. I reentered the node_ids in table node as a primary key. 2. I reentered the datasets, in table dataset. 3. Node_id was already a PK in dataset. 4. I set node_id to also be a foreign key

Re: Duplicate Key Values

On 3/11/25 11:52, mark bradley wrote: there is an index on node_id as it is the Primary Key. Why do you think there is not? My mistake, I misread the output from \d dataset Can you elaborate more on point 3. Are you calling the Foreign Key relationships subclassing? Although I d

Re: Moving from Linux to Linux?

On Tue, Mar 11, 2025 at 2:35 PM Paul Foerster wrote: > Hi Devrim, Thomas, Adrian, Ron, Joe, > > answering to myself as answering to five postings in one go is impossible. > 🤣 > > > Are there any obstacles that definitely make that a no-go? Do I risk > corruption? It's both Linux, just a different

Re: Creating files with testdata

On March 10, 2025 2:26:48 PM GMT-04:00, Adrian Klaver wrote: > > >On 3/10/25 11:09 AM, H wrote: >> I am developing a complex multi-tenant application in postgresql 16 >in Linux. During the development I would like to be able to enter test >data into various related tables at any given time for te

Re: Creating files with testdata

On March 11, 2025 2:59:35 AM EDT, Thiemo Kellner wrote: >DbVisualizer >https://www.dbvis.com/docs/ug/exporting-a-grid/?_gl=1*1aoko6w*_up*MQ..*_ga*MTA0NjM4MTcxNi4xNzQxNjc2MDc0*_ga_9P463C7BC6*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjAuODQ3OTY1NDMx*_ga_PTZV59MQGC*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjA

Re: Duplicate Key Values

there is an index on node_id as it is the Primary Key. Why do you think there is not? My mistake, I misread the output from \d dataset Can you elaborate more on point 3. Are you calling the Foreign Key relationships subclassing? Although I did not explicitly use Postgres to declare inherita

Re: Big script execution

> On Mar 10, 2025, at 09:35, Igor Korot wrote: > > Will the tables also be deleted? > The CREATE TABLE statements are part of this big transaction. Yes. DDL is transactional in PostgreSQL just like DML.

Re: Moving from Linux to Linux?

Hi Devrim, Thomas, Adrian, Ron, Joe, answering to myself as answering to five postings in one go is impossible. 🤣 > Are there any obstacles that definitely make that a no-go? Do I risk > corruption? It's both Linux, just a different distribution. The question was a bit of an idea. So the glibc

Re: Big script execution

On Mon, 2025-03-10 at 16:42 -0500, Igor Korot wrote: > I am trying to execute a huge script (~40K lines) that will populate > my database. > > The script starts with "BEGIN TRANSACTION" and will end > with "COMMIT". > > however I'd like to rollback if there is an error encounter. > > When I exec

Re: Creating files with testdata

On 3/10/25 11:09 AM, H wrote: I am developing a complex multi-tenant application in postgresql 16 in Linux. During the development I would like to be able to enter test data into various related tables at any given time for testing purposes. While this can certainly be accomplished by prede

Re: Creating files with testdata

On Mon, 10 Mar 2025 at 19:17, H wrote: ... > After entering my test data into the markdown file for the given test > scenario, I would then run an awk script or similar to create a SQL file with > the various CTE INSERT statements. Howevever, it gets complex since I need to > handle 1:N relatio

Re: Quesion about querying distributed databases

Dear Laurenz > I hear you, and I agree with that. Thank you. Such a relief. > If that worked well, then it should also work well with PostgreSQL and foreign data wrappers. You're right. We had problems with cross-server queries on SQL Server and MariaDB too. It seems that cross-server queries a

Re: Creating files with testdata

On Mon, Mar 10, 2025 at 12:17 PM H wrote: > There are tables referencing each other using randomly generated IDs, ie. > those IDs are not known until after the parent table row is inserted. > I just reserve half of the number space of bigint, the negatives, for test data and assign known IDs as

Big script execution

Hi, ALL, I am trying to execute a huge script (~40K lines) that will populate my database. The script starts with "BEGIN TRANSACTION" and will end with "COMMIT". however I'd like to rollback if there is an error encounter. When I execute it from the Terminal I do use -v ON_ERROR_STOP=1 but I'd

Re: Big script execution

Hi, Laurenz, On Mon, Mar 10, 2025, 11:31 AM Laurenz Albe wrote: > On Mon, 2025-03-10 at 16:42 -0500, Igor Korot wrote: > > I am trying to execute a huge script (~40K lines) that will populate > > my database. > > > > The script starts with "BEGIN TRANSACTION" and will end > > with "COMMIT". > >

Re: Big script execution

Thank you! On Mon, Mar 10, 2025, 11:37 AM Christophe Pettus wrote: > > > > On Mar 10, 2025, at 09:35, Igor Korot wrote: > > > > Will the tables also be deleted? > > The CREATE TABLE statements are part of this big transaction. > > Yes. DDL is transactional in PostgreSQL just like DML. >

Re: Creating files with testdata

On Mon, 10 Mar 2025 at 20:18, H wrote: > There are tables referencing each other using randomly generated IDs, ie. > those IDs are not known until after the parent table row is inserted. Random? Then they CAN collide. ;-> Do the IDs need to be random or are they just the default value? I've en

Re: Duplicate Key Values

An "interesting" effect of reindexing is that all the records that were dups in the nodes table were deleted, both copies. Also, all rows having node_id as a foreign key in other tables were deleted, which means all rows in these tables were deleted. Fortunately these are not huge tables. I wi

Re: Moving from Linux to Linux?

On 3/11/25 12:30, Devrim Gündüz wrote: Streaming replication won't work between SLES and RHEL, mostly because of glibc incompatibilities. Use logical replication. To be clear, it probably will "work" for some definition of "work" as long as the architecture is the same. In other words if you t

Re: Moving from Linux to Linux?

On Tue, Mar 11, 2025 at 12:23 PM Paul Foerster wrote: > Hi, > > we are considering changing the PostgreSQL platform from SUSE SLE to Red > Hat. To keep service interruptions as short as possible, the idea is to set > up a streaming replication from the SUSE server to be replaced to a > temporary

Re: Moving from Linux to Linux?

On 3/11/25 09:22, Paul Foerster wrote: Hi, we are considering changing the PostgreSQL platform from SUSE SLE to Red Hat. To keep service interruptions as short as possible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat server and then

Re: Moving from Linux to Linux?

Hello, Le mar. 11 mars 2025 à 17:23, Paul Foerster a écrit : > Hi, > > we are considering changing the PostgreSQL platform from SUSE SLE to Red > Hat. To keep service interruptions as short as possible, the idea is to set > up a streaming replication from the SUSE server to be replaced to a > te

Re: Moving from Linux to Linux?

Hi, (Sorry for the top posting) Streaming replication won't work between SLES and RHEL, mostly because of glibc incompatibilities. Use logical replication. Regards, Devrim On 11 March 2025 16:22:26 GMT, Paul Foerster wrote: >Hi, > >we are considering changing the PostgreSQL platform from SUS

Moving from Linux to Linux?

Hi, we are considering changing the PostgreSQL platform from SUSE SLE to Red Hat. To keep service interruptions as short as possible, the idea is to set up a streaming replication from the SUSE server to be replaced to a temporary Red Hat server and then replace that SUSE server with the newly

Re: Duplicate Key Values

A reindex is not going to remove rows from the table, so we need to see how you came to the conclusion that it did. Cheers, Greg -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support

Re: Duplicate Key Values

On 3/11/25 08:16, mark bradley wrote: A) 1) Please do not top post. Either inline or bottom post. It makes things like the below easier to follow. I wrote the post you responded to and it took me a bit to catch the b as a reference to: b b) Or for any node_ids that where duplicated did

Re: Duplicate Key Values

b REINDEX TABLE node; Also reindexed table with node_id as a foreign key in the same way. Best regards, Mark Brady amazon.com/author/markjbrady From: Adrian Klaver Sent: Tuesday, March 11, 2025 11:12 AM To: mark bradley Cc

Re: Duplicate Key Values

Do you *currently* take regular backups? We'd be glad to show you how to take regular logical backups. On Tue, Mar 11, 2025 at 10:29 AM mark bradley wrote: > An "interesting" effect of reindexing is that all the records that were > dups in the nodes table were deleted, both copies. > > Also, al

Re: Duplicate Key Values

On 3/11/25 08:05, mark bradley wrote: The rows that were preserved in the nodes table were the ones that were not dups originally. 1) To be specific: a) If there where two or more rows with a node_id, after the reindexing was there only one left? b) Or for any node_ids that where duplicated

Re: Duplicate Key Values

The rows that were preserved in the nodes table were the ones that were not dups originally. Best regards, Mark Brady amazon.com/author/markjbrady From: Adrian Klaver Sent: Tuesday, March 11, 2025 10:56 AM To: mark bradley

Re: Duplicate Key Values

On 3/11/25 07:28, mark bradley wrote: An "interesting" effect of reindexing is that all the records that were dups in the nodes table were deleted, both copies. I am trying to understand above. Was there at least one row of each node_id left? Also, all rows having node_id as a foreign key i

Re: Duplicate Key Values

On Tue, Mar 11, 2025 at 10:29 AM mark bradley wrote: > An "interesting" effect of reindexing is that all the records that were > dups in the nodes table were deleted, both copies. > Er...that's not just interesting, but alarming - if true. Can you show the steps you took? Cheers, Greg -- Crunc

Re: exclusion constraint question

On Tue, Mar 11, 2025 at 3:06 AM Achilleas Mantzios asked: > is it still harder than the trigger ? > I think the trigger wins: no extension needed, arguably better error output, easier to understand at a glance, and can quickly change the business logic by adjusting the function. Pretty short too.

Re: exclusion constraint question

On 3/11/25 00:28, Rhys A.D. Stewart wrote: Greetings, I think I got it : ALTER TABLE shelves ADD CONSTRAINT shelves_excl EXCLUDE USING gist( array_remove((ARRAY[l_mug_id,c_mug_id,r_mug_id]::int[]),NULL) WITH && ); but the caveat is I had to cast to 32-bit int array. Haven't found any opcla

Creating files with testdata

DbVisualizer https://www.dbvis.com/docs/ug/exporting-a-grid/?_gl=1*1aoko6w*_up*MQ..*_ga*MTA0NjM4MTcxNi4xNzQxNjc2MDc0*_ga_9P463C7BC6*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjAuODQ3OTY1NDMx*_ga_PTZV59MQGC*MTc0MTY3NjA3My4xLjEuMTc0MTY3NjEyNC4wLjAuMA..#generating-test-data Or DBeaver https://dbeaver.com