Errors encountered while Dump/Restoring PostGIS DB from Postgres on EC2 into RDS

Question

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...

Answer 1

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 the public 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 updated out.txt as follows:

pg_restore -v -L out.txt -x -U my-user -d my_db -e --single-transaction ms.pgis-dump

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:

create role my_user with password 'testuser' login;
grant postgres to my_user;
ALTER DATABASE my_db OWNER TO my_user;

Then I had to migrate all of my tables, views, and sequences to have the new user.

# Migrate tables:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select tablename from pg_tables where schemaname = 'public';" my_db` ; do  psql -U my_user -h localhost -p 5432 -c "alter table \"$tbl\" owner to my_user" -d my_db ; done
# views:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user"; done
# sequences:
for tbl in `psql -qAt -U my_user -h localhost -p 5432 -d my_db -c "select table_name from information_schema.views where table_schema = 'public';"` ; do  psql -U my_user -h localhost -p 5432 -d my_db -c "alter table \"$tbl\" owner to my_user" ; done

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 my pg_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