0

I try to migrate database from old PostgreSQL (9.3) to new PostgreSQL(9.5, 9.6) by Bucardo.

Bucardo used Primary key for migrate.

I have many databases with public.databasechangelog without Primary key. How correct add primary key to databasechangelog(PostgreSQL) for Liquibase?

Update answer: I can add Primary Key by SQL, but may be Liquibase have setting for added Primary key in XML?

Safely added Primary Key by SQL to public.databasechangelog ?

Benjamin
  • 234
  • 1
  • 10
Anton Patsev
  • 605
  • 2
  • 13
  • 27
  • https://www.postgresql.org/docs/current/static/sql-altertable.html – Vao Tsun May 15 '17 at 08:37
  • Liquibase [intentionally not using a primary key](https://liquibase.jira.com/browse/CORE-1909) for that table. But the edge case (which is mentioned in the bug) does not seem to apply to PostgreSQL (as long as you don't have **really** large migration IDs -- i.e. above 1GB). – pozs May 15 '17 at 08:37
  • 1
    @pozs: `id` is limited to 255 characters. The total max. size of those three columns is 765 characters - something a modern DBMS should be able to index. The non-existence of the PK is indeed caused by the limitations of some DBMS: http://forum.liquibase.org/topic/why-does-databasechangelog-not-have-a-primary-key (older versions of Liquibase did in fact have a primary key on that table) –  May 15 '17 at 08:40
  • @a_horse_with_no_name ah, I see. Then this truly never going to cause any problems with PostgreSQL. – pozs May 15 '17 at 08:42
  • But why would you want to use a 255 varchar column as a primary key? And having a composite key of *three* of these babies is very close to insanity, IMHO. – joop May 15 '17 at 08:54
  • @joop: why would that be "insanity" if the requirement is that the combination of those three values is unique? –  May 15 '17 at 09:47
  • Because these three columns appear to be FKs to *domain-tables*. They could be replaced by surrogate-keys (serials), which will take less space (and time) – joop May 15 '17 at 10:03
  • @joop: they are not foreign keys to anything. They uniquely identify a single changeSet in Liquibase and each changeSet is a single row in that table. It makes perfect sense to define them as unique in that table. –  May 15 '17 at 16:40

2 Answers2

1

The Liquibase developers decided to remove the primary key on that table because apparently some DBMS have a problem with creating an index on those three columns due to limits on the maximum size of an index entry.

See e.g. this discussion: http://forum.liquibase.org/topic/why-does-databasechangelog-not-have-a-primary-key

Postgres is not subject to those limits, so you should be fine just adding one: 

alter table databasechangelog add primary key (id, author, filename);
1

I create DATABASECHANGELOG table at the beginning with primary key. This may not the correct way, but it will help

CREATE TABLE "DATABASECHANGELOG" (
  "ID" varchar(255) NOT NULL,
  "AUTHOR" varchar(255) NOT NULL,
  "FILENAME" varchar(255) NOT NULL,
  "DATEEXECUTED" datetime NOT NULL,
  "ORDEREXECUTED" varchar(10) NOT NULL,
  "EXECTYPE" varchar(45) NOT NULL,
  "MD5SUM" varchar(35) DEFAULT NULL,
  "DESCRIPTION" varchar(255) DEFAULT NULL,
  "COMMENTS" varchar(255) DEFAULT NULL,
  "TAG" varchar(255) DEFAULT NULL,
  "LIQUIBASE" varchar(20) DEFAULT NULL,
  "CONTEXTS" varchar(255) DEFAULT NULL,
  "LABELS" varchar(255) DEFAULT NULL,
  "DEPLOYMENT_ID" varchar(10) DEFAULT NULL,
  PRIMARY KEY ("ID")
)
mgms_kumara
  • 49
  • 6
  • 1
    The `ID` is **not** the primary key. The primary (unique) key of that table consists of `id`, `author` **and** `filename` –  Feb 14 '21 at 14:12