Re: Logical replication very slow

[Boris Sagadin]

Doing an initial replica.

postgres 119454 93.5 25.9 34613692 32649656 ?   Rs   07:16  32:45  \_
postgres: 10/main: bgworker: logical replication worker for subscription
24783 sync 16500

I've cancelled the sync, set the tables to unlogged type and started it
again. I think it helped, still much slower than binary, but better, about
40MB/s. Will set them back to logged type after initial replica is done.

After the initial replica, there aren't that many updates, so it's OK then.
But if a need for a new slave arises, waiting a few days for initial
replica to finish, which in binary replication case is just hours, can be a
big problem for us.

Boris


On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> On 25/2/19 8:52 π.μ., Boris Sagadin wrote:
>
> Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04,
> NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at
> good speed, around 500MB/s. Trying LR now for a big table (about 1.4TB with
> 2 indexes) and the speed is only about 2MB/s.
>
> Is logical replication subscriber in "streaming" state or in initial
> snapshot? What's the behavior after the initial snapshot, when it gets into
> streaming state?
>
> Checked disk util with iostat and only about 20% utilized on master, 15%
> on target, CPU load on master is low. On slave I can see the "logical
> replication worker" process is taking about 70% CPU time on a single core,
> machine has 16 cores.
>
> Is there a setting I am missing here? Any ideas appreciated.
>
> Boris
>
>
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Thomas Munro]

On Thu, Feb 21, 2019 at 2:42 PM Mike Yeap  wrote:
> openldap-clients.x86_64 2.4.44-21.el7_6
> @updates
> openldap-devel.i686 2.4.44-21.el7_6updates
> openldap-devel.x86_64   2.4.44-21.el7_6updates
> openldap.i686   2.4.44-21.el7_6updates
> openldap-servers-sql.x86_64 2.4.44-21.el7_6updates
> openldap-servers.x86_64 2.4.44-21.el7_6updates
> openldap.x86_64 2.4.44-21.el7_6
> @updates

> On Wed, Feb 20, 2019 at 10:17 PM Tom Lane  wrote:
>> With OpenLDAP versions 2.4.24 through 2.4.31, inclusive, PostgreSQL
>> backends can crash at exit.  Raise a warning during "configure" based on
>> the compile-time OpenLDAP version number, and test the crash scenario in
>> the dblink test suite.  Back-patch to 9.0 (all supported versions).

Clearly 2.4.44 is not in the range 2.4.24 through 2.4.31.  Perhaps the
dangerous range is out of date?  Hmm, so Noah's analysis[1] says this
is a clash between libldap_r.so (used by libpq) and libldap.so (used
by the server), specifically in destructor/exit code.  Curiously, in a
thread about Curl's struggles with this problem, I found a claim[2]
that Debian decided to abandon the non-"_r" variant and just use _r
always.  Sure enough, on my Debian buster VM I see a symlink
libldap-2.4.so.2 -> libldap_r-2.4.so.2.  So essentially Debian and
friends have already forced Noah's first option on users:

> 1. Link the backend with libldap_r, so we never face the mismatch. On some
> platforms, this means also linking in threading libraries.

FreeBSD and CentOS systems near me have separate libraries still.

[1] 
https://www.postgresql.org/message-id/flat/20140612210219.GA705509%40tornado.leadboat.com
[2] https://www.openldap.org/lists/openldap-technical/201608/msg00094.html

-- 
Thomas Munro
https://enterprisedb.com

Re: Logical replication very slow

[Achilleas Mantzios]

On 25/2/19 9:59 π.μ., Boris Sagadin wrote:


Doing an initial replica.

postgres 119454 93.5 25.9 34613692 32649656 ?   Rs   07:16  32:45  \_ postgres: 
10/main: bgworker: logical replication worker for subscription 24783 sync 16500

I've cancelled the sync, set the tables to unlogged type and started it again. I think it helped, still much slower than binary, but better, about 40MB/s. Will set them back to logged type after 
initial replica is done.

Will this work at all? Doesn't unlogged mean no changes are written to the WAL? 
What if you just copy by dump and then just add and refresh without |copy_data ?
|


After the initial replica, there aren't that many updates, so it's OK then. But if a need for a new slave arises, waiting a few days for initial replica to finish, which in binary replication case 
is just hours, can be a big problem for us.


Boris



On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

On 25/2/19 8:52 π.μ., Boris Sagadin wrote:

Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04, 
NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at good 
speed, around 500MB/s. Trying LR now for
a big table (about 1.4TB with 2 indexes) and the speed is only about 2MB/s.


Is logical replication subscriber in "streaming" state or in initial 
snapshot? What's the behavior after the initial snapshot, when it gets into streaming 
state?

Checked disk util with iostat and only about 20% utilized on master, 15% on target, 
CPU load on master is low. On slave I can see the "logical replication worker" 
process is taking about 70%
CPU time on a single core, machine has 16 cores.

Is there a setting I am missing here? Any ideas appreciated.

Boris






-- 
Achilleas Mantzios

IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

Re: Logical replication very slow

[Boris Sagadin]

I think it should. I set it to unlogged on target/slave server only. One
other table which is much smaller and already replicated receives changes
from master.

About settings copy_data to false, nice idea, I'll try that too and compare
speed.



On Mon, Feb 25, 2019 at 9:51 AM Achilleas Mantzios <
ach...@matrix.gatewaynet.com> wrote:

> On 25/2/19 9:59 π.μ., Boris Sagadin wrote:
>
>
> Doing an initial replica.
>
> postgres 119454 93.5 25.9 34613692 32649656 ?   Rs   07:16  32:45  \_
> postgres: 10/main: bgworker: logical replication worker for subscription
> 24783 sync 16500
>
> I've cancelled the sync, set the tables to unlogged type and started it
> again. I think it helped, still much slower than binary, but better, about
> 40MB/s. Will set them back to logged type after initial replica is done.
>
> Will this work at all? Doesn't unlogged mean no changes are written to the
> WAL? What if you just copy by dump and then just add and refresh without 
> copy_data
> ?
>
>
> After the initial replica, there aren't that many updates, so it's OK
> then. But if a need for a new slave arises, waiting a few days for initial
> replica to finish, which in binary replication case is just hours, can be a
> big problem for us.
>
> Boris
>
>
> On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios <
> ach...@matrix.gatewaynet.com> wrote:
>
>> On 25/2/19 8:52 π.μ., Boris Sagadin wrote:
>>
>> Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04,
>> NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at
>> good speed, around 500MB/s. Trying LR now for a big table (about 1.4TB with
>> 2 indexes) and the speed is only about 2MB/s.
>>
>> Is logical replication subscriber in "streaming" state or in initial
>> snapshot? What's the behavior after the initial snapshot, when it gets into
>> streaming state?
>>
>> Checked disk util with iostat and only about 20% utilized on master, 15%
>> on target, CPU load on master is low. On slave I can see the "logical
>> replication worker" process is taking about 70% CPU time on a single core,
>> machine has 16 cores.
>>
>> Is there a setting I am missing here? Any ideas appreciated.
>>
>> Boris
>>
>>
>>
>>
>>
>> --
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>

Re: Logical replication very slow

[Achilleas Mantzios]

On 25/2/19 2:06 μ.μ., Boris Sagadin wrote:

I think it should. I set it to unlogged on target/slave server only. One other 
table which is much smaller and already replicated receives changes from master.

Ah, ok then.


About settings copy_data to false, nice idea, I'll try that too and compare 
speed.



Please note that ppl here tend to not encourage top posting. Generally It's ok 
for business emails elsewhere, but here straight quoting is better and more 
accurate.




On Mon, Feb 25, 2019 at 9:51 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

On 25/2/19 9:59 π.μ., Boris Sagadin wrote:


Doing an initial replica.

postgres 119454 93.5 25.9 34613692 32649656 ?   Rs   07:16  32:45  \_ 
postgres: 10/main: bgworker: logical replication worker for subscription 24783 
sync 16500

I've cancelled the sync, set the tables to unlogged type and started it 
again. I think it helped, still much slower than binary, but better, about 
40MB/s. Will set them back to logged type
after initial replica is done.

Will this work at all? Doesn't unlogged mean no changes are written to the 
WAL? What if you just copy by dump and then just add and refresh without 
|copy_data ?
|


After the initial replica, there aren't that many updates, so it's OK then. 
But if a need for a new slave arises, waiting a few days for initial replica to 
finish, which in binary replication
case is just hours, can be a big problem for us.

Boris



On Mon, Feb 25, 2019 at 8:08 AM Achilleas Mantzios mailto:ach...@matrix.gatewaynet.com>> wrote:

On 25/2/19 8:52 π.μ., Boris Sagadin wrote:

Doing an initial replica and trying to find a bottleneck, Ubuntu 16.04, 
NVMe disks, PgSQL v10.7, AWS. With binary replication, DB is replicated at good 
speed, around 500MB/s. Trying LR now
for a big table (about 1.4TB with 2 indexes) and the speed is only 
about 2MB/s.


Is logical replication subscriber in "streaming" state or in initial 
snapshot? What's the behavior after the initial snapshot, when it gets into streaming 
state?

Checked disk util with iostat and only about 20% utilized on master, 15% on 
target, CPU load on master is low. On slave I can see the "logical replication 
worker" process is taking about
70% CPU time on a single core, machine has 16 cores.

Is there a setting I am missing here? Any ideas appreciated.

Boris






-- 
Achilleas Mantzios

IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt




-- 
Achilleas Mantzios

IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt




--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

RE: BDR 1.0: background worker wants to start that should not be there

[Daniel Fink (PDF)]

Hi Alvaro,

Sorry for the late reply. Your mail was in the spam folder.
I used the commands you suggested already.
But I found another issue.
I had two databases running BDR replication. I did not know that they have
separate bdr.nodes tables.
After I called bdr.bdr_part_by_node_names again, it removed the failing
workers.

Thanks for your help.

Best Regards,
Daniel

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com]
Sent: Tuesday, February 12, 2019 4:15 PM
To: Daniel Fink, PDF
Cc: pgsql-general
Subject: Re: BDR 1.0: background worker wants to start that should not be
there

Hi.

You have deleted the node from BDR setup, but you still have to delete it
from the postgres logical replication:

SELECT * FROM pg_replication_slots;
SELECT pg_drop_replication_slot('YOURSLOT');

As a remark, based on my BDR experience, when your cluster has been damaged,
your best option is to rebuild it.

Saludos,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

(+51-1) 337-7813 Anexo 4002
www.ocs.pe

- Original Message -
From: "Daniel Fink, PDF" 
To: "pgsql-general" 
Sent: Tuesday, 12 February, 2019 09:52:09
Subject: BDR 1.0: background worker wants to start that should not be there

Hi all,



After I used bdr.bdr_part_by_node_names(*p_nodes text[]*) and removed the
nodes from bdr.bdr_nodes table I still get log errors about the nonexistent
pg_replication_slot:



< 2019-02-12 06:26:21.166 PST >LOG:  starting background worker process "bdr
(6208877715678412212,1,22576474,)->bdr (6449651545875285115"



How does postgresql deduce which workers need starting?

Is there a table I am missing?



Best Regards,




*DANIEL FINK*

*Senior Software Engineer*

*tel* (+49) 89.767062.20
*fax*(+49) 89.767062.11
email daniel.f...@pdf.com

*PDF Solutions GmbH*
* (**a PDF Solutions Company)*
Managing Director: Kimon Michaels
Schwanthalerstr. 10
D-80336 München, Germany

München HRB 87307
DE 128214899

*www.pdf.com *

--
This message may contain confidential and privileged information. If it has
been sent to you in error, please reply to advise the sender of the error
and then immediately permanently delete it and all attachments to it from
your systems. If you are not the intended recipient, do not read, copy,
disclose or otherwise use this message or any attachments to it. The sender
disclaims any liability for such unauthorized use.  PLEASE NOTE that all
incoming e-mails sent to PDF e-mail accounts will be archived and may be
scanned by us and/or by external service providers to detect and prevent
threats to our systems, investigate illegal or inappropriate behavior,
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any
concerns about this process, please contact us at legal.departm...@pdf.com
.

-- 
This message may contain confidential and privileged information. If it has 
been sent to you in error, please reply to advise the sender of the error 
and then immediately permanently delete it and all attachments to it from 
your systems. If you are not the intended recipient, do not read, copy, 
disclose or otherwise use this message or any attachments to it. The sender 
disclaims any liability for such unauthorized use.  PLEASE NOTE that all 
incoming e-mails sent to PDF e-mail accounts will be archived and may be 
scanned by us and/or by external service providers to detect and prevent 
threats to our systems, investigate illegal or inappropriate behavior, 
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any 
concerns about this process, please contact us at legal.departm...@pdf.com 
.

RE: bdr replication breaks down

[Daniel Fink (PDF)]

Hi Alvaro,

I could not determine what exactly the command was.
But I "fixed" it by using bdr.skip_changes_upto() to skip this change from
application.
The replication resumed after that.

Best Regards,
Daniel

-Original Message-
From: Alvaro Aguayo Garcia-Rada [mailto:aagu...@opensysperu.com]
Sent: Monday, February 11, 2019 2:21 PM
To: Daniel Fink (PDF)
Cc: pgsql-general
Subject: Re: bdr replication breaks down

What's the command you are running to trigger such behaviour?

Regards,

Alvaro Aguayo
Jefe de Operaciones
Open Comb Systems E.I.R.L.

(+51-1) 337-7813 Anexo 4002
www.ocs.pe

- Original Message -
From: "Daniel Fink (PDF)" 
To: "pgsql-general" 
Sent: Monday, 11 February, 2019 05:18:30
Subject: bdr replication breaks down

Hi all,



I have a bdr replication setup that worked fine until recently.

But it seems like one command cannot be replicated, and it breaks down the
whole process on all hosts where it is sent to.

I appended part of the log file, it gets repeated over and over.

Can I somehow omit this command and try if the next one works?



Best Regards,

Daniel Fink



< 2019-02-08 00:00:01.658 CET >ERROR:  schema "pg_temp_30" does not exist

< 2019-02-08 00:00:01.658 CET >CONTEXT:  apply QUEUED_DROP in commit
7D/2E3143B0, xid 106791715 commited at 2019-01-28 10:12:34.348577+01 (action
#2) from node (6208877715678412212,1,2942745)

< 2019-02-08 00:00:01.661 CET >LOG:  worker process: bdr
(6449651545875285115,1,16388,)->bdr (6208877715678412212,1, (PID 27640)
exited with exit code 1

< 2019-02-08 00:00:06.666 CET >LOG:  starting background worker process "bdr
(6449651545875285115,1,16388,)->bdr (6208877715678412212,1,"

< 2019-02-08 00:00:07.641 CET >ERROR:  schema "pg_temp_30" does not exist

< 2019-02-08 00:00:07.641 CET >CONTEXT:  apply QUEUED_DROP in commit
7D/2E3143B0, xid 106791715 commited at 2019-01-28 10:12:34.348577+01 (action
#2) from node (6208877715678412212,1,2942745)

< 2019-02-08 00:00:07.644 CET >LOG:  worker process: bdr
(6449651545875285115,1,16388,)->bdr (6208877715678412212,1, (PID 27645)
exited with exit code 1

< 2019-02-08 00:00:12.649 CET >LOG:  starting background worker process "bdr
(6449651545875285115,1,16388,)->bdr (6208877715678412212,1,"

< 2019-02-08 00:00:13.633 CET >ERROR:  schema "pg_temp_30" does not exist

< 2019-02-08 00:00:13.633 CET >CONTEXT:  apply QUEUED_DROP in commit
7D/2E3143B0, xid 106791715 commited at 2019-01-28 10:12:34.348577+01 (action
#2) from node (6208877715678412212,1,2942745)




*DANIEL FINK*

*Senior Software Engineer*

*tel* (+49) 89.767062.20
*fax*(+49) 89.767062.11
email daniel.f...@pdf.com

*PDF Solutions GmbH*
* (**a PDF Solutions Company)*
Managing Director: Kimon Michaels
Schwanthalerstr. 10
D-80336 München, Germany

München HRB 87307
DE 128214899

*www.pdf.com *

--
This message may contain confidential and privileged information. If it has
been sent to you in error, please reply to advise the sender of the error
and then immediately permanently delete it and all attachments to it from
your systems. If you are not the intended recipient, do not read, copy,
disclose or otherwise use this message or any attachments to it. The sender
disclaims any liability for such unauthorized use.  PLEASE NOTE that all
incoming e-mails sent to PDF e-mail accounts will be archived and may be
scanned by us and/or by external service providers to detect and prevent
threats to our systems, investigate illegal or inappropriate behavior,
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any
concerns about this process, please contact us at legal.departm...@pdf.com
.

-- 
This message may contain confidential and privileged information. If it has 
been sent to you in error, please reply to advise the sender of the error 
and then immediately permanently delete it and all attachments to it from 
your systems. If you are not the intended recipient, do not read, copy, 
disclose or otherwise use this message or any attachments to it. The sender 
disclaims any liability for such unauthorized use.  PLEASE NOTE that all 
incoming e-mails sent to PDF e-mail accounts will be archived and may be 
scanned by us and/or by external service providers to detect and prevent 
threats to our systems, investigate illegal or inappropriate behavior, 
and/or eliminate unsolicited promotional e-mails (“spam”).  If you have any 
concerns about this process, please contact us at legal.departm...@pdf.com 
.

update table with suppress_redundant_updates_trigger()

[wambacher]

Hi,

i'm doing a lot of updates in my database, but most of them are not
necessary at all (sorry, just detected it)

Therefore i installed a trigger to minimize real updates.

create trigger suppress_redundant_updates_boundaries
   before update on boundaries
   for each row execute procedure suppress_redundant_updates_trigger();

Is there a way to get the count of the real table updates?

Using pl/pgsql i'm updating boundaries  with

  update boundaries
 set id = bT2.id,
 country    = bT2.country,
 type   = 'admin',   
 value  = bT2.value,
...
    ,qm = bT2.qm
    ,lwqm   =
st_area(geography(coalesce(xlandarea,rT.way)))
   where id = bT2.id;

   if (found) then
  if (debug > 0) then raise notice 'real db update
of % done 2', bT2.id; end if;
  updatedDB := updatedDB + 1;
   end if;

i get a "wrong" result, because "found" is always true, even when the
records are identical (hope so) and an update should be suppressed by
the trigger.

Question: will "found" be set when update has been blocked by the
trigger - or does that not matter?

if "found" is always true: what else can i do?

regards

walter

-- 
My projects:

Admin Boundaries of the World 
Missing Boundaries

Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 

Re: Recommended way to enable data-checksums on Centos 7?

[Ken Tanzer]

On Sun, Feb 24, 2019 at 5:03 PM Adrian Klaver 
wrote:

> On 2/24/19 2:39 PM, Ken Tanzer wrote:

> I'm just wondering if there is a more preferred way to do this,

Not seeing anything responsive to this question, I'll assume that
PGSETUP_INITDB_OPTIONS
is the preferred method.



> and/or
> > any particular reason you can't pass options to initdb?  Also, since the
>
> You can pass options to initdb:
>
Yes.  I guess I shoulda said "cant pass option to initdb via the setup
script."



>
> It seems you cannot pass them to the script postgresql-11-setup. That
> would be a question for the packagers:
>
> https://yum.postgresql.org/contact.php
>
>
> > checksums are good for data integrity, and can only be done at init
> > time, I wonder if it's worth adding a note about it to that
> > documentation page?
> >
>

OK.  Do the yum packagers also maintain the documentation for downloading
and installing Postgresql?
(i.e., https://www.postgresql.org/download/linux/redhat/)  And are you
saying this question/topic is inappropriate for this list?

Thanks,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Recommended way to enable data-checksums on Centos 7?

[Adrian Klaver]

On 2/25/19 11:19 AM, Ken Tanzer wrote:



On Sun, Feb 24, 2019 at 5:03 PM Adrian Klaver > wrote:


On 2/24/19 2:39 PM, Ken Tanzer wrote: 

 > I'm just wondering if there is a more preferred way to do this, 

Not seeing anything responsive to this question, I'll assume that 
PGSETUP_INITDB_OPTIONS is the preferred method.


From looking at the script I would yes.




and/or
 > any particular reason you can't pass options to initdb?  Also,
since the

You can pass options to initdb:

Yes.  I guess I shoulda said "cant pass option to initdb via the setup 
script."


Technically using PGSETUP_INITDB_OPTIONS does pass the initdb options to 
the script:) If I am following correctly, what you want is something 
like the below from pg_ctl, correct?:


https://www.postgresql.org/docs/11/app-pg-ctl.html

pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]




It seems you cannot pass them to the script postgresql-11-setup. That
would be a question for the packagers:

https://yum.postgresql.org/contact.php


 > checksums are good for data integrity, and can only be done at init
 > time, I wonder if it's worth adding a note about it to that
 > documentation page?
 >


OK.  Do the yum packagers also maintain the documentation for 
downloading and installing Postgresql?
(i.e., https://www.postgresql.org/download/linux/redhat/)  And are you 
saying this question/topic is inappropriate for this list?


The question is not inappropriate as the above is the community site and 
this is the community list for general questions. It is just that the RH 
family packages are maintained outside of the core source by the folks 
listed at the contacts link. It is they who in the end will or will not 
make any changes. Just a matter of circles within circles in the 
'community'.




Thanks,
Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: update table with suppress_redundant_updates_trigger()

[Adrian Klaver]

On 2/25/19 10:42 AM, wambac...@posteo.de wrote:

Hi,

i'm doing a lot of updates in my database, but most of them are not 
necessary at all (sorry, just detected it)


Would it not be easier to just not do the unnecessary updates?

Or to put it another way what distinguishes necessary/unnecessary?



Therefore i installed a trigger to minimize real updates.

create trigger suppress_redundant_updates_boundaries
    before update on boundaries
    for each row execute procedure suppress_redundant_updates_trigger();

Is there a way to get the count of the real table updates?

Using pl/pgsql i'm updating boundaries  with

   update boundaries
  set id = bT2.id,
  country    = bT2.country,
  type   = 'admin',
  value  = bT2.value,
...
     ,qm = bT2.qm
     ,lwqm   = 
st_area(geography(coalesce(xlandarea,rT.way)))

    where id = bT2.id;

    if (found) then
   if (debug > 0) then raise notice 'real db update 
of % done 2', bT2.id; end if;

   updatedDB := updatedDB + 1;
    end if;

i get a "wrong" result, because "found" is always true, even when the 
records are identical (hope so) and an update should be suppressed by 
the trigger. >
Question: will "found" be set when update has been blocked by the 
trigger - or does that not matter?


if "found" is always true: what else can i do?


Untested:

IF NEW.* != OLD.* THEN
RETURN NEW.*
ELSE
RETURN NULL
END IF;


regards

walter

--
My projects:

Admin Boundaries of the World 
Missing Boundaries 


Emergency Map 
Postal Code Map (Germany only) 
Fools (QA for zipcodes in Germany) 
Postcode Boundaries of Germany 



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Recommended way to enable data-checksums on Centos 7?

[Ken Tanzer]

On Mon, Feb 25, 2019 at 1:35 PM Adrian Klaver 
wrote:

> If I am following correctly, what you want is something
> like the below from pg_ctl, correct?:
>
> https://www.postgresql.org/docs/11/app-pg-ctl.html
>
> pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]

...

> >  > checksums are good for data integrity, and can only be done at
> init
> >  > time, I wonder if it's worth adding a note about it to that
> >  > documentation page?
> >  >
> >


Something like that "-o" option would be nice.  But regardless of the
solution, and given how simple & streamlined the install path/directions
are, it seems to me to make sense to have some way of doing this that
doesn't involve having to read through the setup script.  That could be a
simpler option, or it could just be a footnote on the install page, next to
the initdb command, something like:

Optionally initialize the database and enable automatic start:
  /usr/pgsql-11/bin/postgresql-11-setup initdb (*)
  systemctl enable postgresql-11
  systemctl start postgresql-11

(*) Some options for initializing the database can't be changed later
(except by wiping out and reinitializing).  You can specify options by
running export PGSETUP_INITDB_OPTIONS="" before running this
command.  See  for
options.

Cheers,
Ken


-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Recommended way to enable data-checksums on Centos 7?

[Adrian Klaver]

On 2/25/19 2:05 PM, Ken Tanzer wrote:
On Mon, Feb 25, 2019 at 1:35 PM Adrian Klaver > wrote:


If I am following correctly, what you want is something
like the below from pg_ctl, correct?:

https://www.postgresql.org/docs/11/app-pg-ctl.html

pg_ctl init[db] [-D datadir] [-s] [-o initdb-options]

...

 >      > checksums are good for data integrity, and can only be
done at init
 >      > time, I wonder if it's worth adding a note about it to that
 >      > documentation page?
 >      >
 > 



Something like that "-o" option would be nice.  But regardless of the 
solution, and given how simple & streamlined the install path/directions 
are, it seems to me to make sense to have some way of doing this that 
doesn't involve having to read through the setup script.  That could be 


From looking at the script running:

postgresql-11-setup --help

will get you that information.

a simpler option, or it could just be a footnote on the install page, 
next to the initdb command, something like:


That would be nice, unfortunately I am not the person to get that done.
Your best bet on this would be to file an issue here:

https://yum.postgresql.org/contact.php

You will need a community account to do this.



Optionally initialize the database and enable automatic start:
   /usr/pgsql-11/bin/postgresql-11-setup initdb (*)
   systemctl enable postgresql-11
   systemctl start postgresql-11

(*) Some options for initializing the database can't be changed later 
(except by wiping out and reinitializing).  You can specify options by 
running export PGSETUP_INITDB_OPTIONS="" before running 
this command.  See 
 for options.

Cheers,
Ken


--
AGENCY Software
A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list 
 to

learn more about AGENCY or
follow the discussion.



--
Adrian Klaver
adrian.kla...@aklaver.com

9.0 standby - could not open file global/XXXXX

[Filip Rembiałkowski]

Hi.

There is a large (>5T) database on PostgreSQL 9.0.23.

I would like to setup new WAL-shipping standby.
https://www.postgresql.org/docs/9.0/warm-standby.html

On my way I find unexpected issues. Here's the story, in short:

1. WAL archiving to remote archive is setup & verified

2. base backup is transferred directly to new server using
pg_start_backup + rsync + pg_stop_backup.

3. recovery.conf is created

4. Server is started and consumes all the remaining WAL segments
accumulated in the archive - finishing with optimistic message LOG:
consistent recovery state reached at 9FC1/112BEE10.

5. When I go to postgres on the standby and try to connect system
"postgres" database psql: FATAL:  could not open file "global/11819":
No such file or directory

I guessed the OID refereds to the pg_authid, but other system tables
might be affected too.

What could be wrong here?

Thanks!

Re: 9.0 standby - could not open file global/XXXXX

[Stephen Frost]

Greetings,

* Filip Rembiałkowski (filip.rembialkow...@gmail.com) wrote:
> There is a large (>5T) database on PostgreSQL 9.0.23.

First off, I hope you understand that 9.0 has been *long* out of
support and that you *really* need to upgrade to a supported version of
PostgreSQL (9.4 and up these days...).

> I would like to setup new WAL-shipping standby.
> https://www.postgresql.org/docs/9.0/warm-standby.html
> 
> On my way I find unexpected issues. Here's the story, in short:
> 
> 1. WAL archiving to remote archive is setup & verified
> 
> 2. base backup is transferred directly to new server using
> pg_start_backup + rsync + pg_stop_backup.
> 
> 3. recovery.conf is created
> 
> 4. Server is started and consumes all the remaining WAL segments
> accumulated in the archive - finishing with optimistic message LOG:
> consistent recovery state reached at 9FC1/112BEE10.
> 
> 5. When I go to postgres on the standby and try to connect system
> "postgres" database psql: FATAL:  could not open file "global/11819":
> No such file or directory

That seems pretty odd- does that file exist on the existing database
system..?

Thanks!

Stephen


signature.asc
Description: PGP signature

PostgreSQL DB Maintenance and Partitioning to keep data longer.

[github kran]

Hello pgsql-General Team,



We have currently using Postgresql DB which is growing about 1.4 billion
records/month with a total of about 16 to 17 billion records/year. The DB
storage is growing about 6.8 TB/year including all indexes and data.


Current total DB Storage we got is 60 TB.



*Use case   *

We wanted to move the partitioned data on a monthly basis older than 90
days to a newer historical database from the existing database (which is
another PostgreSQL DB) so that we can build a REST API to access the data
for the clients.  Intention is to keep this data for > 10 years and separate

out the DB data into hot and cold using separate databases.



*Questions.*

1) Can PostgreSQL can handle this volume with good performance if we want
to use the database for next 10 years ? (I know this depends lot of other
factors like our index bloat sizes and size of the record and partition
strategy)

2) Do we have any similar uses cases of this volume and storage ? any
information would be helpful.

3) How many partitions we can have considering better IO, query response
times. ?

4) We want to merge fewer partitions which are based on weekly to 1 month
partitions. Can we merge the partitions and use them in the newer database.

5) Can we use export and import partitions into the newer database ?.



Any information/resources/design ideas would be helpful.



Thanks

atomically replace partition of range partitioned table

[Kevin Wilkinson]

i have a range partitioned table with a brin index that i am using for 
"Internet-of-Things" kind of data (essentially timeseries data about 
some entities). the partition key is a timestamp. data is only loaded to 
the "current" partition and data is never modified. older partitions are 
static. the index key is the entity identifier. my problem is that the 
brin index on the current partition does not perform well (because 
summarization is not immediate) so i also include a b-tree index on the 
current partition. when the current partition is "full", i create a new 
partition.


i then want to optimize the previous current partition by (1) clustering 
the partition on the index key to give me a correlation of 1 and (2) 
dropping the b-tree index to reclaim its storage space. i want to do 
this atomically so that querying over the full table is not interrupted. 
of course, the cluster command is not usable because it takes an 
exclusive lock. so, i do the following.


1. create a new partition table by copying the old partition table, 
ordered by index key. both tables will have the same partition key range.

2. create a brin index on the new table.
3. detach the old partition table from the parent and drop it.
4. attach the new partition table to the parent.

what i need is for steps 3-4 to be atomic or quick. but, step 4 takes 
tens of seconds, sometimes almost a minute. i tried adding a check 
constraint to the new table so that it would not be scanned when 
attached but that does not help. is there any way to do want i want?


thanks,

kevin

Re: 9.0 standby - could not open file global/XXXXX

[Andres Freund]

Hi,

On 2019-02-25 20:06:42 +0100, Filip Rembiałkowski wrote:
> There is a large (>5T) database on PostgreSQL 9.0.23.

As Stephen said, this is long out of support.


> 2. base backup is transferred directly to new server using
> pg_start_backup + rsync + pg_stop_backup.

Do you exclude any files? Remove any? If so which?

Greetings,

Andres Freund

Re: atomically replace partition of range partitioned table

[David Rowley]

On Tue, 26 Feb 2019 at 12:03, Kevin Wilkinson
 wrote:
> 1. create a new partition table by copying the old partition table,
> ordered by index key. both tables will have the same partition key range.
> 2. create a brin index on the new table.
> 3. detach the old partition table from the parent and drop it.
> 4. attach the new partition table to the parent.
>
> what i need is for steps 3-4 to be atomic or quick. but, step 4 takes
> tens of seconds, sometimes almost a minute. i tried adding a check
> constraint to the new table so that it would not be scanned when
> attached but that does not help. is there any way to do want i want?

TBH, I think the check constraint checking code needs a bit more work
in regards to this. It does not appear to be able to use strict quals
in the constraint to validate that the columns cannot be NULL.

# create table rp (a int ) partition by range(a);
CREATE TABLE
# create table rp1 (a int);
CREATE TABLE
# insert into rp1 select generate_series(1,200);
INSERT 0 200
# \timing on

Spot the difference here:

# alter table rp1 add constraint rp1_a_chk check(a >= 1 and a < 201);
ALTER TABLE
Time: 157.391 ms
# alter table rp attach partition rp1 for values from(1) to (201);
ALTER TABLE
Time: 184.188 ms
# alter table rp detach partition rp1;
# alter table rp1 drop constraint rp1_a_chk;

# alter table rp1 add constraint rp1_a_chk check(a is not null and a
>= 1 and a < 201);
ALTER TABLE
Time: 179.750 ms
# alter table rp attach partition rp1 for values from(1) to (201);
INFO:  partition constraint for table "rp1" is implied by existing constraints
ALTER TABLE
Time: 4.969 ms
# alter table rp detach partition rp1;
# alter table rp1 drop constraint rp1_a_chk;

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: LDAP authenticated session terminated by signal 11: Segmentation fault, PostgresSQL server terminates other active server processes

[Mike Yeap]

Hi Thomas, does that mean the bug is still there?

Regards,
Mike Yeap

On Mon, Feb 25, 2019 at 4:06 PM Thomas Munro  wrote:

> On Thu, Feb 21, 2019 at 2:42 PM Mike Yeap  wrote:
> > openldap-clients.x86_64 2.4.44-21.el7_6
> @updates
> > openldap-devel.i686 2.4.44-21.el7_6
> updates
> > openldap-devel.x86_64   2.4.44-21.el7_6
> updates
> > openldap.i686   2.4.44-21.el7_6
> updates
> > openldap-servers-sql.x86_64 2.4.44-21.el7_6
> updates
> > openldap-servers.x86_64 2.4.44-21.el7_6
> updates
> > openldap.x86_64 2.4.44-21.el7_6
> @updates
>
> > On Wed, Feb 20, 2019 at 10:17 PM Tom Lane  wrote:
> >> With OpenLDAP versions 2.4.24 through 2.4.31, inclusive, PostgreSQL
> >> backends can crash at exit.  Raise a warning during "configure"
> based on
> >> the compile-time OpenLDAP version number, and test the crash
> scenario in
> >> the dblink test suite.  Back-patch to 9.0 (all supported versions).
>
> Clearly 2.4.44 is not in the range 2.4.24 through 2.4.31.  Perhaps the
> dangerous range is out of date?  Hmm, so Noah's analysis[1] says this
> is a clash between libldap_r.so (used by libpq) and libldap.so (used
> by the server), specifically in destructor/exit code.  Curiously, in a
> thread about Curl's struggles with this problem, I found a claim[2]
> that Debian decided to abandon the non-"_r" variant and just use _r
> always.  Sure enough, on my Debian buster VM I see a symlink
> libldap-2.4.so.2 -> libldap_r-2.4.so.2.  So essentially Debian and
> friends have already forced Noah's first option on users:
>
> > 1. Link the backend with libldap_r, so we never face the mismatch. On
> some
> > platforms, this means also linking in threading libraries.
>
> FreeBSD and CentOS systems near me have separate libraries still.
>
> [1]
> https://www.postgresql.org/message-id/flat/20140612210219.GA705509%40tornado.leadboat.com
> [2] https://www.openldap.org/lists/openldap-technical/201608/msg00094.html
>
> --
> Thomas Munro
> https://enterprisedb.com
>