Imagine my surprise when I went to combine my shiny new dbmail database
(with table prefixes, w00h00) with an existing customer db that I had
and ran into a conflict over users_pkey! D0h. It looks like even with
the table prefixes, if you ran a pre RC8 version then you'll have table
names with prefixes, but index names, sequences, etc with the old style
of naming. And of course in Postgres, there is no RENAME INDEX or
RENAME SEQUENCE command.
So I hacked together a bunch of sql commands to hopefully migrate a
Postgres DB to the latest schema we have for RC8 I believe. Hope this
helps someone out there. :) And don't just blindly copy+paste this, my
dbmail db was in a pretty bad state and there were a lot of changes that
had occurred that I hadn't yet caught up too, so I do take some
liberties (like deleting the dbmail_auto_replies/notification tables and
just recreating 'em from scratch). Look 'em over and tailor 'em to your
own needs. Everything is contained within 3 transaction blocks, so if
something majorly fails it's either all or nothing.
Also - I noticed in the latest pgsql createtables that dbmail_pbsp
sequence and index names didn't follow the naming convention of
everything else, so a slight modification there.
After doing all of this on my own dbmail install, I then pg_dump'ed the
database, drop'ed it, and then recreated it and imported the data and
I'm noticing a huge improvement in dbmail speed. I'm guessing this is
partly to do with the Postgres Free Space Map settings and how it took
us a while before we set them to a reasonable level. Ended up halving
our database disk usage and performance gained a noticable improvement.
I'd probably suggest all Postgres users might want to do this either by
waiting until the next major Postgres upgrade (8.0) or dbmail 2.0 ships.
-----
"Any sufficiently advanced bug is indistinguishable
from a feature." -- Rich Kulawiec
[EMAIL PROTECTED]
===
# Rename the Tables
BEGIN;
ALTER TABLE acl RENAME TO dbmail_acl;
ALTER TABLE aliases RENAME TO dbmail_aliases;
ALTER TABLE auto_notifications RENAME TO dbmail_auto_notifications;
ALTER TABLE auto_replies RENAME TO dbmail_auto_replies;
ALTER TABLE mailboxes RENAME TO dbmail_mailboxes;
ALTER TABLE messageblks RENAME TO dbmail_messageblks;
ALTER TABLE messages RENAME TO dbmail_messages;
ALTER TABLE pbsp RENAME TO dbmail_pbsp;
ALTER TABLE physmessage RENAME TO dbmail_physmessage;
ALTER TABLE subscription RENAME TO dbmail_subscription;
ALTER TABLE users RENAME TO dbmail_users;
END;
BEGIN;
LOCK TABLE dbmail_aliases, dbmail_users, dbmail_acl,
dbmail_auto_notifications, dbmail_auto_replies, dbmail_mailboxes,
dbmail_subscription, dbmail_dups, dbmail_messageblks, dbmail_messages,
dbmail_pbsp, dbmail_physmessage;
ALTER TABLE dbmail_aliases DROP CONSTRAINT aliases_pkey;
DROP INDEX aliases_alias_idx;
DROP INDEX aliases_alias_low_idx;
DROP SEQUENCE alias_idnr_seq;
ALTER TABLE dbmail_aliases ADD PRIMARY KEY (alias_idnr);
CREATE INDEX dbmail_aliases_alias_idx ON dbmail_aliases(alias);
CREATE INDEX dbmail_aliases_alias_low_idx ON
dbmail_aliases(lower(alias));
CREATE SEQUENCE dbmail_alias_idnr_seq;
SELECT setval('dbmail_alias_idnr_seq', max(alias_idnr)) FROM
dbmail_aliases;
ALTER TABLE dbmail_aliases ALTER alias_idnr SET DEFAULT
nextval('dbmail_alias_idnr_seq'::text);
ALTER TABLE dbmail_users DROP CONSTRAINT users_pkey CASCADE;
DROP INDEX users_name_idx;
DROP INDEX users_userid_idx;
DROP SEQUENCE user_idnr_seq;
ALTER TABLE dbmail_users ADD PRIMARY KEY (user_idnr);
CREATE UNIQUE INDEX dbmail_users_name_idx ON dbmail_users(userid);
CREATE SEQUENCE dbmail_user_idnr_seq;
SELECT setval('dbmail_user_idnr_seq', max(user_idnr)) FROM dbmail_users;
ALTER TABLE dbmail_users ALTER user_idnr SET DEFAULT
nextval('dbmail_user_idnr_seq'::text);
ALTER TABLE dbmail_acl DROP CONSTRAINT acl_pkey;
ALTER TABLE dbmail_acl ADD PRIMARY KEY (user_id, mailbox_id);
ALTER TABLE dbmail_mailboxes DROP CONSTRAINT mailboxes_pkey CASCADE;
ALTER TABLE dbmail_mailboxes ADD PRIMARY KEY (mailbox_idnr);
DROP INDEX mailboxes_name_idx;
DROP INDEX mailboxes_owner_idx;
DROP INDEX mailboxes_owner_name_idx;
CREATE INDEX dbmail_mailboxes_owner_idx ON dbmail_mailboxes(owner_idnr);
CREATE INDEX dbmail_mailboxes_name_idx ON dbmail_mailboxes(name);
CREATE INDEX dbmail_mailboxes_owner_name_idx ON
dbmail_mailboxes(owner_idnr, name);
CREATE SEQUENCE dbmail_mailbox_idnr_seq;
SELECT setval('dbmail_mailbox_idnr_seq', max(mailbox_idnr)) FROM
dbmail_mailboxes;
ALTER TABLE dbmail_mailboxes ALTER mailbox_idnr SET DEFAULT
nextval('dbmail_mailbox_idnr_seq'::text);
DROP SEQUENCE mailbox_idnr_seq;
ALTER TABLE dbmail_subscription DROP CONSTRAINT subscription_pkey;
ALTER TABLE dbmail_subscription ADD PRIMARY KEY (user_id, mailbox_id);
ALTER TABLE dbmail_messageblks DROP CONSTRAINT messageblks_pkey;
DROP INDEX messageblks_physmessage_idx;
DROP INDEX messageblks_physmessage_is_header_idx;
ALTER TABLE dbmail_messageblks ADD PRIMARY KEY (messageblk_idnr);
CREATE INDEX dbmail_messageblks_physmessage_idx ON
dbmail_messageblks(physmessage_id);
CREATE INDEX dbmail_messageblks_physmessage_is_header_idx ON
dbmail_messageblks(physmessage_id, is_header);
CREATE SEQUENCE dbmail_messageblk_idnr_seq;
SELECT setval('dbmail_messageblk_idnr_seq', max(messageblk_idnr)) FROM
dbmail_messageblks;
ALTER TABLE dbmail_messageblks ALTER messageblk_idnr SET DEFAULT
nextval('dbmail_messageblk_idnr_seq'::text);
DROP SEQUENCE messageblk_idnr_seq;
ALTER TABLE dbmail_messages DROP CONSTRAINT messages_pkey;
DROP INDEX messages_mailbox_idx;
DROP INDEX messages_physmessage_idx;
DROP INDEX messages_seen_flag_idx;
DROP INDEX messages_status_idx;
DROP INDEX messages_status_notdeleted_idx;
DROP INDEX messages_unique_id_idx;
ALTER TABLE dbmail_messages ADD PRIMARY KEY (message_idnr);
CREATE INDEX dbmail_messages_mailbox_idx ON
dbmail_messages(mailbox_idnr);
CREATE INDEX dbmail_messages_physmessage_idx ON
dbmail_messages(physmessage_id);
CREATE INDEX dbmail_messages_seen_flag_idx ON
dbmail_messages(seen_flag);
CREATE INDEX dbmail_messages_unique_id_idx ON
dbmail_messages(unique_id);
CREATE INDEX dbmail_messages_status_idx ON dbmail_messages(status);
CREATE INDEX dbmail_messages_status_notdeleted_idx ON
dbmail_messages(status) WHERE status < '2';
CREATE SEQUENCE dbmail_message_idnr_seq;
SELECT setval('dbmail_message_idnr_seq', max(message_idnr)) FROM
dbmail_messages;
ALTER TABLE dbmail_messages ALTER message_idnr SET DEFAULT
nextval('dbmail_message_idnr_seq'::text);
DROP SEQUENCE message_idnr_seq;
DELETE FROM dbmail_pbsp;
ALTER TABLE dbmail_pbsp DROP CONSTRAINT pbsp_pkey;
DROP INDEX idx_since;
ALTER TABLE dbmail_pbsp ADD PRIMARY KEY (idnr);
CREATE UNIQUE INDEX dbmail_idx_ipnumber ON dbmail_pbsp (ipnumber);
CREATE INDEX dbmail_pbsp_since_idx ON dbmail_pbsp (since);
CREATE SEQUENCE dbmail_pbsp_idnr_seq;
SELECT setval('dbmail_pbsp_idnr_seq', max(idnr)) FROM dbmail_pbsp;
ALTER TABLE dbmail_pbsp ALTER idnr SET DEFAULT
nextval('dbmail_pbsp_idnr_seq'::text);
DROP SEQUENCE seq_pbsp_id;
ALTER TABLE dbmail_physmessage DROP CONSTRAINT physmessage_pkey CASCADE;
ALTER TABLE dbmail_physmessage ADD PRIMARY KEY (id);
CREATE SEQUENCE dbmail_physmessage_id_seq;
SELECT setval('dbmail_physmessage_id_seq', max(id)) FROM
dbmail_physmessage;
ALTER TABLE dbmail_physmessage ALTER id SET DEFAULT
nextval('dbmail_physmessage_id_seq');
DROP SEQUENCE physmessage_id_seq;
ALTER TABLE dbmail_acl ADD FOREIGN KEY (user_id) REFERENCES
dbmail_users(user_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_subscription ADD FOREIGN KEY (user_id) REFERENCES
dbmail_users(user_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_mailboxes ADD FOREIGN KEY (owner_idnr) REFERENCES
dbmail_users(user_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_messages ADD FOREIGN KEY (mailbox_idnr) REFERENCES
dbmail_mailboxes(mailbox_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_acl ADD FOREIGN KEY (mailbox_id) REFERENCES
dbmail_mailboxes(mailbox_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_subscription ADD FOREIGN KEY (mailbox_id) REFERENCES
dbmail_mailboxes(mailbox_idnr) ON DELETE CASCADE;
ALTER TABLE dbmail_messageblks ADD FOREIGN KEY (physmessage_id)
REFERENCES dbmail_physmessage(id) ON DELETE CASCADE;
COMMIT;
# Scrap the auto_notifications and auto_replies tables and start over
from scratch
BEGIN;
DROP TABLE dbmail_auto_notifications;
DROP TABLE dbmail_auto_replies;
DROP SEQUENCE auto_reply_seq;
DROP SEQUENCE auto_notification_seq;
CREATE TABLE dbmail_auto_notifications (
user_idnr INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE,
notify_address VARCHAR(100),
PRIMARY KEY (user_idnr)
);
CREATE TABLE dbmail_auto_replies (
user_idnr INT8 REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE,
reply_body TEXT,
PRIMARY KEY (user_idnr)
);
COMMIT;
# Permission Fixes? Did you do everything as the correct user? These
commands wont run as is, you need to add a username to 'em
GRANT SELECT ON
dbmail_alias_idnr_seq,dbmail_auto_notification_seq,dbmail_mailbox_idnr_s
eq,dbmail_message_idnr_seq,dbmail_messageblk_idnr_seq,dbmail_pbsp_idnr_s
eq,dbmail_physmessage_id_seq,dbmail_user_idnr_seq TO ;
GRANT SELECT,INSERT,UPDATE,DELETE ON
dbmail_acl,dbmail_Aliases,dbmail_auto_notifications,dbmail_auto_replies,
dbmail_dups,dbmail_mailboxes,dbmail_messageblks,dbmail_messages,dbmail_p
bsp,dbmail_physmessage,dbmail_subscription,dbmail_users TO ;
