cant read data from master

[Rishabh Chaudhary]

3 node cluster for postgres. Requirement: Data sharding+ replication + partitioning

Tech used: PostgreSQL-9.4, PG_Shard and PG_Partman

What I have done till now: I create master table on one node and worker on the other two. I shard my table on these nodes. I partition those shards on the workers.

What is working: I can insert properly from the master node. The data goes to the correct partition of the correct shard.

What is the issue: I cant read from the master node. Although, I can see the data on the shard and also on the partition by connecting to the worker node.

Any help, please?

[Onder Kalaci]

Hi, 

Could you provide some more details on how you create & distribute tables? For instance, if you follow the instructions here, on which exact step you observe the problem?

Thanks,

Onder

[Rishabh Chaudhary]

Hi,

All seems fine. Insert works. I can see the data on the shard by connecting to the worker. But i cant read data form the master.

[Onder Kalaci]

Hi,

Could you please elaborate the issue more such as the following:

•  What do you mean by "I can't read data from the master" ? Do you see an error or select returns zero rows?
•  What exact steps you followed?

Onder

[Rishabh Chaudhary]

Hi,

I mentioned the following on my first post:

3 node cluster for postgres. Requirement: Data sharding+ replication + partitioning

Tech used: PostgreSQL-9.4, PG_Shard and PG_Partman

What I have done till now: I create master table on one node and worker on the other two. I shard my table on these nodes. I partition those shards on the workers.

What is working: I can insert properly from the master node. The data goes to the correct partition of the correct shard.

What is the issue: I cant read from the master node. Although, I can see the data on the shard and also on the partition by connecting to the worker node.

Addition: Now when i query from the master node (simple select * statement), it shows zero rows.

However, when i connect to a worker node, and query to the shard directly, i can see the data. Moreover, each shard is partitioned, where each partition is also a table in its own. So if i query directly to the partition, even then i can see the inserted row. So i assume here that the partman and shard is coordinating well. But in that case, why does querying through the master show empty table?

One strange thing i observed was: when I enter the data through the master,i see "Insert 0 0 " as the reply. Shouldn't this be 0 1 or something? I am not sure. Is this related?

[Onder Kalaci]

Hi Risbash,

I haven't tried pg_shard with pg_partman. But, I try to understand your use case first. Why do you need to partition the shards on the workers? Is it an experimental task? Doesn't creating more shards work for you?

Onder.

Thanks