We are moving from our own native postgres installation on an EC2 instance to amazon RDS for postgres. And the data migration is done via dump and restore like this:
pg_dump -U postgres test1_main> test1_main.dump
psql -U postgres -h <rds end point> -p 5432 test1_main < test1_main.dump
On restore, we see failures saying:
CREATE SCHEMA
ALTER SCHEMA
ERROR: must be owner of language plpgsql
ALTER LANGUAGE
SET
CREATE TYPE
ALTER TYPE
ERROR: must be superuser to create a base type
ERROR: permission denied for language c
ALTER FUNCTION
ERROR: permission denied for language c
ALTER FUNCTION
ERROR: must be superuser to create a base type
.....
ERROR: permission denied for language c
ERROR: function pgcrypto.armor(bytea) does not exist
ERROR: permission denied for language c
And so on!
We have added the hstore and pgcryptop extensions to template1 database on our Amazon RDS instance[1] so we have these extensions but we cant create a type because creating a base type in postgres 9.3 needs superuser[2]. postgres user is disallowed to do this and the amazon RDS documentation says:
When you create a DB instance, the master user system account that you create is assigned to the rds_superuser role. The rds_superuser role is similar to the PostgreSQL superuser role (customarily named postgres in local instances) but with some restrictions
rdsadmin is the superuser but I cant log in with rdsadmin.
[email protected]:~> psql -U rdsadmin -h <rds end-point> -p 5432
psql.bin: FATAL: pg_hba.conf rejects connection for host "10.144.xx.xxx", user "rdsadmin", database "rdsadmin", SSL on
FATAL: pg_hba.conf rejects connection for host "10.144.xx.xxx", user "rdsadmin", database "rdsadmin", SSL off
Please help.
[2] http://www.postgresql.org/docs/9.3/static/sql-createtype.html