Re: Logical replication

Thanks Michael. On Mon, 6 Apr, 2020, 9:49 PM Michael Lewis, wrote: > There is nothing native to compute this. This was asked a month or so ago. > The best the poster came up with was a regular query on master DB which > updates a timestamptz field. Assuming master and slave have clocks in sync,

Re: How to unnest nested arrays

út 7. 4. 2020 v 7:25 odesílatel Guyren Howe napsal: > > > On Apr 6, 2020, at 19:44 , David G. Johnston > wrote: > > On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe wrote: > >> Consider this: >> >> *select* (*array*[*array*[1, 2], *array*[3, 4]])[i:i] >> *from* *generate_subscripts*(*array*[*array*[1

Re: How to unnest nested arrays

> On Apr 6, 2020, at 19:44 , David G. Johnston > wrote: > > On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe > wrote: > Consider this: > > select (array[array[1, 2], array[3, 4]])[i:i] > from generate_subscripts(array[array[1, 2], array[3, 4]], 1) i > > which produces: >

Re: How to unnest nested arrays

út 7. 4. 2020 v 4:44 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe wrote: > >> Consider this: >> >> *select* (*array*[*array*[1, 2], *array*[3, 4]])[i:i] >> *from* *generate_subscripts*(*array*[*array*[1, 2], *array*[3, 4]], 1) i >

Re: what happens when you issue ALTER SERVER in a hot environment?

"David G. Johnston" writes: > On Monday, April 6, 2020, AC Gomez wrote: >> If you issue an ALTER SERVER command and there are active connections >> with that server in use or new ones are coming in, what happens? Docs on >> this command say nothing regarding active processing using the server >>

Re: what happens when you issue ALTER SERVER in a hot environment?

On Monday, April 6, 2020, AC Gomez wrote: > If you issue an ALTER SERVER command and there are active connections > with that server in use or new ones are coming in, what happens? Docs on > this command say nothing regarding active processing using the server > context and changes to it. So I a

what happens when you issue ALTER SERVER in a hot environment?

If you issue an ALTER SERVER command and there are active connections with that server in use or new ones are coming in, what happens? Docs on this command say nothing regarding active processing using the server context and changes to it. So I assume it's just handled. For example if you alter us

Re: extract property value from set of json arrays

figured it out: select unnest(array_agg(e.db ->> 'e')) as j from tbl_t t cross join lateral jsonb_array_elements((t.jdata->>'b')::jsonb) as c(e) On Mon, Apr 6, 2020 at 10:51 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Apr 6, 2020 at 7:21 PM AC Gomez wrote: > >> I hav

Re: extract property value from set of json arrays

On Mon, Apr 6, 2020 at 7:21 PM AC Gomez wrote: > I have the following in a postgresql table > row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222", > "d":"111", "e": "000"} ]"} > row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666", > "d":"444", "e": "333"}

Re: How to unnest nested arrays

On Mon, Apr 6, 2020 at 6:12 PM Guyren Howe wrote: > Consider this: > > *select* (*array*[*array*[1, 2], *array*[3, 4]])[i:i] > *from* *generate_subscripts*(*array*[*array*[1, 2], *array*[3, 4]], 1) i > > which produces: > > {{1,2}} > {{3,4}} > > I expect and want, from that source: > > {1, 2} > {

extract property value from set of json arrays

I have the following in a postgresql table row 1: {"a": 1, "b": "[{"c": "123", "d":"456", "e": "789"}, {"c": "222", "d":"111", "e": "000"} ]"} row 2: {"a": 2, "b": "[{"c": "XXX", "d":"YYY", "e": "ZZZ"}, {"c": "666", "d":"444", "e": "333"} ]"} How do I pullout all "b":"e" values and end up with

How to unnest nested arrays

Consider this: select (array[array[1, 2], array[3, 4]])[i:i] from generate_subscripts(array[array[1, 2], array[3, 4]], 1) i which produces: {{1,2}} {{3,4}} I expect and want, from that source: {1, 2} {3, 4} These don’t work: select (array[array[1, 2], array[3, 4]])[i:i][:] {{1,2}} {{3,4}} s

Re: Cstore_fdw issue.

Hi, On Thu, 2020-04-02 at 20:40 +, Moses Mafusire wrote: > > 3. I have searched for pg_config directory and found 2 locations; > /usr/bin/pg_config/usr/pgsql-12/bin/pg_config BTW, I think both the OS-supplied and the community packages are installed at the same t

Re: Cstore_fdw issue.

Hi, On Thu, 2020-04-02 at 20:40 +, Moses Mafusire wrote: > Hi,I am new to PostgreSQL, successfully installed PGSql v12.2 on my CentOS 7 > machine and I am trying to install cstore_fdw. yum install cstore_fdw_12 Regards, -- Devrim Gündüz Open Source Solution Architect, Red Hat Certified Eng

Re: How to prevent master server crash if hot standby stops

Hi! If you prefer replication to fail silently, don't use replication slots. Use "wal_keep_segments" instead. I desided to give 1 GB to wal. So I added wal_keep_segments=60 After some time Postgres created 80 files with total size 1.3GB. How to fix this so that no more than 1 GB of disk

Re: Logical replication

There is nothing native to compute this. This was asked a month or so ago. The best the poster came up with was a regular query on master DB which updates a timestamptz field. Assuming master and slave have clocks in sync, it is simple to compute the lag from that. >

Re: How to prevent master server crash if hot standby stops

On Mon, 2020-04-06 at 11:47 +0300, Andrus wrote: > > That's why you use monitoring. > > Never use replication slots without monitoring replication (or at least the > > disk space on the primary). > > How to implement this automatically, without human interaction required ? > > "superuser_reserved

Logical replication

Hi Team, Is there a way or query to measure replication lag on logical replication setup in terms of time ? Thanks Raj

AW: Out of memory in big transactions after upgrade to 12.2

Thanks a lot so far for all your answers. work_mem is 4 MB and max_connections is 100, no pooling. As additional info maybe I should mention that we do an update on one table which cascades to some other tables updating 10ks of rows there and triggering the Perl functions for every row. I’m not

Re: How to prevent master server crash if hot standby stops

Hi! Thank you. When you use replication slots, it is very important to put in place a monitoring solution to check if too much WAL is retained, and note that there is nothing able to do that natively in core Postgres. There are however multiple ways to solve this problem, like a background work

Re: How to prevent master server crash if hot standby stops

Hi! Thank you. That's why you use monitoring. Never use replication slots without monitoring replication (or at least the disk space on the primary). How to implement this automatically, without human interaction required ? "superuser_reserved_connections" setting exists. How about implement

Re: Server with hot standby slave wont start after vacuum

On Mon, 2020-04-06 at 09:39 +0300, Andrus wrote: > Streaming asynchronous binary replication is used with hot standby slave. > > To recover disk space > > vacuumdb --all --full --skip-locked > > is executed in every night is master. > > During this vacuumdb stops with error > > vacuumdb: error

Re: How to prevent master server crash if hot standby stops

On Mon, Apr 06, 2020 at 11:03:20AM +0300, Andrus wrote: > If slave stops responing, master server will create files in pg_wal directory. > If disk becomes full, master server crashes also. > > How to avoid this ? > > If disk is nearly full, master should stop additional files creation > (and mayb

Re: How to prevent master server crash if hot standby stops

On Mon, 2020-04-06 at 11:03 +0300, Andrus wrote: > Streaming asynchronous binary replication is used with hot standby slave. > > If slave stops responing, master server will create files in pg_wal directory. > If disk becomes full, master server crashes also. > > How to avoid this ? > > If disk

How to prevent master server crash if hot standby stops

Hi! Streaming asynchronous binary replication is used with hot standby slave. If slave stops responing, master server will create files in pg_wal directory. If disk becomes full, master server crashes also. How to avoid this ? If disk is nearly full, master should stop additional files creatio