Re: pg_rewind and postgresql.conf

[Tatsuo Ishii]

> 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

Re: pg_rewind and postgresql.conf

[Tatsuo Ishii]

> 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

Re: pg_rewind and postgresql.conf

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: pg_rewind and postgresql.conf

[Christoph Moench-Tegeder]

## Tatsuo Ishii (is...@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

Re: pg_rewind and postgresql.conf

[Chris Travers]

On Fri, May 4, 2018 at 2:06 PM, Isaac Morland 
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  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

Re: pg_rewind and postgresql.conf

[Isaac Morland]

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  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
>
>

Re: pg_rewind and postgresql.conf

[Chris Travers]

On Fri, May 4, 2018 at 12:59 PM, Tatsuo Ishii  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

pg_rewind and postgresql.conf

[Tatsuo Ishii]

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