How to restore postgres 12 generated sql file into postgres 9.6 database

Question

I am trying to restore the database. The database sql file is about 4.5 GB so I couldn't edit it on editor. I dump the database using following command in postgres 12;

pg_dump -d postgres > backup.sql

But I need the same database in postgres 9.6. For this purpose, I write the following code to restore it

psql -d postgres < backup.sql

It shows the error like this (Error in creating sequence). But this is not the duplicated one with given question. error message;

SET
ERROR:  unrecognized configuration parameter "default_table_access_method"
CREATE TABLE
ALTER TABLE
ERROR:  syntax error at or near "AS"
LINE 2:     AS integer
            ^
ERROR:  relation "epicenter.epicenter_gid_seq" does not exist
ERROR:  relation "epicenter.epicenter_gid_seq" does not exist
CREATE TABLE
ALTER TABLE
ERROR:  syntax error at or near "AS"
LINE 2:     AS integer
            ^
ERROR:  relation "public.725_4.5_tur_gid_seq" does not exist
ERROR:  relation "public.725_4.5_tur_gid_seq" does not exist
CREATE TABLE
ALTER TABLE

I saw the answer of this question. The answer already said the sql file will not work on older version. But I want to know, Is there any way to restore using this sql file?

Please show your own error message. It is hard to figure out what is going on in your database based on someone else's error message. This is possible but it might take a bit of wok. Is backup.sql small enough to be edited in a text editor? — jjanes, Dec 15 '19 at 19:58
Does this answer your question? [Error in creating SEQUENCEs when restoring the PostgreSQL database](https://stackoverflow.com/questions/49208448/error-in-creating-sequences-when-restoring-the-postgresql-database) — JGH, Dec 15 '19 at 20:55
I updated my question. My database having 4.5 GB size so I can't edit on text editor. — Tek Kshetri, Dec 16 '19 at 02:57

score 3 · Accepted Answer · answered Dec 16 '19 at 04:00

Thank you @jjanes and @JGH for your kind cooperation. I found the one solution. First I backup the database using following command;

pg_dump -U postgres -h localhost -p 5432 -W earthquake | gzip -c > backup.gz

Then I create earthquake database manually from the pgadmin 4. After getting the backup.gz file, I restore it using following command in terminal;

gzip -d -c backup.gz | sed -e '/AS integer/d' | psql -U postgres -h localhost -p 5432 -W earthquake

How to restore postgres 12 generated sql file into postgres 9.6 database

1 Answers1