On Fri, Jan 19, 2018 at 10:35 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I wrote: > What I think we should do for the time being is to have pg_dump treat > database tablespace as a property it can't adjust after creation, just > as it can't adjust locale or encoding. That's a loss of functionality > for pg_dumpall/pg_upgrade compared to where we are today, in that if > you've set up the template1 or postgres DBs with nondefault tablespace > then that won't propagate to the new cluster. But the same can already > be said about their locale and encoding, and I find it hard to believe > that many people are trying to give those two DBs tablespace settings > different from the cluster default, anyway.
Hm ... actually, there is more than one way to skin this cat.
Let me offer a modest proposal: pg_dumpall/pg_upgrade should simply DROP postgres and template1 in the target cluster, and then re-create them (from template0 of course). With that, we'd not only cope with preserving their tablespace settings, but we'd gain the ability to preserve their locale and encoding, even if the target cluster had been initialized with some other default.
Yes, I agree that this may be simple change to handle this problem.
Already pg_upgrade doesn't work if there is any encoding difference
between source and target databases. Most probably User will create