Segmentation fault on downgraded pg_restore (Postgresql 10 -> Postgresql 9.5)

Question

On a server with Postgresql 10 did a dump and put it to rsync.net

pg_dump -Z1 -Fc <db_name> | ssh <user>@s<server> "dd of=dump"

On the other server with Postgresql 9.5 downgraded created dump and tried to restore database. Steps were

cat dump | sed '/AS integer/d' > downgraded_dump.sql

pg_restore -j 2 -d <db> downgraded_dump.sql

On pg_restore failed with Segmentation fault.

I have no idea what could be wrong.

Answer 1

If you need to downgrade PostgreSQL, your only hope is a "plain format" dump (pg_dump -F p). Other dump formats keep track of the archive version, and will refuse to restore the dump if the version changed between the releases.

You can try to restore the plain format dump into the older PostgreSQL version, but the first attempt will probably fail. Then you have to edit the SQL script and modify the syntax so that it can be restored into the older PostgreSQL version.

It is a tedious procedure, but then downgrade is not supported in PostgreSQL.

Answer 2

You would want to convert the binary dump file to a plain text file, then apply sed to that, and send that output to psql.

pg_restore dump | sed '/AS integer/d' | psql -d db_name

This should succeed as long as the v10 database uses no features which are not backwards compatible with v9.5 other than the change in sequences you are already trying to account for. There is also a chance some of your text data fields will contain the string 'AS integer' and so get mangled by the sed. It is not feasible to use the -j option in this case.

This assumes you are using pg_restore from 9.5.