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

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <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>
> 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.sqlOR $ pg_dumpall -g > globals.sql $ pg_dump -Fc
postgres > postgres.pgdump *

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