Thread: Postgres replication solutions
Besides slony, is there any other postgres replication active project? I
I need a solution where I could mirror a database to a 'spare' server,
for statistical analysis (just SELECTs beeing done there), and 'lag'
between actuall data and the slave server synchronisation can be even
few hours.
It would be awesome if it's not trigger-based.
I looked on gborg.postgres.org, there are numerous replication projects,
but for most of them last activity was in 2002. On pgfoundry I found
nothing (I haven't spent much time there since the web-site is awfully
slow).
So, I'd like a recommendation.
Mario
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
Mario, There's also Command Prompt's Mammoth replicator: http://commandprompt.com/products/mammothreplicator/ Also, you could batch up PITR restores or even just dump restores if the timing was acceptable and you could afford the replicated DB to be down during those batch updates. Another option is using pgpool's replication. On Fri, 28 Jul 2006, Mario Splivalo wrote: > Besides slony, is there any other postgres replication active project? I > I need a solution where I could mirror a database to a 'spare' server, > for statistical analysis (just SELECTs beeing done there), and 'lag' > between actuall data and the slave server synchronisation can be even > few hours. > > It would be awesome if it's not trigger-based. > > I looked on gborg.postgres.org, there are numerous replication projects, > but for most of them last activity was in 2002. On pgfoundry I found > nothing (I haven't spent much time there since the web-site is awfully > slow). > > So, I'd like a recommendation. > > Mario > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
On Fri, 2006-07-28 at 11:01 -0700, Jeff Frost wrote: > Mario, > > There's also Command Prompt's Mammoth replicator: > http://commandprompt.com/products/mammothreplicator/ I sent an email asking if they have an evaluation version of some sort... > Also, you could batch up PITR restores or even just dump restores if the > timing was acceptable and you could afford the replicated DB to be down during > those batch updates. Dump restores are what we're using now, but we're soon going to have to abandon that approach. The dump/restore cycle takes around 20 minutes, and we're expecting rapid growth of the database. The another problem is that Tomcat won't release connections to postgres unless you stop it. PIRT restores are not working for me. I did it like this: I issued pg_start_backup, then I 'tar cvf - pg_data/ | nc destination 9876'-ed the cluster directory, when that was done i did pg_stop_backup, after that I deleted pg_xlog directory, and put new WAL files (created after backup begun) in directory specified in restore.conf. I started postgres, everything went ok. Postgres replayed wal files, and system was ready. Then, when new WAL files arrived, I shut down postgres, put WAL files in place, restarted postgres, but it complained, and didn't replay newly created wal files. It only does so when I 'recreate' original filesystem backup. > Another option is using pgpool's replication. I considered that too, but that adds another layer between the clients and postgres itself, and it allows for only two servers, and I realy don't need failover cluster capabilities. Mike
On Sat, 29 Jul 2006, Mario Splivalo wrote: > PIRT restores are not working for me. I did it like this: I issued > pg_start_backup, then I 'tar cvf - pg_data/ | nc destination 9876'-ed > the cluster directory, when that was done i did pg_stop_backup, after > that I deleted pg_xlog directory, and put new WAL files (created after > backup begun) in directory specified in restore.conf. I started > postgres, everything went ok. Postgres replayed wal files, and system > was ready. Then, when new WAL files arrived, I shut down postgres, put > WAL files in place, restarted postgres, but it complained, and didn't > replay newly created wal files. It only does so when I 'recreate' > original filesystem backup. That is how PITR works. You will always have to redo the base backup before being able to restore again, can't just start the replay from a previously restored version. You could however, just replay from your original base backup if you're keeping those archived WAL files. > >> Another option is using pgpool's replication. > > I considered that too, but that adds another layer between the clients > and postgres itself, and it allows for only two servers, and I realy > don't need failover cluster capabilities. > > Mike > > > -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954
Mario Splivalo wrote: > On Fri, 2006-07-28 at 11:01 -0700, Jeff Frost wrote: >> Mario, >> >> There's also Command Prompt's Mammoth replicator: >> http://commandprompt.com/products/mammothreplicator/ > > I sent an email asking if they have an evaluation version of some > sort... We do, but our 1.7 8.1 version won't be out for another couple of weeks (hoping just in time for LinuxWorld). Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/