CODEWITHSUNDEEP
postgresqlpostgresql-9.5pg-dumppg-restore
fueggit
fueggit

Reputation: 1043

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

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.

Upvotes: 0

Views: 338

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 247535

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.

Upvotes: 0

jjanes
jjanes

Reputation: 44343

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.

Upvotes: 0

Related Questions