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

On Thu, Mar 1, 2018 at 12:22 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:

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

* >Hmmm. I just looked at the script, and it says: >$ pg_dumpall
--schema-only > globals.sql >That's not good. *

*No that's actually correct. pg_dumpall can and will dump the globals*

*pg_dump cannot*

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!