From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Version upgrade: is restoring the postgres database needed? |
Date: | 2018-03-01 16:51:05 |
Message-ID: | 971d555e-545f-fc55-6637-cbfd5dfe44bb@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Melvin Davidson | 2018-03-01 17:03:16 | Re: Version upgrade: is restoring the postgres database needed? |
Previous Message | Melvin Davidson | 2018-03-01 16:50:50 | Re: Enforce primary key on every table during dev? |