Improve replication usability

On Sun, Dec 11, 2011 at 02:58, Andrew Armstrong <phplasma@gmail.com> wrote:

Hello,

First off, thanks for a great product.

I've been looking at setting up replication on Windows between two servers
using pgsql 9.1

I'm going to give up for now though because I'm finding it difficult to get
it working correctly; after copying the \data directory as per the guide
at http://wiki.postgresql.org/wiki/Streaming_Replication the server won't
start anymore - no errors are logged.

If you are using 9.1, you should probably try using pg_basebackup,
that's much easier.

It seems to be this problem, but after making sure the postgres user owns
all data files, it still wont
start, http://archives.postgresql.org/pgsql-admin/2010-11/msg00258.php

I'm a software developer and setup mysql/postgresql when needed; but I find
that both of these products are too complicated when it comes to something
like HA/replication.

Can I please suggest the following improvements for a future release?

1) One line setup of replication. Allow me to issue a 'SELECT
pg_replication_connect(remote host)' that will:
a) Perform a backup/download the current database (from scratch) - don't
need me to copy database files
b) Come online and enter a standby state

That's likely never going to happen as a function inside the database,
as it requires replacing and restarting the slave which can hardly be
done from *inside* the slave.

However, it would be quite possible to resurrect the patch I had for
pg_basebackup that made it automatically write the required
recovery.conf file, which would still make it a single-command step,
just outside the database.

Obviously starting and stopping the database will depend on exactly
how it's installed, so that's really up to the packagers on your
platform to provide a tool for.

2) Allow me to easily test failover (issue a SELECT, or just via the trigger
file?)

9.1 has "pg_ctl promote".

3) Options like archive_command etc that need to be typed out manually
should be obsolete; let the database server sync itself without needing a
manual file copy.

You don't need to set archive_command to use just replication, so they
*are* obsolete from a replication perspective. It's still needed for
log archiving, but not for replication.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/