Materialized views in PGSQL 9.3 with replication on.
Hi There, We are using postgresql 9.3 with streaming replication to 3 standby nodes. I have a question about Materialized views. When the refresh for MV runs on Master how it's data is replicated to Stanby nodes? Does the refresh command runs on the standby nodes as well as it runs on Master and when the new data visible in standbys for the MV? I think it's the changed data replicated from Master to Standby's so changed data is streamed from master to Standbys. Regards Vikas Sharma
Re: Pg Upgrade failing as it is not able to start and stop server properly
Hi All pg_ctl start and stop commands are hanging in a Windows environment. Can i enable some debug logs to understand the root cause of the issue? Thanks Guru Thanks, Guru On Thu, Jan 4, 2018 at 8:54 PM, Bruce Momjian wrote: > On Thu, Jan 4, 2018 at 01:49:17PM +0530, kiran gadamsetty wrote: > > While upgrading the PostgreSQL database from 9.1.4 to 9.6.4 version > on > > windows 2012 server, Pg_upgrade is failing as postgre service start > and > > stop are failing because of time outs. I got the information as > using -m > > immediate mode with solve the problem, but we are interested to know > the > > root cause of this issue. > > Without -m immediate, the server start and stop commands will simply > wait, > > till command timeouts and they fail. > > There is no load on the server that could cause a performance issue. > ... > > Freezing all rows on the new cluster > "c:\EMC\AppSync > > \jboss\datastore_96\engine\bin/vacuumdb" --port 50432 --username ^ > > "apollosuperuser^" --all --freeze --verbose >> > "pg_upgrade_utility.log" 2>& > > 1 > > > > *failure* > > "c:\EMC\AppSync\jboss\datastore_96\engine\bin/pg_ctl" -w -D > "c:\EMC\AppSync > > \jboss\datastore_96\data" -o "" -m fast stop >> > "pg_upgrade_utility.log" 2> > > &1 > > It appears the freeze operation is failing, and I have no idea what > would cause that. > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + As you are, so once was I. As I am, so you will be. + > + Ancient Roman grave inscription + >
Re: Pg Upgrade failing as it is not able to start and stop server properly
Guru Prashanth Thanakodi writes: > pg_ctl start and stop commands are hanging in a Windows environment. Can i > enable some debug logs to understand the root cause of the issue? You could try cranking log_min_messages all the way up to "debug5" in postgresql.conf, but I'm not really sure if that will help much. Is anything at all appearing in the postmaster log file now? regards, tom lane
Supartitions in PGSQL 10
Team, Can you please let us know if Sub-partitions are supported in PGSQL (declarative partitions) 10.1. If yes can it be list-list partitions. We have a situation where a table is very big having around 2 billion rows and is growing. We want to use partitions but not sure if sub-partitions are available. The partition key we are looking for is having around 8000 different values so it will be 8000 partitions and I think that number is really too big number of partitions. For your information RDBMS is not yet Postgres, we are evaluating it to see if it can support. Please suggest. Regards, Virendra This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
Re: Supartitions in PGSQL 10
Kumar, Virendra schrieb am 08.01.2018 um 22:12: Can you please let us know if Sub-partitions are supported in PGSQL (declarative partitions) 10.1. If yes can it be list-list partitions. We have a situation where a table is very big having around 2 billion rows and is growing. We want to use partitions but not sure if sub-partitions are available. The partition key we are looking for is having around 8000 different values so it will be 8000 partitions and I think that number is really too big number of partitions. For your information RDBMS is not yet Postgres, we are evaluating it to see if it can support. Quote from the manual: https://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE Partitions may themselves be defined as partitioned tables, using what is called sub-partitioning Thomas
RES: Supartitions in PGSQL 10
> Kumar, Virendra schrieb am 08.01.2018 um 22:12: > > Can you please let us know if Sub-partitions are supported in PGSQL > > (declarative partitions) 10.1. If yes can it be list-list partitions. > > We have a situation where a table is very big having around 2 billion > > rows and is growing. We want to use partitions but not sure if > > sub-partitions are available. The partition key we are looking for is > > having around 8000 different values so it will be 8000 partitions and > > I think that number is really too big number of partitions. For your > > information RDBMS is not yet Postgres, we are evaluating it to see if > > it can support. > > Quote from the manual: > https://www.postgresql.org/docs/current/static/ddl- > partitioning.html#DDL-PARTITIONING-DECLARATIVE > > Partitions may themselves be defined as partitioned tables, using > what is called sub-partitioning Look: https://www.depesz.com/2017/02/06/waiting-for-postgresql-10-implement-table- partitioning/
How Many Partitions are Good Performing
Can somebody tell us how many partitions are good number without impacting the performance. We are hearing around a thousand, is that a limit. Do we have plan to increase the number of partitions for a table. We would appreciate if somebody can help us with this? Regards, Virendra This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
Re: How Many Partitions are Good Performing
You should have read carefully what I wrote. 1000 is not an upper limit. 1000 partition is the number after which performance starts dropping . There is a blog in www.timescale.com which also highlights the same. Sent: Monday, January 08, 2018 at 6:20 PM From: "Kumar, Virendra" To: "pgsql-gene...@postgresql.org" Subject: How Many Partitions are Good Performing Can somebody tell us how many partitions are good number without impacting the performance. We are hearing around a thousand, is that a limit. Do we have plan to increase the number of partitions for a table. We would appreciate if somebody can help us with this? Regards, Virendra This message is intended only for the use of the addressee and may contain information that is PRIVILEGED AND CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify the sender immediately. Thank you.
Re: How Many Partitions are Good Performing
On Tue, Jan 09, 2018 at 12:54:18AM +0100, Rakesh Kumar wrote: > Can somebody tell us how many partitions are good number without > impacting the performance. We are hearing around a thousand, is that a > limit. Do we have plan to increase the number of partitions for a > table. We would appreciate if somebody can help us with this? What matters here is that the bottleneck comes from the planner which uses basically a O(N^2) algorithm to evaluate all the partitions, so a too high number causes planning time to increase dramatically. In Postgres 11, things get improved with more partition-wise logics. -- Michael signature.asc Description: PGP signature
help with generation_series in pg10
Hi, In pg10 generation series doesn't work like in 9.5. Ex. in 9.5: z=# select generate_series(1, 10), generate_series(1, 5); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 1 7 | 2 8 | 3 9 | 4 10 | 5 (10 registros) so, in version 10 the same sql show different result set. z=# select generate_series(1, 10), generate_series(1,5); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 7 | 8 | 9 | 10 | (10 registros) how can i have the same in pg10 as i have had in pg 9.x? I need it to date type to... if possible. -- Att. Márcio A. Sepp
Re: help with generation_series in pg10
On 01/08/2018 05:25 PM, Márcio A. Sepp wrote: Hi, In pg10 generation series doesn't work like in 9.5. Ex. in 9.5: z=# select generate_series(1, 10), generate_series(1, 5); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 1 7 | 2 8 | 3 9 | 4 10 | 5 (10 registros) so, in version 10 the same sql show different result set. The reason why: https://www.postgresql.org/docs/10/static/release-10.html#idm46428657945600 Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund) Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM-clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see Section 37.4.8. z=# select generate_series(1, 10), generate_series(1,5); generate_series | generate_series -+- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 7 | 8 | 9 | 10 | (10 registros) how can i have the same in pg10 as i have had in pg 9.x? I went to section 37.4.8: https://www.postgresql.org/docs/10/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET I am not seeing a solution, but you might see something that would help you. I need it to date type to... if possible. -- Att. Márcio A. Sepp -- Adrian Klaver adrian.kla...@aklaver.com
Re: help with generation_series in pg10
On Monday, January 8, 2018, Adrian Klaver wrote: > > I am not seeing a solution, but you might see something that would help > you. There is no general solution. For the problem at hand I would union two generate_series(1,5) queries with a query_id column. Then I'd use row_number() over (order by query_id, series_num) to compute the column containing the values 1-10. Alternatively, use the modulus operator (% 5) on 1-10 to generate the second column. David J.
