Thread
Commits
-
Move handling of database properties from pg_dumpall into pg_dump.
- b3f8401205af 11.0 cited
-
Version upgrade: is restoring the postgres database needed?
Ron Johnson <ron.l.johnson@cox.net> — 2018-03-01T10:24:45Z
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?
Melvin Davidson <melvin6925@gmail.com> — 2018-03-01T14:46:58Z
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?
Ron Johnson <ron.l.johnson@cox.net> — 2018-03-01T16:15:42Z
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 > <mailto: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?
Vick Khera <vivek@khera.org> — 2018-03-01T16:37:56Z
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?
Ron Johnson <ron.l.johnson@cox.net> — 2018-03-01T16:51:05Z
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 > <mailto: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.
-
Re: Version upgrade: is restoring the postgres database needed?
Melvin Davidson <melvin6925@gmail.com> — 2018-03-01T17:03:16Z
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/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 <ron.l.johnson@cox.net> — 2018-03-01T17:22:20Z
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 > <mailto: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 >> <mailto: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. > > > *>$ 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.
-
Re: Version upgrade: is restoring the postgres database needed?
Melvin Davidson <melvin6925@gmail.com> — 2018-03-01T17:28:51Z
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/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?
Adrian Klaver <adrian.klaver@aklaver.com> — 2018-03-01T17:46:37Z
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?
Ron Johnson <ron.l.johnson@cox.net> — 2018-03-01T17:54:15Z
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?
Ron Johnson <ron.l.johnson@cox.net> — 2018-03-01T17:59:48Z
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?
Vick Khera <vivek@khera.org> — 2018-03-01T18:20:14Z
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/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?
Adrian Klaver <adrian.klaver@aklaver.com> — 2018-03-01T21:00:16Z
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