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?
Advertisement
Answer
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