1

My postgres database has a column called "id" that runs from 40,000,000 to about 50,000,000. The "id" column is the primary key. I need to change the "id" column values such that they span different numbers in order to merge this database with another.

How can I go about generating code to change the values from 40,000,000 - 50,000,000 to, say, 0 - 10,000,000?

The table definition is

CREATE TABLE public.keyvaluehistory (
   id bigint NOT NULL
      DEFAULT nextval('keyvaluehistory_id_seq'::regclass),
   segkey text NOT NULL,
   dvalue double precision,
   bvalue bytea,
   tstamp timestamp with time zone,
   CONSTRAINT keyvaluehistory_pkey PRIMARY KEY (id)
);

There are no foreign keys on the table.

I can afford downtime on the order of minutes/hours.

  • 2
    Subtract 40,000,000? – Vérace Oct 7 at 1:16
1

I would add another column temporarily.

The first part can run while the database is active:

ALTER TABLE keyvaluehistory ADD new_id bigint;

CREATE SEQUENCE keyvaluehistory_new_id_seq OWNED BY keyvaluehistory.new_id;

/* update in batches to avoid table bloat */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 40000000 AND 40999999;

VACUUM keyvaluehistory;

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE id BETWEEN 41000000 AND 41999999;

VACUUM keyvaluehistory;

...

SET maintenance_work_mem = '1GB';

CREATE UNIQUE INDEX CONCURRENTLY keyvaluehistory_new_pkey (new_id);

The following part locks the table and requires down time.

The most time consuming part is adding the primary key, because that requires scanning the table.

/* downtime starts here */

BEGIN;

LOCK TABLE keyvaluehistory IN ACCESS EXCLUSIVE MODE;

/* catch up */

UPDATE keyvaluehistory SET new_id = id - 39999999
   WHERE new_id IS NULL;

ALTER TABLE keyvaluehistory
   DROP CONSTRAINT keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory
   DROP COLUMN id;

ALTER TABLE keyvaluehistory
   ADD CONSTRAINT keyvaluehistory_pkey USING keyvaluehistory_new_pkey;

ALTER INDEX keyvaluehistory_new_pkey RENAME TO keyvaluehistory_pkey;

ALTER TABLE keyvaluehistory RENAME new_id TO id;

ALTER SEQUENCE keyvaluehistory_new_id_seq RENAME TO keyvaluehistory_id_seq;

SELECT setval('keyvaluehistory_new_id_seq', 10000001);

COMMIT;

Please test befor running it in production; I may have forgotten something.

  • This worked! There was some language/variable mismatch with the indexes and constraints but I managed to work it out. Thanks a lot! – Jake Lyle Oct 8 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.