Re: Database migration to RDS issues permissions

Hi Adrian,

these are some of them:

pg_restore: [archiver (db)] Error from TOC entry 4997; 0 0 SEQUENCE SET account_id_seq owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for sequence account_id_seq
Command was: SELECT pg_catalog.setval('account_id_seq', 26738, true);

pg_restore: [archiver (db)] Error from TOC entry 4548; 0 106491 TABLE DATA account owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for relation account
Command was: COPY account (id, user_id, test, picture, status) FROM stdin;

pg_restore: [archiver (db)] Error from TOC entry 3763; 2604 1179420 DEFAULT id owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of relation trix_venue
Command was: ALTER TABLE ONLY venue ALTER COLUMN id SET DEFAULT eval('venue_id_seq'::regclass);

Thanks in advance.

________________________________
De: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Enviado: sábado, 19 de noviembre de 2016 15:41
Para: Fran ...; pgsql-general(at)postgresql(dot)org
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 05:21 AM, Fran ... wrote:
> Hi,
>
>
> I have to migrate a production database to RDS. This is the size and info:
>
>
> database | owneruser | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
> =Tc/owneruser +| 32 GB | pg_default |
>
>
> Origin database:
>
> 1 database
>
> 1 owneruser with superuser permission
>
>
> Backup archived size is 2G and it takes less than a minute.
>
>
> I am trying to running the following steps in a DEV environment and I am
> having problems with destination permissions.
>
>
> 1º-Creating RDS instance
>
> Done and I can connect to.
>
>
> 2º-Making backup:
>
> pg_dump -F c database > backup_db.dump
>
> 3º-Creating user,database and grant permissions in RDS.
> Create database database;
> CREATE USER owneruser WITH PASSWORD 'owneruser';
> create database database;
> grant all privileges on database to ownerdatabase;
>
> 4º-Restoring backup
> pg_restore -d database -h hostname -U postgres -F c -f log_file.log
> backup.db.dump
>
> While restoring is working it prints a lot of permissions errors.

The permissions errors are ?

A sampling will suffice for now.

>
> It's being hard to find "how to" and documentations about right permissions.
>
> Regards.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com