drupal: บันทึก postgresql

ลองใช้งานกับ postgresql

ดัมป์ข้อมูล
$ pg_dump -dc -U user database > dumpfile.sql
  • -d หรือ --inserts คือให้ใช้คำสั่ง INSERT มาตรฐาน ทำให้ช้าแต่แลกเปลี่ยนข้อมูลกับฐานข้อมูลแบบอื่นได้ง่ายกว่า
  • -c หรือ --clean คือให้ล้างตารางและ Sequence ก่อนสร้างตารางใหม่ ดีสำหรับเวลาซ่อมฐานข้อมูล
สร้างตารางกลับคืน
$ psql -f dumpfile.sql -U user database
นำเข้าข้อมูลจาก text file
$ psql -c "COPY tablename (column,...) FROM filename DELIMITER E\t database"
ในที่นี้ ตัวแยกฟิลด์คือ TAB
ดูเนื้อที่การใช้งาน
$ psql -U user dbname
$ SELECT relname, relpages FROM pg_class ORDER BY relpages DESC limit line; /* tables size */
$ SELECT pg_database_size('dbname'); /* database size */
$ \q
แก้ไขค่าในตารางเป็น UTF-8
ตัวอย่างเป็นการแก้เครื่องหมาย ' และ `
UPDATE table SET field=convert_to(field,'UTF8') WHERE field != cast(convert_to(field,'UTF8') AS field_type);

UPDATE table SET field=replace(field,'\\302\\222','\'') WHERE cast(field as bytea) LIKE '%\\302\\222%';

UPDATE table SET field=replace(field,'\\302\\221','`') WHERE cast(field as bytea) LIKE '%\\302\\221%';

drupal5: บันทึกการย้ายจาก Mysql เป็น PostgreSQL

ต้องการใช้ฐานข้อมูล postgresql เพราะงานส่วนใหญ่ลงใน postgresql
ตอนศึกษา Drupal เลือก mysql เพราะหลายมอดูลใช้กับ postgresql ไม่ได้ เลยใช้ mysql มาเรื่อย
ตอนนี้ต้องการเปลี่ยนกลับแล้ว เลยศึกษาวิธีโอนข้อมูลเตรียมไว้

เที่ยวนี้ต้องทำด้วยมือเป็นส่วนใหญ่ เพราะลองค้นจากกูเกิลแล้ว สคริปต์ต่าง ๆ ที่ค้นได้ ส่วนใหญ่จะเขียนด้วย perl และล้าสมัยหมดแล้ว รันแล้วเกิดข้อผิดพลาดทุกอัน

บันทึกการย้ายฐานข้อมูล Drupal รุ่น 5.6 จาก MySQL-5.0.32 มาเป็น PostgreSQL-8.1.11 บนเดเบียน Etch

  1. คัดลอกไดเรกทอรี่เก่ามายังอันใหม่ทั้งหมด
    $ cd /var/www/
    $ cp -xa drupal newdrupal
  2. ปรับตั้ง drupal ของไดเรกทอรี่ใหม่ ให้เป็นฐานข้อมูลใหม่
    $ cd newdrupal
    $ vi sites/default/setting.php
    ...
    //$db_url = 'mysql://myuser:mypassword@localhost/drupal-mysql';
    $db_url = 'pgsql://pguser:pgpassword@localhost/newdrupal-pgsql';
    ...
  3. ดิดตั้งโดยเข้าไปที่ URL: http://www.example.com/install.php ติดตั้งพร้อมเพิ่มผู้ใช้คือ admin ให้เรียบร้อย
  4. ดูใน drupal เก่า ว่าเปิดใช้มอดูลอะไรบ้าง ก็ให้เปิดใช้ให้เหมือนเดิม ทั้งนี้เพื่อให้ Drupal สร้างตารางขึ้นมารองรับการโอน โดยใช้ URL: admin/build/modules เลือกกาทุกมอดูล
  5. ล้างข้อมูลในตารางทั้งหมด แต่ไม่ต้องลบตาราง (งานนี้ลบด้วยมือ โดยใช้ phppgadmin ประมาณสัก 10 ตารางเห็นจะได้
  6. นำข้อมูลจากฐานข้อมูลเก่ามา โดยใช้ mysqldump
    $ mysqldump -c -t --skip-opt -u myuser -p drupal-mysql > mydrupal.sql
    -c คือ complete insert เพื่อให้เหมาะกับการนำเข้าไปยัง postgresql
    -t คือ --no-create-info ไม่ต้องเขียนการสร้างตารางใหม่
    --skip-opt คือไม่ต้องเขียนการล๊อกตาราง
  7. ปรับแต่งข้อมูลให้เหมาะกับ postgresql โดยการลบ backquote (`) ทิ้ง
    $ vi mydrupal.sql
    :1,$ s/`//g
    :wq
  8. ถึงตรงนี้อาจนำเข้าข้อมูลได้แล้ว แต่จะมีข้อผิดพลาดจากโค๊ดของ Drupal ตรงตาราง node_revision หน่อยนึง ตรงสดมถ์ log ที่ mysql ยอมให้เป็น NULL แต่ postgresql ไม่ยอม
    ต้องไปแก้ตาราง node_revision สดมถ์ log ให้เป็น NULL ได้
    งานนี้ทำผ่าน phppgadmin
  9. นำเข้าฐานข้อมูลใหม่ โดยใช้ psql
    $ psql -f mydrupal.sql -U pguser newdrupal-pgsql
  10. เข้าไซต์ใหม่ แล้วอัปเดตครั้งนึง ถ้ายังมีรายงานข้อผิดพลาด อาจต้องไปลบ Constraints ของตาราง locales_source ออก (ตรงนี้ลืมจดชื่อ Constraints) โดยใช้ phppgadmin

เสร็จแล้ว

แต่หากยังมีข้อผิดพลาดแบบเล็กน้อย เราอาจเริ่มสร้างตารางใหม่ทั้งหมดผ่าน pg_dump ได้ คือ

  • ดัมป์ข้อมูลแบบสั่งให้สร้างตารางใหม่ทั้งหมด
    $ pg_dump -c -U pguser newdrupal-pgsql > newpg.sql
    -c ให้สร้างคำสั่งสร้างตารางด้วย
  • นำเข้าข้อมูลอีกครั้ง
    $ psql -f newpg.sql -U pguser newdrupal-pgsql

เสร็จแล้ว

update 51-01-22
ยังไม่เสร็จจริง พอตรวจจริงแล้วเกิดข้อผิดพลาดคือ postgresql ไม่ยอมปรับค่า sequence ให้ตามจริง ไม่รู้ว่าเป็นเพราะอะไร ต้องมานั่งปรับประโยคใน newpg.sql ให้เลข sequence เป็นตัวเลขล่าสุด ต้องทำสักประมาณ 20 ตาราง
ขั้นตอนตั้งแต่เริ่ม (นับตั้งแต่ผ่านขั้นตอนก่อนหน้านี้มาแล้ว) คือ

  1. สั่งดัมป์จาก postgresql โดยให้มีคำสั่งสร้างตารางด้วย
    $ pg_dump -c -U pguser newdrupal-pgsql > newpg.sql
  2. แก้ไขข้อมูล sql โดยเอาแต่ส่วนหัว ตัดส่วน INSERT ทิ้งไป คือดูตั้งแต่คำสั่ง COPY ... เป็นต้นไป
    $ vi newpg.sql
    /COPY
    dG
    :wq
  3. ไล่ดูว่าตารางไหนมีฟีลด์ที่เป็น SERIAL ให้ดูค่าที่มากที่สุดของฟีลด์นั้น แล้วนำไปปรับแก้ในข้อมูล sql
    เช่นสมมุติว่า ตาราง accesslog มีฟีลด์ที่เป็น SERIAL คือ aid โดยมีค่าที่มากที่สุดเป็น 12345 (ดูจาก phppgadmin) ก็ให้ไปปรับแก้ประโยค sql ตรงส่วนปรับ sequence ให้เป็นตัวเลขนี้ด้วย เช่น
    $ vi pgnew.sql
    ...
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('accesslog', 'aid'), 12345, true);
    ...

    ทำให้ครบทุกตารางที่มีฟิลด์เป็น SERIAL

  4. นำเข้าข้อมูลจาก sql ของ postgresql ที่เพิ่งปรับแก้เมื่อสักครู่ เพื่อนำไปสร้างตาราง
    $ psql -f newpg.sql -U pguser newdrupal-pgsql
  5. นำเข้าข้อมูลจาก sql ของ mysql ที่ได้จากคราวก่อน เพื่อนำไปเป็นข้อมูล
    $ psql -f mydrupal.sql -U pguser newdrupal-pgsql

เสร็จจริง ๆ แล้ว

ยุ่งเป็นลิงถุง สงสัยเที่ยวหน้าทำเป็นสคริปต์ดูจากข้อมูลดัมป์ของ mysql แล้วโยนไปสร้างสดที่ postgresql เลยดีกว่า

drupal5: ปรับปรุงวิธีย้ายจาก Mysql เป็น PostgreSQL

ปรับปรุงจากครั้งก่อน แต่เพื่อให้สมบูรณ์ในตัวและอ้างอิงภายหลังได้ เลยเขียนใหม่ทั้งหมด

บันทึกการย้ายฐานข้อมูล Drupal รุ่น 5.6 จาก MySQL-5.0.32 มาเป็น PostgreSQL-8.1.11 บนเดเบียน Etch

  1. สร้างฐานข้อมูลใหม่
    $ createdb --encoding=UNICODE -U PG_USER NEW_DRUPAL_PG_DB
  2. คัดลอกไดเรกทอรี่เก่ามายังอันใหม่ทั้งหมด เพราะต้องการเอาไดเรกทอรี่ของมอดูลเก่ามาด้วย เพื่อนำมาเป็นข้อมูลทั้งหมดในการสร้างฐานข้อมูลใหม่
    $ cd /var/www/
    $ cp -xa OLD_DRUPAL_DIR NEW_DRUPAL_DIR
  3. ปรับตั้ง drupal ของไดเรกทอรี่ใหม่ ให้เป็นฐานข้อมูลใหม่
    $ cd NEW_DRUPAL_DIR
    $ vi sites/default/setting.php
    ...
    //$db_url = 'mysql://MY_USER:MY_PASSWORD@localhost/OLD_DRUPAL_MY_DB';
    $db_url = 'pgsql://PG_USER:PG_PASSWORD@localhost/NEW_DRUPAL_PG_DB';
    ...
  4. ดิดตั้งโดยเข้าไปที่ URL: http://WWW.EXAMPLE.COM/install.php ติดตั้งพร้อมเพิ่มผู้ใช้คือ admin ให้เรียบร้อย (create new user)
  5. ดูใน drupal เก่า ว่าเปิดใช้มอดูลอะไรบ้าง ก็ให้เปิดใช้ให้เหมือนเดิม ทั้งนี้เพื่อให้ Drupal สร้างตารางขึ้นมารองรับการโอน โดยใช้ URL: admin/build/modules เลือกกาทุกมอดูล (enable all modules)
  6. ล้างข้อมูลในตารางทั้งหมด รวมทั้งเป็นการสำเนาส่วนหัวของตารางใหม่ไปในตัว โดยใช้ pg_dump
    $ pg_dump -cs -U PG_USER NEW_DRUPAL_PG_DB > NEW_PG.SQL
  7. -c คือ --clean ล้างตารางเก่า ก่อนสร้างตารางใหม่
    -s คือ --schema-only เอาแต่ส่วนหัวของการสร้างตาราง ไม่เอาข้อมูล เพราะเราต้องการล้างอยู่แล้ว

  8. สร้างตารางใหม่จาก sql เมื่อกี้นี้ ด้วย psql
    $ psql -f NEW_PG.SQL -U PG_USER NEW_DRUPAL_PG_DB > temp.log
    ตรงนี้ไม่ควรมีข้อผิดพลาดขึ้น
  9. นำข้อมูลจากฐานข้อมูลเก่ามา โดยใช้ mysqldump
    $ mysqldump -c -t --skip-opt -u MY_USER -p OLD_DRUPAL_MY_DB > OLD_MY.SQL
    -c คือ complete insert เพื่อให้เหมาะกับการนำเข้าไปยัง postgresql
    -t คือ --no-create-info ไม่ต้องเขียนการสร้างตารางใหม่
    --skip-opt คือไม่ต้องเขียนการล๊อกตาราง
  10. ปรับแต่งข้อมูลให้เหมาะกับ postgresql โดยการลบ backquote (`) ทิ้ง
    $ vi OLD_MY.SQL
    :1,$ s/`//g
    :wq
  11. ถึงตรงนี้อาจนำเข้าข้อมูลได้แล้ว แต่จะมีข้อผิดพลาดจากโค๊ดของ Drupal ตรงตาราง node_revisions หน่อยนึง ตรงสดมถ์ log ที่ mysql ยอมให้เป็น NULL แต่ postgresql ไม่ยอม
    ต้องไปแก้ตาราง node_revisions สดมถ์ log ให้เป็น NULL ได้
    งานนี้ทำผ่าน phppgadmin (enable NULL in node_revisions.log)
  12. นำเข้าฐานข้อมูลใหม่ โดยใช้ psql
    $ psql -f OLD_MY.SQL -U PG_USER NEW_DRUPAL_PG_DB > temp.log
    ขั้นตอนนี้อาจมีข้อผิดพลาดรายงาน ไม่ต้องสนใจ
  13. แก้เรื่อง sequence ไม่ตรง โดยนำข้อมูลจาก phppgadmin ดูจากหน้า public/Sequences เอาทั้งหมดมาสร้างเป็น sql สมมุติตั้งชื่อว่า fixseq.sql
    $ vi fixseq.sql
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('access', 'aid'), (SELECT MAX(aid) FROM access), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('accesslog', 'aid'), (SELECT MAX(aid) FROM accesslog), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('aggregator_category', 'cid'), (SELECT MAX(cid) FROM aggregator_category), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('aggregator_feed', 'fid'), (SELECT MAX(fid) FROM aggregator_feed), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('aggregator_item', 'iid'), (SELECT MAX(iid) FROM aggregator_item), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('authmap', 'aid'), (SELECT MAX(aid) FROM authmap), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('boxes', 'bid'), (SELECT MAX(bid) FROM boxes), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('client', 'cid'), (SELECT MAX(cid) FROM client), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('comments', 'cid'), (SELECT MAX(cid) FROM comments), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('contact', 'cid'), (SELECT MAX(cid) FROM contact), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('files', 'fid'), (SELECT MAX(fid) FROM files), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('filter_formats', 'format'), (SELECT MAX(format) FROM filter_formats), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('locales_source', 'lid'), (SELECT MAX(lid) FROM locales_source), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('menu', 'mid'), (SELECT MAX(mid) FROM menu), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('node_comment_statistics', 'nid'), (SELECT MAX(nid) FROM node_comment_statistics), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('node', 'nid'), (SELECT MAX(nid) FROM node), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('node_revisions', 'vid'), (SELECT MAX(vid) FROM node_revisions), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('poll_choices', 'chid'), (SELECT MAX(chid) FROM poll_choices), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('profile_fields', 'fid'), (SELECT MAX(fid) FROM profile_fields), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('"role"', 'rid'), (SELECT MAX(rid) FROM role), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('term_data', 'tid'), (SELECT MAX(tid) FROM term_data), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('url_alias', 'pid'), (SELECT MAX(pid) FROM url_alias), false);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('users', 'uid'), (SELECT MAX(uid) FROM users), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('vocabulary', 'vid'), (SELECT MAX(vid) FROM vocabulary), true);
    SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('watchdog', 'wid'), (SELECT MAX(wid) FROM watchdog), true);

    สั่งรันด้วย psql ให้มาซ่อมเรื่อง sequence
    $ psql -f fixseq.sql -U PG_USER NEW_DRUPAL_PG_DB > temp.log

    ขั้นตอนนี้ไม่ควรมีข้อผิดพลาด

  14. อันนี้สำหรับถ้าติดตั้ง Drupal ไว้ในไดเรกทอรี่รองจากโดเมนหลัก (OPTIONAL: IF INSTALL DRUPAL IN SUBDIR) เช่น http://www.example.com/drupal และเปลี่ยนชื่อไดเรกทอรี่ด้วย
    ต้องปรับแก้ข้อมูลในตาราง locales_source ให้ข้อมูลเป็นอันใหม่
    เช่นอันเก่าชื่อ www.example.com/OLDDRUPAL อันใหม่ชื่อ www.example.com/NEWDRUPAL ต้องเขียน sql ดังนี้
    $ vi fixlocale.sql
    UPDATE locales_source SET location = '/NEWDRUPAL/' || substr(location,12) WHERE location LIKE '/OLDDRUPAL/%'

    ตัวเลข 12 ของฟังก์ชั่น substr(location, NUM) เป็นความยาวของอักขระไดเรกทอรี่เก่า คือ OLDDRUPAL บวก 3 คือเครื่องหมาย '/' หน้าหลังและบวกกับตำแหน่งถัดไป
    สั่งรันได้เลย
    $ psql -f fixlocale.sql -U PG_USER NEW_DRUPAL_PG_DB > temp.log

    ต้องไม่มีข้อผิดพลาด

เสร็จจริง ๆ แล้ว

จะมีรายงานข้อผิดพลาด ที่หน้า Administer หนึ่งครั้ง
และตอนสร้างเนื้อหาใหม่อีกหนึ่งครั้ง ก็สามารถใช้งานได้แล้ว

drupal6: ย้ายจาก MySQL เป็น PostgreSQL

บันทึกการย้ายฐานข้อมูล Drupal รุ่น 6.13 จาก MySQL-5.0.51a-24+lenny1 มาเป็น PostgreSQL-8.3.7-0lenny1

เที่ยวนี้ได้สคริปต์ดีจาก Drupal 6.x. site Migration from MySQL to PostgreSQL (howto) จึงสามารถทำตรงกับฐานข้อมูลได้เลย

  1. แบ๊คอัปกันเหนียวเสียก่อน
    $ mysqldump -u my_admin -p my_drupal_db > drupal.mysql.sql
  2. แก้ไข sites/default/settings.php ให้มาใช้ postgres
    $ vi sites/default/settings.php
    ...
    #$db_url = 'mysql://my_admin:my_password@localhost/my_drupal_db';
    $db_url = 'pgsql://pg_admin:pg_password@localhost/pg_drupal_db';
    ...
  3. เข้าไซต์ที่ใช้ฐานข้อมูลใหม่ สั่ง install ล๊อกอินเป็น admin พร้อมทั้งเปิดมอดูลทุกอัน
    http://www.example.com/install.php
    http://www.example.com/?q=user/login
    http://www.example.com/?q=admin/build/modules

    อย่าลืมเปิดมอดูลทุกอัน

  4. ดาวน์โหลดสคริปต์มาใช้งาน แตกไฟล์ และแก้ไขชื่อ admin พร้อมรหัสผ่านให้เรียบร้อย
    $ wget http://blog.lexa.ru/files/drupal-mysql2pgsql.pl.gz
    $ gunzip drupal-mysql2pgsql.pl.gz
    $ vi drupal-mysql2pgsql.pl
    ...
    #my $srcdb = DBI->connect("dbi:mysql:database=$ARGV[0]") or die;
    my $srcdb = DBI->connect("dbi:mysql:database=$ARGV[0]","my_admin","my_password") or die;
    #my $destdb = DBI->connect("dbi:Pg:dbname=$ARGV[1]") or die;
    my $destdb = DBI->connect("dbi:Pg:dbname=$ARGV[1]","pg_admin","pg_password") or die;
    ...
  5. สั่งรันสคริปต์
    $ ./drupal-mysql2pgsql.pl my_drupal_db pg_drupal_db

เสร็จแล้วครับ เข้าไซต์ที่ใช้ฐานข้อมูลใหม่ได้เลย