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

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.