Horton

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

Adrian Klaver <adrian.klaver@aklaver.com>

View thread View on postgr.es
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Ron Johnson <ron.l.johnson@cox.net>, pgsql-general@lists.postgresql.org
Date: 2018-03-01T17:46:37Z
Lists: pgsql-general 
On 03/01/2018 09:22 AM, Ron Johnson wrote:
> On 03/01/2018 11:03 AM, Melvin Davidson wrote:
>>

>>
>>     *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.

Well it would dump the globals, but also the schema definitions for all 
the objects in the cluster. Though at this point we are only half way 
through the process. What is you restore procedure?

> 
> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Commits

  1. Move handling of database properties from pg_dumpall into pg_dump.