From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | "pgsql-general" <pgsql-general(at)postgresql(dot)org> |
Subject: | How to migrate database from 10.1 to 9.1 |
Date: | 2018-05-13 15:54:37 |
Message-ID: | 0B65DCA2835A4244B432B4AA48B6BDEE@dell2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-05-13 19:03:40 | Re: How to migrate database from 10.1 to 9.1 |
Previous Message | Michael Paquier | 2018-05-13 11:45:24 | Re: recovery_target_time and WAL fetch with streaming replication |