For feature requests, please see: https://mattermost.com/suggestions/.
For troubleshooting questions, please post in the following format:
Summary
I tried to do migration from SQL to Postgress wtih help Migration guidelines from MySQL to PostgreSQL — Mattermost documentation
Steps to reproduce
Maybe if you generate same database and try to migrate?
Expected behavior
I expect, that data migration will work without errors and the postgres database can replace mysql database without loosing information
Observed behavior
I got some errors during migration with pgloader
Hi @all,
my old server is broken 
but I did a backup with mysqldump 
Because I bought new Hardware I set up Mattermost and Postgress in a docker container. First I tried to migrate directly into the container, which failed…b
…now I installed MySQL, Postgress and pgloader without docker.
But Im still not able to perform migration.
Here is my migration.load file for pgloader. I did already some changes because there were some errors in the manual:
LOAD DATABASE
FROM mysql://mmuser:MyDatabasePassword@localhost:3306/mattermost
INTO pgsql://mmuser:MyDatabasePassword@localhost:5432/mattermostWITH data only,
workers = 8, concurrency = 1,
multiple readers per thread, rows per range = 50000,
create no tables, create no indexes,
preserve index namesSET PostgreSQL PARAMETERS
maintenance_work_mem to ‘128MB’,
work_mem to ‘12MB’SET MySQL PARAMETERS
net_read_timeout = ‘120’,
net_write_timeout = ‘120’CAST column Channels.Type to “channel_type” drop typemod,
column Teams.Type to “team_type” drop typemod,
column UploadSessions.Type to “upload_session_type” drop typemod,
column Drafts.Priority to text,
type int when (= precision 11) to integer drop typemod,
type bigint when (= precision 20) to bigint drop typemod,
type text to varchar drop typemod,
type tinyint when (<= precision 4) to boolean using tinyint-to-boolean,
type json to jsonb drop typemodEXCLUDING TABLE NAMES MATCHING ~‘IR_’, ~‘focalboard’, ‘schema_migrations’
BEFORE LOAD DO
$$ ALTER SCHEMA public RENAME TO mattermost; $$,
$$ DROP INDEX IF EXISTS idx_posts_message_txt; $$,
$$ DROP INDEX IF EXISTS idx_fileinfo_content_txt; $$AFTER LOAD DO
$$ UPDATE mattermost.db_migrations set name=‘add_createat_to_teamembers’ where version=92; $$,
$$ CREATE INDEX IF NOT EXISTS idx_posts_message_txt ON mattermost.posts USING gin(to_tsvector(‘english’, message)); $$,
$$ CREATE INDEX IF NOT EXISTS idx_fileinfo_content_txt ON mattermost.fileinfo USING gin(to_tsvector(‘english’, content)); $$,
$$ ALTER SCHEMA mattermost RENAME TO public; $$,
$$ SELECT pg_catalog.set_config(‘search_path’, ‘“$user”, public’, false); $$,
$$ ALTER USER mmuser SET SEARCH_PATH TO ‘public’; $$;
This is how my mySQL-Database loooks like:
SHOW TABLES;
±------------------------------+
| Tables_in_mattermost |
±------------------------------+
| Audits |
| Bots |
| ChannelMemberHistory |
| ChannelMembers |
| Channels |
| ClusterDiscovery |
| CommandWebhooks |
| Commands |
| Compliances |
| DesktopTokens |
| Drafts |
| Emoji |
| FileInfo |
| GroupChannels |
| GroupMembers |
| GroupTeams |
| IR_Incident |
| IR_Playbook |
| IR_PlaybookAutoFollow |
| IR_PlaybookMember |
| IR_Run_Participants |
| IR_StatusPosts |
| IR_System |
| IR_TimelineEvent |
| IR_UserInfo |
| IR_ViewedChannel |
| IncomingWebhooks |
| Jobs |
| Licenses |
| LinkMetadata |
| NotifyAdmin |
| OAuthAccessData |
| OAuthApps |
| OAuthAuthData |
| OutgoingOAuthConnections |
| OutgoingWebhooks |
| PersistentNotifications |
| PluginKeyValueStore |
| PostAcknowledgements |
| PostReminders |
| Posts |
| PostsPriority |
| Preferences |
| ProductNoticeViewState |
| PublicChannels |
| Reactions |
| RecentSearches |
| RemoteClusters |
| RetentionIdsForDeletion |
| RetentionPolicies |
| RetentionPoliciesChannels |
| RetentionPoliciesTeams |
| Roles |
| Schemes |
| Sessions |
| SharedChannelAttachments |
| SharedChannelRemotes |
| SharedChannelUsers |
| SharedChannels |
| SidebarCategories |
| SidebarChannels |
| Status |
| Systems |
| TeamMembers |
| Teams |
| TermsOfService |
| ThreadMemberships |
| Threads |
| Tokens |
| TrueUpReviewHistory |
| UploadSessions |
| UserAccessTokens |
| UserGroups |
| UserTermsOfService |
| Users |
| db_lock |
| db_migrations |
| focalboard_blocks |
| focalboard_blocks_history |
| focalboard_board_members |
| focalboard_boards |
| focalboard_boards_history |
| focalboard_file_info |
| focalboard_notification_hints |
| focalboard_schema_migrations |
| focalboard_sessions |
| focalboard_sharing |
| focalboard_subscriptions |
| focalboard_system_settings |
| focalboard_teams |
| focalboard_users |
±------------------------------+
91 rows in set (0.01 sec)
But I all the time get the follwoing error, when running pgloader:
KABOOM!
FATAL error: Database error 3F000: schema “public” does not exist
QUERY: ALTER SCHEMA public RENAME TO mattermost;
An unhandled error condition has been signalled:
Database error 3F000: schema “public” does not exist
QUERY: ALTER SCHEMA public RENAME TO mattermost;What I am doing here?
Database error 3F000: schema “public” does not exist
QUERY: ALTER SCHEMA public RENAME TO mattermost;
Can you please help me to migrate my mysql data into postgress? There are very important Posts
Thanks a lot in advance!
Zugang