Re: [GENERAL] Extension to rewrite queries before execution

[Kyotaro HORIGUCHI]

Hi, although I don't see what exactly you want to do,

> I am looking for an extension or a technique that will allow me to
> intercept a query by the exact query text, and replace that query with a
> different one.
> 
> The context is running a third-party app which issues queries I have no
> control over.  I'd like to intercept a specific query (which has no bind
> parameters) and either replace the query text with a different text which,
> for example, swaps out an "in list" clause to instead be an "exists
> (subquery)".

I don't know such an extension but,

> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.

pg_hint_plan can do this.

http://osdn.jp/projects/pghintplan/

It can change guc parameters for specific queries but only during
planning time. So setting enable_* works as expected but work_mem
may not do exactly as expected.

LOAD 'pg_hint_plan';
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 (SELECT a, -a FROM generate_series(0, ) a);
CREATE INDEX ON t1 (a);
INSERT INTO hint_plan.hints VALUES (0, 'EXPLAIN SELECT * FROM t1 WHERE a = ?;', 
'psql', 'set(enable_indexscan off)set(enable_bitmapscan off)');
ANALYZE t1;
SET pg_hint_plan.enable_hint_table TO yes;
EXPLAIN SELECT * FROM t1 WHERE a = 10;

DELETE FROM hint_plan.hints;
EXPLAIN SELECT * FROM t1 WHERE a = 10;


'SeqScan(t1)' does effectively the same thing for the case.

As you see in the example above, EXPLAIN is not specially treated
so it is needed so that it affects the EXPLAIN query. Setting
pg_hint_plan.debug_print to 'detailed' would be useful to see
what string to be fed as 'normalized query'.

Of course it costs the time to search the hint table per one
query execution.

> Is there anything out there like this?  This would be for 9.4.
> 
> I'm willing to put the query text, and its replacement, directly into the
> extension source code and compile it, but of course something more flexible
> would be ideal.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] repmgr won't update witness after failover

[Jony Cohen]

Hi,
The clone command just clones the data from node2 to node1, you need to
also register it with the `force` option to override the old record. (as if
you're building a new replica node...)
see:
https://github.com/2ndQuadrant/repmgr#converting-a-failed-master-to-a-standby

Regards,
 - Jony


On Sun, Aug 16, 2015 at 3:19 PM, Aviel Buskila  wrote:

> Hey,
> I think I know what the problem is,
> after the first failover when I clone the old master to be standby with
> the 'repmgr standby clone' command it seems that nothing updates the
> repl_nodes table with the new standby in my cluster so on the next failover
> the repmgrd is failed to find a new upcoming standby to failover..
>
> this issue is confirmed after that I manually updated the repl_nodes table
> after the clone so that the old master is now a standby database.
>
> now my question is:
> Where does is suppose to happen that after I issue the 'repmgr standby
> clone' the repl_nodes should be updated too about the new standby server?
>
> Best regards,
> Aviel Buskila
>
>
>
> 2015-08-16 12:11 GMT+03:00 Aviel Buskila :
>
>> hey,
>>
>> I have tried to set the configuration all over again, now the status of
>> 'repl_nodes' before the failover is:
>>
>> id | type | upstream_node_id | cluster | name | conninfo | priority |
>> active
>>
>> +-+---++--+-
>> 1 | master |  | cluster_name |node1| host=node1
>> dbname=repmgr port=5432 user=repmgr | 100 | t
>> 2 | standby|1| cluster_name |node2| host=node2
>> dbname=repmgr port=5432 user=repmgr | 100 | t
>>
>> 3 | witness|| cluster_name |node3| host=node3
>> dbname=repmgr port=5499 user=repmgr | 100 | t
>>
>>
>> repmgr is started on node2 and node3 (standby and witness) now when I
>> kill postgresmaster process I can see in the
>>
>> repmgrd log the following messages:
>>
>> [WARNING] connection to master has been lost, trying to recover... 60
>> seconds before failover decision
>>
>> [WARNING] connection to master has been lost, trying to recover... 50
>> seconds before failover decision
>>
>> [WARNING] connection to master has been lost, trying to recover... 40
>> seconds before failover decision
>>
>> [WARNING] connection to master has been lost, trying to recover... 30
>> seconds before failover decision
>>
>> [WARNING] connection to master has been lost, trying to recover... 20
>> seconds before failover decision
>>
>> [WARNING] connection to master has been lost, trying to recover... 10
>> seconds before failover decision
>>
>>
>> and than when it tried to elect node2 to be promoted it shows the
>> following messages:
>>
>> [DEBUG] connecting to: 'host=node2 user=repmgr dbname=repmgr
>> fallback_application_name='repmgr''
>>
>> [WARNING] unable to defermmine a valid master server; waiting 10 seconds
>> to retry...
>>
>> [ERROR] unable to determine a valid master node, terminating...
>>
>> [INFO] repmgrd terminating..
>>
>>
>>
>> what am I doing wrong?
>>
>>
>> El 14/08/15 a las 04:14, Aviel Buskila escribió:
>> > Hey,
>> > yes I did .. and still it wont fail back..
>>
>> Can you send over the output of "repmgr cluster show" before and after
>> the failover process?
>>
>> The output of SELECT * FROM repmgr_schema.repl_nodes; after the failover
>> (you need to change repmgr_schema with what you have configured).
>>
>> Also, which version of repmgr are you running?
>>
>> > 2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen > >:
>> >
>> >> Hi, did you make the old master follow the new one using repmgr?
>> >>
>> >> It doesn't update itself automatically...
>> >> From the looks of it repmgr thinks you have 2 masters - the old one
>> >> offline and the new one online.
>>
>> Regards,
>>
>> --
>> Martín Marquéshttp://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>

Re: [GENERAL] [BDR] vs pgpool-II v3

[Craig Ringer]

On 13 August 2015 at 23:52, Wayne E. Seguin  wrote:

> The context of this is using BDR to implement a HA solution where we  have
> one node getting all connections at a time, if the node fails we move all
> connections to another node. (eg. only one node gets all connections at any
> given time).

This sounds like a job better suited to a normal active/standby
configuration with regular built-in streaming replication. Use a tool
like repmgr to manage failover and a proxy like pgbouncer to redirect
traffic.

There's no reason to use async multi-master replication when simple
single-master replication will do just as well.

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgresql jsonb

[Deepak Balasubramanyam]

Thank you Bill and David. I'll take a look at `pg_buffercache ` and explain
with buffers.

---
>>> What problem are you seeing?
---

I don't have a problem at the moment.

---
>>> What is your performance requirement, and what is the observed
performance?
---

The observed performance is within my requirement. My question was aimed at
getting it to stay that way and your answers have helped.

Thanks again
-Deepak


On Fri, Aug 14, 2015 at 6:19 PM, David Rowley 
wrote:

> On 15 August 2015 at 00:09, Deepak Balasubramanyam 
> wrote:
>
>> Hi,
>>
>> I have a table (20 million rows) in Postgresql 9.4 that contains a bigint
>> id as the primary key and another column that contains jsonb data. Queries
>> run on this table look like so...
>>
>> 
>> ## Query
>> 
>> select ... from table
>> WHERE table.column ->'item'->> 'name' = 'value'
>> 
>>
>> I'd like to make an effort to get Postgresql to keep all data available
>> in this table and any index on this table in memory. This would ensure that
>> sequence or index scans made on the data are fairly fast.
>>
>> Research into this problem indicates that there is no reliable way to get
>> Postgresql to run off of RAM memory completely (
>> http://stackoverflow.com/a/24235439/830964). Assuming the table and its
>> indexes amount to 15 gb of data  on the disk and the machine contains 64GB
>> of RAM with shared buffers placed at anywhere from 16-24 GB, here are my
>> questions...
>>
>> 1. When postgresql returns data from this query, how can I tell how much
>> of the data was cached in memory?
>>
>>
> It depends which memory you're talking about. If you mean pages that are
> in the shared buffers then you can just
>
> EXPLAIN (ANALYZE, BUFFERS) select ... from table;
>
> You'll see Buffers: shared read=N if any buffers were "read from disk" but
> keep in mind they still might not be coming from disk, they could be cached
> by the operating system in memory.
>
> Regards
>
> David Rowley
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
> 
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] repmgr won't update witness after failover

[Aviel Buskila]

Hey,
I think I know what the problem is,
after the first failover when I clone the old master to be standby with the
'repmgr standby clone' command it seems that nothing updates the repl_nodes
table with the new standby in my cluster so on the next failover the
repmgrd is failed to find a new upcoming standby to failover..

this issue is confirmed after that I manually updated the repl_nodes table
after the clone so that the old master is now a standby database.

now my question is:
Where does is suppose to happen that after I issue the 'repmgr standby
clone' the repl_nodes should be updated too about the new standby server?

Best regards,
Aviel Buskila



2015-08-16 12:11 GMT+03:00 Aviel Buskila :

> hey,
>
> I have tried to set the configuration all over again, now the status of
> 'repl_nodes' before the failover is:
>
> id | type | upstream_node_id | cluster | name | conninfo | priority |
> active
>
> +-+---++--+-
> 1 | master |  | cluster_name |node1| host=node1
> dbname=repmgr port=5432 user=repmgr | 100 | t
> 2 | standby|1| cluster_name |node2| host=node2
> dbname=repmgr port=5432 user=repmgr | 100 | t
>
> 3 | witness|| cluster_name |node3| host=node3
> dbname=repmgr port=5499 user=repmgr | 100 | t
>
>
> repmgr is started on node2 and node3 (standby and witness) now when I kill
> postgresmaster process I can see in the
>
> repmgrd log the following messages:
>
> [WARNING] connection to master has been lost, trying to recover... 60
> seconds before failover decision
>
> [WARNING] connection to master has been lost, trying to recover... 50
> seconds before failover decision
>
> [WARNING] connection to master has been lost, trying to recover... 40
> seconds before failover decision
>
> [WARNING] connection to master has been lost, trying to recover... 30
> seconds before failover decision
>
> [WARNING] connection to master has been lost, trying to recover... 20
> seconds before failover decision
>
> [WARNING] connection to master has been lost, trying to recover... 10
> seconds before failover decision
>
>
> and than when it tried to elect node2 to be promoted it shows the
> following messages:
>
> [DEBUG] connecting to: 'host=node2 user=repmgr dbname=repmgr
> fallback_application_name='repmgr''
>
> [WARNING] unable to defermmine a valid master server; waiting 10 seconds
> to retry...
>
> [ERROR] unable to determine a valid master node, terminating...
>
> [INFO] repmgrd terminating..
>
>
>
> what am I doing wrong?
>
>
> El 14/08/15 a las 04:14, Aviel Buskila escribió:
> > Hey,
> > yes I did .. and still it wont fail back..
>
> Can you send over the output of "repmgr cluster show" before and after
> the failover process?
>
> The output of SELECT * FROM repmgr_schema.repl_nodes; after the failover
> (you need to change repmgr_schema with what you have configured).
>
> Also, which version of repmgr are you running?
>
> > 2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen  >:
> >
> >> Hi, did you make the old master follow the new one using repmgr?
> >>
> >> It doesn't update itself automatically...
> >> From the looks of it repmgr thinks you have 2 masters - the old one
> >> offline and the new one online.
>
> Regards,
>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [GENERAL] repmgr won't update witness after failover

[Aviel Buskila]

hey,

I have tried to set the configuration all over again, now the status of
'repl_nodes' before the failover is:

id | type | upstream_node_id | cluster | name | conninfo | priority | active
+-+---++--+-
1 | master |  | cluster_name |node1| host=node1
dbname=repmgr port=5432 user=repmgr | 100 | t
2 | standby|1| cluster_name |node2| host=node2
dbname=repmgr port=5432 user=repmgr | 100 | t

3 | witness|| cluster_name |node3| host=node3
dbname=repmgr port=5499 user=repmgr | 100 | t


repmgr is started on node2 and node3 (standby and witness) now when I kill
postgresmaster process I can see in the

repmgrd log the following messages:

[WARNING] connection to master has been lost, trying to recover... 60
seconds before failover decision

[WARNING] connection to master has been lost, trying to recover... 50
seconds before failover decision

[WARNING] connection to master has been lost, trying to recover... 40
seconds before failover decision

[WARNING] connection to master has been lost, trying to recover... 30
seconds before failover decision

[WARNING] connection to master has been lost, trying to recover... 20
seconds before failover decision

[WARNING] connection to master has been lost, trying to recover... 10
seconds before failover decision


and than when it tried to elect node2 to be promoted it shows the following
messages:

[DEBUG] connecting to: 'host=node2 user=repmgr dbname=repmgr
fallback_application_name='repmgr''

[WARNING] unable to defermmine a valid master server; waiting 10 seconds to
retry...

[ERROR] unable to determine a valid master node, terminating...

[INFO] repmgrd terminating..



what am I doing wrong?


El 14/08/15 a las 04:14, Aviel Buskila escribió:
> Hey,
> yes I did .. and still it wont fail back..

Can you send over the output of "repmgr cluster show" before and after
the failover process?

The output of SELECT * FROM repmgr_schema.repl_nodes; after the failover
(you need to change repmgr_schema with what you have configured).

Also, which version of repmgr are you running?

> 2015-08-13 16:23 GMT+03:00 Jony Vesterman Cohen :
>
>> Hi, did you make the old master follow the new one using repmgr?
>>
>> It doesn't update itself automatically...
>> From the looks of it repmgr thinks you have 2 masters - the old one
>> offline and the new one online.

Regards,

--
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services