Database migration to RDS issues permissions

Started by Fran ...over 9 years ago13 messagesgeneral

Jump to latest

Fran ...

Bryan691@hotmail.com

over 9 years ago

Hi,

I have to migrate a production database to RDS. This is the size and info:

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.

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

Regards.

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#1)

Re: 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.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Fran ...

Bryan691@hotmail.com

over 9 years ago

In reply to: Adrian Klaver (#2)

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.klaver@aklaver.com>
Enviado: sábado, 19 de noviembre de 2016 15:41
Para: Fran ...; pgsql-general@postgresql.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.klaver@aklaver.com

Tom Lane

tgl@sss.pgh.pa.us

over 9 years ago

In reply to: Fran ... (#3)

Re: Database migration to RDS issues permissions

"Fran ..." <Bryan691@hotmail.com> writes:

these are some of them:

These look to be cascading damage from some earlier failure. I'd advise
looking at the first one or two errors and solving them, then repeat
as necessary.

In general, though, pg_dump of a single database is not a complete
representation of where you were: it lacks any information about
global objects (roles and tablespaces). I suspect your problems
ultimately trace back to not having created the right roles in
the target installation before starting the restore. You might
find "pg_dumpall -g" to be helpful.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#3)

Re: Database migration to RDS issues permissions

On 11/19/2016 07:21 AM, Fran ... wrote:

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.

In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Fran ...

Bryan691@hotmail.com

over 9 years ago

In reply to: Adrian Klaver (#5)

Re: Database migration to RDS issues permissions

Hi,

I run "pg_dumpall" command and there are the permissions por the user:

CREATE ROLE dlapuser;
ALTER ROLE dlapuser WITH SUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD 'md5XXXXXXXXXXXXXXXXXXXXXXafac';

I think I would solve the problem granting "superuser" permission but this is not possible in RDS.

I also saw the first errors and they are weird.

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4751; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: [archiver (db)] Error from TOC entry 4752; 0 0 COMMENT EXTENSION pg_stat_statements
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension pg_stat_statements
Command was: COMMENT ON EXTENSION pg_stat_statements IS 'track execution statistics of all SQL statements executed';

pg_restore: [archiver (db)] Error from TOC entry 4753; 0 0 COMMENT EXTENSION postgres_fdw
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension postgres_fdw
Command was: COMMENT ON EXTENSION postgres_fdw IS 'foreign-data wrapper for remote PostgreSQL servers';

pg_restore: [archiver (db)] Error from TOC entry 408; 1255 563407 FUNCTION cksum2(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c
Command was: CREATE FUNCTION cksum2(text) RETURNS smallint
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum2';

pg_restore: [archiver (db)] could not execute query: ERROR: function public.cksum2(text) does not exist
Command was: ALTER FUNCTION public.cksum2(text) OWNER TO postgres;

pg_restore: [archiver (db)] Error from TOC entry 411; 1255 563408 FUNCTION cksum4(text) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: permission denied for language c
Command was: CREATE FUNCTION cksum4(text) RETURNS integer
LANGUAGE c
AS '$libdir/pgc_checksum', 'text_checksum4';

pg_restore: [archiver (db)] could not execute query: ERROR: function public.cksum4(text) does not exist
Command was: ALTER FUNCTION public.cksum4(text) OWNER TO postgres;

@Adrian Klaver<mailto:adrian.klaver@aklaver.com> what others permissions do you suggest?

Origin and target are the same version of course. PostgreSQL 9.4.1

Thanks in advance.

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

On 11/19/2016 07:21 AM, Fran ... wrote:

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.

In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html
PostgreSQL: Documentation: 9.5: GRANT<https://www.postgresql.org/docs/9.5/static/sql-grant.html>
www.postgresql.org
GRANT on Database Objects. This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those ...

--
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane

tgl@sss.pgh.pa.us

over 9 years ago

In reply to: Fran ... (#6)

Re: Database migration to RDS issues permissions

"Fran ..." <Bryan691@hotmail.com> writes:

I think I would solve the problem granting "superuser" permission but this is not possible in RDS.

It looks like your other errors are also due to doing the restore as
a non-superuser. Not sure if you have any good alternatives here ---
you could just ignore the errors relating to plpgsql, but if you have
C-language functions that you need to migrate, there is no way to
install those without superuser privileges.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#6)

Re: Database migration to RDS issues permissions

On 11/19/2016 09:33 AM, Fran ... wrote:

Hi,

I run "pg_dumpall" command and there are the permissions por the user:

/CREATE ROLE dlapuser;/
/ALTER ROLE dlapuser WITH *SUPERUSER* INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION PASSWORD 'md5XXXXXXXXXXXXXXXXXXXXXXafac';/

I think I would solve the problem granting "superuser" permission but
this is not possible in RDS.

I don't use RDS, but from what I gather the above is not strictly true:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

I also saw the first errors and they are weird.

Indications that you are not running the restore as a user with
sufficient privileges.

Is the database you are dumping from an RDS instance or a regular
Postgres database?

@Adrian Klaver <mailto:adrian.klaver@aklaver.com> what others
permissions do you suggest?

Origin and target are the same version of course. PostgreSQL 9.4.1

Thanks in advance.

------------------------------------------------------------------------
*De:* Adrian Klaver <adrian.klaver@aklaver.com>
*Enviado:* sï¿½bado, 19 de noviembre de 2016 18:24
*Para:* Fran ...; pgsql-general@postgresql.org
*Asunto:* Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 07:21 AM, Fran ... wrote:

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.

In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html
PostgreSQL: Documentation: 9.5: GRANT
<https://www.postgresql.org/docs/9.5/static/sql-grant.html>
www.postgresql.org
GRANT on Database Objects. This variant of the GRANT command gives
specific privileges on a database object to one or more roles. These
privileges are added to those ...

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Fran ...

Bryan691@hotmail.com

over 9 years ago

In reply to: Adrian Klaver (#8)

Re: Database migration to RDS issues permissions

Hi,

You were right and I have tried to grant that role to user and I get following errors..

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: relation "public.worker_status" does not exist
Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT worker_id_refs_id_6fd8ce95;

pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046 INDEX id_e owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: index "id_e" does not exist
Command was: DROP INDEX public.id_e;

I run the dump from origin with postgres user.

________________________________
De: Adrian Klaver <adrian.klaver@aklaver.com>
Enviado: sábado, 19 de noviembre de 2016 18:41
Para: Fran ...; pgsql-general@postgresql.org; tgl@sss.pgh.pa.us
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 09:33 AM, Fran ... wrote:

Hi,

I run "pg_dumpall" command and there are the permissions por the user:

/CREATE ROLE dlapuser;/
/ALTER ROLE dlapuser WITH *SUPERUSER* INHERIT NOCREATEROLE NOCREATEDB
LOGIN NOREPLICATION PASSWORD 'md5XXXXXXXXXXXXXXXXXXXXXXafac';/

I think I would solve the problem granting "superuser" permission but
this is not possible in RDS.

I don't use RDS, but from what I gather the above is not strictly true:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html
Importing Data into PostgreSQL on Amazon RDS - Amazon ...<http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html>
docs.aws.amazon.com
If you have an existing PostgreSQL deployment that you want to move to Amazon RDS, the complexity of your task depends on the size of your database and the ...

I also saw the first errors and they are weird.

Indications that you are not running the restore as a user with
sufficient privileges.

Is the database you are dumping from an RDS instance or a regular
Postgres database?

@Adrian Klaver <mailto:adrian.klaver@aklaver.com> what others
permissions do you suggest?

Origin and target are the same version of course. PostgreSQL 9.4.1

Thanks in advance.

------------------------------------------------------------------------
*De:* Adrian Klaver <adrian.klaver@aklaver.com>
*Enviado:* sábado, 19 de noviembre de 2016 18:24
*Para:* Fran ...; pgsql-general@postgresql.org
*Asunto:* Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 07:21 AM, Fran ... wrote:

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.

In addition to what Tom said:

create database database;
grant all privileges on database to ownerdatabase;

is probably not doing what you think it is or want.

A GRANT on a database only grants connect privileges and the ability to
create schemas in the database. It does not allow creating of objects
within the schema. For more details see:

https://www.postgresql.org/docs/9.5/static/sql-grant.html
PostgreSQL: Documentation: 9.5: GRANT
<https://www.postgresql.org/docs/9.5/static/sql-grant.html>
www.postgresql.org<http://www.postgresql.org>
GRANT on Database Objects. This variant of the GRANT command gives
specific privileges on a database object to one or more roles. These
privileges are added to those ...

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

#10

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#9)

Re: Database migration to RDS issues permissions

On 11/19/2016 11:12 AM, Fran ... wrote:

Hi,

You were right and I have tried to grant that role to user and I get
following errors..

GRANT what role to what user?

/pg_restore: [archiver (db)] Error while PROCESSING TOC:/
/pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK
CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.worker_status" does not exist/
/ Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT
worker_id_refs_id_6fd8ce95;/

/pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046
INDEX id_e owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: index
"id_e" does not exist/
/ Command was: DROP INDEX public.id_e;/

I run the dump from origin with postgres user.

Pretty sure the issue is less where it is coming from then where it is going.
RDS has constraints on what a user can do. As I said before I do not use it,
so I cannot be of much help other then to point you at the docs:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts

Using the rds_superuser Role

Seems to be you need to use the above role to do your restore.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#11

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#9)

Re: Database migration to RDS issues permissions

On 11/19/2016 11:12 AM, Fran ... wrote:

Hi,

You were right and I have tried to grant that role to user and I get
following errors..

/pg_restore: [archiver (db)] Error while PROCESSING TOC:/
/pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK
CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.worker_status" does not exist/
/ Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT
worker_id_refs_id_6fd8ce95;/

/pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046
INDEX id_e owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: index
"id_e" does not exist/
/ Command was: DROP INDEX public.id_e;/

I run the dump from origin with postgres user.

Alright, I bit the bullet and set up a test Postgres RDS instance. The only way I
could get anything to load was to follow the instructions here:

http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds

and that was only after finding a database that did not have plpythonu
installed as that is uninstallable.

So I ended up with:

pg_restore -C -d test -h testdb.xxxxxxxxxxx.rds.amazonaws.com -p 5432 -U rds_user --no-owner --no-privileges b_app.out

I think I will stick with my policy of not using RDS.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#12

Fran ...

Bryan691@hotmail.com

over 9 years ago

In reply to: Adrian Klaver (#11)

Re: Database migration to RDS issues permissions

Hi Adrian,

I followed you link and I had again errors:

pg_restore: [archiver (db)] Error from TOC entry 4368; 2606 151317 FK CONSTRAINT type_id_3940becf ownersuser
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "type_id_3940becf" of relation "store" does not exist
Command was: ALTER TABLE ONLY public.store DROP CONSTRAINT type_id_3940becf;

pg_restore: [archiver (db)] Error from TOC entry 4273; 1259 1179680 INDEX profile_id owneruser
pg_restore: [archiver (db)] could not execute query: ERROR: index "profile_id" does not exist
Command was: DROP INDEX public.profile_id;

pg_restore: [archiver (db)] Error from TOC entry 4751; 0 0 COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql
Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

pg_restore: [archiver (db)] Error from TOC entry 4756; 0 0 USER MAPPING USER MAPPING dwhuser SERVER pg_rest postgres
pg_restore: [archiver (db)] could not execute query: ERROR: role "user" does not exist
Command was: CREATE USER MAPPING FOR user SERVER pg_rest OPTIONS (
password 'XXXXX',
"user" 'user'
);

Regards.

________________________________
De: Adrian Klaver <adrian.klaver@aklaver.com>
Enviado: sábado, 19 de noviembre de 2016 23:49
Para: Fran ...; pgsql-general@postgresql.org; tgl@sss.pgh.pa.us
Asunto: Re: [GENERAL] Database migration to RDS issues permissions

On 11/19/2016 11:12 AM, Fran ... wrote:

Hi,

You were right and I have tried to grant that role to user and I get
following errors..

/pg_restore: [archiver (db)] Error while PROCESSING TOC:/
/pg_restore: [archiver (db)] Error from TOC entry 4335; 2606 151422 FK
CONSTRAINT worker_id_refs_id_6fd8ce95 owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: relation
"public.worker_status" does not exist/
/ Command was: ALTER TABLE ONLY public.worker_status DROP CONSTRAINT
worker_id_refs_id_6fd8ce95;/

/pg_restore: [archiver (db)] Error from TOC entry 4079; 1259 151046
INDEX id_e owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: index
"id_e" does not exist/
/ Command was: DROP INDEX public.id_e;/

I run the dump from origin with postgres user.

Alright, I bit the bullet and set up a test Postgres RDS instance. The only way I
could get anything to load was to follow the instructions here:

http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds
[http://cdn.sstatic.net/Sites/dba/img/apple-touch-icon@2.png?v=246e2cb2439c&a]<http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds>

Moving a Postgres database from standalone local DB to ...<http://dba.stackexchange.com/questions/66372/moving-a-postgres-database-from-standalone-local-db-to-amazon-rds>
dba.stackexchange.com
I have a working Ruby on Rails 4 app running locally on my Mac. I'm running Postgres locally. I want to move the database (and only the database, not the whole app ...

and that was only after finding a database that did not have plpythonu
installed as that is uninstallable.

So I ended up with:

pg_restore -C -d test -h testdb.xxxxxxxxxxx.rds.amazonaws.com -p 5432 -U rds_user --no-owner --no-privileges b_app.out

I think I will stick with my policy of not using RDS.

--
Adrian Klaver
adrian.klaver@aklaver.com

#13

Adrian Klaver

adrian.klaver@aklaver.com

over 9 years ago

In reply to: Fran ... (#12)

Re: Database migration to RDS issues permissions

On 11/21/2016 03:34 PM, Fran ... wrote:

Hi Adrian,

I followed you link and I had again errors:

What was the command you used?

/pg_restore: [archiver (db)] Error from TOC entry 4368; 2606 151317 FK
CONSTRAINT type_id_3940becf ownersuser/
/pg_restore: [archiver (db)] could not execute query: ERROR: constraint
"type_id_3940becf" of relation "store" does not exist/
/ Command was: ALTER TABLE ONLY public.store DROP CONSTRAINT
type_id_3940becf;/

Can't DROP what does not exist. The end result is the same anyway. You
can avoid this type of error with --if-exists.

/
/
/pg_restore: [archiver (db)] Error from TOC entry 4273; 1259 1179680
INDEX profile_id owneruser/
/pg_restore: [archiver (db)] could not execute query: ERROR: index
"profile_id" does not exist/
/ Command was: DROP INDEX public.profile_id;/

See above.

/
/
/pg_restore: [archiver (db)] Error from TOC entry 4751; 0 0 COMMENT
EXTENSION plpgsql /
/pg_restore: [archiver (db)] could not execute query: ERROR: must be
owner of extension plpgsql/
/ Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural
language';/

Not adding a COMMENT, not necessarily fatal. Best guess plpgsql is
actually installed, have you checked?

/
/

/pg_restore: [archiver (db)] Error from TOC entry 4756; 0 0 USER MAPPING
USER MAPPING dwhuser SERVER pg_rest postgres/
/pg_restore: [archiver (db)] could not execute query: ERROR: role
"user" does not exist/
/ Command was: CREATE USER MAPPING FOR user SERVER pg_rest OPTIONS (/
/ password 'XXXXX',/
/ "user" 'user'/
/);/

This is probably because you could not import the global roles from your
original database.

Regards.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general