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)';
Best Answer
The error message is harmless, it only means that you imported the dump as a non-superuser and the extensions already existed.
There are potential problems with using a non-superuser (ownership usually cannot be restored), but since you didn't get any such error messages, you are fine.
The only important consideration is that you need to use
pg_dump
from version 10 to dump the 9.5 database, not the 9.5pg_dump
. While v10 knows how to dump a 9.5 database so that it can be loaded into v10 properly, 9.5 lacks that knowledge.