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

[Vick Khera]

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson  wrote:

> 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 |
>
>
FWIW none of my databases other than template0 and template1 have anything
listed for Access privileges like that. I'm not even sure exactly what
those are for :(

Any privileges from REVOKEs and GRANTs will be in the dump, so those are
restored.

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

[Ron Johnson]

On 03/01/2018 11:46 AM, Adrian Klaver wrote:
[snip]

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?


$ psql < globals.sql
$ pg_restore --clean --create --if-exists --exit-on-error --jobs=2 
${SRC}/${DB}.pgdump



--
Angular momentum makes the world go 'round.

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

[Ron Johnson]

On 03/01/2018 11:28 AM, Melvin Davidson wrote:
[snip]


*>Hmmm.  I just looked at the script, and it says:

>$ pg_dumpall --schema-only > globals.sql

>That's not good.


*
*No that's actually correct.
pg_dumpall  can and will dump the globals
*
*pg_dump cannot*


I was invoking --schema-only and piping it to globals.sql.  That's deceptive.

--
Angular momentum makes the world go 'round.

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

[Adrian Klaver]

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.kla...@aklaver.com

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

[Melvin Davidson]

On Thu, Mar 1, 2018 at 12:22 PM, Ron Johnson  wrote:

> On 03/01/2018 11:03 AM, Melvin Davidson wrote:
>
>
>
> On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson 
> wrote:
>
>> On 03/01/2018 10:37 AM, Vick Khera wrote:
>>
>> On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson 
>> 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.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.
>
>
> --
> Angular momentum makes the world go 'round.
>








* >Hmmm.  I just looked at the script, and it says: >$ pg_dumpall
--schema-only > globals.sql >That's not good. *


*No that's actually correct. pg_dumpall  can and will dump the globals*

*pg_dump cannot*

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

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

[Melvin Davidson]

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson  wrote:

> On 03/01/2018 10:37 AM, Vick Khera wrote:
>
> On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson 
> 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!

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

[Ron Johnson]

On 03/01/2018 10:37 AM, Vick Khera wrote:
On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson > 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.

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

[Vick Khera]

On Thu, Mar 1, 2018 at 11:15 AM, Ron Johnson  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.

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

[Ron Johnson]

On 03/01/2018 08:46 AM, Melvin Davidson wrote:



On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson > wrote:



Or do we just apply the globals.sql created by "pg_dumpall
--globals-only"?

(We're upgrading by restoring all databases on a new server, that,
naturally, has it's own new postgres, template0 and template1 databases.)


>is restoring the postgres database needed?

That would depend on how you did the dump. If you did a_complete 
pg_dumpall (did not use -g or any other limiting flags), then all roles 
and databases are contained
in the output file created. NOTE: restoring from the dumped file will 
require rebuilding all indexes, because indexes are not dumped.


No, I do:

$ pg_dump -Fc PROD > PROD.pgdump
$ pg_dump --globals-only postgres > globals.sql
$ pg_dump -Fc postgres > postgres.pgdump



--
Angular momentum makes the world go 'round.

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

[Melvin Davidson]

On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson  wrote:

>
> Or do we just apply the globals.sql created by "pg_dumpall --globals-only"?
>
> (We're upgrading by restoring all databases on a new server, that,
> naturally, has it's own new postgres, template0 and template1 databases.)
>
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>
>
>is restoring the postgres database needed?

That would depend on how you did the dump. If you did a_complete pg_dumpall
(did not use -g or any other limiting flags), then all roles and databases
are contained
in the output file created. NOTE: restoring from the dumped file will
require rebuilding all indexes, because indexes are not dumped.

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

Version upgrade: is restoring the postgres database needed?

[Ron Johnson]

Or do we just apply the globals.sql created by "pg_dumpall --globals-only"?

(We're upgrading by restoring all databases on a new server, that, 
naturally, has it's own new postgres, template0 and template1 databases.)



Thanks

--
Angular momentum makes the world go 'round.