[PATCH] Make pg_basebackup configure and start standby
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.
Comments?
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
Attachments:
01-pg_basebackup.patchtext/x-patch; name=01-pg_basebackup.patchDownload
diff -durpN postgresql.orig/doc/src/sgml/ref/pg_basebackup.sgml postgresql/doc/src/sgml/ref/pg_basebackup.sgml
--- postgresql.orig/doc/src/sgml/ref/pg_basebackup.sgml 2012-06-18 07:06:42.774009894 +0200
+++ postgresql/doc/src/sgml/ref/pg_basebackup.sgml 2012-07-01 12:51:51.050652170 +0200
@@ -186,6 +186,28 @@ PostgreSQL documentation
</varlistentry>
<varlistentry>
+ <term><option>-R</option></term>
+ <term><option>--write-recovery-conf</option></term>
+ <listitem>
+ <para>
+ Write a minimal recovery.conf into the output directory using
+ the connection parameters from the command line to ease
+ setting up the standby. Conflicts with <option>--xlog</option>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-S</option></term>
+ <term><option>--start-standby</option></term>
+ <listitem>
+ <para>
+ Start the standby database server. Implies <option>-R</option>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-X <replaceable class="parameter">method</replaceable></option></term>
<term><option>--xlog-method=<replaceable class="parameter">method</replaceable></option></term>
<listitem>
diff -durpN postgresql.orig/src/bin/pg_basebackup/pg_basebackup.c postgresql/src/bin/pg_basebackup/pg_basebackup.c
--- postgresql.orig/src/bin/pg_basebackup/pg_basebackup.c 2012-06-26 09:10:21.301759598 +0200
+++ postgresql/src/bin/pg_basebackup/pg_basebackup.c 2012-07-01 12:44:36.920813960 +0200
@@ -46,6 +46,8 @@ int compresslevel = 0;
bool includewal = false;
bool streamwal = false;
bool fastcheckpoint = false;
+bool writerecoveryconf = false;
+bool startstandby = false;
int standby_message_timeout = 10 * 1000; /* 10 sec = default */
/* Progress counters */
@@ -107,6 +109,10 @@ usage(void)
printf(_("\nOptions controlling the output:\n"));
printf(_(" -D, --pgdata=DIRECTORY receive base backup into directory\n"));
printf(_(" -F, --format=p|t output format (plain (default), tar)\n"));
+ printf(_(" -R, --write-recovery-conf\n"
+ " write recovery.conf after backup\n"));
+ printf(_(" -S, --start-standby start standby after backup, implies -R\n"
+ " conflicts with -x\n"));
printf(_(" -x, --xlog include required WAL files in backup (fetch mode)\n"));
printf(_(" -X, --xlog-method=fetch|stream\n"
" include required WAL files with specified method\n"));
@@ -1193,6 +1199,85 @@ BaseBackup(void)
fprintf(stderr, "%s: base backup completed\n", progname);
}
+static void WriteRecoveryConf(void)
+{
+ char filename[MAXPGPATH];
+ char cfhost[MAXPGPATH] = "";
+ char cfport[MAXPGPATH] = "";
+ char cfuser[MAXPGPATH] = "";
+ char cfpass[MAXPGPATH] = "";
+ FILE *cf;
+
+ if (!writerecoveryconf)
+ return;
+
+ memset(cfhost, 0, MAXPGPATH);
+ if (dbhost)
+ snprintf(cfhost, MAXPGPATH-1, "host=%s", dbhost);
+
+ memset(cfport, 0, MAXPGPATH);
+ if (dbport)
+ snprintf(cfport, MAXPGPATH-1, "port=%s", dbport);
+
+ memset(cfuser, 0, MAXPGPATH);
+ if (dbuser)
+ snprintf(cfuser, MAXPGPATH-1, "user=%s", dbuser);
+
+ memset(cfpass, 0, MAXPGPATH);
+ if (dbpassword)
+ snprintf(cfpass, MAXPGPATH-1, "password='%s'", dbpassword);
+ else
+ printf("add password to primary_conninfo in %s if needed\n", filename);
+
+ sprintf(filename, "%s/recovery.conf", basedir);
+
+ cf = fopen(filename, "w");
+ if (cf == NULL)
+ {
+ fprintf(stderr, _("cannot create %s"), filename);
+ exit(1);
+ }
+
+ fprintf(cf, "standby_mode = 'on'\n");
+ fprintf(cf, "primary_conninfo = '%s %s %s'\n", cfhost, cfport, cfuser);
+
+ fclose(cf);
+}
+
+static void StartStandby(const char *command)
+{
+ char *path;
+ char *args[5];
+ int pos, len;
+
+ if (!startstandby)
+ return;
+
+ path = xstrdup(command);
+ len = strlen(path);
+
+ for (pos = len; pos > 0; pos--)
+ {
+ if (path[pos - 1] == '/'
+#ifdef WIN32
+ || path[pos - 1] == '\\'
+#endif
+ )
+ break;
+ }
+
+ sprintf(&path[pos], "pg_ctl");
+
+ args[0] = path;
+ args[1] = "-D";
+ args[2] = basedir;
+ args[3] = "start";
+ args[4] = NULL;
+
+ if (execvp(path, args) < 0)
+ fprintf(stderr, _("Cannot execute pg_ctl: %s"),
+ strerror(errno));
+}
int
main(int argc, char **argv)
@@ -1203,6 +1288,8 @@ main(int argc, char **argv)
{"pgdata", required_argument, NULL, 'D'},
{"format", required_argument, NULL, 'F'},
{"checkpoint", required_argument, NULL, 'c'},
+ {"write-recovery-conf", no_argument, NULL, 'R'},
+ {"start-standby", no_argument, NULL, 'S'},
{"xlog", no_argument, NULL, 'x'},
{"xlog-method", required_argument, NULL, 'X'},
{"gzip", no_argument, NULL, 'z'},
@@ -1240,7 +1327,7 @@ main(int argc, char **argv)
}
}
- while ((c = getopt_long(argc, argv, "D:F:xX:l:zZ:c:h:p:U:s:wWvP",
+ while ((c = getopt_long(argc, argv, "D:F:RSxX:l:zZ:c:h:p:U:s:wWvP",
long_options, &option_index)) != -1)
{
switch (c)
@@ -1260,6 +1347,12 @@ main(int argc, char **argv)
exit(1);
}
break;
+ case 'R':
+ writerecoveryconf = true;
+ break;
+ case 'S':
+ startstandby = true;
+ break;
case 'x':
if (includewal)
{
@@ -1422,6 +1515,16 @@ main(int argc, char **argv)
}
#endif
+ if (startstandby)
+ writerecoveryconf = true;
+
+ if (writerecoveryconf && includewal)
+ {
+ fprintf(stderr,
+ _("--xlog and --writerecoveryconf are mutually exclusive\n"));
+ exit(1);
+ }
+
/*
* Verify that the target directory exists, or create it. For plaintext
* backups, always require the directory. For tar backups, require it
@@ -1433,5 +1536,9 @@ main(int argc, char **argv)
BaseBackup();
+ WriteRecoveryConf();
+
+ StartStandby(argv[0]);
+
return 0;
}
diff -durpN postgresql.orig/src/bin/pg_basebackup/streamutil.c postgresql/src/bin/pg_basebackup/streamutil.c
--- postgresql.orig/src/bin/pg_basebackup/streamutil.c 2012-06-11 06:22:48.200921787 +0200
+++ postgresql/src/bin/pg_basebackup/streamutil.c 2012-07-01 12:09:56.938418170 +0200
@@ -28,7 +28,7 @@ char *dbhost = NULL;
char *dbuser = NULL;
char *dbport = NULL;
int dbgetpassword = 0; /* 0=auto, -1=never, 1=always */
-static char *dbpassword = NULL;
+char *dbpassword = NULL;
PGconn *conn = NULL;
/*
diff -durpN postgresql.orig/src/bin/pg_basebackup/streamutil.h postgresql/src/bin/pg_basebackup/streamutil.h
--- postgresql.orig/src/bin/pg_basebackup/streamutil.h 2012-04-16 19:57:22.589917242 +0200
+++ postgresql/src/bin/pg_basebackup/streamutil.h 2012-07-01 12:09:42.562326418 +0200
@@ -5,6 +5,7 @@ extern char *dbhost;
extern char *dbuser;
extern char *dbport;
extern int dbgetpassword;
+extern char *dbpassword;
/* Connection kept global so we can disconnect easily */
extern PGconn *conn;
On Sun, Jul 1, 2012 at 8:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.Comments?
Could you add the patch to the next CommitFest?
If the backup is taken from the standby server, the standby's recovery.conf
is included in the backup. What happens in this case?
Regards,
--
Fujii Masao
Hi,
2012-07-01 17:38 keltezéssel, Fujii Masao írta:
On Sun, Jul 1, 2012 at 8:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.Comments?
Could you add the patch to the next CommitFest?
I will.
If the backup is taken from the standby server, the standby's recovery.conf
is included in the backup. What happens in this case?
As documented, the command line parameters of pg_basebackup
will be used for recovery.conf. So, the new standby will replicate
the previous one. Cascading replication works since 9.2.
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
On Sun, Jul 1, 2012 at 1:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.
I like the writing of recovery.conf. In fact, I had it in my code at
one very early point and took it out in order to get a clean patch
ready :)
But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?
Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?
I'm not sure we should go the way of providing the "start slave".
Given thta how you want to start the slave differs so much on
platforms. The most glaring example is on windows you really need to
*start the service* rather than use pg_ctl. Sure, you can document
your way around that, but I'm not sure the functionality added is
really worth it. What about all the other potential connection
parameters?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
On Mon, Jul 2, 2012 at 12:42 AM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
2012-07-01 17:38 keltezéssel, Fujii Masao írta:
On Sun, Jul 1, 2012 at 8:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.Comments?
Could you add the patch to the next CommitFest?
I will.
If the backup is taken from the standby server, the standby's
recovery.conf
is included in the backup. What happens in this case?As documented, the command line parameters of pg_basebackup
will be used for recovery.conf. So, the new standby will replicate
the previous one. Cascading replication works since 9.2.
So pg_basebackup overwrites the recovery.conf which was backed up
from the standby with the recovery.conf which was created by using
the command line parameters of pg_basebackup?
Regards,
--
Fujii Masao
On Mon, Jul 2, 2012 at 12:44 AM, Magnus Hagander <magnus@hagander.net> wrote:
On Sun, Jul 1, 2012 at 1:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.I like the writing of recovery.conf.
Agreed.
In fact, I had it in my code at
one very early point and took it out in order to get a clean patch
ready :)But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?
What about something like PQconninfo which returns the connection
string value established at connection?
Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?
+1
I'm not sure we should go the way of providing the "start slave".
Given thta how you want to start the slave differs so much on
platforms. The most glaring example is on windows you really need to
*start the service* rather than use pg_ctl. Sure, you can document
your way around that, but I'm not sure the functionality added is
really worth it.
Agreed.
Regards,
--
Fujii Masao
On Jul 1, 2012, at 5:44 PM, Magnus Hagander wrote:
On Sun, Jul 1, 2012 at 1:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.I like the writing of recovery.conf. In fact, I had it in my code at
one very early point and took it out in order to get a clean patch
ready :)But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?
one option would be to check the environments and take them if needed.
however, i am not sure if this is a good idea either - just thing of PGPASSWORD or so. do we really want to take it and write it to a file straight away? i guess there are arguments for both ideas.
still, i guess your argument is a reasonable one.
Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?
this would make things redundant. i am quite sure some users might not get the distinction straight away.
I'm not sure we should go the way of providing the "start slave".
Given thta how you want to start the slave differs so much on
platforms. The most glaring example is on windows you really need to
*start the service* rather than use pg_ctl. Sure, you can document
your way around that, but I'm not sure the functionality added is
really worth it. What about all the other potential connection
parameters.
regards,
hans
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
On mån, 2012-07-02 at 01:10 +0900, Fujii Masao wrote:
But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?What about something like PQconninfo which returns the connection
string value established at connection?Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?+1
I think both of these would be necessary to make this work smoothly.
You also need to take into account situations like when pg_basebackup
found a server with the help of PG* environment variables that might no
longer be set when the server tries to start recovery. So you need
something like PQconninfo.
On Tue, Jul 3, 2012 at 9:47 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On mån, 2012-07-02 at 01:10 +0900, Fujii Masao wrote:
But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?What about something like PQconninfo which returns the connection
string value established at connection?Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?+1
I think both of these would be necessary to make this work smoothly.
You also need to take into account situations like when pg_basebackup
found a server with the help of PG* environment variables that might no
longer be set when the server tries to start recovery. So you need
something like PQconninfo.
Zoltan,
are you planning to work on the things discussed in this thread? I
notice the patch is sitting with "waiting on author" in the CF app -
so the second question is that if you are doing that, do you think it
will be done within the scope of the current CF?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
Hi,
2012-10-03 10:25 keltezéssel, Magnus Hagander írta:
On Tue, Jul 3, 2012 at 9:47 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On mån, 2012-07-02 at 01:10 +0900, Fujii Masao wrote:
But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?What about something like PQconninfo which returns the connection
string value established at connection?Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?+1
I think both of these would be necessary to make this work smoothly.
You also need to take into account situations like when pg_basebackup
found a server with the help of PG* environment variables that might no
longer be set when the server tries to start recovery. So you need
something like PQconninfo.Zoltan,
are you planning to work on the things discussed in this thread? I
notice the patch is sitting with "waiting on author" in the CF app -
so the second question is that if you are doing that, do you think it
will be done within the scope of the current CF?
yes, I am on it so it can be done in this CF.
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/
On Wed, Oct 3, 2012 at 10:37 AM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
2012-10-03 10:25 keltezéssel, Magnus Hagander írta:
On Tue, Jul 3, 2012 at 9:47 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On mĺn, 2012-07-02 at 01:10 +0900, Fujii Masao wrote:
But I think that part is lacking in functionality: AFAICT it's
hardcoded to only handle host, port, user and password. What about
other connection parameters, likely passed to pg_basebackup through
the environment in that case? isn't that quite likely to break the
server later?What about something like PQconninfo which returns the connection
string value established at connection?Maybe the proper way around that is to provide the ability for
pg_basebackup to take a full connection string, just like we allow
psql to do?+1
I think both of these would be necessary to make this work smoothly.
You also need to take into account situations like when pg_basebackup
found a server with the help of PG* environment variables that might no
longer be set when the server tries to start recovery. So you need
something like PQconninfo.Zoltan,
are you planning to work on the things discussed in this thread? I
notice the patch is sitting with "waiting on author" in the CF app -
so the second question is that if you are doing that, do you think it
will be done within the scope of the current CF?yes, I am on it so it can be done in this CF.
Great, thanks!
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/
2012-07-01 18:01 keltezéssel, Fujii Masao írta:
On Mon, Jul 2, 2012 at 12:42 AM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
2012-07-01 17:38 keltezéssel, Fujii Masao írta:
On Sun, Jul 1, 2012 at 8:02 PM, Boszormenyi Zoltan <zb@cybertec.at> wrote:
Hi,
attached is a patch that does $SUBJECT.
It's a usability enhancement, to take a backup, write
a minimalistic recovery.conf and start the streaming
standby in one go.Comments?
Could you add the patch to the next CommitFest?
I will.
If the backup is taken from the standby server, the standby's
recovery.conf
is included in the backup. What happens in this case?As documented, the command line parameters of pg_basebackup
will be used for recovery.conf. So, the new standby will replicate
the previous one. Cascading replication works since 9.2.So pg_basebackup overwrites the recovery.conf which was backed up
from the standby with the recovery.conf which was created by using
the command line parameters of pg_basebackup?
Only if requested.
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/