On 7/23/19 4:04 PM, 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
^not
that in your examples.
S
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
