Re: [GENERAL] [OT] Slony (initial) Replication - Slow
Andrew Sullivan wrote: On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote: swooping elephants must be an interesting sight. If pigs can fly ... Is this what you had in mind? http://www.amoeba.com/dynamic-images/blog/dumbo.gif Hmm, something like that, but Dumbo does not look truly fell ;-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] [OT] Slony (initial) Replication - Slow
I'm just wetting my hands with slony and during the setup of the slave, I did and dump and restore of the master DB to the Slave DB. However during the startup of slony, I noticed that it issues a truncate command to the (to be) replicated table. Hence, this means that there's no such need for me to do a dump/restore in the 1st place. can someone confirm this? It _is_ taking long time (for slony) to do the \copy (~60GB in multiple tables being replicated, including (on the fly) index creation) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote: I'm just wetting my hands with slony and during the setup of the slave, I did and dump and restore of the master DB to the Slave DB. Nope, you don't need to do that. You need a copy of the _schema_ on the target machine. But slony will remove all the contents and build the replica anew. can someone confirm this? It _is_ taking long time (for slony) to do the \copy (~60GB in multiple tables being replicated, including (on the fly) index creation) It takes approximately the same time as it would to do a psql -h [remotehost] -f dumpfile.sql restore (i.e. copying the entire data contents across the network). A ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
[EMAIL PROTECTED] (Andrew Sullivan) writes: On Thu, Jan 03, 2008 at 11:15:23AM +0800, Ow Mun Heng wrote: I'm just wetting my hands with slony and during the setup of the slave, I did and dump and restore of the master DB to the Slave DB. Nope, you don't need to do that. You need a copy of the _schema_ on the target machine. But slony will remove all the contents and build the replica anew. Right. The argument for doing so is that this approach (TRUNCATE + COPY on the subscriber) is the only way that Slony-I can be certain that it has all data on the subscriber that was on the provider. That way, it doesn't need to trust any dodgy claims that oh, I copied all the data - honest! can someone confirm this? It _is_ taking long time (for slony) to do the \copy (~60GB in multiple tables being replicated, including (on the fly) index creation) It takes approximately the same time as it would to do a psql -h [remotehost] -f dumpfile.sql restore (i.e. copying the entire data contents across the network). In 1.2.x, it should be a little bit quicker than the pg_dump | psql approach as all index generation takes place together for each table. When you do a restore of a pg_dump, the indexes are generated in a somewhat arbitrary order, where there may be a separation in time between when different indexes on a given table get created. In contrast, Slony-I regenerates all the indexes on a given table in a one swell foop fashion, which might be expected to allow cacheing to provide a bit better performance than you could get with pg_dump | psql. -- cbbrowne,@,cbbrowne.com http://linuxdatabases.info/info/emacs.html Microsoft Outlook: Deploying Viruses Has Never Been This Easy! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
Chris Browne wrote: In contrast, Slony-I regenerates all the indexes on a given table in a one swell foop fashion, which might be expected to allow cacheing to provide a bit better performance than you could get with pg_dump | psql. I'm left wondering whether the swoop is actually fell. I imagine most people would not survive an elephant herd, or slony, turning fell. Now, swooping elephants must be an interesting sight. If pigs can fly ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
On Fri, Jan 04, 2008 at 06:11:40PM -0300, Alvaro Herrera wrote: swooping elephants must be an interesting sight. If pigs can fly ... Is this what you had in mind? http://www.amoeba.com/dynamic-images/blog/dumbo.gif A ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] [OT] Slony (initial) Replication - Slow
{resend as don't see it on the list after 4 hours}
I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.
However during the startup of slony, I noticed that it issues a truncate
command to the (to be) replicated table. Hence, this means that there's
no such need for me to do a dump/restore in the 1st place.
can someone confirm this? It _is_ taking long time (for slony) to do the
\copy (~60GB in multiple tables being replicated, including (on the fly)
index creation)
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
In response to Ow Mun Heng [EMAIL PROTECTED]: I'm just wetting my hands with slony and during the setup of the slave, I did and dump and restore of the master DB to the Slave DB. However during the startup of slony, I noticed that it issues a truncate command to the (to be) replicated table. Hence, this means that there's no such need for me to do a dump/restore in the 1st place. can someone confirm this? Confirmed. It's how Slony is designed to work. It _is_ taking long time (for slony) to do the \copy (~60GB in multiple tables being replicated, including (on the fly) index creation) 1) It only needs to be done once 2) You can remove the indexes from the replica and add them back in after the initial sync is complete. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
Ow Mun Heng wrote:
{resend as don't see it on the list after 4 hours}
I'm just wetting my hands with slony and during the setup of the slave,
I did and dump and restore of the master DB to the Slave DB.
You don't need to do this.
However during the startup of slony, I noticed that it issues a truncate
command to the (to be) replicated table. Hence, this means that there's
no such need for me to do a dump/restore in the 1st place.
can someone confirm this? It _is_ taking long time (for slony) to do the
\copy (~60GB in multiple tables being replicated, including (on the fly)
index creation)
This is correct. You want an empty replication database. When you
start replication slony will bring the master and slave into sync.
--
Until later, Geoffrey
Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
- Benjamin Franklin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] [OT] Slony (initial) Replication - Slow
On Thu, 2008-01-03 at 19:17 -0500, Geoffrey wrote: Ow Mun Heng wrote: However during the startup of slony, I noticed that it issues a truncate command to the (to be) replicated table. Hence, this means that there's no such need for me to do a dump/restore in the 1st place. This is correct. You want an empty replication database. When you start replication slony will bring the master and slave into sync. Thanks to you and others who has responded for confirmation. I would additionally like to know if there was any way for me to use the dump/restore method and have slony pick up where it was left off? BTW, it's working right now after some unsuccessful attempts. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
