I am migrating from a manually managed database on Postgres 9.4.10 and PostGIS 2.1 to an RDS instance on Postgres 9.4.9 and PostGIS 2.1.
The restore seems successful, but the output gave me 27 ignored errors. Here is the output: http://developers.mapseed.org/posts/restore-output.txt
Should I be concerned? All of my unit tests are passing, but this is a production database, so I want to be sure. They seem to be errors related to permissions, but both my
pg_restore commands were run with the
postgres user. Although perhaps I'm missing something.
step by step of the dump/restore:
My database is named
shareabouts_v2, so on my RDS instance, I ran a
create database shareabouts_v2 and
create extension postgis.
Then I created a dump file from my current database with the following command:
pg_dump -f backup.dump -Fc -p 5432 -U postgres -h localhost shareabouts_v2
and I restored that dump file into my RDS instance with the following command:
pg_restore -v -U postgres -h <my-RDS-host> -p 5432 -d shareabouts_v2 --disable-triggers -e backup.dump
I have followed numerous posts and best practices around dumping and restoring PostGIS data
These errors were also encountered on this SO answer: https://stackoverflow.com/a/22247401/1884158
I was able to "resolve" the errors with the function declarations by removing them from the dump file, as described here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192
where I basically did this:
pg_restore -l -f out.txt backup.dump
# then edit out.txt to delete all of the function definitions and spatial_ref_system:
pg_restore -L out.txt -U my_user -h my_host -p 5432 -d my_db -e --single-transaction backup.dump
But I'm not sure if that will result in other issues. I'll post more updates as I continue...