Transfer database from PostgreSQL 12 to PostgreSQL 9.2.24

Question

I need to transfer db from PostgreSQL 12 to PostgreSQL 9.2.24. I try make backup my db from PostgreSQL 12 and restore on PostgreSQL 9.2.24 but I can't do it because have an error like this:

LINE 1: SELECT pg_catalog.setval('public.schemas_headers_types_id_se...
                                 ^


LINE 2:     ADD CONSTRAINT user_id PRIMARY KEY (id) INCLUDE (id);
                                                    ^
Command was: ALTER TABLE ONLY public.users

...

I can't update db version from PostgreSQL 9.2.24 to PostgreSQL 12 because my hosting provider doesn't allow me to do it. How i can't truly transfer my db from PostgreSQL 12 to PostgreSQL 9.2.24?

How are you doing your dump? Postgresql have different options on pg_dump formats. I believa that you can try the custom format. As follows: postgresql.org/docs/current/app-pgdump.html — William Prigol Lopes
– William Prigol Lopes, Commented Apr 28, 2020 at 17:27

jjanes · Accepted Answer · 2020-04-28 17:35:07Z

1

You would have to edit the dump to remove the INCLUDE (id) (which is a pretty useless use of the INCLUDE feature anyway, to include the same column twice). But who knows how many following on errors you will find?

my hosting provider doesn't allow me to do it

Seems like the real solution then is find a different hosting provider.

answered Apr 28, 2020 at 17:35

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Schwern · Accepted Answer · 2020-04-28 17:38:46Z

pg_dump says...

Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. Use of the --quote-all-identifiers option is recommended in cross-version cases, as it can prevent problems arising from varying reserved-word lists in different PostgreSQL versions.

include is a feature which simply does not exist in Postgres 9.2. These and other features will have to be replaced with alternates, if possible.

9.2.24 reached its end-of-life in November 2017. It's possible you can use this fact to get your provider to upgrade. If not, consider whether you want to use a hosting provider which refuses to update software which is no longer supported.

Collectives™ on Stack Overflow

Transfer database from PostgreSQL 12 to PostgreSQL 9.2.24

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related