1

I have a system that only supports version 9.1 of postgres and a client (company) has been managing their data with version 10. There are many tables and I need to know if there is a script to downgrade the database from version 10 to 9.1 . Thanks in advance!

Elermino
  • 11
  • 1
  • 5
  • 3
    Postgres 9.1 is 5+ years after EOL, do not use it. Why does the system only run 9.1? The chances of going from 10 --> 9.1 successfully is slim to none. – Adrian Klaver Apr 22 '22 at 22:39
  • You have no support for version 9.1 at all. Software that can only use this version, has no support either, it’s outdated for at least 5 years. Software that old and without support, should be avoided like the plague – Frank Heikens Apr 23 '22 at 07:32

1 Answers1

3

As other commenters have already stated, PostgreSQL 9.1 is long obsolete, and this is a bad idea in general. However, to answer your question, you would have to dump the data with pg_dump into plain SQL statements using the latest pg_dump utility from PG 10.

pg_dump --column-inserts

which will produce a plain text file that can be restored back to 9.1 using the psql utility from 9.1. It will be super slow and painful, as it should be when you are going in the decidedly wrong direction. A better answer would be to figure out why 9.1 is necessary, as @adrian.klaver mentions.

Yes, @AdrianKlaver is correct. Any new features will break the transfer.

Kirk Roybal
  • 17,273
  • 1
  • 29
  • 38
  • 1
    You would have to hope the 10 instance did not use JSON or Range types or any other new features that where added in the 6 major releases from 9.1 --> 10. – Adrian Klaver Apr 24 '22 at 15:26
  • Postgres v10 syntax has not been used, so I think there would be no problem doing pgDump and restoring. Another thing, when restoring, if it is a plain text file, can it be restored by creating a database and throwing it there? – Elermino Apr 26 '22 at 20:20
  • And unfortunately the system does not support another version since it was built in 2014, that is why this whole process is necessary. – Elermino Apr 26 '22 at 20:23
  • you can always add the PGDG repository to an existing system, and use the latest version. Go here: https://www.postgresql.org/download/ and follow the prompts for your distro. – Kirk Roybal Apr 28 '22 at 15:12