Re: [HACKERS] Refactor handling of database attributes between pg_dump and pg_dumpall

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
with same encoding.

Regards,
Hari Babu
Fujitsu Australia