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

On 03/01/2018 11:03 AM, Melvin Davidson wrote:
>
>
> On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net
> <mailto:ron(dot)l(dot)johnson(at)cox(dot)net>> wrote:
>
> On 03/01/2018 10:37 AM, Vick Khera wrote:
>> On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net
>> <mailto:ron(dot)l(dot)johnson(at)cox(dot)net>> wrote:
>>
>> No, I do:
>>
>> $ pg_dump -Fc PROD > PROD.pgdump
>> $ pg_dump --globals-only postgres > globals.sql
>> $ pg_dump -Fc postgres > postgres.pgdump
>>
>>
>> That's how I back them up as well. You are correct that all you need
>> to do is restore the globals.sql, then each "pgdump" file
>> individually. Just ignore the warning when it tries to restore your
>> initial postgres superuser, since it was created by the initdb already.
>>
>> You probably don't need the "postgres" db at all, since it is just
>> there to allow the client to connect to something on initial install.
>> Normally you don't use it in production.
>
> Good.  What, then, have I forgotten to restore such that the "Access
> privileges" are showing on my current 9.2 servers, but not on the
> newly-restored 9.6.6 server?
>
> *Current*
> postgres=# \l
>      List of databases
>     Name     |  Owner   | Encoding | Collate   |    Ctype    |  
> Access privileges
> -------------+----------+----------+-------------+-------------+-----------------------
> CSSCAT_STI   | CSS      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS          +
>              |          | |             |             |
> =Tc/CSS              +
>              |          | |             |             | app_user=CTc/CSS
> CSSCAT_STIB  | CSS      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS          +
>              |          | |             |             |
> =Tc/CSS              +
>              |          | |             |             | app_user=CTc/CSS
> CSSCAT_STIC  | CSS      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> CSS=CTc/CSS          +
>              |          | |             |             |
> =Tc/CSS              +
>              |          | |             |             | app_user=CTc/CSS
>
> *Newly restored*
> postgres=# \l
>      List of databases
>     Name     |  Owner   | Encoding | Collate   |    Ctype    |  
> Access privileges
> -------------+----------+----------+-------------+-------------+-----------------------
> CSSCAT_STIB | CSS      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> CSSCAT_STIC | CSS      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>
>
> --
> Angular momentum makes the world go 'round.
>
>
> *>$ pg_dump -Fc PROD > PROD.pgdump
> >$ pg_dump --globals-only postgres > globals.sql
> >$ pg_dump -Fc postgres > postgres.pgdump
>
> *
> *The last I looked, pg_dump does not have a "--globals-only"
> *
> *Did you mean?
> $ pg_dump -Fc PROD > PROD.pgdump
>
> $ pg_dumpall --globals-only postgres > globals.sql
> OR
> $ pg_dumpall -g > globals.sql
>
> $ pg_dump -Fc postgres > postgres.pgdump*

Hmmm.  I just looked at the script, and it says:

$ pg_dumpall --schema-only > globals.sql

That's not good.

--
Angular momentum makes the world go 'round.