Обсуждение: pg_dump not dumping default_text_search_config WAI?
I just got bitten by dumping and restoring a database to a new cluster, and the default_text_search_config was not copied for the database.
A check of the dump file shows no 'ALTER DATABASE' command.
I used an 'ALTER DATABASE...' to set it correctly and then did a dump, and the config was not in the dump.
From my understanding this is a cluster setting that only applies to this database, and ISTM that database config *should* be in the dump.
Is this WAI? Are there other database settings that are not exported by a pg_dump?
Philip Warner <pjw@rhyme.com.au> writes:
> I just got bitten by dumping and restoring a database to a new cluster,
> and the default_text_search_config was not copied for the database.
> A check of the dump file shows no 'ALTER DATABASE' command.
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.
regards, tom lane
> Philip Warner <pjw@rhyme.com.au> writes:
>> I just got bitten by dumping and restoring a database to a new cluster,
>> and the default_text_search_config was not copied for the database.
>
>> A check of the dump file shows no 'ALTER DATABASE' command.
>
> 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? The --create option should create the DB, yes. But...DB settings should, IMO, be done as part of the normal state dump/restore
ie. If I have previously issued a "create database" then a pg_restore should be able to create a matching database.
Would there be any appetite for a patch to at least implement this as an option? Which leads to setting other config parameters and the settings for ALLOW_CONNECTIONS and CONNECTION LIMIT.
Sent from my Android device with K-9 Mail. Please excuse my brevity.
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?
Well, it's what pg_dump has always done, at least since it inherited
that work from pg_dumpall (which was even less flexible).
It's not that easy/safe to do it differently. 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.
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 ...
regards, tom lane
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.
I'm not sure where this leaves this idea. I don't want to write the patch to be told its not acceptable for the reasons outlined in this brief discussion, but I am happy to iterate a patch process if the idea is at least viable.
The basic idea would be to add:
--include-database-config
On a pg_dump that does not include --create, then dump specialised ALTER DATABASE commands for all database config, where these commands are (somehow) flagged as needing the database name substituted into them. Or add ALTER CURRENT DATABASE (see below).
Possibilities include:
- add a new "database settings" TOC entry and get pg_restore to interpret it.
(Thus would mean always dumping, and putting --include-database-config on the restore)
- add settings on the TOC entry that says "substitute db name for <something>"
- add "ALTER CURRENT DATABASE" as part of the process, then just issue relevant commands in the dump.
The first option is appealing for its simplicity. The second option may have more general utility. The third option is by far the most elegant, IMO.
Neither of the first two options work with plain text dumps, but the data could go in comments.
So, should I bother pursuing this or am I already at a dead end?
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.
Sent from my Android device with K-9 Mail. Please excuse my brevity.