Question 250777 in PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    If you need to downgrade PostgreSQL, your only hope is a "plain format" dump (pg_dump -F p). If you hadn’t mutilated the dump file, pg_restore would have told you that it refuses to restore a dump from a more recent version.

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search