pg_upgrade documentation improvement patch

On 03/20/2016 06:40 PM, Yuri Niyazov wrote:

[4:35pm] yuri_niyazov: "Again, use compatible initdb flags that match
the old cluster" - this is when running initdb before running pg_upgrade
[4:36pm] RhodiumToad: same encoding, locale, and checksum settings
[4:36pm] yuri_niyazov: Can I recover those from the data directory? The
previous initdb was ran 3 admins ago
[4:37pm] RhodiumToad: psql -l against the old server and look at the
encoding and locale for template0
[4:37pm] RhodiumToad: checksums are almost certainly not enabled, but
you can check that by running pg_controldata against the old data dir
[4:38pm] yuri_niyazov: thank you!
[4:38pm] RhodiumToad: (if the old server is too old for checksums, then
don't enable them in the new initdb)

I took RhodiumToad's comments and made them into a patch to pg_upgrade's
documentation.

I think it's worth clarifying the documentation along the lines you
suggest, but I'm not sure about the details. A better way to get the
*cluster* locale is from pg_controldata. I don't know to what extent
pg_upgrade actually requires the encoding to match, since that is mostly
determined by the locale, and in the cases it's not, it might not matter.

Interesting to me would be a way, perhaps with an option in initdb, to
just say, initialize this cluster compatibly with that other cluster, so
you don't have to worry about these details.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Peter Eisentraut <peter_e@gmx.net> writes:

Interesting to me would be a way, perhaps with an option in initdb, to
just say, initialize this cluster compatibly with that other cluster, so
you don't have to worry about these details.

Good idea, though I'd think of it as a pg_upgrade option more than being
initdb's problem.  Either way, though, it would be on the code's head
to do something about converting the postgresql.conf, pg_hba.conf, etc
configuration files from the old cluster to the new, which means this
isn't just a trivial matter of running initdb on the target PGDATA
location.  That turns it into a bit of a research project I'm afraid
--- but if we could get there, it'd be a nice improvement.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Apr 13, 2016 at 6:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Interesting to me would be a way, perhaps with an option in initdb, to
just say, initialize this cluster compatibly with that other cluster, so
you don't have to worry about these details.

Good idea, though I'd think of it as a pg_upgrade option more than being
initdb's problem.  Either way, though, it would be on the code's head
to do something about converting the postgresql.conf, pg_hba.conf, etc
configuration files from the old cluster to the new, which means this
isn't just a trivial matter of running initdb on the target PGDATA
location.  That turns it into a bit of a research project I'm afraid
--- but if we could get there, it'd be a nice improvement.

regards, tom lane

There were other things that happened while doing this cluster upgrade that
required more documentation searching - I believe some wal-related
configuration options that go into postgresql.conf were deprecated in 9.5,
so the server complained upon starting up - however, the documentation in
that case was pretty clear about how to replace the old with the new.

The phrase "Many prebuilt installers do this step automatically" - it was
there originally, and I left it in, but I don't know any details about it.
If this is true, then it seems to me that the work that goes into that can
profitably go into pg_upgrade, no?

From the point of view of a PG-admin noob like me, it's unclear *from the
documentation* to what extent locale and encoding at the cluster level must
match. Certainly the relatively stern phrase "Again, use compatible initdb
flags that match the old cluster" in the documentation stopped me in my
tracks until I got some further clarification, because the consequences of
not doing so were not mentioned at all, and I lean towards conservatism
when it comes to scary things like upgrading a production machine across a
major version release.

Should I update the documentation patch to instruct the use of
pg_controldata exclusively?

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Interesting to me would be a way, perhaps with an option in initdb, to
just say, initialize this cluster compatibly with that other cluster, so
you don't have to worry about these details.

Good idea, though I'd think of it as a pg_upgrade option more than being
initdb's problem.

Agreed. It's always seemed to me that having pg_upgrade require you to
run initdb is unfriendly; it seems so much more convenient to have it do
the initdb etc for you, where you just specify the target directory
(probably created ahead of time because of ownership -- but initdb
already knows how to deal with that case).

Also, pg_upgrade receiving a pre-existing cluster is inconvenient
because it needs to verify that it's empty etc, for no good reason.

Either way, though, it would be on the code's head to do something
about converting the postgresql.conf, pg_hba.conf, etc configuration
files from the old cluster to the new, which means this isn't just a
trivial matter of running initdb on the target PGDATA location. That
turns it into a bit of a research project I'm afraid --- but if we
could get there, it'd be a nice improvement.

I don't think we've ever had a backwards-incompatible change in
pg_hba.conf (so we could just copy it over verbatim), and the
postgresql.conf changes should be mostly easy to deal with (so we could
just copy it over and modify a small number of lines), but I wonder if
things like nonstandard locations of config files might be problematic.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Tue, Apr 19, 2016 at 3:42 AM, Yuri Niyazov <yuri@academia.edu> wrote:

Should I update the documentation patch to instruct the use of
pg_controldata exclusively?

I guess so. Marked as returned with feedback because the thread has died.
--
Michael

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Oct 3, 2016 at 10:36:30AM +0900, Michael Paquier wrote:

On Tue, Apr 19, 2016 at 3:42 AM, Yuri Niyazov <yuri@academia.edu> wrote:

Should I update the documentation patch to instruct the use of
pg_controldata exclusively?

I guess so. Marked as returned with feedback because the thread has died.

I did review this suggestion and concur that there just isn't enough
justification to list in the docs everything pg_upgrade checks for
compatibility, and a partial list might be worse than no list at all.

I agree it would be nice if we could somehow pull the initdb flags out
of a PGDATA and use them.

The good news is that pg_upgrade --check will do the verification and
report any problems, which might be why we have not seems this
complained about before.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+                     Ancient Roman grave inscription +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers