Re: Version upgrade: is restoring the postgres database needed?

On 03/01/2018 09:59 AM, Ron Johnson wrote:
> On 03/01/2018 11:46 AM, Adrian Klaver wrote:
> [snip]
>>> Hmmm.  I just looked at the script, and it says:
>>>
>>> $ pg_dumpall --schema-only > globals.sql
>>>
>>> That's not good.
>>
>> Well it would dump the globals, but also the schema definitions for
>> all the objects in the cluster. Though at this point we are only half
>> way through the process. What is you restore procedure?
>
> $ psql < globals.sql

Assuming globals.sql was creating your previously shown command:

pg_dumpall --schema-only > globals.sql

Then the above added the globals to the cluster and installed the schema
objects(but not data) for the cluster.

> $ pg_restore --clean --create --if-exists --exit-on-error --jobs=2

When you do --clean and --create you DROP the database from the cluster
before it is restored. A quick test here shows that the database
permissions are not restored in that case. This is something that us
contained in the globals.

I fix for this I believe is covered in this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3f8401205afdaf63cb20dc316d44644c933d5a1

> ${SRC}/${DB}.pgdump
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com