pg_dump schema in pg11 without global permissions
Hi, I'm trying to copy a schema from one PG database (ver 11) to PG 10. Previously the first database version was 9.6 and the way I did the copying was ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 -d dbnme However after migrating from PG 9.6 to 11, when I did the same thing as before, I started getting a bunch of commands in the dump like this GRANT CONNECT ON DATABASE dbname TO usernameXX; which don't work for me because the list of users is different between different machines. It is clear that the change is related to the way pg_dump is implemented now in PG11 that global objects are dumped. But the question is how do I duplicate the previous behaviour, i.e. Dump just the schema and permission on the schema, not on the database. I do know that I could grep the dump, but that's very annoying when dumping hundreds of millions of rows. Thanks in advance for the help. Sergey
Re: pg_dump schema in pg11 without global permissions
On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > Hi, > > > > I'm trying to copy a schema from one PG database (ver 11) to PG 10. > > Previously the first database version was 9.6 and the way I did the copying > > was > > > > ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump --no-tablespaces -n > > schemaname -Fc -U dbadmin dbname' | pg_restore -U dbadmin -h localhost -1 > > -d dbnme > > > > However after migrating from PG 9.6 to 11, when I did the same thing as > > before, I started getting a bunch of commands in the dump like this > > > > GRANT CONNECT ON DATABASE dbname TO usernameXX; > > > > which don't work for me because the list of users is different between > > different machines. > > It is clear that the change is related to the way pg_dump is implemented > > now in PG11 that global objects are dumped. > > But the question is how do I duplicate the previous behaviour, i.e. Dump > > just the schema and permission on the schema, not on the database. > https://www.postgresql.org/docs/11/app-pgdump.html > > -x > --no-privileges > --no-acl > > Prevent dumping of access privileges (grant/revoke commands). Yes I saw that, but that will not dump privileges on the schema itself, which were dumped before as far as I understand ... S
Re: pg_dump schema in pg11 without global permissions
On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: > On 7/23/19 3:23 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > > > > > > > > > Hi, > > > > > > > > I'm trying to copy a schema from one PG database (ver 11) to PG 10. > > > > Previously the first database version was 9.6 and the way I did the > > > > copying was > > > > > > > > ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump > > > > --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | pg_restore -U > > > > dbadmin -h localhost -1 -d dbnme > > > > > > > > However after migrating from PG 9.6 to 11, when I did the same thing as > > > > before, I started getting a bunch of commands in the dump like this > > > > > > > > GRANT CONNECT ON DATABASE dbname TO usernameXX; > > > > > > > > which don't work for me because the list of users is different between > > > > different machines. > > > > It is clear that the change is related to the way pg_dump is > > > > implemented now in PG11 that global objects are dumped. > > > > But the question is how do I duplicate the previous behaviour, i.e. > > > > Dump just the schema and permission on the schema, not on the database. > > > https://www.postgresql.org/docs/11/app-pgdump.html > > > > > > -x > > > --no-privileges > > > --no-acl > > > > > > Prevent dumping of access privileges (grant/revoke commands). > > Yes I saw that, but that will not dump privileges on the schema itself, > > which were dumped before as far as I understand ... > So the roles for the schema don't change, but everything else does? The schema permissions are granted to a generic user 'dbuser'. And a bunch of users are members of this role and that's how they access the schema. The database permissions on the other hand are granted specifically per individual user. I am not saying this is optimal, but it would be very annoying if now it became impossible to deal with this... S
Re: pg_dump schema in pg11 without global permissions
On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: > On 7/23/19 3:42 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:23 PM, Sergey Koposov wrote: > > > > > > > > > > > > On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > > > > > > > > > > > > > > > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > > > > > > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > I'm trying to copy a schema from one PG database (ver 11) to PG 10. > > > > > > Previously the first database version was 9.6 and the way I did the > > > > > > copying was > > > > > > > > > > > > ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump > > > > > > --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | > > > > > > pg_restore -U dbadmin -h localhost -1 -d dbnme > > > > > > > > > > > > However after migrating from PG 9.6 to 11, when I did the same > > > > > > thing as before, I started getting a bunch of commands in the dump > > > > > > like this > > > > > > > > > > > > GRANT CONNECT ON DATABASE dbname TO usernameXX; > > > > > > > > > > > > which don't work for me because the list of users is different > > > > > > between different machines. > > > > > > It is clear that the change is related to the way pg_dump is > > > > > > implemented now in PG11 that global objects are dumped. > > > > > > But the question is how do I duplicate the previous behaviour, i.e. > > > > > > Dump just the schema and permission on the schema, not on the > > > > > > database. > > > > > https://www.postgresql.org/docs/11/app-pgdump.html > > > > > > > > > > -x > > > > > --no-privileges > > > > > --no-acl > > > > > > > > > > Prevent dumping of access privileges (grant/revoke commands). > > > > Yes I saw that, but that will not dump privileges on the schema itself, > > > > which were dumped before as far as I understand ... > > > So the roles for the schema don't change, but everything else does? > > The schema permissions are granted to a generic user 'dbuser'. And a bunch > > of users are members of this role and that's how they access the schema. > > The database permissions on the other hand are granted specifically per > > individual user. > What version of pg_dump are you using? > > When I dump a version 10 database using a version 11 pg_dump I do not > see: GRANT CONNECT ON DATABASE > > pg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f > schema_test.out > > pg_restore -f schema_test_fc.sql schema_test.out I'm dumping version 11 database using version 11 pg_dump. I double checked this. I don't know if in your test-case you have custom users whom you granted connect permissions. I do have them. Also what I'm seeing matches perfectly the release notes on pgdump which explicitely mention dumping of the global properties. https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4 S
Re: pg_dump schema in pg11 without global permissions
On Tue, 2019-07-23 at 16:04 -0700, Adrian Klaver wrote: > On 7/23/19 3:58 PM, Sergey Koposov wrote: > > > > On Tue, 2019-07-23 at 15:52 -0700, Adrian Klaver wrote: > > > > > > On 7/23/19 3:42 PM, Sergey Koposov wrote: > > > > > > > > > > > > On Tue, 2019-07-23 at 15:37 -0700, Adrian Klaver wrote: > > > > > > > > > > > > > > > On 7/23/19 3:23 PM, Sergey Koposov wrote: > > > > > > > > > > > > > > > > > > > > > > > > On Tue, 2019-07-23 at 15:21 -0700, Adrian Klaver wrote: > > > > > > > > > > > > > > > > > > > > > > > > > > > > On 7/23/19 3:19 PM, Sergey Koposov wrote: > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Hi, > > > > > > > > > > > > > > > > I'm trying to copy a schema from one PG database (ver 11) to PG > > > > > > > > 10. > > > > > > > > Previously the first database version was 9.6 and the way I did > > > > > > > > the copying was > > > > > > > > > > > > > > > > ssh -o Compression=no user@host '/opt/pgsql/bin/pg_dump > > > > > > > > --no-tablespaces -n schemaname -Fc -U dbadmin dbname' | > > > > > > > > pg_restore -U dbadmin -h localhost -1 -d dbnme > > > > > > > > > > > > > > > > However after migrating from PG 9.6 to 11, when I did the same > > > > > > > > thing as before, I started getting a bunch of commands in the > > > > > > > > dump like this > > > > > > > > > > > > > > > > GRANT CONNECT ON DATABASE dbname TO usernameXX; > > > > > > > > > > > > > > > > which don't work for me because the list of users is different > > > > > > > > between different machines. > > > > > > > > It is clear that the change is related to the way pg_dump is > > > > > > > > implemented now in PG11 that global objects are dumped. > > > > > > > > But the question is how do I duplicate the previous behaviour, > > > > > > > > i.e. Dump just the schema and permission on the schema, not on > > > > > > > > the database. > > > > > > > https://www.postgresql.org/docs/11/app-pgdump.html > > > > > > > > > > > > > > -x > > > > > > > --no-privileges > > > > > > > --no-acl > > > > > > > > > > > > > > Prevent dumping of access privileges (grant/revoke > > > > > > > commands). > > > > > > Yes I saw that, but that will not dump privileges on the schema > > > > > > itself, which were dumped before as far as I understand ... > > > > > So the roles for the schema don't change, but everything else does? > > > > The schema permissions are granted to a generic user 'dbuser'. And a > > > > bunch of users are members of this role and that's how they access the > > > > schema. > > > > The database permissions on the other hand are granted specifically per > > > > individual user. > > > What version of pg_dump are you using? > > > > > > When I dump a version 10 database using a version 11 pg_dump I do not > > > see: GRANT CONNECT ON DATABASE > > > > > > pg_dump -Fc -n utility -s -d production -U postgres -p 5422 -f > > > schema_test.out > > > > > > pg_restore -f schema_test_fc.sql schema_test.out > > I'm dumping version 11 database using version 11 pg_dump. I double checked > > this. > > > > I don't know if in your test-case you have custom users whom you granted > > connect permissions. I do have them. > > Also what I'm seeing matches perfectly the release notes on pgdump which > > explicitely mention dumping of the global properties. > > https://www.postgresql.org/docs/11/release-11.html#id-1.11.6.9.4 > That is only supposed to happen if you use -C(--create) and I am seeing > that in your examples. For some reason I see when I just have a command like this it doesnt' have a grant connect on database ~/soft/pgsql_install/bin/pg_dump -U skoposov -h localhost -n xx --no-tablespaces test1 but when I add '-Fc' flag to pg_dump, I can see 'grant connect' inside the output (interdispersed with binary stuff) I don't get it S > > > > > > > > S > > >
Re: pg_dump schema in pg11 without global permissions
On Tue, 2019-07-23 at 19:24 -0400, Tom Lane wrote: > [ hey guys, please trim your replies ] sorry > > Experimenting, however, I see that that only works as intended if > I use v11 pg_restore. I can replicate your result if I use v10 > pg_restore. So ... don't do that. Thanks very much! I've started the pg_dump|pg_restore using the v11 pg_restore. I'll see it works in a few hours. S
strange nested loop row count estimates
Hi, I'm currently trying to understand the expected row counts for a query involving a nested loop join and bitmap index scan on the functional index and a custom operator. And the numbers that I see don't make sense to me currently. Hopefully somebody here can shed some light on it, or confirm this is some kind of issue. Here is the query and explain analyze explain analyze select * from twomass.psc as t , gaia_dr2.gaia_source as g where ( (q3c_ang2ipix(g.ra,g.dec) between q3c_nearby_it(t.ra, t.decl, 0.0003, 0) and q3c_nearby_it(t.ra, t.decl, 0.0003, 1)) or (q3c_ang2ipix(g.ra,g.dec) between q3c_nearby_it(t.ra, t.decl, 0.0003, 1) and q3c_nearby_it(t.ra, t.decl, 0.0003, 3)) ) and 0.0003 ==<<>>== (g.ra,g.dec,t.ra,t.decl)::q3c_type limit 10; https://explain.depesz.com/s/vcNd What I can't understand at all is how the estimate of 3E15 rows is obtained by the nested loop given that the bitmap heap scan is expected to return *one* single row for each row of the 'left' table. So in my mind the estimate of the total number of rows should be ~ 1e9 rows after the nested loop. Because of this crazy overestimate, I actually have to force the nested loop in this query by disabling seqscan. (if I don't disable the seqscan -- this is the plan I get which ignores the indices: https://explain.depesz.com/s/EIiG Some more details about the query: q3c_ang2ipix(ra,dec) is the function mapping (double,double) -> bigint and the tables have a functional index on that. Like this: Table "gaia_dr2.gaia_source" Column | Type| Modifiers --+---+--- ra | double precision | dec | double precision | ... Indexes: "gaia_source2_q3c_ang2ipix_idx" btree (q3c_ang2ipix(ra, "dec")) q3c_nearby_() function just returns bigint. The ==<<>== is the custom operator with custom low selectivity (1e-12 in this case) The tables in the join in question have 450 mill and 1.5 billion rows. I hope somebody can help me understand what's going on. Thank you in advance. Sergey PS the kind of query that I show comes from the q3c module ( https://github.com/segasai/q3c ) that is used for spatial queries of large astronomical catalogues.
Re: strange nested loop row count estimates
On Thu, 2019-05-02 at 00:36 -0400, Tom Lane wrote: > Sergey Koposov writes: > > > > I'm currently trying to understand the expected row counts for a query > > involving a nested loop join and bitmap index scan > > on the functional index and a custom operator. And the numbers that I see > > don't make sense to me currently. > What sort of selectivity estimator have you got attached to that custom > operator? This is the code, but basically it is just a constant based on the search radius (which is the leftmost float argument of the operator) https://github.com/segasai/q3c/blob/361140d4f1f36bf16c9c53721d1c4f03cb4de930/q3c.c#L89 For the query in question it should be ~ 1e-12 The whole idea of the operator was to specifically inform PG that this query returns a small number of rows. (the underlying idea of the query is that it does positional crossmatch between datasets on the sphere with a certain small radius). And obviously the selectivity of this is is extremely tiny).
Re: strange nested loop row count estimates
On Thu, 2019-05-02 at 01:05 -0400, Tom Lane wrote: > Sergey Koposov writes: > > > > On Thu, 2019-05-02 at 00:36 -0400, Tom Lane wrote: > > > > > > What sort of selectivity estimator have you got attached to that custom > > > operator? > > > > This is the code, but basically it is just a constant based on the search > > radius (which is the leftmost float argument of the operator) > > https://github.com/segasai/q3c/blob/361140d4f1f36bf16c9c53721d1c4f03cb4de930/q3c.c#L89 > Hm, that query should be paying attention to join selectivity, and > you don't have a join selectivity function. > > I think that it applies the restriction selectivity while > estimating the size of the bitmap scan's output. But that's not > what's going to determine the estimated size of the join output. > > Too tired to look at this really closely, but I think basically > the inconsistency boils down to the lack of consistency between > your restriction estimator (1e-12) and your join estimator > (which, since you haven't got one, is going to default to > something way larger, possibly 0.5). Thanks very much checking, Tom! Adding the join selectivity estimator fixed the problem. I think I initially tried it, but it wasn't clear whether it was called at all or not. Plus I was confused by the fact that the bitmap scan prediction showed 1 row, so it looked like the selectivity worked. Sergey
