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
aklaver@ranger:~> grep CONNECT schema_test_fc.sql
aklaver@ranger:~>
I am not saying this is optimal, but it would be very annoying if now it became impossible to deal with this...
S
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
