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_dump
and 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
Update
These errors were also encountered on this SO answer: https://stackoverflow.com/a/22247401/1884158
UPDATE
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…
Best Answer
I managed to resolve these errors by doing 3 things:
1. Removing all definitions that are already defined in the PostGIS extension from the database dump
All of the function, operator family, and operator class definitions are already included in the new database because I installed PostGIS on the new database. This is also true of the
spatial_ref_sys
table in thepublic
schema. So I avoided restoring this in my database dump using the following approach:Create a table of contents list from my dump file with the following command:
pg_restore -l -f out.txt backup.dump
Then I edited that
out.txt
to remove the function definitions. After that file was edited, I imported my dump using the updatedout.txt
as follows:I found out about this approach here: https://stackoverflow.com/questions/9715434/how-to-exclude-pl-pgsql-functions-in-export/31043192#31043192
2. All of my database's tables were created with the
postgres
admin user, so I migrated them to a regular users.First I created my new user, then update the owner of my database to the new user:
Then I had to migrate all of my tables, views, and sequences to have the new user.
I found out about these scripts here:
https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql https://stackoverflow.com/questions/16959764/reassign-owned-by-for-1-specified-database?noredirect=1&lq=1
3. I had to avoid restoring user privileges:
I was getting error calling REVOKE commands on the existing
postgres
user. So I added the-x
flag to mypg_restore
command to prevent restoring privileges to the admin user.Details were outlined here:
https://stackoverflow.com/questions/37271402/pg-restore-error-role-xxx-does-not-exist