Restoring Database on Version 11 does not restore database comment

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Restoring Database on Version 11 does not restore database comment

George Weaver
Good afternoon,

If I backup a PostgreSQL 10.14 database using pg_dump from Version
11.10, and restore to Version 11, the comment for the database is lost.

I haven't encountered this before when upgrading between major versions.

Test case:

C:\Program Files\PostgreSQL\10\bin>psql
psql (10.14)

postgres=# CREATE DATABASE test_comment TEMPLATE = template0;
CREATE DATABASE
postgres=# COMMENT ON DATABASE test_comment IS 'Test Comment Database';
COMMENT
postgres=# SELECT pg_catalog.shobj_description(d.oid, 'pg_database') as
"Version"
postgres-# FROM pg_catalog.pg_database d
postgres-#   where d.datname = 'test_comment';
         Version
-----------------------
  Test Comment Database
(1 row)

C:\Program Files\PostgreSQL\11\bin>pg_dump -h localhost -p 5433 -U
postgres -f "G:\test_comment.bak" -Fc -O test_comment

C:\Program Files\PostgreSQL\11\bin>psql -p 5435
psql (11.10)

postgres=# CREATE DATABASE test_comment TEMPLATE = template0;
CREATE DATABASE
postgres=# \q

C:\Program Files\PostgreSQL\11\bin>pg_Restore -p 5435  -U postgres -d
test_comment "G:\test_comment.bak"

C:\Program Files\PostgreSQL\11\bin>psql -p 5435
psql (11.10)

postgres=# SELECT pg_catalog.shobj_description(d.oid, 'pg_database') as
"Version"
postgres-# FROM pg_catalog.pg_database d
postgres-#   where d.datname = 'test_comment';
  Version
---------

(1 row)

What am I missing?

Thanks,
George




Reply | Threaded
Open this post in threaded view
|

Re: Restoring Database on Version 11 does not restore database comment

David G Johnston
On Fri, Nov 20, 2020 at 3:58 PM George Weaver <[hidden email]> wrote:
What am I missing?


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."

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Restoring Database on Version 11 does not restore database comment

Bruce Momjian
On Fri, Nov 20, 2020 at 04:01:26PM -0700, David G. Johnston wrote:

> On Fri, Nov 20, 2020 at 3:58 PM George Weaver <[hidden email]> wrote:
>
>     What am I missing?
>
>
>
> Release notes.
>
> https://www.postgresql.org/docs/11/release-11.html
>
> "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."

Yeah, I realize this new behavior is kind of odd, but logically, it
makes sense.

--
  Bruce Momjian  <[hidden email]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee



Reply | Threaded
Open this post in threaded view
|

Re: Restoring Database on Version 11 does not restore database comment

George Weaver
In reply to this post by David G Johnston

Thanks David!

Missed that...

On 20/11/2020 5:01 p.m., David G. Johnston wrote:
On Fri, Nov 20, 2020 at 3:58 PM George Weaver <[hidden email]> wrote:
What am I missing?


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."

David J.

-- 
Cleartag Software, Inc.
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
(204) 284-9838 fax
[hidden email]

Fast. Accurate. Easy.