Re: replication

On 4/25/25 14:50, Marc Millas wrote: Hello, One of my customers tells me he did a replication with the master on a debian 10 and the slave on a debian 12, both with a pg13.20, one on a pgdg110+1, the other a pgdg120.1 is this a good idea ??? (I don't think so...) Sorry I dont quite get t

Re: Clarification on RLS policy

On 4/25/25 14:01, Vydehi Ganti wrote: This is my Scenario: CREATE OR REPLACE FUNCTION one.get_country( powner name, ptable_name name)     RETURNS character varying LANGUAGE 'plpgsql'     COST 100     STABLE PARALLEL UNSAFE AS $BODY$ DECLARE     lOSUser         varchar(4000) := UPPER(SUBSTR

Re: Clarification on RLS policy

On 4/25/25 08:08, Vydehi Ganti wrote: Hi Team, We are presently using Postgresql:PostgreSQL 15.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-23), 64-bit I have a scenario where 1.I need to enforce RLS policy on a table for Select by calling a Function 2.The f

Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote: Hi, You can use pgaudit and pgauditlogtofile extension (https://github.com/fmbiete/pgauditlogtofile) together to write audit logs in a separate file. One issue we have with pgaudit is that it prints AUDIT records even if the xaction gets rollbacked, how

Re: timescaledb vs NULL vs pg_timeseries vs partman + pgcron + pg_ivm

On 4/9/25 04:50, Amitabh Kant wrote: Thank you, I meant the paid/supported service not the community version. Which of the two do you use? I use the community version. On 4/9/25 05:23, Brent Wood wrote: I also use the free community edition on internal servers, but under Ubuntu.

timescaledb vs NULL vs pg_timeseries vs partman + pgcron + pg_ivm

Hi in continuation of "Ideas about presenting data coming from sensors" https://www.postgresql.org/message-id/flat/8d2dd92a-da16-435b-a38e-fe72191fc9d1%40cloud.gatewaynet.com we got the system working in single tables fashion (3 kinds of them), since no timeseries solution seemed to fit 100% al

Re: ERROR: could not read block 0 in file when creating an index out of a function

On 3/12/25 14:31, Luca Ferrari wrote: On Wed, Mar 12, 2025 at 12:54 PM Artur Zakirov wrote: I can reproduce this with the table `t` on PG 15.10. I didn't mention I'm running 16.6, but I'm pretty sure it is reproducible on other versions too. In your case `base/357283/365810` file is a new in

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

Re: exclusion constraint question

On 3/10/25 10:43, Achilleas Mantzios - cloud wrote: On 3/8/25 21:01, Rhys A.D. Stewart wrote: Greetings All, I have the following table: CREATE TABLE shelves( shelf_id bigint PRIMARY KEY, l_mug_id bigint UNIQUE, c_mug_id bigint UNIQUE, r_mug_id bigint UNIQUE, CHECK

Re: exclusion constraint question

On 3/8/25 21:01, Rhys A.D. Stewart wrote: Greetings All, I have the following table: CREATE TABLE shelves( shelf_id bigint PRIMARY KEY, l_mug_id bigint UNIQUE, c_mug_id bigint UNIQUE, r_mug_id bigint UNIQUE, CHECK (l_mug_id <> c_mug_id AND l_mug_id <> r_mug_id AND c_mu

Re: No. Of wal files generated

On 3/7/25 17:59, Atul Kumar wrote: Hi, Please could you help me by sharing any redhat linux command through which I can count the no. of wal files and no. of ".ready" files generated in last 10 minutes. Do you have wal archiving enabled? If yes, then simply go to the archive dir and suit y

Re: Quesion about querying distributed databases

On 3/5/25 11:55, Laurenz Albe wrote: On Wed, 2025-03-05 at 12:57 +0330, me nefcanto wrote: Right now this data is in MariaDB, on separate databases (schema) but on one server. The solution in this situation is to have a cross-database query. (this is the status quo of our application) Now our

Re: Ideas about presenting data coming from sensors

On 2/27/25 09:05, Achilleas Mantzios - cloud wrote: On 2/26/25 18:29, Adrian Klaver wrote: On 2/26/25 01:27, Achilleas Mantzios - cloud wrote: Hi Again Up to this day we have set the data acquisition system running for just one ship and writing the code to display the data. For less than

Re: Ideas about presenting data coming from sensors

On 2/26/25 18:29, Adrian Klaver wrote: On 2/26/25 01:27, Achilleas Mantzios - cloud wrote: Hi Again Up to this day we have set the data acquisition system running for just one ship and writing the code to display the data. For less than 20 days we have 6M rows. I gave a shot to timescale

Re: Ideas about presenting data coming from sensors

. So this is serious decision, so ppl please share your stories with timescale . On 2/14/25 12:46, Achilleas Mantzios - cloud wrote: On 2/14/25 11:34, Allan Kamau wrote: On Thu, Feb 13, 2025 at 9:44 PM Thiemo Kellner wrote: 13.02.2025 10:54:05 Achilleas Mantzios - cloud

Re: Ideas about presenting data coming from sensors

On 2/13/25 20:44, Thiemo Kellner wrote: 13.02.2025 10:54:05 Achilleas Mantzios - cloud : If we followed a strict normalized approach then we would create additionally 11 tables each tag of type c) . And we are not guaranteed that the same tags would have the same structure over the whole

Re: Ideas about presenting data coming from sensors

On 2/14/25 11:34, Allan Kamau wrote: On Thu, Feb 13, 2025 at 9:44 PM Thiemo Kellner wrote: 13.02.2025 10:54:05 Achilleas Mantzios - cloud : > If we followed a strict normalized approach then we would create additionally 11 tables each tag of type c) . And we are

Re: Ideas about presenting data coming from sensors

On 2/13/25 19:12, Adrian Klaver wrote: On 2/13/25 01:53, Achilleas Mantzios - cloud wrote: Now my problem is on the design . We have : a) tags that have primitive values, float4 lets say - this is the majority, e.g. 60% of all tags b) tags that contain alarms data also with defined

Re: Ideas about presenting data coming from sensors

On 1/30/25 18:45, Adrian Klaver wrote: On 1/30/25 06:18, Achilleas Mantzios - cloud wrote: Dear PostgreSQL people We have a project having to do with capturing sensor data and alarms from various machinery using various protocols/standards (e.g. NMEA , MODBUS). We have about 150 sites

Ideas about presenting data coming from sensors

Dear PostgreSQL people We have a project having to do with capturing sensor data and alarms from various machinery using various protocols/standards (e.g. NMEA , MODBUS). We have about 150 sites (vessels of various types) and each site will generate about 500 rows per minute. We have solved mo

Re: Clusters and shared permissions using LDAP

On 12/10/24 17:59, Felipe Matas wrote: Hi all! I'm trying to build three postgres clusters (in different servers), I'm thinking to use CITUS to make the data available across the clusters, I'm checking the docs. What I have not been able to found in any place, is how to handle the permission

Re: Seamless age (xid) replacement

On 12/4/24 17:51, Ivan Shershnev wrote: Hello! I need to use the 'age (xid)' function, but I have noticed that it is deprecated without a clear alternative. I know that xid is also kinda deprecated, so it makes sense not to use it. I can get xid8 from 'pg_current_xact_id()', which replaced

Re: CVE-2024-10979 Vulnerability Impact on PostgreSQL 11.10

On 11/22/24 10:00, Matthias Apitz wrote: El día viernes, noviembre 22, 2024 a las 05:52:34 +0100, Laurenz Albe escribió: On Fri, 2024-11-22 at 10:01 +0530, Subhash Udata wrote: Currently, my environment is running PostgreSQL 15.0. I understand that version 15.9 contains the fix for CVE-2024-

Re: Suddenly all queries moved to seq scan

On 11/20/24 12:50, Sreejith P wrote: Hi, We are using PostgresQL 10 in our production database. We have around 890 req /s request on peak time. We have 1 primary and 4 slave databases as well in the same postgres cluster. 2 days back we applied some patches in the primary server and rest

Re: Fwd: A million users

On 11/13/24 12:29, Alvaro Herrera wrote: On 2024-Nov-13, Vijaykumar Jain wrote: I tried to grant select permissions to 5000 different roles on one table, It failed with row size too big already at 2443. But you can grant select to one "reader" role, and grant that one role to however many ot

Re: Duplicate key error

On 11/11/24 07:40, yudhi s wrote: We have a merge query as below for a partition table which is range partitioned on a truncated date column 'part_date'. And the only unique key in this table is a composite primary key on (id, part_date). And this merge queries ON condition is based on one of

Re: Plans for partitioning of inheriting tables

On 10/24/24 21:58, David G. Johnston wrote: On Thursday, October 24, 2024, wrote: Up to version 17, partitioning of tables inheriting from other tables is not possible. psql:../code_files/data_storage/PostgreSQL/tables/TOPO_FILES.pg_sql:68: ERROR:  no se puede crear u

Re: Strange permission effect depending on DEFERRABILITY

On 9/10/24 00:09, Laurenz Albe wrote: On Mon, 2024-09-09 at 16:14 +0300, Achilleas Mantzios - cloud wrote: The below runs on PostgreSQL 16.4 We are trying to implement a certain operation based on a security definer function : mariner_update_availability_date This is supposed to update a

Re: Strange permission effect depending on DEFERRABILITY

On 9/9/24 17:21, Tom Lane wrote: Achilleas Mantzios - cloud writes: As you noticed the last trigger is a CONSTRAINT DEFERRABLE trigger. This function mariner_update_availability_date is supposed to be run by a user : cbt_results_import strippedof any privileges to the rest of the system. Here

Re: ssh to DB server and su normal users very slow :

On 9/9/24 16:18, KK CHN wrote: update :  the      ssh -v root@db_Server_IP from my Windows cmd   pasted below for more details Why is your windows cmd important here, show ssh debugging from your DB host to the pgbackrest host. On Mon, Sep 9, 2024 at 4:50 PM KK CHN wrote: List,

Strange permission effect depending on DEFERRABILITY

Dear List The below runs on PostgreSQL 16.4 We are trying to implement a certain operation based on a security definer function : mariner_update_availability_date This is supposed to update a table : mariner , which has several other triggers : mariner_build_natural_id_tg BEFORE INSERT OR

Re: postgresql FDW vs dblink for DDL

On 9/8/24 23:46, Adrian Klaver wrote: On 9/8/24 13:04, Achilleas Mantzios wrote: Hi for remote DDL execution (such as CREATE TABLE) is dblink my only option? You will need to define in what context you are considering options. For instance you can do remote DDL operations by passing a com

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

hi On 6/20/24 10:23, Dmitry O Litvintsev wrote: Hello, I am in the process of migrating DB to Alma9 host. The databse is rather large - few TBs. I have run pg_basebackup on Alma9 host and established replication from production to it. The idea is to quickly switch from master to this new host

pl/pgsql outside the DB, (i.e. on the terminal) possible ?

Hello I notice both my kids struggling with either C or Python as first programming languages. I believe both are unsuitable for use as introductory languages to college juniors. Python IMHO is too advanced, too rich, weird indentation rules, no simple for loop etc. C, ok, punishing little

Re: postgresql custom variable in pg_settings table

On 1/9/24 09:38, Yi Sun wrote: Hello, We custom set variable Added patroni.nodes_count = 2 in postgresql.conf postgres=# show patroni.nodes_count;  patroni.nodes_count -  2 (1 row) postgres=# select current_setting('patroni.nodes_count');  current_setting

Re: pgBackRest on old installation

file, but if you give password on the command line or env variable then you give this info to all users in the system. On Mon, Nov 20, 2023 at 4:16 PM Achilleas Mantzios - cloud wrote: On 11/20/23 12:31, KK CHN wrote: list, I am trying pgBackRest on an RHEL 7.6 and old EDB

Re: pgBackRest on old installation

On 11/20/23 12:31, KK CHN wrote: list, I am trying pgBackRest on an RHEL 7.6 and old EDB 10 database cluster( a legacy application.) I have installed pgbackrest through  package install on RHEL7.6 But unable to get the basic stanza-creation working It throws an error. * /etc/pgbackrest.con

PostgreSQL inheritance vs https://wiki.postgresql.org/wiki/Don%27t_Do_This

Hello All in the wiki above and specifically in this commit : https://wiki.postgresql.org/index.php?title=Don%27t_Do_This&type=revision&diff=33210&oldid=33082 someone added this section about inheritance : " Don't use table inheritance Don't use table inheritance

Re: Presentation tools used ?

Thank you All people!

Re: pg_basebackup / recovery

On 4/12/23 12:32, Fabrice Chapuis wrote: During recovery process of a self contained backup, how postgres know to stop reading wal when consistency is reached? Because it knows the full packup info. It will observe the STOP WAL LOCATION: 3BC7/4B000130 (file 00023BC7004B) inside

Re: PostgreSQL vs MariaDB

On 3/28/23 06:44, Thomas Guyot wrote: On 2023-03-24 07:07, Inzamam Shafiq wrote: Hi Team, Hope you are doing well. Can someone please list pros and cons of MariaDB vs PostgreSQL that actually needs serious consideration while choosing the right database for large OLTP DBs (Terabytes)?