I would like to know if it is safe to import a dump made from a database running on a PostgreSQL server 9.5.10 to a PostgreSQL server 10.9.
Basically I am able to do the import. It does not fail. But how can I be sure nothing in the schema has been lost somehow ? We are talking Production here.
What I have done:
I have dumped the db from the current RDS server (9.5):
/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxx --no-password -Fc xxxxxxx -f psql-9-data.dmp
Using pg_restore version 9.5 I have created the restore file:
pg_restore -l xxxxxxxxxxx | sed '/COMMENT - EXTENSION/s/^/; (filtered out) /' > db_restore
I have dropped the database at the new RDS server (this worked using psql version 9.5 on a psql server version 10):
psql -X -h server-psql-10.amazonaws.com -U xxxxxxxxxxxx -d xxxxxxxxxxx -f drop_all.sql
Using pg_restore version 10 I have restored the database using the dump of the 9.5 db:
pg_restore -h server-psql-10.amazonaws.com -U xxxxxxxxxxx -d xxxxxxxxxxxxx --no-password --no-owner --no-privileges -L db_restore psql-9-data.dmp
Using pg_dump version 10 I have dumped the schema of the 10 db:
pg_dump -h server-psql-10.amazonaws.com -U xxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-10-schema.dmp
Using pg_dump version 9 I have dumped the schema of the 9.5 db:
/usr/lib/postgresql/9.5/bin/pg_dump -h server-psql-9.5.amazonaws.com -U xxxxxxxxxxxxx --no-password -Fp xxxxxxxxxxx -s -f psql-9-schema.dmp
Then I used diff to compare the two schema dumps and it shows there is a lot of difference between the schema of 9.5 and 10. That is why I don't feel safe AT ALL doing this. Why arent the schemas identical ?
If I eliminate the step of creating a restore file and I import the dump directly (as suggested in the comments) I get this error:
pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 3613; 0 0 COMMENT EXTENSION plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; pg_restore: [archiver (db)] Error from TOC entry 3614; 0 0 COMMENT EXTENSION citext pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension citext Command was: COMMENT ON EXTENSION citext IS 'data type for case-insensitive character strings'; pg_restore: [archiver (db)] Error from TOC entry 3615; 0 0 COMMENT EXTENSION "uuid-ossp" pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension uuid-ossp Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';