PostgreSQL source database

What to watch out for when migrating data from PostgreSQL

Review limitations and implement suggested workarounds to successfully migrate data from PostgreSQL to YugabyteDB.

Contents

Adding primary key to a partitioned table results in an error

GitHub: Issue #612

Description: If you have a partitioned table in which primary key is added later using ALTER TABLE, then the table creation fails with the following error:

ERROR: adding primary key to a partitioned table is not yet implemented (SQLSTATE XX000)

Workaround: Manual intervention needed. Add primary key in the CREATE TABLE statement.

Example

An example schema on the source database is as follows:

CREATE TABLE public.sales_region (
    id integer NOT NULL,
    amount integer,
    branch text,
    region text NOT NULL
)
PARTITION BY LIST (region);

ALTER TABLE ONLY public.sales_region ADD CONSTRAINT sales_region_pkey PRIMARY KEY (id, region);

Suggested change to the schema is as follows:

CREATE TABLE public.sales_region (
    id integer NOT NULL,
    amount integer,
    branch text,
    region text NOT NULL,
    PRIMARY KEY(id, region)
)
PARTITION BY LIST (region);

Index creation on partitions fail for some YugabyteDB builds

GitHub: Issue #14529

Description: If you have a partitioned table with indexes on it, the migration will fail with an error for YugabyteDB 2.15 or 2.16 due to a regression.

Note that this is fixed in release 12.17.1.0.

Workaround: N/A

Example

An example schema on the source database is as follows:

DROP TABLE IF EXISTS list_part;

CREATE TABLE list_part (id INTEGER, status TEXT, arr NUMERIC) PARTITION BY LIST(status);

CREATE TABLE list_active PARTITION OF list_part FOR VALUES IN ('ACTIVE');

CREATE TABLE list_archived PARTITION OF list_part FOR VALUES IN ('EXPIRED');

CREATE TABLE list_others PARTITION OF list_part DEFAULT;

INSERT INTO list_part VALUES (1,'ACTIVE',100), (2,'RECURRING',20), (3,'EXPIRED',38), (4,'REACTIVATED',144), (5,'ACTIVE',50);

CREATE INDEX list_ind ON list_part(status);

Creation of certain views in the rule.sql file

GitHub: Issue #770

Description: There may be few cases where certain exported views come under the rule.sql file and the view.sql file might contain a dummy view definition. This pg_dump behaviour may be due to how PostgreSQL handles views internally (via rules).

Note

This does not affect the migration as YugabyteDB Voyager takes care of the DDL creation sequence internally.

Workaround: Not required

Example

An example schema on the source database is as follows:

CREATE TABLE foo(n1 int PRIMARY KEY, n2 int);
CREATE VIEW v1 AS
  SELECT n1,n2
  FROM foo
  GROUP BY n1;

The exported schema for view.sql is as follows:

CREATE VIEW public.v1 AS
  SELECT
    NULL::integer AS n1,
    NULL::integer AS n2;

The exported schema for rule.sql is as follows:

CREATE OR REPLACE VIEW public.v1 AS
  SELECT foo.n1,foo.n2
  FROM public.foo
  GROUP BY foo.n1;