CODEWITHSUNDEEP
postgresql
Oleg Ovcharenko
Oleg Ovcharenko

Reputation: 537

Transfer database from PostgreSQL 12 to PostgreSQL 9.2.24

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?

Upvotes: 0

Views: 199

Answers (2)

Schwern
Schwern

Reputation: 165596

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.

Upvotes: 0

jjanes
jjanes

Reputation: 44403

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.

Upvotes: 1

Related Questions