How to migrate database from 10.1 to 9.1

Поиск
Список
Период
Сортировка
От Andrus
Тема How to migrate database from 10.1 to 9.1
Дата
Msg-id 0B65DCA2835A4244B432B4AA48B6BDEE@dell2
обсуждение исходный текст
Ответы Re: How to migrate database from 10.1 to 9.1  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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. 



В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: recovery_target_time and WAL fetch with streaming replication
Следующее
От: Tom Lane
Дата:
Сообщение: Re: How to migrate database from 10.1 to 9.1