CREATE TABLE IF NOT EXISTS bpd.object
(
id bigint NOT NULL DEFAULT
nextval('bpd.object_general_id_seq'::regclass),
id_class bigint NOT NULL,
id_position bigint NOT NULL DEFAULT '-1'::integer,
bquantity numeric NOT NULL,
id_position_root bigint NOT NULL,
id_conception bigint NOT NULL,
barcode_unit bigint NOT NULL DEFAULT 0,
id_unit_conversion_rule integer NOT NULL,
"timestamp" timestamp without time zone NOT NULL DEFAULT
LOCALTIMESTAMP,
on_freeze boolean NOT NULL DEFAULT false,
timestamp_class timestamp without time zone NOT NULL DEFAULT
LOCALTIMESTAMP(3),
name character varying(255) COLLATE pg_catalog."default" NOT NULL,
id_class_root bigint NOT NULL,
id_group bigint NOT NULL,
id_group_root bigint NOT NULL,
id_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
"desc" character varying(2044) COLLATE pg_catalog."default" NOT
NULL DEFAULT 'н/д'::character varying,
id_class_prop_object_carrier bigint NOT NULL DEFAULT '-1'::integer,
id_pos_temp_prop bigint NOT NULL DEFAULT '-1'::integer,
is_inside boolean GENERATED ALWAYS AS (((id_object_carrier > 0) OR
(id_pos_temp_prop > 0))) STORED,
mc numeric NOT NULL DEFAULT 0,
CONSTRAINT object_pkey PRIMARY KEY (id),
CONSTRAINT unique_id_object_id_object_prop UNIQUE (id,
id_class_prop_object_carrier),
CONSTRAINT lnk_class_snapshot_object FOREIGN KEY (id_class,
timestamp_class)
REFERENCES bpd.class_snapshot (id, "timestamp") MATCH FULL
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lnk_conception_object FOREIGN KEY (id_conception)
REFERENCES bpd.conception (id) MATCH FULL
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT lnk_position_object FOREIGN KEY (id_position)
REFERENCES bpd."position" (id) MATCH FULL
ON UPDATE CASCADE
ON DELETE CASCADE,
CONSTRAINT check_self_integration CHECK (id <> id_object_carrier)
)
TABLESPACE pg_default;
CREATE TABLE IF NOT EXISTS bpd.plan_calendar
(
id bigint NOT NULL DEFAULT
nextval('bpd.work_calendar_id_seq'::regclass),
work_date date NOT NULL,
work_year integer NOT NULL GENERATED ALWAYS AS
(date_part('year'::text, work_date)) STORED,
work_month integer NOT NULL GENERATED ALWAYS AS
(date_part('month'::text, work_date)) STORED,
work_month_day integer NOT NULL GENERATED ALWAYS AS
(date_part('day'::text, work_date)) STORED,
day_type bpd.day_type NOT NULL DEFAULT 'working'::bpd.day_type,
work_year_day integer GENERATED ALWAYS AS (date_part('doy'::text,
work_date)) STORED,
week40_day numeric,
week40_month numeric,
week39_day numeric,
week39_month numeric,
week36_day numeric,
week36_month numeric,
week35_day numeric,
week35_month numeric,
week33_day numeric,
week33_month numeric,
week30_day numeric,
week30_month numeric,
week24_day numeric,
week24_month numeric,
week20_day numeric,
week20_month numeric,
week18_day numeric,
week18_month numeric,
range_night_part1 tsrange GENERATED ALWAYS AS
(tsrange((work_date)::timestamp without time zone, (work_date +
'06:00:00'::interval), '[]'::text)) STORED,
range_night_part2 tsrange GENERATED ALWAYS AS (tsrange((work_date +
'22:00:00'::interval), (work_date + '24:00:00'::interval), '[)'::text))
STORED,
work_week_day integer GENERATED ALWAYS AS
(date_part('isodow'::text, work_date)) STORED,
work_week_day_name character varying COLLATE pg_catalog."default"
GENERATED ALWAYS AS (
CASE date_part('isodow'::text, work_date)
WHEN 1 THEN 'ПН'::character varying
WHEN 2 THEN 'ВТ'::character varying
WHEN 3 THEN 'СР'::character varying
WHEN 4 THEN 'ЧТ'::character varying
WHEN 5 THEN 'ПН'::character varying
WHEN 6 THEN 'СБ'::character varying
WHEN 7 THEN 'ВС'::character varying
ELSE 'ПН'::character varying
END) STORED,
work_week_day_name_full character varying COLLATE
pg_catalog."default" GENERATED ALWAYS AS (
CASE date_part('isodow'::text, work_date)
WHEN 1 THEN 'Понедельник'::character varying
WHEN 2 THEN 'Вторник'::character varying
WHEN 3 THEN 'Среда'::character varying
WHEN 4 THEN 'Четверг'::character varying
WHEN 5 THEN 'Пятница'::character varying
WHEN 6 THEN 'Суббота'::character varying
WHEN 7 THEN 'Воскресенье'::character varying
ELSE 'Понедельник'::character varying
END) STORED,
work_year_week integer GENERATED ALWAYS AS (date_part('week'::text,
work_date)) STORED,
week40_week numeric,
week39_week numeric,
week36_week numeric,
week35_week numeric,
week33_week numeric,
week30_week numeric,
week24_week numeric,
week20_week numeric,
week18_week numeric,
CONSTRAINT plan_calendar_pkey PRIMARY KEY (id),
CONSTRAINT unique_plan_calendar UNIQUE (work_date)
)
TABLESPACE pg_default;
вс, 21 нояб. 2021 г. в 06:38, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>>:
On 11/20/21 17:11, Дмитрий Иванов wrote:
> Yes and yes.
> I ended up using the pg_dump of the receiving server.
>
> sudo /usr/lib/postgresql/14/bin/pg_dump --file
> "/home/dismay/uchet/Uchet.backup" --host "server" --port "5999"
> --username "back" --no-password --verbose --format=c
> --quote-all-identifiers --blobs --column-inserts --inserts --create
> --disable-triggers --encoding="UTF8" "Uchet"
>
> sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
> "5432" --username "back" --no-password --dbname "Uchet"
> --disable-triggers --format=c --create --verbose
> "/home/dismay/uchet/Uchet.backup"
>
> sudo /usr/lib/postgresql/14/bin/pg_restore --host "127.0.0.1" --port
> "5432" --username "back" --no-password --dbname "Uchet"
> --disable-triggers --table="bpd.object" --format=c --verbose
> "/home/dismay/uchet/Uchet.backup"
>
> Receiving server:
> PostgreSQL 14.1 (Debian 14.1-1.pgdg110+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
>
> Server source:
> PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit
> EDB assembly installed from "Application Stack Builder"
>
> вс, 21 нояб. 2021 г. в 00:06, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
Hmm. I cannot replicate, though in my case both servers(12.9, 14.1) are
one same Linux machine.
What is the history of the database in the 12.0 instance?
Was it upgraded from another instance?
If so dump/restore or pg_upgrade?
Is it a promoted replica?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
