pg_rewind and postgresql.conf

On Fri, May 4, 2018 at 12:59 PM, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

Currently pg_rewind copies all files including postgresql.conf. It
would be nice if pg_rewind has an option to not copy
postgresql.conf. I sometimes create multiple PostgreSQL database
clusters with different port number which postmaster is listening on
for a testing purpose. So existing postgresql.conf on the target
cluster being overwritten by pg_rewind is annoying. I believe there
are some use cases where different port numbers are used among
PostgreSQL database clusters in the real world.

Comments?

I totally agree. Ideally, rewind would just rewind data dirs by default
and provide an option to include other files as specified by the
administrator.

However I ran out of time this time to do this patch and hope to submit
again after 11 is finalized.

There are two other things that would really be nice to make work too (but
think that's another major version away):

1. Make pg_rewind work over the replication protocol so it doesn't require
db superuser
2. Unify, to the extent possible, the code base with pg_basebackup.

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

--
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin

Would it work to use ALTER SYSTEM (postgresql.auto.conf)? Or is that copied
by pg_rewind also?

Even if so, using the ALTER SYSTEM command after pg_rewind might be an easy
way of correcting the port back to what you want without doing more
complicated processing or re-writing the entire postgresql.conf.

On 4 May 2018 at 06:59, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

On Fri, May 4, 2018 at 2:06 PM, Isaac Morland <isaac.morland@gmail.com>
wrote:

Would it work to use ALTER SYSTEM (postgresql.auto.conf)? Or is that
copied by pg_rewind also?

Yes that is copied currently by pg_rewind, as are server logs if they are
in the data directory. I think replication slots also might be copied but
would have to check.

Even if so, using the ALTER SYSTEM command after pg_rewind might be an
easy way of correcting the port back to what you want without doing more
complicated processing or re-writing the entire postgresql.conf.

On 4 May 2018 at 06:59, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

Currently pg_rewind copies all files including postgresql.conf. It
would be nice if pg_rewind has an option to not copy
postgresql.conf. I sometimes create multiple PostgreSQL database
clusters with different port number which postmaster is listening on
for a testing purpose. So existing postgresql.conf on the target
cluster being overwritten by pg_rewind is annoying. I believe there
are some use cases where different port numbers are used among
PostgreSQL database clusters in the real world.

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

--
Best Regards,
Chris Travers
Database Administrator

Tel: +49 162 9037 210 | Skype: einhverfr | www.adjust.com
Saarbrücker Straße 37a, 10405 Berlin

On Fri, May 04, 2018 at 02:10:46PM +0200, Chris Travers wrote:

Yes that is copied currently by pg_rewind, as are server logs if they are
in the data directory.

Yes, that is valid for all the existing versions of pg_rewind in the
core tree, as well as what I maintain for VMware (9.3 and 9.4
versions).

I think replication slots also might be copied but would have to check.

No. At least not since 266b6ac which will be part of Postgres 11. All
that is described in the documentation (see the bottom):
https://www.postgresql.org/docs/devel/static/app-pgrewind.html

Those are copied up to v10 though.
--
Michael

## Tatsuo Ishii (ishii@sraoss.co.jp):

Currently pg_rewind copies all files including postgresql.conf. It
would be nice if pg_rewind has an option to not copy
postgresql.conf.

How about including a file outside the data directory with "local"
settings? Like "include /some/where/else/local.conf".
Or use a setup with all config files (except postgresql.auto.conf)
outside the data directory (as on Debian/Ubuntu)?

Regards,
Christoph

--
Spare Space

On Fri, May 04, 2018 at 02:05:25PM +0200, Chris Travers wrote:

I totally agree. Ideally, rewind would just rewind data dirs by default
and provide an option to include other files as specified by the
administrator.

That's actually a potential please-shoot-both-my-feet option. Imagine
if one uses it for accidentally (or imagine reduce the amount of network
bandwidth) removing critical data like what's in pg_xact/... So I am
not especially a fan of such options.

There are two other things that would really be nice to make work too (but
think that's another major version away):

1. Make pg_rewind work over the replication protocol so it doesn't require
db superuser

Actually, there is no need to use a superuser now for v11. A user just
needs to be part of the system group pg_read_server_files. Switching to
the replication protocol could introduce more bugs which are not worth
the risk.

2. Unify, to the extent possible, the code base with pg_basebackup.

Yeah, that's what happened with 266b6ac. You could unify things a bit
more by sharing the exclude filter lists now in basebackup.c and
filemap.c into a common header, but some variables are declared only on
backend-side code and I would love to avoid tweaks like in pg_resetwal
which include postgres.h and enforce FRONTEND to 1 without using
postgres_fe.h.
--
Michael

Even if so, using the ALTER SYSTEM command after pg_rewind might be an easy
way of correcting the port back to what you want without doing more
complicated processing or re-writing the entire postgresql.conf.

To make ALTER SYSTEM works, the server needs to start up in the first
place. But without changing the port number, it's impossible to start
the server in some cases. In my case, I create several PostgreSQL
database clusters on single server by assigning different port
numbers. If the port number is already used by other server (in
pg_rewind case the source server aparently uses the port), the rewound
server won't start up.

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

I totally agree. Ideally, rewind would just rewind data dirs by default
and provide an option to include other files as specified by the
administrator.

Something like "--copy-including-files=file1,file2,..."? However I
would like to also have "--copy-excluding-files=". The latter would be
more convenient for most users like me.

However I ran out of time this time to do this patch and hope to submit
again after 11 is finalized.

Sounds great.

There are two other things that would really be nice to make work too (but
think that's another major version away):

1. Make pg_rewind work over the replication protocol so it doesn't require
db superuser
2. Unify, to the extent possible, the code base with pg_basebackup.

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