On 2025-11-10 03:03, Tom Lane wrote:
Philip Warner <
pjw@rhyme.com.au> writes:
On 2025-11-09 04:06, Tom Lane wrote: IIRC you'd need to use --create to prod pg_dump to produce
a CREATE DATABASE command as well as any database-level
ALTER commands.
Is that a good approach?
Without --create,
pg_dump is not chartered to make a new database but only to restore
into whatever DB you've connected to. So the present approach of
issuing "ALTER DATABASE foo" commands would be completely wrong.
This could be solved if we had some kind of "ALTER CURRENT DATABASE"
command, but we don't.
That's a good point; any such change might involve the addition of "ALTER CURRENT DATABASE" which would be good in and of itself, I think.
But I note that, when one restores to an existing database, pg_restore *does* know the DB name (it's on the command line). So it could issue the 'ALTER DATABASE <name>' commands, I think.
Plus there's the question of how the results would interact with
any existing DB-level settings. I'm not really sure that issuing
such commands against a pre-existing DB is a good idea ...
I think it is a good idea, at least for what I think is a common use case:
- pg_dump someDb
- Create new test DB: create database someTestDb;
- pg_restore -> someTestDb
I came to this problem because I naively expected this to make a functionally equivalent database copy. It did not. Text search failed. There may be other settings that materially affect functionality.
I don't mind if performance-related and backend related settings are not copied (though it should be an option), but actually having a dump/restore create a non-functional DB seems wrong to me.
Worst case scenario (for backward compatibility) this would need to be an optional behaviour.
