Open source monitoring streaming replication

[Vasu Madhineni]

Hi Team,

Is there any open source monitoring tool can we use to configure monitoring
PostgreSQL Primary-Standby as a single group instead of adding servers as
individual monitoring.

In my case I am using repmgr for automatic failover between primary and
standby.  primary, standby instances are docker containers in different
hosts.


Thanks in advance.

Regards,
Vasu Madhineni

facing problem in outparameters in c

[Mahesh Bodepati]

Hi Team,

i am facing the problem in passing the outparameters in postgres package 
calling by using c language.can you please help us to solve the problem..

below is way iam calling the package.

 snprintf(buffer, sizeof(buffer),"SELECT  
smp_ss7_mig_dev.pr_pkg_oss_networkserver_pr_sp_apply_sl_yn('%s','%s','%s', %d 
,%d)",m_h_imsi.arr,m_h_hlraddress.arr,m_h_vlraddress.arr,m_ht_message,m_ht_msgType,allow_yn,po_ns_relay_reason,po_sl_relay_reason);

 res = PQexec(con,buffer);
 if (PQresultStatus(res) != PGRES_TUPLES_OK) {

printf("No data retrieved\n");
PQclear(res);
PQfinish(con);
exit(1);
}



printf("allow_yn===%d\tpo_ns_relay_reason==%d\tpo_sl_relay_reason==%s\n",allow_yn,po_ns_relay_reason,po_sl_relay_reason.arr);




Thanks & Regards,
Mahesh Bodepati
Software Engineer- Engineering
XIUS
INNOVATE . LEAD.
mahesh.bodep...@xius.com
http://www.xius.com

Re: postgres materialized view refresh performance

[Philip Semanchuk]

> On Oct 25, 2020, at 10:52 PM, Ayub M  wrote:
> 
> Thank you both.
> 
> As for the mview refresh taking long --
>   • The mview gets refreshed in a couple of mins sometimes and sometimes it 
> takes hours. When it runs for longer, there are no locks and no resource 
> shortage, the number of recs in the base table is 6m (7.5gb) which is not 
> huge so why does it take so long to refresh the mview?
> 
> Does the run time correlate with the number of changes being made?  
> 
> -- Almost the same number of records are present in the base table (6 million 
> records). The base table gets truncated and reloaded everytime with almost 
> the same number of records. 
> 
> And the mview is a simple select from this one base table. 
> 
> The mview has around 10 indexes, 1 unique and 9 non-unique indexes. 
> 
> Population of the base tables takes about 2 mins, using "insert into select 
> from table", but when the mview is created for the first time it takes 16 
> minutes. Even when I remove all but one unique index it takes about 7 
> minutes. Any clue as to why it is taking longer than the create of the base 
> table (which is 2 mins).

Do you know if it’s executing a different plan when it takes a long time? 
auto_explain can help with that.



> 
> On Fri, Oct 23, 2020 at 10:53 AM Philip Semanchuk 
>  wrote:
> 
> 
> > On Oct 23, 2020, at 9:52 AM, Ravi Krishna  wrote:
> > 
> >> My understanding is that when CONCURRENTLY is specified, Postgres 
> >> implements the refresh as a series of INSERT, UPDATE,
> >> and DELETE statements on the existing view. So the answer to your question 
> >> is no, Postgres doesn’t create another table and
> >> then swap it.
> > 
> > The INSERTS/UPDATE/DELETE happens only for the difference.  PG first 
> > creates a new temp table and then compares it with
> > the MV and detects the difference.  That is why for CONCURRENTLY, a unique 
> > index is required on the MV.
> 
> Yes, thank you, that’s what I understand too but I expressed it very poorly. 
> 
> 
> 
> -- 
> Regards,
> Ayub

PG Crashed at CheckExprStillValid with state == NULL (PG 11.2)

[Andy Fan]

Hi:

The call stack is below:

#0 0x7fdecbe924eb in raise () from /lib64/libpthread.so.0
#1 0x00886888 in . ()
#2 
#3 0x006cb364 in ?? ()
#4 0x006cb2a4 in CheckExprStillValid ()  --> *state == NULL*
#5 0x006c9563 in ExecInterpExprStillValid ()
#6 0x006f2601 in ExecIndexEvalRuntimeKeys ()
#7 0x006ea8b5 in ExecReScanBitmapIndexScan ()
#8 0x006c20fa in ExecReScan ()
#9 0x006e8c18 in ExecReScanBitmapAnd ()
#10 0x006c21ca in ExecReScan ()
#11 0x006c2232 in ExecReScan ()
#12 0x006fed4f in ExecReScanNestLoop ()
#13 0x006c212e in ExecReScan ()
#14 0x006fea5b in ?? ()
#15 0x006dab67 in ?? ()
#16 0x006fea7d in ?? ()
#17 0x006dab67 in ?? ()
#18 0x006e32c5 in ?? ()
#19 0x006dab67 in ?? ()
#20 0x00701ba0 in ?? ()
#21 0x006dab67 in ?? ()
#22 0x00702161 in ?? ()
#23 0x006dab67 in ?? ()
#24 0x006d22ed in standard_ExecutorRun ()
#25 0x7fdec4140537 in ?? () from /u01/..._stat_plans.so
#26 0x7fdec3b21670 in ?? () from /u01/...l/pg_stat_statements.so
#27 0x7fdec3719850 in ?? () from /u01/.../auto_explain.so
#28 0x0088b3ef in ?? ()
#29 0x0088af73 in PortalRun ()
#30 0x00885cf1 in PostgresMain ()
#31 0x007d9598 in ?? ()
#32 0x007d8aa3 in ?? ()
#33 0x007d5579 in PostmasterMain ()
#34 0x0072cefc in main ()


In my environment, if I run the query in psql, everything is good.  However
if
I run it in some java code(including other sql),  it would crash for 95%
cases.
I'm sorry that I can't provide a runnable test case,  I'm posting here just
to see
if anyone has run into the similar case before or can provide some hints on
this.
Thanks!

-- 
Best Regards
Andy Fan

Re: postgres materialized view refresh performance

[Ayub M]

It's a simple sequential scan plan of one line, just reading the base table
sequentially.

On Mon, Oct 26, 2020, 9:21 AM Philip Semanchuk 
wrote:

>
>
> > On Oct 25, 2020, at 10:52 PM, Ayub M  wrote:
> >
> > Thank you both.
> >
> > As for the mview refresh taking long --
> >   • The mview gets refreshed in a couple of mins sometimes and sometimes
> it takes hours. When it runs for longer, there are no locks and no resource
> shortage, the number of recs in the base table is 6m (7.5gb) which is not
> huge so why does it take so long to refresh the mview?
> >
> > Does the run time correlate with the number of changes being made?
> >
> > -- Almost the same number of records are present in the base table (6
> million records). The base table gets truncated and reloaded everytime with
> almost the same number of records.
> >
> > And the mview is a simple select from this one base table.
> >
> > The mview has around 10 indexes, 1 unique and 9 non-unique indexes.
> >
> > Population of the base tables takes about 2 mins, using "insert into
> select from table", but when the mview is created for the first time it
> takes 16 minutes. Even when I remove all but one unique index it takes
> about 7 minutes. Any clue as to why it is taking longer than the create of
> the base table (which is 2 mins).
>
> Do you know if it’s executing a different plan when it takes a long time?
> auto_explain can help with that.
>
>
>
> >
> > On Fri, Oct 23, 2020 at 10:53 AM Philip Semanchuk <
> phi...@americanefficient.com> wrote:
> >
> >
> > > On Oct 23, 2020, at 9:52 AM, Ravi Krishna 
> wrote:
> > >
> > >> My understanding is that when CONCURRENTLY is specified, Postgres
> implements the refresh as a series of INSERT, UPDATE,
> > >> and DELETE statements on the existing view. So the answer to your
> question is no, Postgres doesn’t create another table and
> > >> then swap it.
> > >
> > > The INSERTS/UPDATE/DELETE happens only for the difference.  PG first
> creates a new temp table and then compares it with
> > > the MV and detects the difference.  That is why for CONCURRENTLY, a
> unique index is required on the MV.
> >
> > Yes, thank you, that’s what I understand too but I expressed it very
> poorly.
> >
> >
> >
> > --
> > Regards,
> > Ayub
>
>

postgis update wants to install postgresql-13

[Hu Bert]

Hi there,

i hope i'm right here... i just wanted to install some debian updates
(buster) and saw that there are postgres/postgis updates as well.
Currently installed:

postgis   3.0.2+dfsg-2.pgdg100+1
postgis-doc   3.0.2+dfsg-2.pgdg100+1
postgresql-12 12.4-1.pgdg100+1
postgresql-12-postgis-2.5 2.5.5+dfsg-1.pgdg100+1
postgresql-12-postgis-2.5-scripts 2.5.5+dfsg-1.pgdg100+1
postgresql-12-postgis-3   3.0.2+dfsg-2.pgdg100+1
postgresql-12-postgis-3-scripts   3.0.2+dfsg-2.pgdg100+1

I use postgis extension in v2.5. The postgis package is needed for
shp2pgsql etc. Upgradeable packages are:

postgis-doc/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 all [upgradable from:
3.0.2+dfsg-2.pgdg100+1]
postgis/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 amd64 [upgradable from:
3.0.2+dfsg-2.pgdg100+1]
postgresql-12-postgis-3-scripts/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 all
[upgradable from: 3.0.2+dfsg-2.pgdg100+1]
postgresql-12-postgis-3/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 amd64
[upgradable from: 3.0.2+dfsg-2.pgdg100+1]
postgresql-client-common/buster-pgdg 220.pgdg100+1 all [upgradable
from: 217.pgdg100+1]
postgresql-common/buster-pgdg 220.pgdg100+1 all [upgradable from: 217.pgdg100+1]

But there are some unexpected packages to be installed:

The following NEW packages will be installed:
 postgresql-13 postgresql-13-postgis-3 postgresql-13-postgis-3-scripts
postgresql-client-13

If i get it right for the minor postgis update now postgresql-13 will
be installed. Is there any reason for this?


Best regards,
Hubert

Re: postgres materialized view refresh performance

[Philip Semanchuk]

> On Oct 26, 2020, at 10:45 AM, Ayub M  wrote:
> 
> It's a simple sequential scan plan of one line, just reading the base table 
> sequentially. 

Well, unless I have misunderstood you, the materialized view is basically just 
"select * from some_other_table”, the number of records in the source table is 
~6m and doesn’t change much, there are no locking delays and no resource 
shortages, but sometimes the refresh takes minutes, and sometimes hours. 
There’s something missing from the story here.

Some things to try or check on —
 - activity (CPU, disk, memory) during the period when the mat view is 
refreshing 
 - each time after you refresh the mat view, vacuum it
 - even better, if you can afford a brief lock on reads, run a vacuum full 
instead of just regular vacuum
 - if possible, at the same time as you create the problematic mat view, run a 
similar process that writes to a different mat view (tmp_throwaway_mat_view) 
without the CONCURRENTLY keyword and see if it behaves similarly. 



> 
> On Mon, Oct 26, 2020, 9:21 AM Philip Semanchuk  
> wrote:
> 
> 
> > On Oct 25, 2020, at 10:52 PM, Ayub M  wrote:
> > 
> > Thank you both.
> > 
> > As for the mview refresh taking long --
> >   • The mview gets refreshed in a couple of mins sometimes and sometimes it 
> > takes hours. When it runs for longer, there are no locks and no resource 
> > shortage, the number of recs in the base table is 6m (7.5gb) which is not 
> > huge so why does it take so long to refresh the mview?
> > 
> > Does the run time correlate with the number of changes being made?  
> > 
> > -- Almost the same number of records are present in the base table (6 
> > million records). The base table gets truncated and reloaded everytime with 
> > almost the same number of records. 
> > 
> > And the mview is a simple select from this one base table. 
> > 
> > The mview has around 10 indexes, 1 unique and 9 non-unique indexes. 
> > 
> > Population of the base tables takes about 2 mins, using "insert into select 
> > from table", but when the mview is created for the first time it takes 16 
> > minutes. Even when I remove all but one unique index it takes about 7 
> > minutes. Any clue as to why it is taking longer than the create of the base 
> > table (which is 2 mins).
> 
> Do you know if it’s executing a different plan when it takes a long time? 
> auto_explain can help with that.
> 
> 
> 
> > 
> > On Fri, Oct 23, 2020 at 10:53 AM Philip Semanchuk 
> >  wrote:
> > 
> > 
> > > On Oct 23, 2020, at 9:52 AM, Ravi Krishna  wrote:
> > > 
> > >> My understanding is that when CONCURRENTLY is specified, Postgres 
> > >> implements the refresh as a series of INSERT, UPDATE,
> > >> and DELETE statements on the existing view. So the answer to your 
> > >> question is no, Postgres doesn’t create another table and
> > >> then swap it.
> > > 
> > > The INSERTS/UPDATE/DELETE happens only for the difference.  PG first 
> > > creates a new temp table and then compares it with
> > > the MV and detects the difference.  That is why for CONCURRENTLY, a 
> > > unique index is required on the MV.
> > 
> > Yes, thank you, that’s what I understand too but I expressed it very 
> > poorly. 
> > 
> > 
> > 
> > -- 
> > Regards,
> > Ayub
> 

Re: Feature Requests

[Magnus Hagander]

On Sun, Oct 25, 2020 at 10:26 AM Nikolai Lusan  wrote:

> Hi,
>
> I was wondering where I can see open feature requests. One I would like
> to see is multi-master replication ... I did find a 2016 request that
> was marked as "planned", but to the best of my knowledge it hasn't made
> production yet.
>

PostgreSQL does not have such a thing as "open feature requests".

You can find patches that are currently being worked on at
https://commitfest.postgresql.org/, or on discussions in the list archives.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

Re: postgis update wants to install postgresql-13

[Adrian Klaver]

On 10/26/20 6:34 AM, Hu Bert wrote:

Hi there,

i hope i'm right here... i just wanted to install some debian updates
(buster) and saw that there are postgres/postgis updates as well.
Currently installed:

postgis   3.0.2+dfsg-2.pgdg100+1
postgis-doc   3.0.2+dfsg-2.pgdg100+1
postgresql-12 12.4-1.pgdg100+1
postgresql-12-postgis-2.5 2.5.5+dfsg-1.pgdg100+1
postgresql-12-postgis-2.5-scripts 2.5.5+dfsg-1.pgdg100+1
postgresql-12-postgis-3   3.0.2+dfsg-2.pgdg100+1
postgresql-12-postgis-3-scripts   3.0.2+dfsg-2.pgdg100+1

I use postgis extension in v2.5. The postgis package is needed for
shp2pgsql etc. Upgradeable packages are:

postgis-doc/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 all [upgradable from:
3.0.2+dfsg-2.pgdg100+1]
postgis/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 amd64 [upgradable from:
3.0.2+dfsg-2.pgdg100+1]
postgresql-12-postgis-3-scripts/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 all
[upgradable from: 3.0.2+dfsg-2.pgdg100+1]
postgresql-12-postgis-3/buster-pgdg 3.0.2+dfsg-4.pgdg100+1 amd64
[upgradable from: 3.0.2+dfsg-2.pgdg100+1]
postgresql-client-common/buster-pgdg 220.pgdg100+1 all [upgradable
from: 217.pgdg100+1]
postgresql-common/buster-pgdg 220.pgdg100+1 all [upgradable from: 217.pgdg100+1]

But there are some unexpected packages to be installed:

The following NEW packages will be installed:
  postgresql-13 postgresql-13-postgis-3 postgresql-13-postgis-3-scripts
postgresql-client-13

If i get it right for the minor postgis update now postgresql-13 will
be installed. Is there any reason for this?


Yes:

https://wiki.postgresql.org/wiki/Apt

"Note: This repository provides "postgresql", "postgresql-contrib", and 
"postgresql-client" meta-packages that depend on the latest 
postgresql-x.y, ... packages, similar to the ones present in Debian and 
Ubuntu. Once a new PostgreSQL version is released, these meta-packages 
will be updated to depend on the new version. If you rather want to stay 
with a particular PostgreSQL version, you should install specific 
packages like "postgresql-11" instead of "postgresql". "





Best regards,
Hubert





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

Copy json from couchbase to postgres

[Rushikesh socha]

Any suggestions on copying .json which is exported from couchbase
using cbexport json. I used copy command but most of them are throwing
error.

pgdocstore=# copy schaname.tablename from 'path/filename.json';
ERROR:  invalid input syntax for type json
DETAIL:  Token "H" is invalid.

Re: Copy json from couchbase to postgres

[David G. Johnston]

On Mon, Oct 26, 2020 at 1:10 PM Rushikesh socha 
wrote:

> Any suggestions on copying .json which is exported from couchbase
> using cbexport json. I used copy command but most of them are throwing
> error.
>
> pgdocstore=# copy schaname.tablename from 'path/filename.json';
> ERROR:  invalid input syntax for type json
> DETAIL:  Token "H" is invalid.
>

I'd suggest making a self-contained posting including the data you are
trying to import and gloss over how it is being generated.  You've gotten
enough correct to get a json input syntax error so its the actual data that
seems to matter.

David J.