aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorHabeas Codice <habeascodice@federated.social>2014-11-17 12:26:24 -0800
committerHabeas Codice <habeascodice@federated.social>2014-11-17 12:26:24 -0800
commit9443ef8a3d2436e1e9fa10e12527c5e268d10871 (patch)
tree081130f642f3225265f8f1df94c127e461b57d02
parent540b7af2c12666047710dd8f5e0c54d23ae8872c (diff)
downloadvolse-hubzilla-9443ef8a3d2436e1e9fa10e12527c5e268d10871.tar.gz
volse-hubzilla-9443ef8a3d2436e1e9fa10e12527c5e268d10871.tar.bz2
volse-hubzilla-9443ef8a3d2436e1e9fa10e12527c5e268d10871.zip
simple command-line migrator from mysql to postgres
provided with the WorksForMe(tm) warranty of fitness for a purpose implied or otherwise
-rw-r--r--install/migrate-mypg.php306
-rw-r--r--install/migrate_mypg_fixseq.sql55
2 files changed, 361 insertions, 0 deletions
diff --git a/install/migrate-mypg.php b/install/migrate-mypg.php
new file mode 100644
index 000000000..b7a7ced1b
--- /dev/null
+++ b/install/migrate-mypg.php
@@ -0,0 +1,306 @@
+<?php
+function ask_question($menu, $options, $default) {
+ $r=null;
+ while(!$r) {
+ echo $menu;
+ $str = substr(strtolower(fgets(STDIN)),0,-1);
+ if($str == '')
+ $r = $default;
+ else if(in_array($str, $options))
+ $r = $str;
+ }
+ return $r;
+}
+
+function get_data($prompt, $regex) {
+ do {
+ echo $prompt;
+ $r = substr(fgets(STDIN), 0, -1);
+ if(!preg_match($regex, $prompt))
+ $r = '';
+ } while($r === '');
+ return $r;
+}
+
+function parse_htconfig($file) {
+ if(!file_exists($file))
+ return array();
+ $conf = file_get_contents($file);
+ preg_match_all('/\$db\_(host|port|user|pass|data|type)\s*=\s*\'([[:print:]]+)\'/', $conf, $matches);
+ return array_combine($matches[1], $matches[2]);
+}
+
+function get_configtype(array $data) {
+ if(!isset($data['host'], $data['user'], $data['pass'], $data['data']))
+ return 'none';
+ if($data['type'] == 1)
+ return 'pgsql';
+ return 'mysql';
+}
+
+function phpquote($str) {
+ for($r = '', $x=0, $l=strlen($str); $x < $l; $x++)
+ if($str{$x} == '\'' || $str{$x} == '\\')
+ $r .= '\\' . $str{$x};
+ else
+ $r .= $str{$x};
+ return $r;
+}
+
+function run_sql($file, $db, &$err, &$n) {
+ $sql = file_get_contents($file);
+ $sql = explode(';', $sql);
+ $err = 0; $n = 0;
+ $c = count($sql);
+ if(!$c) {
+ echo "Unknown error.\n";
+ exit();
+ }
+ foreach($sql as $stmt) {
+ if($stmt == '' || $stmt == "\n" || $stmt == "\n\n")
+ continue;
+ $r = $db->exec($stmt);
+ if($r===false) {
+ echo "\nError executing $stmt: ".var_export($db->errorInfo(), true)."\n";
+ $err++;
+ } else {
+ $n++;
+ }
+ if($n % 5 == 0)
+ echo "\033[255DExecuting: $file, $n/$c\033[K";
+ }
+ echo "\n";
+}
+
+$drivers=true;
+if(!class_exists('PDO'))
+ $drivers=false;
+if($drivers) {
+ $drivers = PDO::getAvailableDrivers();
+ if(!in_array('pgsql', $drivers) || !in_array('mysql', $drivers))
+ $drivers = false;
+}
+if(!$drivers) {
+ echo "Sorry. This migration tool requires both mysql and pgsql PDO drivers.\n";
+ $r = ask_question("If you are on dreamhost you can enable them. This might work on other shared hosts too. Type 'n' to do it yourself.\nWould you like to try (Y/n)? ", array('y', 'n'), 'y');
+ if($r=='y') {
+ $path = $_SERVER['HOME'] . '/.php/5.4';
+ if(!file_exists($path))
+ mkdir($path, 0770, true);
+
+ $rcfile = $path . '/phprc';
+
+ $str = '';
+ $mods = get_loaded_extensions();
+ foreach(array('pdo_mysql','pdo_pgsql','pgsql') as $ext)
+ if(!in_array($ext, $mods))
+ $str .= 'extension=' . $ext . ".so\n";
+
+ file_put_contents($rcfile, $str, FILE_APPEND );
+ echo "drivers enabled.\nNow type: \033[1m/usr/local/bin/php-5.4 install/".basename($argv[0])."\033[0m\n";
+ }
+ exit();
+}
+
+foreach(array('install','include','mod','view') as $dir) {
+ if(!file_exists($dir)) {
+ echo "You must execute from inside the webroot like the cron\n";
+ exit();
+ }
+}
+
+$cfg = parse_htconfig('.htconfig.php');
+$type = get_configtype($cfg);
+if($type != 'mysql') {
+ echo "Error. Must start with standard mysql installation in .htconfig.php.\n";
+ exit();
+}
+
+if(!$cfg['port'])
+ $cfg['port'] = 3306;
+try {
+ $mydb = new PDO("mysql:host={$cfg['host']};dbname={$cfg['data']};port={$cfg['port']}", $cfg['user'], $cfg['pass']);
+} catch (PDOException $e) {
+ echo "Error connecting to mysql DB: " . $e->getMessage() . "\n";
+ exit();
+}
+
+if(!file_exists('.htconfig.php-pgsql')) {
+ echo "Enter postgres server info:\n";
+ $p['host'] = get_data("Hostname: ", '/[\w.]+/');
+ $p['port'] = get_data("Enter port (0 for default): ", '/\d+/');
+ $p['user'] = get_data("Username: ", '/\w+/');
+ $p['pass'] = get_data("Password: ", '/[[:print:]]+/');
+ $p['data'] = get_data("Database name: ", '/\w+/');
+ $old = file_get_contents('.htconfig.php');
+ $new = preg_replace(
+ array(
+ '/^(\$db_host\s*=\s*\')([\w.]+)(\';)$/m',
+ '/^(\$db_port\s*=\s*\')(\d+)(\';)$/m',
+ '/^(\$db_user\s*=\s*\')(\w+)(\';)$/m',
+ '/^(\$db_pass\s*=\s*\')([[:print:]]+)(\';)/m',
+ '/^(\$db_data\s*=\s*\')(\w+)(\';)$/m',
+ '/^(\$db_type\s*=\s*\')(\d)(\';)$/m' // in case they already have it
+ ), array(
+ "$1{$p['host']}$3",
+ "\${1}{$p['port']}$3",
+ "$1{$p['user']}$3",
+ "$1{$p['pass']}$3",
+ "$1{$p['data']}$3\n\$db_type = '1';\n", // they probably don't
+ "\${1}1$3"
+ ),
+ $old,
+ 1,
+ $repl
+ );
+ if($new === false || $repl < 5) {
+ echo "Failed. Please make a postgres config file named .htconfig.php-pgsql - Be sure to add \"\$db_type = '1';\" to your config.\n";
+ exit();
+ }
+ file_put_contents('.htconfig.php-pgsql', $new);
+}
+
+$pcfg = parse_htconfig('.htconfig.php-pgsql');
+$ptype = get_configtype($pcfg);
+if($ptype != 'pgsql') {
+ echo "Error. Must have a valid pgsql config named .htconfig.php-pgsql. Be sure to add \"\$db_type = '1';\" to your config.\n";
+ exit();
+}
+
+if(!$pcfg['port'])
+ $pcfg['port'] = 5432;
+try {
+ $pgdb = new PDO("pgsql:host={$pcfg['host']};dbname={$pcfg['data']};port={$pcfg['port']}", $pcfg['user'], $pcfg['pass']);
+} catch (PDOException $e) {
+ echo "Error connecting to pgsql DB: " . $e->getMessage() . "\n";
+ echo "cfg string: " . "pgsql:host={$pcfg['host']};dbname={$pcfg['data']};port={$pcfg['port']}\n";
+ exit();
+}
+$B = "\033[0;34m";
+$H = "\033[0;35m";
+$W = "\033[1;37m";
+$M = "\033[1;31m";
+$N = "\033[0m";
+$r = ask_question("Ready to migrate {$W}Red{$M}(#){$W}Matrix$N from mysql db @$B{$cfg['host']}$N/$B{$cfg['data']}$N to postgres db @$B{$pcfg['host']}$N/$B{$pcfg['data']}$N.
+The site will be disabled during the migration by moving the $H.htconfig.php$N file to $H.htconfig.php-mysql$N.
+If for any reason the migration fails, you will need to move the config file back into place manually before trying again.
+
+Are you ready to begin (N/y)? ", array('y','n'), 'n'
+);
+
+if($r == 'n')
+ exit();
+
+rename('.htconfig.php', '.htconfig.php-mysql');
+
+run_sql('install/schema_postgres.sql', $pgdb, $err, $n);
+if($err) {
+ echo "There were $err errors creating the pgsql schema. Unable to continue.\n";
+/* $r = ask_question("There were $err errors (and $n ok) creating the schema. This shouldn't happen and continuing will probably fail.
+Do you want to continue migration anyway (N/y)? ", array('y','n'), 'n'
+ );
+ if($r == 'n')*/
+ exit();
+}
+
+echo "pgsql schema created. $n queries executed successfully.\n";
+
+$res = $pgdb->query("select relname, attname, pg_type.typname from ((pg_attribute inner join pg_class on attrelid=pg_class.oid) inner join pg_type on atttypid=pg_type.oid) inner join pg_namespace on relnamespace=pg_namespace.oid where nspname='public' and atttypid not in (26,27,28,29) and relkind='r' and attname <> 'item_search_vector';");
+if($res === false) {
+ echo "Error reading back schema. Unable to continue.\n";
+ var_export($pgdb->errorInfo());
+ exit();
+}
+$schema = array();
+while(($row = $res->fetch()) !== false)
+ $schema[$row[0]][$row[1]] = $row[2];
+
+$err = 0; $n = 0;
+$reserved = array('ignore','key','with');
+foreach($schema as $table=>$fields) {
+ $fnames = array_keys($fields);
+ $pfnames = array_keys($fields);
+
+ foreach($fnames as &$fname)
+ if(in_array($fname, $reserved))
+ $fname = '`' . $fname . '`';
+ $fstr = implode(',', $fnames);
+
+ foreach($pfnames as &$pfname)
+ if(in_array($pfname, $reserved))
+ $pfname = '"' . $pfname . '"';
+ $pfstr = implode(',', $pfnames);
+
+ $res = $mydb->query("SELECT count(*) FROM $table;");
+ if($res === false) {
+ echo "Fatal error counting table $table: ".var_export($mydb->errorInfo(), true)."\n";
+ exit();
+ }
+ $nrows = $res->fetchColumn(0);
+ if(!$nrows) {
+ echo "\nTABLE $table has 0 rows in mysql db.\n";
+ continue;
+ }
+
+ $pstr = '';
+ for($x=0, $c=count($fields); $x < $c; $x++)
+ $pstr .= ($x ? ',?' : '?');
+
+ $crow = 0;
+ echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K";
+
+ $res = $mydb->query("SELECT $fstr FROM $table;");
+ if($res === false) {
+ echo "Fatal Error importing table $table: ".var_export($mydb->errorInfo(), true)."\n";
+ exit();
+ }
+
+ $istmt = $pgdb->prepare("INSERT INTO $table ($pfstr) VALUES ($pstr);");
+ if($istmt === false) {
+ echo "Fatal error preparing query. Aborting.\n";
+ var_export($pgdb->errorInfo());
+ exit();
+ }
+
+ while(($row = $res->fetch(PDO::FETCH_NUM)) !== false) {
+ foreach($row as $idx => &$val)
+ if(array_slice(array_values($fields),$idx,1)[0] == 'timestamp' && $val == '0000-00-00 00:00:00')
+ $istmt->bindParam($idx+1, ($nulldate='0001-01-01 00:00:00'));
+ else if(array_slice(array_values($fields),$idx,1)[0] == 'bytea')
+ $istmt->bindParam($idx+1, $val, PDO::PARAM_LOB);
+ else
+ $istmt->bindParam($idx+1, $val);
+ $r = $istmt->execute();
+ if($r === false) {
+ $err++;
+ echo "\nInsert error: ".var_export(array($pgdb->errorInfo(), $table, $fields, $row), true)."\n";
+ //exit();
+ } else
+ $n++;
+ $crow++;
+ if(($crow % 10) == 0 || $crow == $nrows)
+ echo "\033[255DTABLE: $table [$c fields] $crow/$nrows (".number_format(($crow/$nrows)*100,2)."%)\033[K";
+ }
+}
+
+echo "Done with $err errors and $n inserts.\n";
+if($err) {
+ echo "Migration had errors. Aborting.\n";
+ exit();
+}
+
+run_sql('install/migrate_mypg_fixseq.sql', $pgdb, $err, $n);
+echo "Sequences updated with $err errors and $n inserts.\n";
+if($err)
+ exit();
+
+$r = ask_question("Everything successful. Once you connect up the pg database there is no going back. Do you want to make it live (N,y)?", array('y', 'n'), 'n');
+if($r == 'n') {
+ echo "You can make active by renaming .htconfig.php-pgsql to .htconfig.php, or start over by renaming .htconfig.php-mysql to .htconfig.php\n";
+ exit();
+}
+
+rename('.htconfig.php-pgsql', '.htconfig.php');
+echo "Done. {$W}Red{$M}(#){$W}Matrix$N now running on postgres.\n";
+
+
diff --git a/install/migrate_mypg_fixseq.sql b/install/migrate_mypg_fixseq.sql
new file mode 100644
index 000000000..806c5cf31
--- /dev/null
+++ b/install/migrate_mypg_fixseq.sql
@@ -0,0 +1,55 @@
+ SELECT SETVAL('public.abook_abook_id_seq', COALESCE(MAX(abook_id), 1) ) FROM public.abook;
+ SELECT SETVAL('public.account_account_id_seq', COALESCE(MAX(account_id), 1) ) FROM public.account;
+ SELECT SETVAL('public.addon_id_seq', COALESCE(MAX(id), 1) ) FROM public.addon;
+ SELECT SETVAL('public.app_id_seq', COALESCE(MAX(id), 1) ) FROM public.app;
+ SELECT SETVAL('public.attach_id_seq', COALESCE(MAX(id), 1) ) FROM public.attach;
+ SELECT SETVAL('public.channel_channel_id_seq', COALESCE(MAX(channel_id), 1) ) FROM public.channel;
+ SELECT SETVAL('public.chat_chat_id_seq', COALESCE(MAX(chat_id), 1) ) FROM public.chat;
+ SELECT SETVAL('public.chatpresence_cp_id_seq', COALESCE(MAX(cp_id), 1) ) FROM public.chatpresence;
+ SELECT SETVAL('public.chatroom_cr_id_seq', COALESCE(MAX(cr_id), 1) ) FROM public.chatroom;
+ SELECT SETVAL('public.config_id_seq', COALESCE(MAX(id), 1) ) FROM public.config;
+ SELECT SETVAL('public.conv_id_seq', COALESCE(MAX(id), 1) ) FROM public.conv;
+ SELECT SETVAL('public.event_id_seq', COALESCE(MAX(id), 1) ) FROM public.event;
+ SELECT SETVAL('public.fcontact_id_seq', COALESCE(MAX(id), 1) ) FROM public.fcontact;
+ SELECT SETVAL('public.ffinder_id_seq', COALESCE(MAX(id), 1) ) FROM public.ffinder;
+ SELECT SETVAL('public.fserver_id_seq', COALESCE(MAX(id), 1) ) FROM public.fserver;
+ SELECT SETVAL('public.fsuggest_id_seq', COALESCE(MAX(id), 1) ) FROM public.fsuggest;
+ SELECT SETVAL('public.group_member_id_seq', COALESCE(MAX(id), 1) ) FROM public.group_member;
+ SELECT SETVAL('public.groups_id_seq', COALESCE(MAX(id), 1) ) FROM public.groups;
+ SELECT SETVAL('public.hook_id_seq', COALESCE(MAX(id), 1) ) FROM public.hook;
+ SELECT SETVAL('public.hubloc_hubloc_id_seq', COALESCE(MAX(hubloc_id), 1) ) FROM public.hubloc;
+ SELECT SETVAL('public.issue_issue_id_seq', COALESCE(MAX(issue_id), 1) ) FROM public.issue;
+ SELECT SETVAL('public.item_id_id_seq', COALESCE(MAX(id), 1) ) FROM public.item_id;
+ SELECT SETVAL('public.item_id_seq', COALESCE(MAX(id), 1) ) FROM public.item;
+ SELECT SETVAL('public.likes_id_seq', COALESCE(MAX(id), 1) ) FROM public.likes;
+ SELECT SETVAL('public.mail_id_seq', COALESCE(MAX(id), 1) ) FROM public.mail;
+ SELECT SETVAL('public.manage_id_seq', COALESCE(MAX(id), 1) ) FROM public.manage;
+ SELECT SETVAL('public.menu_item_mitem_id_seq', COALESCE(MAX(mitem_id), 1) ) FROM public.menu_item;
+ SELECT SETVAL('public.menu_menu_id_seq', COALESCE(MAX(menu_id), 1) ) FROM public.menu;
+ SELECT SETVAL('public.notify_id_seq', COALESCE(MAX(id), 1) ) FROM public.notify;
+ SELECT SETVAL('public.obj_obj_id_seq', COALESCE(MAX(obj_id), 1) ) FROM public.obj;
+ SELECT SETVAL('public.pconfig_id_seq', COALESCE(MAX(id), 1) ) FROM public.pconfig;
+ SELECT SETVAL('public.photo_id_seq', COALESCE(MAX(id), 1) ) FROM public.photo;
+ SELECT SETVAL('public.poll_elm_pelm_id_seq', COALESCE(MAX(pelm_id), 1) ) FROM public.poll_elm;
+ SELECT SETVAL('public.poll_poll_id_seq', COALESCE(MAX(poll_id), 1) ) FROM public.poll;
+ SELECT SETVAL('public.profdef_id_seq', COALESCE(MAX(id), 1) ) FROM public.profdef;
+ SELECT SETVAL('public.profext_id_seq', COALESCE(MAX(id), 1) ) FROM public.profext;
+ SELECT SETVAL('public.profile_check_id_seq', COALESCE(MAX(id), 1) ) FROM public.profile_check;
+ SELECT SETVAL('public.profile_id_seq', COALESCE(MAX(id), 1) ) FROM public.profile;
+ SELECT SETVAL('public.register_id_seq', COALESCE(MAX(id), 1) ) FROM public.register;
+ SELECT SETVAL('public.session_id_seq', COALESCE(MAX(id), 1) ) FROM public.session;
+ SELECT SETVAL('public.shares_share_id_seq', COALESCE(MAX(share_id), 1) ) FROM public.shares;
+ SELECT SETVAL('public.sign_id_seq', COALESCE(MAX(id), 1) ) FROM public.sign;
+ SELECT SETVAL('public.source_src_id_seq', COALESCE(MAX(src_id), 1) ) FROM public.source;
+ SELECT SETVAL('public.spam_id_seq', COALESCE(MAX(id), 1) ) FROM public.spam;
+ SELECT SETVAL('public.sys_perms_id_seq', COALESCE(MAX(id), 1) ) FROM public.sys_perms;
+ SELECT SETVAL('public.term_tid_seq', COALESCE(MAX(tid), 1) ) FROM public.term;
+ SELECT SETVAL('public.updates_ud_id_seq', COALESCE(MAX(ud_id), 1) ) FROM public.updates;
+ SELECT SETVAL('public.verify_id_seq', COALESCE(MAX(id), 1) ) FROM public.verify;
+ SELECT SETVAL('public.vote_vote_id_seq', COALESCE(MAX(vote_id), 1) ) FROM public.vote;
+ SELECT SETVAL('public.xchat_xchat_id_seq', COALESCE(MAX(xchat_id), 1) ) FROM public.xchat;
+ SELECT SETVAL('public.xconfig_id_seq', COALESCE(MAX(id), 1) ) FROM public.xconfig;
+ SELECT SETVAL('public.xign_id_seq', COALESCE(MAX(id), 1) ) FROM public.xign;
+ SELECT SETVAL('public.xlink_xlink_id_seq', COALESCE(MAX(xlink_id), 1) ) FROM public.xlink;
+ SELECT SETVAL('public.xtag_xtag_id_seq', COALESCE(MAX(xtag_id), 1) ) FROM public.xtag;
+