Re: PostgreSQL logical replication depends on WAL segments?

On 23/1/19 5:26 π.μ., Jeremy Finzel wrote: On Tue, Jan 22, 2019 at 8:19 PM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote: On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote: On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel mailto:finz...@gmail.com>> wrote: any one of

Re: PostgreSQL logical replication depends on WAL segments?

On Tue, Jan 22, 2019 at 8:19 PM Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > > On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote: > > On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel wrote: > >> any one of those WAL files will not be archived and recycled as long as >>> it no longer needed b

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 10:18 μ.μ., Jeremy Finzel wrote: On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel > wrote: any one of those WAL files will not be archived and recycled as long as it no longer needed by a replication slot :(.  s/no longer/still.  I meant to sa

Re: PostgreSQL logical replication depends on WAL segments?

On Tue, Jan 22, 2019 at 2:16 PM Jeremy Finzel wrote: > any one of those WAL files will not be archived and recycled as long as it >> no longer needed by a replication slot >> > :(. s/no longer/still. I meant to say any one of those WAL files will not be archived and recycled as long as it still

Re: PostgreSQL logical replication depends on WAL segments?

> > wal retention in pg_wal and wal archiving are different things. The OP got > problems cause he deliberately went on to delete files in pg_wal which > means he used the wrong method to address a wrong situation. > > However, if the OP has still those WAL files archived he can use them to > bring

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 8:59 μ.μ., Jeremy Finzel wrote: On Tue, Jan 22, 2019 at 12:52 PM Andres Freund > wrote: Hi, On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote: > P.S. do heed the advice of the others and get more familiar with the docs > around WAL arch

Re: Memory and hard ware calculation :

Have you analyzed the queries to ensure that they are efficient? Have you examined the tables to ensure that they have indexes to support the joins? Have you minimized the amount of data selected? On 1/22/19 6:54 AM, Rangaraj G wrote: Hi, My question Our connection is 1100 parallel connect

RE: Memory and hard ware calculation :

Hi, My question Our connection is 1100 parallel connection and 1 GB I/p data and 1 GB o/p data in each connection, currently we are using 64 GB RAM and 8 core. But we need all the reports below 3 seconds. So kindly suggest expanding my hard ware and work memory. Is there any possibility to get

Re: PostgreSQL logical replication depends on WAL segments?

> > Note replication slots only prevent old *catalog* rows from being > removed, not old row versions in user created tables. > Thank you for that clarification. I can see this is noted clearly in the CAUTION statement here: https://www.postgresql.org/docs/current/logicaldecoding-explanation.html

Re: PostgreSQL logical replication depends on WAL segments?

On Tue, Jan 22, 2019 at 12:52 PM Andres Freund wrote: > Hi, > > On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote: > > P.S. do heed the advice of the others and get more familiar with the docs > > around WAL archiving. > > Logical replication doesn't normally interact with WAL archiving in any >

Re: PostgreSQL logical replication depends on WAL segments?

Hi, On 2019-01-22 11:57:00 -0600, Jeremy Finzel wrote: > > > > That is news to me. Can you provide a citation for this? > > > > I can see the confusion in what I said. To clarify, I really meant that in > order to retrieve that data that you need in really old WAL segments, you > need to keep yo

Re: PostgreSQL logical replication depends on WAL segments?

Hi, On 2019-01-22 11:10:27 -0600, Jeremy Finzel wrote: > P.S. do heed the advice of the others and get more familiar with the docs > around WAL archiving. Logical replication doesn't normally interact with WAL archiving in any way, so that seems orthogonal. Greetings, Andres Freund

Re: PostgreSQL logical replication depends on WAL segments?

Hi, On 2019-01-22 14:18:12 +0100, Josef Machytka wrote: > Hello, I already tried to ask on stackoverflow but so far without success. > ( > https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments > ) > > Could someone help me please? > > > > I am

Re: PostgreSQL logical replication depends on WAL segments?

> > That is news to me. Can you provide a citation for this? > I can see the confusion in what I said. To clarify, I really meant that in order to retrieve that data that you need in really old WAL segments, you need to keep your replication slot in a position that will hold that WAL in place. A

Re: PostgreSQL logical replication depends on WAL segments?

On 1/22/19 9:10 AM, Jeremy Finzel wrote: Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set wal_keep_segments to some really high number and stop

Re: PostgreSQL logical replication depends on WAL segments?

> > Thanks, I see... So if I understand it correctly - since I have quite big > partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in > several others I presume I had to set wal_keep_segments to some really high > number and stop our security cronjob cleaning old WAL segments (b

Re: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

On 2019-Jan-22, Denisa Cirstescu wrote: > I am trying to add a new column to a really big table and to define an INDEX > and a FOREIGN KEY on that new column using the following instructions: > > ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER; > CREATE INDEX IDX_Employee_DepartmentId ON Em

Re: Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

> The table is huge and it takes a lot of time to add the INDEX and the FOREIGN > KEY although all values are NULL. > Considering that the new DepartmentId column is NULL for all rows at this > point, is there a way to make the INDEX and FOREIGN KEY creation run faster? In your script to creat

Decrease time needed to CREATE INDEX and FOREIGN KEY on new table column which has all values NULL

Hello all, I am trying to add a new column to a really big table and to define an INDEX and a FOREIGN KEY on that new column using the following instructions: ALTER TABLE Employee ADD COLUMN DepartmentId INTEGER; CREATE INDEX IDX_Employee_DepartmentId ON Employee(DepartmentId); ALTER TABLE Emplo

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 6:01 μ.μ., Josef Machytka wrote: https://www.postgresql.org/docs/11/logical-replication-architecture.html Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume

Re: PostgreSQL logical replication depends on WAL segments?

> https://www.postgresql.org/docs/11/logical-replication-architecture.html > > Thanks, I see... So if I understand it correctly - since I have quite big partitions like ~30 GB each in one parent table and from ~1GB to ~5 GB in several others I presume I had to set wal_keep_segments to some really h

Re: PostgreSQL logical replication depends on WAL segments?

On 1/22/19 5:18 AM, Josef Machytka wrote: Hello, I already tried to ask on stackoverflow but so far without success. (https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments) Could someone help me please? I am successfully using logical replicat

Re: Marc G. Fournier Invoice

Greetings, Hopefully everyone realizes this but just to be clear- the below (truncated) was spam and has been hidden from the archives. Apologies for it getting through, we'll look into what we can do to avoid having it happen again in the future... Please do *not* click the link that was in tha

Re: PostgreSQL logical replication depends on WAL segments?

On 22/1/19 3:18 μ.μ., Josef Machytka wrote: Hello, I already tried to ask on stackoverflow but so far without success. (https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments) Could someone help me please? I am successfully using logical replic

PostgreSQL logical replication depends on WAL segments?

Hello, I already tried to ask on stackoverflow but so far without success. ( https://stackoverflow.com/questions/54292816/postgresql-logical-replication-depends-on-wal-segments ) Could someone help me please? I am successfully using logical replication between 2 PG 11 cloud VMs for latest d

RE: Tuning threshold for BAS_BULKREAD (large tables)

On Tuesday, January 22, 2019 5:36 PM, Ron wrote: >How can a subset of the database be larger than the database? Oops. Sorry, I made a mistake on that part. What I meant was how does Postgres handle the large relations caching in terms of performance, especially if majority of the data it has to r

Re: Tuning threshold for BAS_BULKREAD (large tables)

On 1/22/19 1:35 AM, Jamison, Kirk wrote: Hi, I have a source code-related question on BufferAccessStrategyType BAS_BULKREAD. Currently, this access method is set internally to cache tables larger than 1/4 of shared_buffers. src/backend/access/heap/heapam.c:initscan() if (!RelationUs