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

Version upgrade: is restoring the postgres database needed?

From

Ron Johnson

Date:

01 March 2018, 16:24:45

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.

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

From

Melvin Davidson

Date:

01 March 2018, 20:46:58

On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson <ron.l.johnson@cox.net> 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!

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

From

Ron Johnson

Date:

01 March 2018, 22:15:42

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

On Thu, Mar 1, 2018 at 5:24 AM, Ron Johnson <ron.l.johnson@cox.net> 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?

From

Vick Khera

Date:

01 March 2018, 22:37:56

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.

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

From

Ron Johnson

Date:

01 March 2018, 22:51:05

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.

--
Angular momentum makes the world go 'round.

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

From

Melvin Davidson

Date:

01 March 2018, 23:03:16

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:

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/CSSCSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSCSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSNewly restoredpostgres=# \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?

From

Ron Johnson

Date:

01 March 2018, 23:22:20

On 03/01/2018 11:03 AM, Melvin Davidson wrote:

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:
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/CSSCSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSCSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSNewly restoredpostgres=# \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

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.

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

From

Melvin Davidson

Date:

01 March 2018, 23:28:51

On Thu, Mar 1, 2018 at 12:22 PM, Ron Johnson <ron.l.johnson@cox.net> wrote:

On 03/01/2018 11:03 AM, Melvin Davidson wrote:

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.johnson@cox.net> wrote:
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/CSSCSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSCSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSNewly restoredpostgres=# \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

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?

From

Adrian Klaver

Date:

01 March 2018, 23:46:37

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

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

From

Ron Johnson

Date:

01 March 2018, 23:54:15

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?

From

Ron Johnson

Date:

01 March 2018, 23:59:48

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?

From

Vick Khera

Date:

02 March 2018, 00:20:14

On Thu, Mar 1, 2018 at 11:51 AM, Ron Johnson <ron.l.johnson@cox.net> 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/CSSCSSCAT_STIB | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSCSSCAT_STIC | CSS | UTF8 | en_US.UTF-8 | en_US.UTF-8 | CSS=CTc/CSS + | | | | | =Tc/CSS + | | | | | app_user=CTc/CSSNewly restoredpostgres=# \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?

From

Adrian Klaver

Date:

02 March 2018, 03:00:16

On 03/01/2018 09:59 AM, Ron Johnson wrote:
> 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

Assuming globals.sql was creating your previously shown command:

pg_dumpall --schema-only > globals.sql

Then the above added the globals to the cluster and installed the schema 
objects(but not data) for the cluster.

> $ pg_restore --clean --create --if-exists --exit-on-error --jobs=2 

When you do --clean and --create you DROP the database from the cluster 
before it is restored. A quick test here shows that the database 
permissions are not restored in that case. This is something that us 
contained in the globals.

I fix for this I believe is covered in this commit:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b3f8401205afdaf63cb20dc316d44644c933d5a1

> ${SRC}/${DB}.pgdump
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com