[GENERAL] Cluster, repmgr, pgbouncer, pgpool, ha proxy, virtual IP, replication, failover and load balance

Hi guys > I would like to deploy load balance on Postgres master/slave(RW/R) servers > and also use a virtual IP on this cluster > > I currently have Postgres 9.4 using Repmgr for replication and manual > failover to avoid split-brain problems and save a witness server. Also, there > is Pgbounc

Re: [GENERAL] cluster question

On 17 August 2017 at 10:51, Ian Barwick wrote: > On 08/16/2017 02:41 PM, Alex Samad wrote: > (...) > > > > okay think I have it setup, but when i do a switch over it gets stuck > here. > > > > > > > > NOTICE: STANDBY PROMOTE successful > > NOTICE: Executing pg_rewind on old master server > > NOTI

Re: [GENERAL] cluster question

On 08/16/2017 02:41 PM, Alex Samad wrote: (...) > > okay think I have it setup, but when i do a switch over it gets stuck here. > > > > NOTICE: STANDBY PROMOTE successful > NOTICE: Executing pg_rewind on old master server > NOTICE: 3 files copied to /var/lib/pgsql/9.6/data > NOTICE: restarting ser

Re: [GENERAL] cluster question

On 15 August 2017 at 16:35, Andreas Kretschmer wrote: > > > Am 15.08.2017 um 05:15 schrieb Alex Samad: > >> Hi >> >> Quick question. I have a 2 node cluster - each node has its own ip. >> >> But from reading this, I really need a 3rd ip, which potentially floats >> between the nodes to which eve

Re: [GENERAL] cluster question

Am 15.08.2017 um 05:15 schrieb Alex Samad: Hi Quick question. I have a 2 node cluster - each node has its own ip. But from reading this, I really need a 3rd ip, which potentially floats between the nodes to which ever is the master / rw node. Is that right? Sort of makes sense I guess

Re: [GENERAL] cluster question

Hi Quick question. I have a 2 node cluster - each node has its own ip. But from reading this, I really need a 3rd ip, which potentially floats between the nodes to which ever is the master / rw node. Is that right? Sort of makes sense I guess A On 14 August 2017 at 16:47, Andreas Kretschmer

Re: [GENERAL] cluster question

On 14 August 2017 08:39:54 GMT+02:00, Alex Samad wrote: >Hi > >I have setup a streaming replicating cluster, with a hot standby. > >Now I would like to change the RW to hot standby and change the hot >standby >to be the RW server. > >Is it just a matter of updating recover.conf file ? > >Alex I w

[GENERAL] cluster question

Hi I have setup a streaming replicating cluster, with a hot standby. Now I would like to change the RW to hot standby and change the hot standby to be the RW server. Is it just a matter of updating recover.conf file ? Alex

Re: [GENERAL] cluster on brin indexes?

Samuel Williams wrote: > I see this, but no follow up: > > https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com > > So, is it possible or not? The general idea seems like it should be doable, but I haven't looked at it in detail. Contribut

[GENERAL] cluster on brin indexes?

I see this, but no follow up: https://www.postgresql.org/message-id/CAEepm%3D2LUCLZ2J4cwPv5DisHqD9BE_AXnqHGqf0Tj-cvtiqVcQ%40mail.gmail.com So, is it possible or not? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresq

[GENERAL] cluster on table rewrite

I need to add a new column to my largest table, something like: alter table foo add column col_15 text not null default 'foobar'; I am tempted to add the column as NULL, and then use coalesce and nullif in the client code to re-interpret NULL as being the value 'foobar'. But I think that that wo

Re: [GENERAL] Cluster on NAS and data center.

Hello, Thanks to emails, we have achieved what we wanted. This is what we’ve done: Compilation: CFLAGS="-mx32 -fexcess-precision=standard -O2" CXXFLAGS="-mx32" ./configure --without-zlib --disable-float8-byval --without-readline --host=x86_64-linux-gnux32 We also have installed libx32. Right

Re: [GENERAL] Cluster on NAS and data center.

On Mon, Jul 4, 2016 at 10:01 AM, Adrian Klaver wrote: > On 07/04/2016 02:35 AM, Krzysztof Kaczkowski wrote: > >> Hello everyone, >> >> Right now we have PostgreSQL on Windows Server (main data center) and >> cluster is placed on NAS. We have emergency data center on UNIX >> architecture. We want

Re: [GENERAL] Cluster on NAS and data center.

On 07/04/2016 02:35 AM, Krzysztof Kaczkowski wrote: Hello everyone, Right now we have PostgreSQL on Windows Server (main data center) and cluster is placed on NAS. We have emergency data center on UNIX architecture. We want that emergency data center could continue work on PostgreSQL cluster tha

[GENERAL] Cluster on NAS and data center.

Hello everyone, Right now we have PostgreSQL on Windows Server (main data center) and cluster is placed on NAS. We have emergency data center on UNIX architecture. We want that emergency data center could continue work on PostgreSQL cluster that has been used by Windows PostgreSQL. We know that s

Re: [GENERAL] Cluster seems broken after pg_basebackup

2015-02-09 16:10 GMT-05:00 Jim Nasby : > On 2/9/15 11:51 AM, Guillaume Lelarge wrote: > >> According to this page >> , exception >> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx >> referenced memory at 0x%08lx. The mem

Re: [GENERAL] Cluster seems broken after pg_basebackup

On 02/10/2015 08:38 AM, Guillaume Drolet wrote: Adrian, in response to your question: 2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas So where is role 208375PT$ supposed to come from? I found that when I stop/start/restart pgsql through the services.msc application in

Re: [GENERAL] Cluster seems broken after pg_basebackup

Adrian, in response to your question: > 2015-02-06 07:11:38 EST FATAL: le rôle « 208375PT$ » n'existe pas > So where is role 208375PT$ supposed to come from? I found that when I stop/start/restart pgsql through the services.msc application in Windows, this message is issued in the log file. Th

Re: [GENERAL] Cluster seems broken after pg_basebackup

2015-02-09 16:10 GMT-05:00 Jim Nasby : > On 2/9/15 11:51 AM, Guillaume Lelarge wrote: > >> According to this page >> , exception >> 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx >> referenced memory at 0x%08lx. The mem

Re: [GENERAL] Cluster seems broken after pg_basebackup

On 2/9/15 11:51 AM, Guillaume Lelarge wrote: According to this page , exception 0xC005 means STATUS_ACCESS_VIOLATION - The instruction at 0x%08lx referenced memory at 0x%08lx. The memory could not be %s. This is not of much help to me.

Re: [GENERAL] Cluster seems broken after pg_basebackup

On 02/09/2015 08:34 AM, Guillaume Drolet wrote: CCing list so the information stays in the thread. 2015-02-06 18:44 GMT-05:00 Adrian Klaver mailto:adrian.kla...@aklaver.com>>: On 02/06/2015 09:17 AM, Guillaume Drolet wrote: Dear Adrian, Thanks for helping me. Sorry for t

Re: [GENERAL] Cluster seems broken after pg_basebackup

Guillaume: the cluster I try to start is the one used with pg_basebackup, not the result of the backup. 2015-02-09 12:51 GMT-05:00 Guillaume Lelarge : > 2015-02-09 18:40 GMT+01:00 Guillaume Drolet : > >> I tried starting the cluster again. Once again everything looked fine at >> the start (first

Re: [GENERAL] Cluster seems broken after pg_basebackup

2015-02-09 18:40 GMT+01:00 Guillaume Drolet : > I tried starting the cluster again. Once again everything looked fine at > the start (first three lines of this log, in English this time): > > 2015-02-09 11:40:55 EST LOG: database system was shut down at 2015-02-06 > 09:50:21 EST > 2015-02-09 11:4

Re: [GENERAL] Cluster seems broken after pg_basebackup

I tried starting the cluster again. Once again everything looked fine at the start (first three lines of this log, in English this time): 2015-02-09 11:40:55 EST LOG: database system was shut down at 2015-02-06 09:50:21 EST 2015-02-09 11:40:55 EST LOG: database system is ready to accept connecti

Re: [GENERAL] Cluster seems broken after pg_basebackup

2015-02-07 1:24 GMT-05:00 Guillaume Lelarge : > Le 6 févr. 2015 17:31, "Adrian Klaver" a > écrit : > > > > On 02/06/2015 05:03 AM, Guillaume Drolet wrote: > >> > >> Hi, > >> > >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed, > >> my cluster doesn't work properly. I tried

Re: [GENERAL] Cluster seems broken after pg_basebackup

Le 6 févr. 2015 17:31, "Adrian Klaver" a écrit : > > On 02/06/2015 05:03 AM, Guillaume Drolet wrote: >> >> Hi, >> >> Yesterday I ran a pg_basebackup of my cluster. Since it has completed, >> my cluster doesn't work properly. I tried restarting the computer (or >> service) a few times but I always

Re: [GENERAL] Cluster seems broken after pg_basebackup

On 02/06/2015 09:17 AM, Guillaume Drolet wrote: Dear Adrian, Thanks for helping me. Sorry for the lack of details, I had said to myself I had to not forget to give these details but I hit the send button too fast. You know how it is... I added more info in your reply below. First some que

Re: [GENERAL] Cluster seems broken after pg_basebackup

Dear Adrian, Thanks for helping me. Sorry for the lack of details, I had said to myself I had to not forget to give these details but I hit the send button too fast. You know how it is... I added more info in your reply below. 2015-02-06 11:28 GMT-05:00 Adrian Klaver : > On 02/06/2015 05:03 AM

Re: [GENERAL] Cluster seems broken after pg_basebackup

On 02/06/2015 05:03 AM, Guillaume Drolet wrote: Hi, Yesterday I ran a pg_basebackup of my cluster. Since it has completed, my cluster doesn't work properly. I tried restarting the computer (or service) a few times but I always get the same messages in my logs (it's in French. If someone is willi

[GENERAL] Cluster seems broken after pg_basebackup

Hi, Yesterday I ran a pg_basebackup of my cluster. Since it has completed, my cluster doesn't work properly. I tried restarting the computer (or service) a few times but I always get the same messages in my logs (it's in French. If someone is willing to help me I can try to translate the logs. Jus

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

On 04/18/2014 06:25 AM, Guillaume Drolet wrote: Thanks for the doc on pg_resetxlog. Will try it next week when I'm back from Easter holiday, first backuping $PGDATA. Most of my databases are stored in a default tablespace I defined on a different disk than that of my PG installation (and $PGDATA

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

ge > From: Adrian Klaver > Date:04-17-2014 20:36 (GMT-05:00) > To: Guillaume Drolet ,pgsql-general@postgresql.org > Subject: Re: [GENERAL] Cluster recovery - FATAL: database files are > incompatible with server. Different PG_CONTROL_VERSION for cluster and > server. > &

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

Thanks for the doc on pg_resetxlog. Will try it next week when I'm back from Easter holiday, first backuping $PGDATA. Most of my databases are stored in a default tablespace I defined on a different disk than that of my PG installation (and $PGDATA): could I just reinstall PG and then re-link it w

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

On 04/17/2014 01:29 PM, Guillaume Drolet wrote: Dear list users, For some tests, I installed a new cluster with different parameters than the ones I had used a while ago to create the cluster I use for my day-to-day activities (let's call it my main cluster). I used initdb --no-locale -E UTF8 -D

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

On 04/17/2014 01:29 PM, Guillaume Drolet wrote: Dear list users, For some tests, I installed a new cluster with different parameters than the ones I had used a while ago to create the cluster I use for my day-to-day activities (let's call it my main cluster). I used initdb --no-locale -E UTF8 -D

Re: [GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

On 04/17/2014 01:29 PM, Guillaume Drolet wrote: Dear list users, For some tests, I installed a new cluster with different parameters than the ones I had used a while ago to create the cluster I use for my day-to-day activities (let's call it my main cluster). I used initdb --no-locale -E UTF8 -D

[GENERAL] Cluster recovery - FATAL: database files are incompatible with server. Different PG_CONTROL_VERSION for cluster and server.

Dear list users, For some tests, I installed a new cluster with different parameters than the ones I had used a while ago to create the cluster I use for my day-to-day activities (let's call it my main cluster). I used initdb --no-locale -E UTF8 -D . Then I used pg_ctl -D to start my new cluster.

Re: [GENERAL] CLUSTER, REINDEX and VACUUM on batch ops

On Wed, Apr 24, 2013 at 8:49 AM, François Beausoleil wrote: > Presently, I'm migrating each partition individually to add NOT NULL, set a > default value and update the table to have correct values. Essentially, I'm > doing this: > > ALTER TABLE parent ADD COLUMN new_field int; -- adds the field

[GENERAL] CLUSTER, REINDEX and VACUUM on batch ops

Hi all! I have a partitioned table with millions of rows per weekly partition. I am adding new fields, with null values and no default values to ensure I had a reasonable migration time. All downstream code knows how to work with null fields. Presently, I'm migrating each partition individuall

Re: [GENERAL] Cluster table and order information

Quoting Andy Colson : On 2/1/2011 9:08 AM, Dario Beraldi wrote: Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like this: CREATE TABLE newtable AS SELECT * FROM t

Re: [GENERAL] Cluster table and order information

On 2/1/2011 10:17 AM, Dario Beraldi wrote: Quoting Andy Colson : On 2/1/2011 9:08 AM, Dario Beraldi wrote: Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like thi

Re: [GENERAL] Cluster table and order information

On 2/1/2011 9:08 AM, Dario Beraldi wrote: Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like this: CREATE TABLE newtable AS SELECT * FROM table ORDER BY columnlis

[GENERAL] Cluster table and order information

Hello, From the documentation of CLUSTER table (http://www.postgresql.org/docs/8.4/static/sql-cluster.html) I understand that clustering can be achieved by re-creating the table like this: CREATE TABLE newtable AS SELECT * FROM table ORDER BY columnlist; My question is: If I upload w

[GENERAL] Cluster with LATIN1 and UTF-8

Hi all, Im try install postgres 9.0 on rhel and I try install cluster with 2 options of databases LATIN1 and UTF-8. It's possible? Paulo

Re: [GENERAL] CLUSTER cannot complete

Vick Khera wrote: On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol wrote: I didn't add or delete many rows from that table. Quite a lot of UPDATEs are running daily, though. Every update is equal to insert + delete, except in certain circumstances in 8.3 and above. Well, it's still a

Re: [GENERAL] CLUSTER cannot complete

On Mon, Feb 15, 2010 at 9:54 AM, Marcin Krol wrote: > I didn't add or delete many rows from that table. Quite a lot of UPDATEs are > running daily, though. Every update is equal to insert + delete, except in certain circumstances in 8.3 and above. -- Sent via pgsql-general mailing list (pgsql-g

Re: [GENERAL] CLUSTER on 2 indexes ?

On Mon, Feb 15, 2010 at 9:47 AM, Joao Ferreira gmail wrote: > Hello, > > Considering the CLUSTER operation on a frequently updated table, if I > have 2 indexes on the table how do I choose one of them ? or is it > possible to have CLUSTER take both into consideration... You could have a multi-col

[GENERAL] CLUSTER on 2 indexes ?

Hello, Considering the CLUSTER operation on a frequently updated table, if I have 2 indexes on the table how do I choose one of them ? or is it possible to have CLUSTER take both into consideration... my table is read from based on two columns: a 'timestamp' integer column (actually a UTC integer

[GENERAL] CLUSTER cannot complete

Hello everyone, Resolved, I've done: hrs=# CLUSTER hosts_ip_idx ON hosts; CLUSTER hrs=# hrs=# hrs=# SELECT relpages * 8192 AS size_in_bytes, relname FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') ORDER BY size_in_bytes DESC LIMIT 10; size_in_bytes

[GENERAL] CLUSTER cannot complete

Hello everyone, I got this, first CLUSTER was hanging forever, I cancelled after half an hour: hrs=# CLUSTER hosts; Cancel request sent ERROR: canceling statement due to user request Then I restarted postgres and get this now: hrs=# CLUSTER hosts; ERROR: there is no previously clustered

Re: [GENERAL] cluster replication over only http protocol

On Wed, Aug 26, 2009 at 01:13:48AM +0200, Szabolcs MMMrton wrote: > my situation is: > - i have a running/working postgre databse > - i have to make another database exactly the same as the first (for > development purposes) > - the two databases have only(!) http or https connection, nothing else

Re: [GENERAL] cluster replication over only http protocol

2009/8/25 Szabolcs Márton : > Hi, > > > somebody could give me an advice what should i look for? > > my situation is: > - i have a running/working postgre databse > - i have to make another database exactly the same as the first (for > development purposes) > - the two databases have only(!) http o

[GENERAL] cluster replication over only http protocol

Hi, somebody could give me an advice what should i look for? my situation is: - i have a running/working postgre databse - i have to make another database exactly the same as the first (for development purposes) - the two databases have only(!) http or https connection, nothing else ! is there

Re: [GENERAL] Cluster Up-time.

On Mon, Aug 18, 2008 at 03:53:32AM -0700, Alexi Gen wrote: > Is there a table/view available from where I can check what time the cluster > was started? > Need this to calculate the uptime of the cluster. In PostgreSQL 8.1 and later you can run these queries to get the start time and uptime: SEL

[GENERAL] Cluster Up-time.

Hello, Is there a table/view available from where I can check what time the cluster was started? Need this to calculate the uptime of the cluster. Or is there something else that I need to do in order to calculate this? Any help on this is appreciated. Cheers! Alexi Send instant messages to y

Re: [GENERAL] Cluster table

"Kopljan Michael" <[EMAIL PROTECTED]> writes: > Table is physically ordered by primary key. I try to cluster table with : > CLUSTER cluster_idx_dok" ON "ITerp"."Dok" ; > but Table is still physically ordered by primary key. What's your evidence for saying so? regards, tom

[GENERAL] Cluster table

I'm have this table : CREATE TABLE "ITerp"."Dok" ( "id_dok" INTEGER DEFAULT "AutoIncGet"('Dok'::text) NOT NULL, "sifvrste" CHAR(3) NOT NULL, "id_objekat1" INTEGER DEFAULT 0, "id_objekat2" INTEGER DEFAULT 0, "id_partner" INTEGER DEFAULT 0, "broj" INTEGER NOT NULL, "ext

[GENERAL] Cluster table

I'm have this table : CREATE TABLE "ITerp"."Dok" ( "id_dok" INTEGER DEFAULT "AutoIncGet"('Dok'::text) NOT NULL, "sifvrste" CHAR(3) NOT NULL, "id_objekat1" INTEGER DEFAULT 0, "id_objekat2" INTEGER DEFAULT 0, "id_partner" INTEGER DEFAULT 0, "broj" INTEGER NOT NULL, "ext

Re: [GENERAL] Cluster using tablespaces?

Alvaro Herrera wrote: > Alvaro Herrera wrote: >Probably most of the time is going into creating the new table then. > >If you are looking for a short-term solution to your problem, maybe the >best is to follow the recommendation on CLUSTER ref page: I've read that section before, but I have lots

Re: [GENERAL] Cluster using tablespaces?

Rainer Bauer wrote: > Alvaro Herrera wrote: > >It has been theorized that cluster would be faster in general if instead > >of doing an indexscan we would instead use a seqscan + sort step. It > >would be good to measure it. > > Could a reindex on the clustered index speed up the clustering (when

Re: [GENERAL] Cluster using tablespaces?

Alvaro Herrera wrote: >Rainer Bauer wrote: >> Ok, I expected that. Does this work: >> ALTER TABLE foo CLUSTER ON index_name SET TABLESPACE new_tablespace >> >> I.e. is the table moved to the other tablespace and clustered at the same >> time >> or are these independant operations? > >No, the ALT

Re: Btree indexes temp copy Was [Re: [GENERAL] Cluster using tablespaces?]

Ow Mun Heng <[EMAIL PROTECTED]> writes: > On Wed, 2007-11-28 at 18:30 -0500, Tom Lane wrote: >> For btree indexes, there is a temporary copy of the index data, which >> will go wherever you have arranged for temp files to go. (I think that >> easy user control of this may be new for 8.3, though.)

Btree indexes temp copy Was [Re: [GENERAL] Cluster using tablespaces?]

On Wed, 2007-11-28 at 18:30 -0500, Tom Lane wrote: > For btree indexes, there is a temporary copy of the index data, which > will go wherever you have arranged for temp files to go. (I think that > easy user control of this may be new for 8.3, though.) In 8.2.5 is there a way to control this? I

Re: [GENERAL] Cluster using tablespaces?

Rainer Bauer wrote: > Tom Lane wrote: > > >Rainer Bauer <[EMAIL PROTECTED]> writes: > > > >> "During the cluster operation, a temporary copy of the table is created > >> that > >> contains the table data in the index order. Temporary copies of each index > >> on > >> the table are created as wel

Re: [GENERAL] Cluster using tablespaces?

Tom Lane wrote: >Rainer Bauer <[EMAIL PROTECTED]> writes: > >> "During the cluster operation, a temporary copy of the table is created that >> contains the table data in the index order. Temporary copies of each index on >> the table are created as well." > >That's probably a bit misleading. Ther

Re: [GENERAL] Cluster using tablespaces?

Rainer Bauer <[EMAIL PROTECTED]> writes: > Erik Jones wrote: >> What temporary created data are you referring to? > The one described in the manual > : > "During the cluster operation, a temporary copy of the table is created that > cont

Re: [GENERAL] Cluster using tablespaces?

Erik Jones wrote: >> is there a way to instruct cluster to store the temporary created >> data on a >> different tablespace (i.e. drive)? If not, wouldn't that have a decent >> performance impact or is most of the time spend retrieving the data >> in index >> order? > >What temporary created d

Re: [GENERAL] Cluster using tablespaces?

start here http://www.postgresql.org/docs/8.1/static/creating-cluster.html M- - Original Message - From: "Erik Jones" <[EMAIL PROTECTED]> To: "Rainer Bauer" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, November 28, 2007 5:59 PM Subject: Re: [GENERAL] Cluster

Re: [GENERAL] Cluster using tablespaces?

On Nov 28, 2007, at 4:47 PM, Rainer Bauer wrote: Hello, is there a way to instruct cluster to store the temporary created data on a different tablespace (i.e. drive)? If not, wouldn't that have a decent performance impact or is most of the time spend retrieving the data in index order?

[GENERAL] Cluster using tablespaces?

Hello, is there a way to instruct cluster to store the temporary created data on a different tablespace (i.e. drive)? If not, wouldn't that have a decent performance impact or is most of the time spend retrieving the data in index order? Rainer ---(end of broadcast)--

Re: [GENERAL] CLUSTER = slower vacuum?

Phoenix Kiula escribió: > After I clustered the primary key index of a table with about 300,000 > rows, my vacuum/analyze on that table is taking too long ... over 15 > mins when originally it was 15 seconds! Nothing else has been changed > with this table. Is clustering not good for vacuums? No.

[GENERAL] CLUSTER = slower vacuum?

After I clustered the primary key index of a table with about 300,000 rows, my vacuum/analyze on that table is taking too long ... over 15 mins when originally it was 15 seconds! Nothing else has been changed with this table. Is clustering not good for vacuums? ---(end of b

Re: [GENERAL] Cluster and MVCC

On Fri, Aug 10, 2007 at 06:34:03PM +0100, Simon Riggs wrote: > On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote: > > I just want to confirm that the cluster/MVCC issues are due to > > transaction visibility. Assuming that no concurrent access is happening > > to a given table when the clust

Re: [GENERAL] Cluster and MVCC

On Fri, 2007-08-10 at 10:02 -0400, Brad Nicholson wrote: > I just want to confirm that the cluster/MVCC issues are due to > transaction visibility. Assuming that no concurrent access is happening > to a given table when the cluster command is issued (when takes it > visibility snapshot), it is saf

[GENERAL] Cluster and MVCC

I just want to confirm that the cluster/MVCC issues are due to transaction visibility. Assuming that no concurrent access is happening to a given table when the cluster command is issued (when takes it visibility snapshot), it is safe to cluster that table. Correct? -- Brad Nicholson 416-673-41

Re: [GENERAL] Cluster all tables in database to PK index

> Besides writing a script that looks through the DDL of all tables, and > CLUSTERs all tables with PK constraints, is there a quicker way? Is this really a sensible thing to do? As often as not, you want to cluster on foreign keys... -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (

[GENERAL] Cluster all tables in database to PK index

Besides writing a script that looks through the DDL of all tables, and CLUSTERs all tables with PK constraints, is there a quicker way? Thanks. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 4: Have you searched our list archives?

Re: [GENERAL] Cluster/redundancy question

On Thu, Oct 13, 2005 at 10:06:51AM -0600, Aly S.P Dharshi wrote: > Andrew, > > I disagree, I wouldn't want to contend with all the complexities > and kludge of Oracle thank you very much. If there was a way to get > PostgreSQL to do better than the current clustering methods, then why not, i

Re: [GENERAL] Cluster/redundancy question

Andrew, I disagree, I wouldn't want to contend with all the complexities and kludge of Oracle thank you very much. If there was a way to get PostgreSQL to do better than the current clustering methods, then why not, it would be a big win for us. PostgreSQL *is* an enterprise clas

Re: [GENERAL] Cluster/redundancy question

On Tue, Oct 11, 2005 at 11:38:22AM -0500, Scott Marlowe wrote: > > Don't get me wrong, if replication is one of the things you need, then > consider it, but if you're putting bad data into your database, what > good is replicating it gonna do ya? But if real, ORAC-style clustering is what you nee

Re: [GENERAL] Cluster/redundancy question

Note that pgcluster is statement-based, which has some drawbacks. AFAIK MySQL's 'clustering' is as well. Many people use Slony to replicate to many slaves and use pgpool to hit them. But remember if you do that you need to make sure any statement that changes data hits your master and not the slav

Re: [GENERAL] Cluster/redundancy question

Don't forget that MySQL replication also has a habit of silently failing on you and in my experience needs continuous monitoring to make sure it actually keeps reasonably up to date (e.g. not days of data behind on the slaves.) That was a while ago though, maybe they fixed it? British Telec

Re: [GENERAL] Cluster/redundancy question

On Mon, 2005-10-10 at 15:16, Travis Brady wrote: > All, > > Forgive me if this has been answered before, but I've searched the > archives and the net extensively and have come up mostly empty so far. > > I'm working at convincing my firm to implement a postgresql database > cluster. > Specifical

Re: [GENERAL] Cluster/redundancy question

Hello Travis, I don't know if there are a Oracle RAC style cluster system for PGSQL but this software that can do something similar, http://pgcluster.projects.postgresql.org/feature.html may help. You can always use Slony for replication services. Cheers, Aly. On M

[GENERAL] Cluster/redundancy question

All,Forgive me if this has been answered before, but I've searched the archives and the net extensively and have come up mostly empty so far.I'm working at convincing my firm to implement a postgresql database cluster. Specifically, we'd like to get a few machines running to be more available and t

Re: [GENERAL] CLUSTER equivalent

On Thu, Sep 15, 2005 at 05:09:51PM -0400, Kevin Murphy wrote: > Neil Conway wrote: > > >Kevin Murphy wrote: > > > >>I just wanted to confirm that the COPY command always stores data in > >>the table in the order in which it appears in the import file. > > > >This is not the case -- depending on t

Re: [GENERAL] CLUSTER equivalent

Neil Conway wrote: Kevin Murphy wrote: I just wanted to confirm that the COPY command always stores data in the table in the order in which it appears in the import file. This is not the case -- depending on the content of the FSM, the newly added rows might be distributed throughout the t

Re: [GENERAL] CLUSTER equivalent

Kevin Murphy wrote: I just wanted to confirm that the COPY command always stores data in the table in the order in which it appears in the import file. This is not the case -- depending on the content of the FSM, the newly added rows might be distributed throughout the table. -Neil ---

[GENERAL] CLUSTER equivalent

I just wanted to confirm that the COPY command always stores data in the table in the order in which it appears in the import file. I.e., if the import file is sorted ahead of time, am I correct in assuming that the COPY command can have the same effect as CLUSTER'ing (or as creating a new tab

Re: [GENERAL] CLUSTER equivalent

Kevin Murphy <[EMAIL PROTECTED]> writes: > Are the two following options equivalent? > OPTION A (ordered insert): > CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER); > CREATE INDEX idx1 ON table1(cluster_col); > INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM > table1 ORD

[GENERAL] CLUSTER equivalent

Are the two following options equivalent? OPTION A (ordered insert): CREATE TABLE table1 (cluster_col TEXT, col2 INTEGER); CREATE INDEX idx1 ON table1(cluster_col); INSERT INTO table1 (cluster_col, col2) SELECT cluster_col, col2 FROM table1 ORDER BY cluster_col; OPTION B (unordered insert fol

[GENERAL] CLUSTER

I have a table that II am constantly inserting into (around 10 times a second right now but hope to increase latter). I hold these rows for a week then summarize and delete them. During that week I need to access ranges of these rows based on a timestamp in each row set to now() when I insert

Re: [GENERAL] "Cluster" means "tangle" for me

Jeff Eckermann <[EMAIL PROTECTED]> writes: > I would appreciate any advice on getting out of this strange situation. My > table now doesn't exist, but I can't recreate it either (at least under that > name). Hmm, was "dedcolo" a temp table? It looks like clustering a temp table gets confused.

[GENERAL] "Cluster" means "tangle" for me

I would appreciate any advice on getting out of this strange situation. My table now doesn't exist, but I can't recreate it either (at least under that name). jeffe@kiyoko=> psql -V psql (PostgreSQL) 7.0.0 jeffe@kiyoko=> uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu