Thread: Creating streaming replication standby

It would be nice if we could have an SQL command or a function which
creates a streaming replication standby from existing primary. Some of
public cloud already provide such that feature. Pgpool-II also
provides it. Of course we can do it today by combining existing
functions/SQL/tools but the feature will greatly make admin's life
easier.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

On Fri, Mar 30, 2018 at 07:49:37AM +0900, Tatsuo Ishii wrote:
> It would be nice if we could have an SQL command or a function which
> creates a streaming replication standby from existing primary. Some of
> public cloud already provide such that feature. Pgpool-II also
> provides it. Of course we can do it today by combining existing
> functions/SQL/tools but the feature will greatly make admin's life
> easier.

A complain that I have about such concepts is that it creates a
dependency between utilities which are usually classified by
distributions as client-side (pg_basebackup goes to postgresql-client
for Debian), and the server itself.  There is perhaps room for an
extension though.  If many vendors are providing such an option at SQL
level, it would mean that there is demand for it.
--
Michael

> A complain that I have about such concepts is that it creates a
> dependency between utilities which are usually classified by
> distributions as client-side (pg_basebackup goes to postgresql-client
> for Debian), and the server itself.  There is perhaps room for an
> extension though.  If many vendors are providing such an option at SQL
> level, it would mean that there is demand for it.

I think new function/SQL would not be necessarily bound to
pg_basebackup. Rather, new GUC would be invented so that which tool is
executed by the function/SQL for backup. For example,

create_streaming_standby_command = '/path/to/create_standby.sh %h %p %d'

where %h is the host, %p is the port on which postmaster is listening
on and %d is the database cluster directory for the standby server to
be created.

Then the new function (suppose it is called
"create_streaming_standby") would be called something like:

create_streaming_standby('standby_host', 5432, '/usr/local/pgsql/data')

and it execute create_standby.sh with the arguments.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp