2

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

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.

  • Thank you so much Laurenz ! :) I will use pg_dump 10 to dump the 9.5. Thanks a lot again ! – Francesco Fazio Sep 17 '19 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.