Postgres Pro
Downloads
Home   >   mailing lists

Re: Version upgrade: is restoring the postgres database needed? - Mailing list pgsql-general

From Ron Johnson
Subject Re: Version upgrade: is restoring the postgres database needed?
Date
Msg-id 971d555e-545f-fc55-6637-cbfd5dfe44bb@cox.net
Whole thread Raw
In response to Version upgrade: is restoring the postgres database needed?  (Ron Johnson <ron.l.johnson@cox.net>)
Responses Re: Version upgrade: is restoring the postgres database needed?
Re: Version upgrade: is restoring the postgres database needed?
List pgsql-general
Tree view
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.

pgsql-general by date:

Previous
From: Melvin Davidson
Date:
Subject: Re: Enforce primary key on every table during dev?
Next
From: Melvin Davidson
Date:
Subject: Re: Version upgrade: is restoring the postgres database needed?