We are migrating some products, one of the steps is to migrate the product databases.

I have steps to

• export the existing DB pg_dump --no-owner --clean --blobs --no-privileges -U dbuser old_dbname -f bkpfile.sql

• import the dump to a different DB psql -U dbuser2 new_dbname -f bkpfile.sql

The problem is the old database contains statement COMMENT ON DATABASE old_dbname IS 'Rxxxxx';

The new DB user must not have permissions on the old database and imho it's not good to refer the old database name anyway in the dump.

Is there a way to create a complete DB dump without the COMMENT ON DATABASE statement?

Edit:

PostgreSQL 9.6 Steps to reproduce:

CREATE DATABASE testdb;
COMMENT ON DATABASE testdb IS 'some comment';
CREATE TABLE xx (id int);

and then dump the database, the dump contains reference to the database name COMMENT ON DATABASE testdb IS 'some comment'; which prevents importing the backup to a new database

pg_dump --no-owner --clean --blobs --no-privileges testdb

We could manually remove the comment statement or filter the comment using different tools (grep), but manual intervention or text-based filtering on top of the backup could cause data corruption.

CodePudding user response：

This comment is only dumped in PostgreSQL versions below v11. See this entry in the release notes:

pg_dump and pg_restore, without --create, no longer dump/restore database-level comments and security labels; those are now treated as properties of the database.

9.6 will go out of support soon anyway, so this is a good opportunity to upgrade.