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