Re: Permissions pg_dump / import

Check out the --no-owner and/or --no-acl flags when performing the dump.
These eliminate the statements that set and/or alter ownership of database
objects.

For use in a test server where the username of the test-server database is
different than the username on the production server *and* where you don't
have lots of roles with different ownership and permissions across your
database you should be fine.

Or create role(s) on your test database that match those on the production
database. This may require updating pg_hba.conf on the test database.

Cheers,
Steve

On Wed, Aug 17, 2016 at 3:16 PM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:

> Hi guys,
>
> I'm running a pg_dump and then importing the dump into a test server. I'm
> using PostgreSQL 9.5.
>
> *pg_dump:*
>
>> pg_dump --dbname=prod1 --host= servername --port=5432 --username=user1 -v
>> -f test1_NEW.sql
>
> *Steps into the new database (test1):*
>
>> CREATE SCHEMA public;
>> GRANT ALL ON SCHEMA public TO user1;
>> psql -h servername -U master --port=5432 --dbname=test1 -f test1_NEW.sql
>
>
> I get lots of errors like:
>
> psql:test1_NEW.sql:212: ERROR: must be owner of relation accounts_id_seq
>
>
> prod1=> \d+ accounts_id_seq
>> Sequence "public.accounts_id_seq"
>> Column | Type | Value | Storage
>> ---------------+---------+---------------------+---------
>> sequence_name | name | accounts_id_seq | plain
>> last_value | bigint | 33 | plain
>> start_value | bigint | 1 | plain
>> increment_by | bigint | 1 | plain
>> max_value | bigint | 9223372036854775807 | plain
>> min_value | bigint | 1 | plain
>> cache_value | bigint | 1 | plain
>> log_cnt | bigint | 32 | plain
>> is_cycled | boolean | f | plain
>> is_called | boolean | t | plain
>> Owned by: public.accounts.id
>
>
> What do I have to do? Should I revoke the permissions on the prod1
> database before performing the dump?
>
> Cheers;
> Patrick
>