Re: Improve replication usability

On Sun, Dec 11, 2011 at 02:58, Andrew Armstrong <phplasma(at)gmail(dot)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/