Thread: pg_upgrade documentation improvement patch
I was upgrading a cluster, went on the IRC channel to clarify a few things, the following discussion ensued:
yuri_niyazov: Hi. I am trying to upgrade to 9.5. The instructions for pg_upgrade include the following bit:
[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. Patch was created via "git diff --patience HEAD^ HEAD | filterdiff --format=context" as recommended on https://wiki.postgresql.org/wiki/Working_with_Git
Attachment
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.
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
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
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
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 +