How to migrate database from 10.1 to 9.1

Started by Andrusalmost 8 years ago2 messagesgeneral
Jump to latest
#1Andrus
kobruleht2@hot.ee

Database is created in

"PostgreSQL 10.1 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built
by MSYS2 project) 4.9.2, 64-bit"

and contains tables with autogenerated primary keys like

CREATE TABLE public.logifail
(
id integer NOT NULL DEFAULT nextval('logifail_id_seq'::regclass),
..
CONSTRAINT logifail_pkey PRIMARY KEY (id),
CONSTRAINT logifail_id_check CHECK (id > 0)
)
WITH (
OIDS=FALSE
);

if this database is restored to Postgres 9.1 from custom backup using

pg_restore --role=$1_owner --no-owner --dbname=$1 --jobs=4 --verbose --username=postgres

automatic primary key generation stops working.

id column is restored without sequnece. Table becomes:

CREATE TABLE logifail
(
id integer NOT NULL,
...
CONSTRAINT logifail_pkey PRIMARY KEY (id),
CONSTRAINT logifail_id_check CHECK (id > 0)
)
WITH (
OIDS=FALSE
);

How to fix this so that sequences are also restored ?
I tried to restore using both 10.1 and 9.1 pg_restore but autogenerated
primary key columns are still lost.

Posted also in

https://stackoverflow.com/questions/50317935/how-to-ove-database-from-postgres-10-1-to-9-1

Andrus.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Andrus (#1)
Re: How to migrate database from 10.1 to 9.1

"Andrus" <kobruleht2@hot.ee> writes:

Database is created in
"PostgreSQL 10.1 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5, Built
by MSYS2 project) 4.9.2, 64-bit"
and contains tables with autogenerated primary keys like
...
if this database is restored to Postgres 9.1 from custom backup using
...
automatic primary key generation stops working.

This is a bit unfortunate, because it's not obvious to the naked eye that
a SERIAL column (I assume you actually defined the column as SERIAL?) is
using any new-in-v10 syntax. But it is. If you'd looked at the error
output you'd have noticed that the CREATE SEQUENCE command is getting a
syntax error:

psql:testv10.dump:58: ERROR: syntax error at or near "AS"
LINE 2: AS integer
^

You'd need to edit the dump output to remove the "AS integer" clause
in order to get this dump to load into a pre-v10 server.

Alternatively, I believe creating the column as BIGSERIAL would result in
dump files that would reload into older servers without adjustments.

regards, tom lane