Primary keys and composite unique keys(basic question)

Hello, We have UUIDs in our tables which are primary keys. But in some cases we also identify a composite unique key apart from the primary key. My assumption is that there should be a unique key index created by us using the composite key. And when we fetch using this composite key i

Looking for some help with HA / Log Log-Shipping

Hello, We are running Postgresql 12 and I'm trying to put in place streaming wal replication. I followed officiale documentation ( https://www.postgresql.org/docs/12/warm-standby.html ) as well as this tutorial : https://scalegrid.io/blog/managing-high-availability-in-postgresql-part-1/  

Re: BDR question on dboid conflicts

I actually just recently encountered this very same problem when calling bdr_group_join(). The dboid generated is the same as that of an existing node, and the tuple (sysid, timeline, dboid) is the same as well. I saw this manifest two different ways in the logs: A) < 2021-03-30 02:42:56.942 U

JSON operator feature request

Good morning, we have everywhere in code: COALESCE(json_data, '{}'::json(b)) || json(b)_build_object(...) or something like IF json_data IS NULL THEN I made some code refactoring with CREATE OR REPLACE FUNCTION public.jsonb_concat_null(a jsonb, b jsonb) RETURNS jsonb AS $$

pg_rewind

Hi, I'm trying to get my get my head around pg_rewind. Why does it need full_page_writes and wal_log_hints on the target? As far as I could see it only needs old target WAL to see what pages have been touched since the last checkpoint before diverge point. Why can't it get this data from partia

Re: Primary keys and composite unique keys(basic question)

Mohan Radhakrishnan writes: > We have UUIDs in our tables which are primary keys. But in > some cases > we also identify a composite unique key apart from the primary key. > My assumption is that there should be a unique key index created by us > using the composite key. And when we

Re: Primary keys and composite unique keys(basic question)

I will cover the UUIDs first. They are indispensable to us. 1. The data is distributed over regions So we need the row to be unique. 2. This distributed data is sent to services as events. That is the application architecture. But we don't search using UUIDs always. Only when data from another dis

Re: Looking for some help with HA / Log Log-Shipping

Replying to myself :) It seems pg_basebackup did all the tricks, even restarting the slave. And it is in standby mode.Do I have anything to do in addition ? Thanks Le mercredi 31 mars 2021 à 12:51:29 UTC+2, Laurent FAILLIE a écrit : Hello, We are running Postgresql 12 and I'm trying

standby fails with out-of-order XID insertion

Hi all, So we have master(pg11.8) and standbyA(pg11.11), 24T data, quite busy data-warehouse on Amazon Linux. We've built a standbyB(pg11.11) by creating basebackup from standbyA and collecting wals from the master. When basebackup completed we started recovery from collected wals. When standbyB

RE: Issues with using plpgsql debugger using PG13 on Centos 7

Thank you for the pointer. We were able to build it correctly after setting the PG-13 path PATH=$PATH:/usr/pgsql-13/bin From: Ian Lawrence Barwick Sent: Tuesday, February 2, 2021 2:54 AM To: Jain, Ankit Cc: pgsql-gene...@postgresql.org Subject: Re: Issues with using plpgsql debugger using PG13

Re: Primary keys and composite unique keys(basic question)

Etiquette on these lists is to reply in line or below the relevant portion, not top-post with full quoting like default gmail behavior. On Wed, Mar 31, 2021 at 9:18 AM Mohan Radhakrishnan < radhakrishnan.mo...@gmail.com> wrote: > But we don't search using UUIDs always. Only when data from another

Re: Debugging leaking memory in Postgresql 13.2/Postgis 3.1

On 30.03.2021 20:46, Tom Lane wrote: Stephan Knauss writes: The wiki suggested to dump MemoryContext states for more details, but something strange happens when attaching gdb. It seems that the process is immediately killed and I can no longer dump such details. (I think the -v option is the o

Copy Statistics Tables During Upgrade

Hello Team, I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and came across the question that what is reason PostgreSQL is not doing copy of stats and we have to analyze right after upgrade. There are two situations where this will help when we use this DB as our enterprise dat

Re: Debugging leaking memory in Postgresql 13.2/Postgis 3.1

Stephan Knauss writes: > Hello Tom, the output below looks similar to the OOM output you > expected. Can you give a hint how to interpret the results? Looks like the answer is that wherever the leak is, it's not accounted for by this info; none of those contexts are particularly large. Based on

Re: Debugging leaking memory in Postgresql 13.2/Postgis 3.1

> On Mar 31, 2021, at 11:24 AM, Tom Lane wrote: > > Stephan Knauss writes: >> Hello Tom, the output below looks similar to the OOM output you >> expected. Can you give a hint how to interpret the results? > > Looks like the answer is that wherever the leak is, it's not accounted > for by th

Failed to initialize 13.2-1 cluster on Windows

Hello, I'm trying to install 13.2-1 on Windows 10. Previous version was 10.5, installed using the EnterpriseDB installer, without any problems. That version was completely removed before attempting to install 13.2. EnterpriseDB installer failed to initialize the cluster. The error says that i

Postgres connection to hot standby

I have a third party application running on postgresql. I have a primary server ans secondary. I have setup the DBon primary to replicate to the secondary.(streaming) as a hot standby. Is it possible to have an instance of the application access the DB on secondary in readonly mode? I have tried

Proposed Italian Translation of Code of Conduct Policy

The PostgreSQL Community Code of Conduct Committee has received a draft of the Italian translation of the Code of Conduct Policy updated August 18, 2020 for review. The English version of the Policy is at: https://www.postgresql.org/about/policies/coc/ The translation was created by Federic

Re: Failed to initialize 13.2-1 cluster on Windows

Update: I've just tried with version 12.6-1 and works perfectly fine. Clearly, there is some bug in the 13.2-1 published version. On 2021-03-31 16:35, Martin wrote: Hello, I'm trying to install 13.2-1 on Windows 10. Previous version was 10.5, installed using the EnterpriseDB installer, witho

accessing cross-schema materialized views

We have: create materialized view schema1.matview. grant select on table schema1.matview to mygroup create view schema2.usingview as select ... from schema1.matview grant select on table schema2.using to mygroup and yet we receive "permission denied for materialized view" on

Re: accessing cross-schema materialized views

On 3/31/21 4:31 PM, Tim Clarke wrote: We have:     create materialized view schema1.matview.     grant select on table schema1.matview to mygroup     create view schema2.usingview as select ... from schema1.matview     grant select on table schema2.using to mygroup Is that schema2.using

Re: accessing cross-schema materialized views

Tim Clarke writes: > We have: > create materialized view schema1.matview. > grant select on table schema1.matview to mygroup > create view schema2.usingview as select ... from schema1.matview > grant select on table schema2.using to mygroup > and yet we receive "permission de

How to implement expiration in PostgreSQL?

Hi, I guess this question has been asked a million times, but all solutions I can find online don’t really work well for my case. I’ll list them here and hope someone can shed some light. My use case is to implement joining clubs that require entrance fee: 1. Each clubs only allows maximum num