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.l.johnson@cox.net 
> <mailto:ron.l.johnson@cox.net>> wrote:
>
>     On 03/01/2018 10:37 AM, Vick Khera wrote:
>>     On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson <ron.l.johnson@cox.net
>>     <mailto:ron.l.johnson@cox.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.