Can you import a Postgresql 9.5.10 db into a Postgresql 10.9 server?

Question

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)';

Answer 1

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

• Francesco Fazio: Thank you so much Laurenz ! :) I will use pg_dump 10 to dump the 9.5. Thanks a lot again !