Postgresql – Error when restoring Postgres Database on RDS

amazon-rdsawspostgresql

I am following this to restore an externally backed up Postgres DB onto my RDS instance – https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

However, when I try to restore I get the following errors –

CREATE EXTENSION
psql:database.sql:2615: ERROR:  must be owner of extension plpgsql
CREATE EXTENSION
psql:database.sql:2629: ERROR:  must be owner of extension pg_trgm

I believe this has to do with assigning superuser privileges to the user. However, I know this is not possible to do in RDS. How can I workaround this?

Best Answer

These errors are harmless and can be ignored.

They stem from COMMENT ON EXTENSION statements in the dump.

OPTION #1 : Change old_password Protocol for EC2

Stop replication to the RDS Slave
As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0;
Start replication to the RDS Slave

OPTION #2 : Lower the Security for EC2

Stop replication to the RDS Slave
As root@localhost run this : SET GLOBAL secure_auth = OFF;
Start replication to the RDS Slave

For more information, please read the old_passwords and secure_auth MySQL Documentation.

Give it a Try !!!

UPDATE 2017-07-20 16:32 EDT

Neither of these methods work for MySQL 5.7 as both options are deprecated.

Mysql – RDS Replication Error (Apply Error 1406 / Truncation)

Get 5.6.3 (or later). As I understand it, these need to be applied to the Replica:

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- or COMPRESSED

That should allow you to keep 255 (instead of 191), thereby avoiding the truncation.