CODEWITHSUNDEEP
postgresqlpostgresql-9.6pg-dump
gusto2
gusto2

Reputation: 12085

PSQL prevent "COMMENT ON" on the DB dump

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

I have steps to

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.

Upvotes: 1

Views: 247

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

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.

Upvotes: 2

Related Questions