PostgreSQL source database
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
- Index creation on partitions fail for some YugabyteDB builds
- Creation of certain views in the rule.sql file
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 2.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;