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."



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Postgres replication solutions

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
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Postgres replication solutions

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Postgres replication solutions

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
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Postgres replication solutions

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/



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings