tables in the DB is not available after pg_restore.
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/11/app-pgrestore.html Description: Hi, I have one question about pg_restore tool, tables in the DB is not available after pg_restore. Even if the user is still the owner of table after restore. Steps: 1.Create one database and the owner is user1. And create table and pop the data with user1 2.Use pg_dump tool to backup one database with superuser postgres. 3.Use pg_restore tool to restore the database with the parameter –clean and supper user. 4.Connect the db with user1 and found the user does not select table as below. cdb=> \dt Did not find any relations. Thanks Zeng
Re: The word "virgin" used incorrectly and probably better off replaced
> On 7 Nov 2019, at 22:50, Alvaro Herrera wrote: > > On 2019-Nov-07, Bruce Momjian wrote: > >> On Thu, Nov 7, 2019 at 07:55:22PM +0100, Daniel Gustafsson wrote: On 7 Nov 2019, at 16:03, Alvaro Herrera wrote: > We could say "empty", which seems better suited than both "virgin" and "pristine" anyway. >>> >>> empty is a lot better, but still isn't conveying the state of the database >>> without there being room for interpretation. (My grasp of the english >>> language >>> isn't enough to suggest a better alternative however). >> >> I am thinking "pristine" would be a good word here. > > But you would have to explain that a database created as a copy of > template1 may somehow not be pristine. Maybe we should just use a > phrase that describes what we mean, something like "a database that > doesn't contain objects other than default system ones." Agreed. I like your suggestion, or the inverse of it: "a database without any user defined objects". cheers ./daniel
Re: The word "virgin" used incorrectly and probably better off replaced
On 2019-Nov-08, Daniel Gustafsson wrote: > Agreed. I like your suggestion, or the inverse of it: "a database without any > user defined objects". Here's a proposed patch. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >From b394a1b2b6227d68e0cd2c32afb75ef3bfd317ef Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 8 Nov 2019 10:09:05 -0300 Subject: [PATCH] No more virgins --- doc/src/sgml/manage-ag.sgml | 5 +++-- doc/src/sgml/ref/create_database.sgml | 2 +- 2 files changed, 4 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 0154064e50..a939ce8313 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -199,11 +199,12 @@ createdb -O rolename dbnameCREATE DATABASE to copy template0 instead - of template1, you can create a virgin user + of template1, you can create a user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a - virgin database to ensure that one recreates the correct contents + database without any user-defined objects, to ensure that one recreates + the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on. diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 4014f6703b..e56aca6d30 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -54,7 +54,7 @@ CREATE DATABASE name system database template1. A different template can be specified by writing TEMPLATE name. In particular, - by writing TEMPLATE template0, you can create a virgin + by writing TEMPLATE template0, you can create a database containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying -- 2.20.1
Re: The word "virgin" used incorrectly and probably better off replaced
> On 8 Nov 2019, at 14:10, Alvaro Herrera wrote: > > On 2019-Nov-08, Daniel Gustafsson wrote: > >> Agreed. I like your suggestion, or the inverse of it: "a database without >> any >> user defined objects". > > Here's a proposed patch. +1, LGTM cheers ./daniel
Re: The word "virgin" used incorrectly and probably better off replaced
Alvaro Herrera writes: > Here's a proposed patch. I don't like this wording much, because "no user-defined objects" is not a sufficient specification of what we are talking about. You need to also capture the property that none of the system- defined objects have been altered. Now that we explicitly support things like altering the ACLs of system-defined objects, I do not think it's okay to take that part for granted. regards, tom lane
Re: The word "virgin" used incorrectly and probably better off replaced
On 2019-Nov-08, Tom Lane wrote: > Alvaro Herrera writes: > > Here's a proposed patch. > > I don't like this wording much, because "no user-defined objects" > is not a sufficient specification of what we are talking about. > You need to also capture the property that none of the system- > defined objects have been altered. Now that we explicitly support > things like altering the ACLs of system-defined objects, I do not > think it's okay to take that part for granted. Hmm. Maybe we can say "pristine database" and then add this explanation in a parenthical comment: This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a pristine database (one where no user-defined objects exist and where system objects have not been altered), to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: The word "virgin" used incorrectly and probably better off replaced
Alvaro Herrera writes: > Hmm. Maybe we can say "pristine database" and then add this explanation > in a parenthical comment: > This is particularly handy when restoring a > pg_dump dump: the dump script should be restored in a > pristine database (one where no user-defined objects exist and where > system objects have not been altered), to ensure that one recreates > the correct contents of the dumped database, without conflicting > with objects that might have been added to > template1 later on. So the patch becomes s/virgin/pristine/g plus add a parenthetical definition for the first use? Works for me. regards, tom lane
Re: The word "virgin" used incorrectly and probably better off replaced
> On 8 Nov 2019, at 16:19, Tom Lane wrote: > So the patch becomes s/virgin/pristine/g plus add a parenthetical > definition for the first use? Works for me. Agreed. cheers ./daniel
Re: The word "virgin" used incorrectly and probably better off replaced
On 2019-Nov-08, Tom Lane wrote: > Alvaro Herrera writes: > > Hmm. Maybe we can say "pristine database" and then add this explanation > > in a parenthical comment: > > > This is particularly handy when restoring a > > pg_dump dump: the dump script should be restored in a > > pristine database (one where no user-defined objects exist and where > > system objects have not been altered), to ensure that one recreates > > the correct contents of the dumped database, without conflicting > > with objects that might have been added to > > template1 later on. > > So the patch becomes s/virgin/pristine/g plus add a parenthetical > definition for the first use? Works for me. Well, there are three uses of the word "virgin". The first is for "virgin user", and the patch turns that into just "user". The second one is for "virgin database" and the patch has the effect you describe. The third one is also s/virgin//. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml index 0154064e50..7e7f0ed00c 100644 --- a/doc/src/sgml/manage-ag.sgml +++ b/doc/src/sgml/manage-ag.sgml @@ -197,15 +197,17 @@ createdb -O rolename dbnamePostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead - of template1, you can create a virgin user + of template1, you can create a user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a - virgin database to ensure that one recreates the correct contents + pristine database (one where no user-defined objects exist and where + system objects have not been altered), to ensure that one recreates + the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on. diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 4014f6703b..e56aca6d30 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -52,11 +52,11 @@ CREATE DATABASE name By default, the new database will be created by cloning the standard system database template1. A different template can be specified by writing TEMPLATE name. In particular, - by writing TEMPLATE template0, you can create a virgin + by writing TEMPLATE template0, you can create a database containing only the standard objects predefined by your version of PostgreSQL. This is useful if you wish to avoid copying any installation-local objects that might have been added to template1.
Re: The word "virgin" used incorrectly and probably better off replaced
Alvaro Herrera writes: > On 2019-Nov-08, Tom Lane wrote: >> So the patch becomes s/virgin/pristine/g plus add a parenthetical >> definition for the first use? Works for me. > Well, there are three uses of the word "virgin". The first is for > "virgin user", and the patch turns that into just "user". Uh, no, read the next lines. In both cases those are referring to "virgin user database" or "virgin database", and this patch is removing an important qualifier. It needs to be s/virgin/pristine/ in all these places. Since the third case is well separated from the other two, maybe we need to repeat the parenthetical definition there too. regards, tom lane
