Hi,
does anyone have a sql script to migrate a PostgreSQL database to table
name prefixes (it would be a good habit to only commit changes if there
is a script to migrate the database) ?
I tried the following:
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;
That leads to:
Aug 14 22:49:27 geht-schon dbmail/lmtpd[12101]: dbpgsql.c, db_query:
Error executing query [SELECT currval('dbmail_physmessage_id_seq')] :
[ERROR: relation "dbmail_physmessage_id_seq" does not exist ]
So I renamed all sequences:
ALTER TABLE alias_idnr_seq RENAME TO dbmail_alias_idnr_seq;
ALTER TABLE user_idnr_seq RENAME TO dbmail_user_idnr_seq;
ALTER TABLE mailbox_idnr_seq RENAME TO dbmail_mailbox_idnr_seq;
ALTER TABLE physmessage_id_seq RENAME TO dbmail_physmessage_id_seq;
ALTER TABLE message_idnr_seq RENAME TO dbmail_message_idnr_seq;
ALTER TABLE messageblk_idnr_seq RENAME TO dbmail_messageblk_idnr_seq;
ALTER TABLE seq_pbsp_id RENAME TO dbmail_seq_pbsp_id;
When I try to copy a message using IMAP I get:
Aug 14 23:20:28 geht-schon dbmail/imap4d[14333]: dbpgsql.c, db_query:
Error executing query [INSERT INTO dbmail_messages
(mailbox_idnr,physmessage_id, seen_flag, answered_flag, deleted_flag,
flagged_flag, recent_flag, draft_flag, unique_id, status) SELECT '12',
physmessage_id, seen_flag, answered_flag, deleted_flag, flagged_flag,
recent_flag, draft_flag, '240a61ab17e793b2b5137f879e12a7bf', status FROM
dbmail_messages WHERE message_idnr = '166659'] : [ERROR: relation
"message_idnr_seq" does not exist ]
So I have to rename some sequences? Strange ...
Thomas
--
http://www.tmueller.com for pgp key (95702B3B)
