{"id":38146,"date":"2019-10-31T22:21:54","date_gmt":"2019-10-31T19:21:54","guid":{"rendered":"https:\/\/prohoster.info\/blog\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\/"},"modified":"2019-10-31T22:21:54","modified_gmt":"2019-10-31T19:21:54","slug":"perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","title":{"rendered":"Replikimi i kryq\u00ebzuar midis PostgreSQL dhe MySQL","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Replikimi i kryq\u00ebzuar midis PostgreSQL dhe MySQL\" src=\"\/wp-content\/uploads\/2019\/09\/47cf48f9024f406d625ecb6b21020c74.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Do t'ju jap nj\u00eb p\u00ebrshkrim t\u00eb shkurt\u00ebr rreth replikimit t\u00eb kryq\u00ebzuar midis PostgreSQL dhe MySQL, si dhe metodave p\u00ebr t\u00eb konfiguruar replikimin e kryq\u00ebzuar midis k\u00ebtyre dy server\u00ebve t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. N\u00eb p\u00ebrgjith\u00ebsi, bazat e t\u00eb dh\u00ebnave n\u00eb replikimin e kryq\u00ebzuar quhen homogjene, dhe kjo \u00ebsht\u00eb nj\u00eb metod\u00eb e p\u00ebrshtatshme p\u00ebr t\u00eb kaluar nga nj\u00eb server i sistemit t\u00eb menaxhimit t\u00eb t\u00eb dh\u00ebnave relacione n\u00eb nj\u00eb tjet\u00ebr.<\/p>\n<p><\/p>\n<p>Baza t\u00eb dh\u00ebnash PostgreSQL dhe MySQL zakonisht merren si relacionale, por me zgjerime t\u00eb tjera ato ofrojn\u00eb mund\u00ebsi NoSQL. K\u00ebtu do t\u00eb diskutojm\u00eb p\u00ebr replikimin midis PostgreSQL dhe MySQL, nga perspektiva e sistemetve t\u00eb menaxhimit t\u00eb t\u00eb dh\u00ebnave relacione.<\/p>\n<p><\/p>\n<p>Nuk do t\u00eb p\u00ebrshkruajm\u00eb t\u00eb gjith\u00eb metodologjin\u00eb e brendshme, por vet\u00ebm parimet baz\u00eb, p\u00ebr t\u00eb marr\u00eb nj\u00eb ide rreth konfigurimit t\u00eb replikimit midis server\u00ebve t\u00eb bazave t\u00eb t\u00eb dh\u00ebnave, avantazhet, kufizimet dhe skenar\u00ebt e p\u00ebrdorimit.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>Zakonisht, replikimi midis dy server\u00ebve identik\u00eb t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave realizohet ose n\u00eb modin binar, ose p\u00ebrmes k\u00ebrkesave midis nodit kryesor (i njohur gjithashtu si botues, kryesor ose aktiv) dhe nodit ndihm\u00ebs (abonues, prit\u00ebs ose pasiv). Q\u00ebllimi i replikimit \u00ebsht\u00eb t\u00eb ofroj\u00eb nj\u00eb kopje n\u00eb koh\u00eb reale t\u00eb baz\u00ebs kryesore t\u00eb t\u00eb dh\u00ebnave n\u00eb an\u00ebn e nodit ndihm\u00ebs. N\u00eb k\u00ebt\u00eb rast, t\u00eb dh\u00ebnat transmetohen nga nodi kryesor te ai ndihm\u00ebs, dometh\u00ebn\u00eb nga aktiv n\u00eb pasiv, sepse replikimi realizohet vet\u00ebm n\u00eb nj\u00eb drejtim. Por, \u00ebsht\u00eb e mundur t\u00eb konfigurohet replikimi midis dy bazave t\u00eb t\u00eb dh\u00ebnave n\u00eb t\u00eb dy drejtimet, duke lejuar q\u00eb t\u00eb dh\u00ebnat t\u00eb transmetohen nga nodi ndihm\u00ebs te nodi kryesor n\u00eb nj\u00eb konfigurim \"aktiv-aktiv\". T\u00eb gjitha k\u00ebto, p\u00ebrfshir\u00eb replikimin kaskad\u00eb, jan\u00eb t\u00eb mundshme midis dy ose m\u00eb shum\u00eb server\u00ebve identik\u00eb t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Konfigurimi \"aktiv-aktiv\" ose \"aktiv-pasiv\" varet nga nevojat, disponueshm\u00ebria e k\u00ebtyre mund\u00ebsive n\u00eb konfigurimin burimor ose p\u00ebrdorimi i zgjidhjeve t\u00eb jashtme p\u00ebr konfigurimin dhe kompromiset ekzistuese.<\/p>\n<p><\/p>\n<p>Konfigurimi i p\u00ebrshkruar \u00ebsht\u00eb i mundsh\u00ebm midis server\u00ebve t\u00eb ndrysh\u00ebm t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Nj\u00eb server mund t\u00eb konfigurohet p\u00ebr t\u00eb pranuar t\u00eb dh\u00ebna t\u00eb riprodukuara nga nj\u00eb server tjet\u00ebr i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe nj\u00ebkoh\u00ebsisht t\u00eb ruaj\u00eb skenat e t\u00eb dh\u00ebnave t\u00eb riprodukuara n\u00eb koh\u00eb reale. MySQL dhe PostgreSQL ofrojn\u00eb shumic\u00ebn e k\u00ebtyre konfigurimeve p\u00ebrmes mjeteve t\u00eb tyre ose me ndihm\u00ebn e zgjerimeve nga pal\u00ebt e treta, p\u00ebrfshir\u00eb metodat e regjistrit binar, bllokimit t\u00eb disqeve dhe metodat e bazuara n\u00eb operator\u00eb dhe rreshta.<\/p>\n<p><\/p>\n<p>Replikimi i nd\u00ebrmjet\u00ebm midis MySQL dhe PostgreSQL nevojitet p\u00ebr migruar nj\u00eb her\u00eb nga nj\u00eb server i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb nj\u00eb tjet\u00ebr. K\u00ebto baza t\u00eb t\u00eb dh\u00ebnave p\u00ebrdorin protokolle t\u00eb ndryshme, prandaj nuk \u00ebsht\u00eb e mundur t'i lidhni ato drejtp\u00ebrdrejt. N\u00eb m\u00ebnyr\u00eb p\u00ebr t\u00eb krijuar shk\u00ebmbimin e t\u00eb dh\u00ebnave, mund t\u00eb p\u00ebrdorni nj\u00eb mjet t\u00eb hapur, p\u00ebr shembull pg_chameleon.<\/p>\n<p><\/p>\n<h3 id=\"chto-takoe-pg_chameleon\">\u00c7far\u00eb \u00ebsht\u00eb pg_chameleon<\/h3>\n<p><\/p>\n<p>pg_chameleon \u00ebsht\u00eb nj\u00eb sistem replikimi nga MySQL n\u00eb PostgreSQL i nd\u00ebrtuar n\u00eb Python 3. P\u00ebrdor bibliotek\u00ebn e hapur mysql-replication, gjithashtu n\u00eb Python. Rreshtat e t\u00eb dh\u00ebnave nxirren nga tabelat MySQL dhe ruajten si objekte JSONB n\u00eb databaz\u00ebn PostgreSQL, e m\u00eb pas deshifrohen nga funksioni pl\/pgsql dhe riprodhohen n\u00eb databaz\u00ebn PostgreSQL.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-pg_chameleon\">Aft\u00ebsit\u00eb e pg_chameleon<\/h3>\n<p><\/p>\n<p>Mund t\u00eb replikoni disa skema MySQL nga nj\u00eb grup n\u00eb nj\u00eb databaz\u00eb t\u00eb vetme PostgreSQL me konfigurimin 'nj\u00eb n\u00eb shum\u00eb'<br \/>\nEmrat e skemave burimore dhe t\u00eb synimeve nuk mund t\u00eb p\u00ebrputhen.<br \/>\nT\u00eb dh\u00ebnat e replikimit mund t\u00eb nxirren nga nj\u00eb replik\u00eb kaskad\u00eb MySQL.<br \/>\nTabelat q\u00eb nuk mund t\u00eb replikohen ose q\u00eb shkaktojn\u00eb gabime p\u00ebrjashtohen.<br \/>\n\u00c7do funksion replikimi kontrollohet nga demon\u00ebt.<br \/>\nKontrolli me an\u00eb t\u00eb parametrave dhe skedar\u00ebve t\u00eb konfigurimit n\u00eb baz\u00eb YAML.<\/p>\n<p><\/p>\n<h3 id=\"primer\">Shembulli<\/h3>\n<p><\/p>\n<p>Host<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Versioni i OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.5 x86_64<\/p>\n<p><strong>Versioni i serverit t\u00eb B.D.<\/strong><br \/>\nMySQL 5.7.26<br \/>\nPostgreSQL 10.5<\/p>\n<p><strong>Porti i B.D.<\/strong><br \/>\n3306<br \/>\n5433<\/p>\n<p><strong>Adresa IP<\/strong><br \/>\n192.168.56.102<br \/>\n192.168.56.106<\/p>\n<p><\/p>\n<p>S\u00eb pari p\u00ebrgatitni t\u00eb gjitha komponent\u00ebt e nevojsh\u00ebm p\u00ebr instalimin e pg_chameleon. N\u00eb k\u00ebt\u00eb shembull \u00ebsht\u00eb instaluar Python 3.6.8, e cila krijon nj\u00eb ambient virtual dhe e aktivizon at\u00eb.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; wget https:\/\/www.python.org\/ftp\/python\/3.6.8\/Python-3.6.8.tar.xz\n$&gt; tar -xJf Python-3.6.8.tar.xz\n$&gt; cd Python-3.6.8\n$&gt; .\/configure --enable-optimizations\n$&gt; make altinstall<\/code><\/pre>\n<p><\/p>\n<p>Pas instalimit t\u00eb suksessh\u00ebm t\u00eb Python 3.6 duhet t\u00eb p\u00ebrmbushen k\u00ebrkesat e tjera, si\u00e7 \u00ebsht\u00eb krijimi dhe aktivizimi i ambientit virtual. Po ashtu, moduli pip p\u00ebrmir\u00ebsohet n\u00eb versionin e fundit dhe p\u00ebrdoret p\u00ebr instalimin e pg_chameleon. N\u00eb komandat m\u00eb posht\u00eb q\u00ebllimisht instalohet pg_chameleon 2.0.9, edhe pse versioni i fundit \u00ebsht\u00eb 2.0.10. Kjo b\u00ebhet p\u00ebr t\u00eb shmangur gabimet e reja n\u00eb versionin e azhurnuar.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; python3.6 -m venv venv\n$&gt; source venv\/bin\/activate\n(venv) $&gt; pip install pip --upgrade\n(venv) $&gt; pip install pg_chameleon==2.0.9<\/code><\/pre>\n<p><\/p>\n<p>Pastaj th\u00ebrrasim pg_chameleon (chameleon \u00ebsht\u00eb komanda) me argumentin set_configuration_files, p\u00ebr t\u00eb aktivizuar pg_chameleon dhe p\u00ebr t\u00eb krijuar katalog\u00ebt dhe skedar\u00ebt e konfigurimit nga pik\u00ebpamja e default-it.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">(venv) $&gt; chameleon set_configuration_files\ncreating directory \/root\/.pg_chameleon\ncreating directory \/root\/.pg_chameleon\/configuration\/\ncreating directory \/root\/.pg_chameleon\/logs\/\ncreating directory \/root\/.pg_chameleon\/pid\/\ncoping configuration example in \/root\/.pg_chameleon\/configuration\/\/config-example.yml<\/code><\/pre>\n<p><\/p>\n<p>Tani tani po e krijojm\u00eb nj\u00eb kopje t\u00eb config-example.yml si default.yml, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb b\u00ebhet skedari i konfigurimit t\u00eb parazgjedhur. Nj\u00eb most\u00ebr e skedarit t\u00eb konfigurimit p\u00ebr k\u00ebt\u00eb shembull \u00ebsht\u00eb dh\u00ebn\u00eb m\u00eb posht\u00eb.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat default.yml\n---\n#vendosjet globale\npid_dir: '~\/.pg_chameleon\/pid\/'\nlog_dir: '~\/.pg_chameleon\/logs\/'\nlog_dest: file\nlog_level: info\nlog_days_keep: 10\nrollbar_key: ''\nrollbar_env: ''\n\n# type_override lejon p\u00ebrdoruesin t\u00eb anashkaloj\u00eb konvertimin e parazgjedhur t\u00eb tipit n\u00eb nj\u00eb tjet\u00ebr.\ntype_override:\n  \"tinyint(1)\":\n    override_to: boolean\n    override_tables:\n      - \"*\"\n\n# lidhja e destinacionit postgres\npg_conn:\n  host: \"192.168.56.106\"\n  port: \"5433\"\n  user: \"usr_replica\"\n  password: \"pass123\"\n  database: \"db_replica\"\n  charset: \"utf8\"\n\nsources:\n  mysql:\n    db_conn:\n      host: \"192.168.56.102\"\n      port: \"3306\"\n      user: \"usr_replica\"\n      password: \"pass123\"\n      charset: 'utf8'\n      connect_timeout: 10\n    schema_mappings:\n      world_x: pgworld_x\n    limit_tables:\n#      - delphis_mediterranea.foo\n    skip_tables:\n#      - delphis_mediterranea.bar\n    grant_select_to:\n      - usr_readonly\n    lock_timeout: \"120s\"\n    my_server_id: 100\n    replica_batch_size: 10000\n    replay_max_rows: 10000\n    batch_retention: '1 dit\u00eb'\n    copy_max_memory: \"300M\"\n    copy_mode: 'file'\n    out_dir: \/tmp\n    sleep_loop: 1\n    on_error_replay: continue\n    on_error_read: continue\n    auto_maintenance: \"disabled\"\n    gtid_enable: No\n    type: mysql\n    skip_events:\n      insert:\n        - delphis_mediterranea.foo #anashkalon futjet n\u00eb tabel\u00ebn delphis_mediterranea.foo\n      delete:\n        - delphis_mediterranea #anashkalon fshirjet n\u00eb skem\u00ebn delphis_mediterranea\n      update:<\/code><\/pre>\n<p><\/p>\n<p>Skedari i konfigurimit n\u00eb k\u00ebt\u00eb shembull \u00ebsht\u00eb nj\u00eb most\u00ebr e skedarit me pg_chameleon me ndryshime t\u00eb vogla sipas mjeteve burimore dhe t\u00eb synuara, dhe m\u00eb posht\u00eb \u00ebsht\u00eb nj\u00eb p\u00ebrmbledhje e seksioneve t\u00eb ndryshme t\u00eb skedarit t\u00eb konfigurimit.<\/p>\n<p><\/p>\n<p>N\u00eb skedarin e konfigurimit default.yml ka nj\u00eb seksion p\u00ebr vendosjet globale (global settings), ku mund t\u00eb menaxhoni cil\u00ebsime t\u00eb tilla si vendndodhja e skedarit t\u00eb bllokimit, vendndodhja e log\u00ebve, periudha e ruajtjes s\u00eb log\u00ebve etj. M\u00eb pas vjen seksioni p\u00ebr anashkalimin e tipeve (type override), ku specifikohen nj\u00eb s\u00ebr\u00eb rregullash p\u00ebr t\u00eb anashkaluar tipet gjat\u00eb replikimit. N\u00eb shembullin e parazgjedhur p\u00ebrdoret rregulli i anashkalimit t\u00eb tipi q\u00eb konverton tinyint(1) n\u00eb nj\u00eb vler\u00eb logjike. N\u00eb seksionin e ardhsh\u00ebm hapim detajet e lidhjes me baz\u00ebn e t\u00eb dh\u00ebnave t\u00eb synuar. N\u00eb rastin ton\u00eb kjo \u00ebsht\u00eb baza e t\u00eb dh\u00ebnave PostgreSQL, e quajtur pg_conn. N\u00eb seksionin e fundit specifikojm\u00eb t\u00eb dh\u00ebnat e burimit, pra parametrat e lidhjes p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave burimore, hartimin e skemave t\u00eb bazave t\u00eb t\u00eb dh\u00ebnave burimore dhe t\u00eb synuara, tabelat q\u00eb duhet t\u00eb anashkalohen, koha e pritjes, memorja, madh\u00ebsia e grumbullit. Vini re se \"sources\" \u00ebsht\u00eb e specifikuar n\u00eb shum\u00ebs, dmth ne mund t\u00eb shtojm\u00eb disa baza t\u00eb t\u00eb dh\u00ebnave burimore p\u00ebr nj\u00eb t\u00eb vetme, p\u00ebr t\u00eb konfiguruar nj\u00eb konfigurim \"shum\u00eb n\u00eb nj\u00eb\".<\/p>\n<p><\/p>\n<p>Database world_x in the example contains 4 tables with rows that the MySQL community offers as a sample. It can be downloaded <noindex><a rel=\"nofollow\" href=\"https:\/\/dev.mysql.com\/doc\/index-other.html\">k\u00ebtu<\/a><\/noindex>. The sample database comes as a tar file and a compressed archive with instructions for creating and importing the rows.<\/p>\n<p><\/p>\n<p>In MySQL and PostgreSQL databases, a special user with the same name usr_replica is created. In MySQL, it is granted additional rights to read all replicated tables.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; CREATE USER usr_replica ;\nmysql&gt; SET PASSWORD FOR usr_replica='pass123';\nmysql&gt; GRANT ALL ON world_x.* TO 'usr_replica';\nmysql&gt; GRANT RELOAD ON *.* to 'usr_replica';\nmysql&gt; GRANT REPLICATION CLIENT ON *.* to 'usr_replica';\nmysql&gt; GRANT REPLICATION SLAVE ON *.* to 'usr_replica';\nmysql&gt; FLUSH PRIVILEGES;<\/code><\/pre>\n<p><\/p>\n<p>On the PostgreSQL side, a database db_replica is created, which will accept changes from the MySQL database. The user usr_replica in PostgreSQL is automatically configured as the owner of the two schemas pgworld_x and sch_chameleon, which contain the actual replicated tables and the replication catalog tables, respectively. The automatic configuration is handled by the create_replica_schema argument, as you will see below.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">postgres=# CREATE USER usr_replica WITH PASSWORD 'pass123';\nCREATE ROLE\npostgres=# CREATE DATABASE db_replica WITH OWNER usr_replica;\nCREATE DATABASE<\/code><\/pre>\n<p><\/p>\n<p>The MySQL database is configured with some parameter changes to prepare it for replication, as shown below. The database server will need to be restarted for the changes to take effect.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; vi \/etc\/my.cnf\nbinlog_format= ROW\nbinlog_row_image=FULL\nlog-bin = mysql-bin\nserver-id = 1<\/code><\/pre>\n<p><\/p>\n<p>It is now important to check the connection to both database servers to avoid issues when executing pg_chameleon commands.<\/p>\n<p><\/p>\n<p>On the PostgreSQL node:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; mysql -u usr_replica -Ap'admin123' -h 192.168.56.102 -D world_x<\/code><\/pre>\n<p><\/p>\n<p>On the MySQL node:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; psql -p 5433 -U usr_replica -h 192.168.56.106 db_replica<\/code><\/pre>\n<p><\/p>\n<p>The following three pg_chameleon commands (chameleon) prepare the environment, add the source, and initialize the replica. The create_replica_schema argument in pg_chameleon creates the default schema (sch_chameleon) and the replication schema (pgworld_x) in the PostgreSQL database, as we mentioned. The add_source argument adds the source database to the configuration by reading the configuration file (default.yml), and in our case, this is mysql, and init_replica initializes the configuration based on the parameters in the configuration file.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon create_replica_schema --debug\n$&gt; chameleon add_source --config default --source mysql --debug\n$&gt; chameleon init_replica --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<p>Daljet e k\u00ebtyre tre komandave qart\u00eb tregojn\u00eb kryerjen e suksesshme t\u00eb tyre. T\u00eb gjitha d\u00ebshtimet ose gabimet sintaksore shfaqen n\u00eb mesazhe t\u00eb thjeshta dhe kuptimplota me tregues p\u00ebr rregullimin e problemeve.<\/p>\n<p><\/p>\n<p>M\u00eb n\u00eb fund, do ta fillojm\u00eb replikimin duke p\u00ebrdorur start_replica dhe do t\u00eb marrim nj\u00eb mesazh p\u00ebr p\u00ebrfundimin e suksessh\u00ebm.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon start_replica --config default --source mysql \noutput: Duke filluar procesin e replik\u00ebs p\u00ebr burimin mysql<\/code><\/pre>\n<p><\/p>\n<p>Statusi i replikimit mund t\u00eb k\u00ebrkohet me arg\u00ebmentin show_status, nd\u00ebrsa gabimet mund t\u00eb shihen duke p\u00ebrdorur arg\u00ebmentin show_errors.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/iYMdAf0b\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Si\u00e7 e tham\u00eb m\u00eb par\u00eb, \u00e7do funksion replikimi menaxhohet nga demon\u00ebt. P\u00ebr t\u00eb par\u00eb ata, do ta k\u00ebrkojm\u00eb tabel\u00ebn e proceseve me komand\u00ebn Linux ps, si\u00e7 \u00ebsht\u00eb e treguar m\u00eb posht\u00eb.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/59GfdvHA\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Replikimi nuk konsiderohet se \u00ebsht\u00eb konfiguruar deri sa ta testojm\u00eb n\u00eb koh\u00eb reale, si\u00e7 \u00ebsht\u00eb treguar m\u00eb posht\u00eb. Ne krijojm\u00eb nj\u00eb tabel\u00eb, futim disa regjistrime n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave MySQL dhe th\u00ebrrasim arg\u00fcmentin sync_tables n\u00eb pg_chameleon p\u00ebr t\u00eb p\u00ebrdit\u00ebsuar demon\u00ebt dhe p\u00ebr t\u00eb replikuar tabel\u00ebn me regjistrimet n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table t1 (n1 int primary key, n2 varchar(10));\nQuery OK, 0 rows affected (0.01 sec)\nmysql&gt; insert into t1 values (1,'one');\nQuery OK, 1 row affected (0.00 sec)\nmysql&gt; insert into t1 values (2,'two');\nQuery OK, 1 row affected (0.00 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon sync_tables --tables world_x.t1 --config default --source mysql\nProcesi i sinkronizimit t\u00eb tabelave p\u00ebr burimin mysql ka filluar.<\/code><\/pre>\n<p><\/p>\n<p>P\u00ebr t\u00eb konfirmuar rezultate t\u00eb prov\u00ebs, k\u00ebrkojm\u00eb tabel\u00ebn nga baza e t\u00eb dh\u00ebnave PostgreSQL dhe nxjerrim rreshtat.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; psql -p 5433 -U usr_replica -d db_replica -c \"select * from pgworld_x.t1\";\n n1 |  n2\n----+-------\n  1 | one\n  2 | two<\/code><\/pre>\n<p><\/p>\n<p>N\u00ebse po kryejm\u00eb migrimin, komandat e ardhshme t\u00eb pg_chameleon do ta mbyllin at\u00eb. Komandat duhet t\u00eb ekzekutohen pasi t\u00eb jemi t\u00eb sigurt q\u00eb rreshtat e t\u00eb gjitha tabelave t\u00eb q\u00ebllimit jan\u00eb replikuar dhe rezultati do t\u00eb jet\u00eb nj\u00eb baz\u00eb e dh\u00ebnash PostgreSQL e transferuar n\u00eb m\u00ebnyr\u00eb t\u00eb sakt\u00eb pa lidhje me baz\u00ebn e t\u00eb dh\u00ebnave origjinale ose skem\u00ebn e replikimit (sch_chameleon).<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon stop_replica --config default --source mysql \n$&gt; chameleon detach_replica --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<p>Me d\u00ebshir\u00eb, komandat e m\u00ebposhtme mund t\u00eb p\u00ebrdoren p\u00ebr t\u00eb fshir\u00eb konfigurimin p\u00ebr burimin dhe skem\u00ebn e replikimit.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; chameleon drop_source --config default --source mysql --debug\n$&gt; chameleon drop_replica_schema --config default --source mysql --debug<\/code><\/pre>\n<p><\/p>\n<h3 id=\"preimuschestva-pg_chameleon\">P\u00ebrfitimet e pg_chameleon<\/h3>\n<p><\/p>\n<p>Konfigurim dhe vendosje t\u00eb thjeshta.<br \/>\nDiagnoza e leht\u00eb dhe identifikimi i anomalis\u00eb me mesazhe t\u00eb qarta gabimi.<br \/>\nTabela t\u00eb tjera speciale mund t\u00eb shtohen n\u00eb replikim pas fillimit, pa ndryshuar konfigurimin e mbetur.<br \/>\n\u00cbsht\u00eb e mundur t\u00eb konfigurohen disa burime t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave p\u00ebr nj\u00eb destinacion, dhe kjo \u00ebsht\u00eb shum\u00eb e p\u00ebrshtatshme n\u00ebse po bashkoni t\u00eb dh\u00ebna nga nj\u00eb ose m\u00eb shum\u00eb baza t\u00eb dh\u00ebnash MySQL n\u00eb nj\u00eb baz\u00eb t\u00eb dh\u00ebnash PostgreSQL.<br \/>\nMund t\u00eb mos replikoni tabelat e zgjedhura.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-pg_chameleon\">Disavantazhet e pg_chameleon<\/h3>\n<p><\/p>\n<p>P\u00ebrkrahja ofrohet vet\u00ebm p\u00ebr MySQL 5.5 e lart si burim dhe PostgreSQL 9.5 e lart si baz\u00eb t\u00eb dh\u00ebnash destinacion.<br \/>\n\u00c7do tabel\u00eb duhet t\u00eb ket\u00eb nj\u00eb \u00e7el\u00ebs primar ose unik, p\u00ebrndryshe tabelat inicializohen gjat\u00eb procesit init_replica, por nuk replikohen.<br \/>\nReplikimi nj\u00ebdrejtimor \u2014 vet\u00ebm nga MySQL n\u00eb PostgreSQL. Prandaj, \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm vet\u00ebm p\u00ebr skem\u00ebn \"aktiv-passiv\".<br \/>\nBurimi mund t\u00eb jet\u00eb vet\u00ebm nj\u00eb baz\u00eb t\u00eb dh\u00ebnash MySQL, nd\u00ebrsa mb\u00ebshtetje p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL si burim \u00ebsht\u00eb vet\u00ebm experimental dhe me kufizime (merrni informacion m\u00eb shum\u00eb) <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/configuration_file.html#postgresql-source-type-experimental\">k\u00ebtu<\/a><\/noindex>)<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-pg_chameleon\">P\u00ebrfundimet p\u00ebr pg_chameleon<\/h3>\n<p><\/p>\n<p>Metoda e replikimit n\u00eb pg_chameleon \u00ebsht\u00eb shum\u00eb e p\u00ebrshtatshme p\u00ebr migrimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave nga MySQL n\u00eb PostgreSQL. Nj\u00eb disavantazh i duksh\u00ebm \u00ebsht\u00eb se replikimi \u00ebsht\u00eb vet\u00ebm nj\u00ebdrejtimor, prandaj specialist\u00ebt e bazave t\u00eb dh\u00ebnash nuk do t\u00eb d\u00ebshirojn\u00eb ta p\u00ebrdorin at\u00eb p\u00ebr ndonj\u00eb gj\u00eb tjet\u00ebr p\u00ebrve\u00e7 migrimit. Por problemi i replikimit nj\u00ebdrejtimor mund t\u00eb zgjidh\u00ebt me nj\u00eb mjet tjet\u00ebr me burim t\u00eb hapur \u2014 SymmetricDS.<\/p>\n<p><\/p>\n<p>Lexoni m\u00eb shum\u00eb n\u00eb dokumentacionin zyrtar <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/\">k\u00ebtu<\/a><\/noindex>. Informacionin p\u00ebr komand\u00ebn e linj\u00ebs mund ta gjeni <noindex><a rel=\"nofollow\" href=\"https:\/\/pgchameleon.org\/documents\/usage.html#https:\/\/pgchameleon.org\/documents\/usage.html\">k\u00ebtu<\/a><\/noindex>.<\/p>\n<p><\/p>\n<h3 id=\"obzor-symmetricds\">P\u00ebrmbledhja e SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS \u00ebsht\u00eb nj\u00eb mjet me burim t\u00eb hapur q\u00eb replikon \u00e7do baz\u00eb t\u00eb dh\u00ebnash n\u00eb \u00e7do baz\u00eb t\u00eb njohur t\u00eb dh\u00ebnash t\u00eb tjera: Oracle, MongoDB, PostgreSQL, MySQL, SQL Server, MariaDB, DB2, Sybase, Greenplum, Informix, H2, Firebird dhe instance t\u00eb tjera t\u00eb baz\u00ebs s\u00eb dh\u00ebnash n\u00eb cloud, si Redshift, dhe Azure etj. Funksionet e disponueshme: sinkronizimi i bazave t\u00eb dh\u00ebnash dhe skedareve, replikimi i disa bazave t\u00eb dh\u00ebnash kryesore, sinkronizimi i filtruar, transformimi dhe t\u00eb tjera. Ky \u00ebsht\u00eb nj\u00eb mjet n\u00eb Java dhe k\u00ebrkon nj\u00eb version standard t\u00eb JRE ose JDK (versionet 8.0 e lart). K\u00ebtu mund t\u00eb regjistroni ndryshimet e t\u00eb dh\u00ebnave me an\u00eb t\u00eb triggereve n\u00eb burimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe t'i drejtoni ato n\u00eb baz\u00ebn e dh\u00ebnash p\u00ebrkat\u00ebse si paketa.<\/p>\n<p><\/p>\n<h3 id=\"vozmozhnosti-symmetricds\">Mund\u00ebsit\u00eb e SymmetricDS<\/h3>\n<p><\/p>\n<p>Mjeti nuk varet nga platforma, dmth dy ose m\u00eb shum\u00eb baza t\u00eb ndryshme t\u00eb dh\u00ebnash mund t\u00eb ndajn\u00eb t\u00eb dh\u00ebna.<br \/>\nBaza t\u00eb dh\u00ebnash relacionalet sinkronizohen p\u00ebrmes regjistrimit t\u00eb ndryshimeve t\u00eb t\u00eb dh\u00ebnave, nd\u00ebrsa bazat e dh\u00ebnash t\u00eb bazuara n\u00eb sisteme skedari p\u00ebrdorin sinkronizimin e skedareve.<br \/>\nReplikimi dyansh\u00ebm duke p\u00ebrdorur metodat Push dhe Pull mbi baz\u00ebn e nj\u00eb grupi rregullash.<br \/>\nTransferimi i t\u00eb dh\u00ebnave \u00ebsht\u00eb i mundur p\u00ebrmes rrjeteve t\u00eb mbrojtura dhe rrjeteve me kapacitet t\u00eb ul\u00ebt kalimi.<br \/>\nRind\u00ebrtimi automatik kur rinisja e nyjave pas nj\u00eb d\u00ebshtimi dhe zgjidhja automatik e konflikteve.<br \/>\nP\u00ebrputhshm\u00ebria me cloud dhe API-t\u00eb e efikase t\u00eb zgjerimit.<\/p>\n<p><\/p>\n<h3 id=\"primer-1\">Shembulli<\/h3>\n<p><\/p>\n<p>SymmetricDS mund t\u00eb konfigurohet n\u00eb nj\u00eb nga dy variante:<br \/>\nNj\u00eb nyje kryesore (prind) e cila koordinon n\u00eb m\u00ebnyr\u00eb centralizuar replikimin e t\u00eb dh\u00ebnave midis dy nyjave n\u00ebnordh\u00ebruese (f\u00ebmij\u00eb), dhe shk\u00ebmbimi i t\u00eb dh\u00ebnave midis nyjave f\u00ebmij\u00eb b\u00ebhet vet\u00ebm p\u00ebrmes prindit.<br \/>\nNj\u00eb nyje aktive (nyja 1) mund t\u00eb shk\u00ebmbej\u00eb t\u00eb dh\u00ebna p\u00ebr replikim me nj\u00eb nyje tjet\u00ebr aktive (nyja 2) pa nd\u00ebrmjet\u00ebs.<\/p>\n<p><\/p>\n<p>N\u00eb t\u00eb dy variantet, shk\u00ebmbimi i t\u00eb dh\u00ebnave ndodh p\u00ebrmes Push dhe Pull. N\u00eb k\u00ebt\u00eb shembull do t\u00eb shqyrtojm\u00eb konfigurimin 'aktiv-aktiv'. T\u00eb p\u00ebrshkruash t\u00ebr\u00eb arkitektur\u00ebn \u00ebsht\u00eb shum\u00eb e gjat\u00eb, k\u00ebshtu q\u00eb studio <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.10\/html\/user-guide.html#_architecture\">manual<\/a><\/noindex>, p\u00ebr t\u00eb m\u00ebsuar m\u00eb shum\u00eb rreth pajisjes SymmetricDS.<\/p>\n<p><\/p>\n<p>Instalimi i SymmetricDS \u00ebsht\u00eb shum\u00eb i thjesht\u00eb: shkarkoni versionin open-source t\u00eb skedarit zip <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/download\">k\u00ebtu<\/a><\/noindex> dhe nxirrni at\u00eb, kudo q\u00eb d\u00ebshiron. Tabela m\u00eb posht\u00eb jep informacion mbi vendin e instalimit dhe versionin e SymmetricDS n\u00eb k\u00ebt\u00eb shembull, si dhe versionet e bazave t\u00eb t\u00eb dh\u00ebnave, versionet e Linux, adresat IP dhe portet p\u00ebr t\u00eb dy nyjat.<\/p>\n<p><\/p>\n<p>Host<br \/>\nvm1<br \/>\nvm2<\/p>\n<p><strong>Versioni i OS<\/strong><br \/>\nCentOS Linux 7.6 x86_64<br \/>\nCentOS Linux 7.6 x86_64<\/p>\n<p><strong>Versioni i serverit t\u00eb B.D.<\/strong><br \/>\nMySQL 5.7.26<br \/>\nPostgreSQL 10.5<\/p>\n<p><strong>Porti i B.D.<\/strong><br \/>\n3306<br \/>\n5832<\/p>\n<p><strong>Adresa IP<\/strong><br \/>\n192.168.1.107<br \/>\n192.168.1.112<\/p>\n<p><strong>Versioni i SymmetricDS<\/strong><br \/>\nSymmetricDS 3.9<br \/>\nSymmetricDS 3.9<\/p>\n<p><strong>Rruga e instalimit t\u00eb SymmetricDS<\/strong><br \/>\n\/usr\/local\/symmetric-server-3.9.20<br \/>\n\/usr\/local\/symmetric-server-3.9.20<\/p>\n<p><strong>Emri i nyj\u00ebs s\u00eb SymmetricDS<\/strong><br \/>\ncorp-000<br \/>\nstore-001<\/p>\n<p><\/p>\n<p>K\u00ebtu po instalojm\u00eb SymmetricDS n\u00eb \/usr\/local\/symmetric-server-3.9.20, dhe k\u00ebtu do t\u00eb ruhet nj\u00eb gam\u00eb e ndryshme e katalog\u00ebve dhe skedar\u00ebve t\u00eb ndrysh\u00ebm. Na interesojn\u00eb katalog\u00ebt e brendsh\u00ebm samples dhe engines. N\u00eb katalogun samples jan\u00eb skedar\u00ebt e konfigurimit me cil\u00ebsit\u00eb e nyj\u00ebs, si dhe shembujt e skenar\u00ebve SQL p\u00ebr fillimin e shpejt\u00eb t\u00eb demonstrimit.<\/p>\n<p><\/p>\n<p>N\u00eb katalogun samples shohim tre skedar\u00eb konfigurimi me cil\u00ebsit\u00eb e nyj\u00ebs - emri tregon natyr\u00ebn e nyj\u00ebs n\u00eb nj\u00eb skem\u00eb t\u00eb caktuar.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">corp-000.properties\nstore-001.properties\nstore-002.properties<\/code><\/pre>\n<p><\/p>\n<p>N\u00eb SymmetricDS ka t\u00eb gjitha skedar\u00ebt e nevojsh\u00ebm t\u00eb konfigurimit p\u00ebr nj\u00eb skem\u00eb themelore me 3 nyje (varianti 1), dhe t\u00eb nj\u00ebjtit skedar\u00eb mund t\u00eb p\u00ebrdoren p\u00ebr nj\u00eb skem\u00eb me 2 nyje (varianti 2). Kopjojm\u00eb skedarin e nevojsh\u00ebm t\u00eb konfigurimit nga katalogu samples n\u00eb engines n\u00eb hostin vm1. Ky \u00ebsht\u00eb rezultati:<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat engines\/corp-000.properties\nengine.name=corp-000\ndb.driver=com.mysql.jdbc.Driver\ndb.url=jdbc:mysql:\/\/192.168.1.107:3306\/replica_db?autoReconnect=true&amp;useSSL=false\ndb.user=root\ndb.password=admin123\nregistration.url=\nsync.url=http:\/\/192.168.1.107:31415\/sync\/corp-000\ngroup.id=corp\nexternal.id=000<\/code><\/pre>\n<p><\/p>\n<p>Ky ky\u00e7 n\u00eb konfigurimin e SymmetricDS quhet corp-000, nd\u00ebrsa lidhja me baz\u00ebn e t\u00eb dh\u00ebnave p\u00ebrpunohen nga piloti mysql jdbc, i cili p\u00ebrdor stringun e lidhjes t\u00eb specifikuar m\u00eb lart dhe akreditimet p\u00ebr hyrje. Ne lidhemi me baz\u00ebn e t\u00eb dh\u00ebnave replica_db, dhe gjat\u00eb krijimit t\u00eb skem\u00ebs do t\u00eb krijohen tabelat. sync.url tregon vendin e nd\u00ebrlidhjes me nyj\u00ebn p\u00ebr sinkronizim.<\/p>\n<p><\/p>\n<p>Nyja 2 n\u00eb hostin vm2 konfigurohet si store-001, dhe e gjith\u00eb e tjera \u00ebsht\u00eb e specifikuar n\u00eb skedarin node.properties, i cili jepet m\u00eb posht\u00eb. Nyja store-001 ekzekuton baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL, nd\u00ebrsa pgdb_replica \u00ebsht\u00eb baza e t\u00eb dh\u00ebnave p\u00ebr replikim. registration.url lejon hostin vm2 t\u00eb lidhet me hostin vm1 dhe t\u00eb marr\u00eb nga ai detajet e konfigurimit.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">$&gt; cat engines\/store-001.properties\nengine.name=store-001\ndb.driver=org.postgresql.Driver\ndb.url=jdbc:postgresql:\/\/192.168.1.112:5832\/pgdb_replica\ndb.user=postgres\ndb.password=admin123\nregistration.url=http:\/\/192.168.1.107:31415\/sync\/corp-000\ngroup.id=store\nexternal.id=001<\/code><\/pre>\n<p><\/p>\n<p>Nj\u00eb shembull i gatsh\u00ebm i SymmetricDS p\u00ebrmban parametra p\u00ebr konfigurimin e replikimit dyansh\u00ebm midis dy server\u00ebve t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave (dy nyjave). Hapat e m\u00ebposht\u00ebm ekzekutohen n\u00eb hostin vm1 (corp-000), i cili do t\u00eb krijoj\u00eb nj\u00eb shembull skem\u00eb me 4 tabela. M\u00eb pas, ekzekutimi i create-sym-tables me komand\u00ebn symadmin krijon tabelat e katalog\u00ebve, ku do t\u00eb ruhen rregullat dhe drejtimi i replikimit midis nyjave. S\u00eb fundi, t\u00eb dh\u00ebna shembuj ngarkohen n\u00eb tabelat.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm1$&gt; .\/dbimport --engine corp-000 --format XML create_sample.xml\nvm1$&gt; .\/symadmin --engine corp-000 create-sym-tables\nvm1$&gt; .\/dbimport --engine corp-000 insert_sample.sql<\/code><\/pre>\n<p><\/p>\n<p>N\u00eb this shembull, tabelat item dhe item_selling_price jan\u00eb konfigurur automatikisht p\u00ebr replikim nga corp-000 n\u00eb store-001, dhe tabelat sale (sale_transaction dhe sale_return_line_item) jan\u00eb konfigurur automatikisht p\u00ebr replikim nga store-001 n\u00eb corp-000. Tani krijojm\u00eb skem\u00ebn n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL n\u00eb hostin vm2 (store-001), p\u00ebr ta p\u00ebrgatitur p\u00ebr pranimin e t\u00eb dh\u00ebnave nga corp-000.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm2$&gt; .\/dbimport --engine store-001 --format XML create_sample.xml<\/code><\/pre>\n<p><\/p>\n<p>Sigurohuni t\u00eb kontrolloni q\u00eb n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave MySQL n\u00eb vm1 ka tabela shembuj dhe tabelat e katalog\u00ebve t\u00eb SymmetricDS. Vini re se tabelat sistemike t\u00eb SymmetricDS (me prefiksin sym_) tani jan\u00eb t\u00eb disponuesh\u00ebm vet\u00ebm n\u00eb nyj\u00ebn corp-000, sepse atje ekzekutuam komand\u00ebn create-sym-tables dhe do t\u00eb administrojm\u00eb replikimin. P\u00ebr m\u00eb tep\u00ebr, n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave n\u00eb nyj\u00ebn store-001 do t\u00eb ket\u00eb vet\u00ebm 4 tabela shembuj pa t\u00eb dh\u00ebna.<\/p>\n<p><\/p>\n<p>T\u00eb gjitha. Mjedisi \u00ebsht\u00eb i gatsh\u00ebm p\u00ebr t\u00eb nisur proceset e serverit sym n\u00eb t\u00eb dy nyjat, si\u00e7 tregohet m\u00eb posht\u00eb.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm1$&gt; sym 2&gt;&amp;1 &amp;<\/code><\/pre>\n<p><\/p>\n<p>Regjistrimet e logeve d\u00ebrgohen n\u00eb skedarin e logut t\u00eb prapavij\u00ebs (symmetric.log) n\u00eb dosjen e logeve n\u00eb katalogun ku \u00ebsht\u00eb instaluar SymmetricDS, si dhe n\u00eb daljet standarde. Serveri sym tani mund t\u00eb inicializohet n\u00eb nyj\u00ebn store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; cd \/usr\/local\/symmetric-server-3.9.20\/bin\nvm2$&gt; sym 2&gt;&amp;1 &amp;<\/code><\/pre>\n<p><\/p>\n<p>N\u00ebse filloni procesin server sym n\u00eb hostin vm2, ai do t\u00eb krijoj\u00eb tabela katalogu SymmetricDS gjithashtu n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL. N\u00ebse filloni procesin server sym n\u00eb t\u00eb dy nyjat, ato do t\u00eb koordinohen me nj\u00ebri-tjetrin p\u00ebr t\u00eb replikuar t\u00eb dh\u00ebnat nga corp-000 n\u00eb store-001. N\u00ebse pas pak sekondash k\u00ebrkojm\u00eb t\u00eb gjitha 4 tabelat n\u00eb t\u00eb dy an\u00ebt, do t\u00eb shohim se replikimi \u00ebsht\u00eb realizuar me sukses. Ose mund t\u00eb d\u00ebrgojm\u00eb nj\u00eb ngarkes\u00eb fillestare n\u00eb nyj\u00ebn store-001 nga corp-000 me komand\u00ebn e m\u00ebposhtme.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; .\/symadmin --engine corp-000 reload-node 001<\/code><\/pre>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb pik\u00eb, n\u00eb tabel\u00ebn item n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave MySQL n\u00eb nyj\u00ebn corp-000 (host: vm1) shtohet nj\u00eb regjistrim i ri, dhe mund t\u00eb kontrollojm\u00eb replikimin e saj n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave PostgreSQL n\u00eb nyj\u00ebn store-001. Ne shohim nj\u00eb operacion Pull p\u00ebr t\u00eb transferuar t\u00eb dh\u00ebnat nga corp-000 n\u00eb store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into item values ('22000002','Jelly Bean');\nQuery OK, 1 row affected (0.00 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">vm2$&gt; psql -p 5832 -U postgres pgdb_replica -c \"select * from item\"\n item_id  |   name\n----------+-----------\n 11000001 | Yummy Gum\n 22000002 | Jelly Bean\n(2 rows)<\/code><\/pre>\n<p><\/p>\n<p>P\u00ebr t\u00eb realizuar operacionin Push p\u00ebr t\u00eb transferuar t\u00eb dh\u00ebnat nga store-001 n\u00eb corp-000, shtojm\u00eb nj\u00eb regjistrim n\u00eb tabel\u00ebn sale_transaction dhe kontrollojm\u00eb q\u00eb replikimi \u00ebsht\u00eb realizuar.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/xpaste.pro\/p\/B23cJ4sP\">Result.<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Ne shohim nj\u00eb konfigurim t\u00eb suksessh\u00ebm t\u00eb replikimit dyansh\u00ebm t\u00eb tabelave mes bazave t\u00eb t\u00eb dh\u00ebnave MySQL dhe PostgreSQL. P\u00ebr t\u00eb konfiguruar replikimin p\u00ebr tabelat e reja p\u00ebrdoruese, kryejm\u00eb hapat e m\u00ebposht\u00ebm. Krijojm\u00eb tabel\u00ebn t1 p\u00ebr shembull dhe konfigurojm\u00eb rregullat e saj t\u00eb replikimit si m\u00eb posht\u00eb. K\u00ebshtu ne vendosim vet\u00ebm replikimin nga corp-000 n\u00eb store-001.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; create table t1 (no integer);\nQuery OK, 0 rows affected (0.01 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_channel (channel_id,create_time,last_update_time) \nvalues ('t1',current_timestamp,current_timestamp);\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_trigger (trigger_id, source_table_name,channel_id,\nlast_update_time, create_time) values ('t1', 't1', 't1', current_timestamp,\ncurrent_timestamp);\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<p><\/p>\n<pre><code class=\"plaintext\">mysql&gt; insert into sym_trigger_router (trigger_id, router_id,\nInitial_load_order, create_time,last_update_time) values ('t1',\n'corp-2-store-1', 1, current_timestamp,current_timestamp);\nQuery OK, 1 row affected (0.01 sec)<\/code><\/pre>\n<p><\/p>\n<p>Pastaj konfigurimi merr nj\u00eb njoftim p\u00ebr ndryshimin e skem\u00ebs, dometh\u00ebn\u00eb shtimin e nj\u00eb tabele t\u00eb re, me komand\u00ebn symadmin me argumentin sync-triggers, i cili rip\u00ebrdor tri tragjedit\u00eb p\u00ebr t\u00eb nd\u00ebrlidhur p\u00ebrkufizimet e tabelave. Ekzekutohet send-schema p\u00ebr t\u00eb d\u00ebrguar ndryshimet e skem\u00ebs n\u00eb nyj\u00ebn store-001, dhe replikimi i tabel\u00ebs t1 \u00ebsht\u00eb i konfiguruar.<\/p>\n<p><\/p>\n<pre><code class=\"plaintext\">vm1$&gt; .\\\/symadmin -e corp-000 --node=001 sync-triggers    \nvm1$&gt; .\\\/symadmin send-schema -e corp-000 --node=001 t1<\/code><\/pre>\n<p><\/p>\n<h3 id=\"preimuschestva-symmetricds\">Avantazhet e SymmetricDS<\/h3>\n<p><\/p>\n<p>Instalimi dhe konfigurimi i thjesht\u00eb, duke p\u00ebrfshir\u00eb nj\u00eb grup t\u00eb gatsh\u00ebm skedash me parametra p\u00ebr krijimin e skem\u00ebs me tri ose dy nyje.<br \/>\nKros-platform\u00ebs p\u00ebr bazat e t\u00eb dh\u00ebnave dhe pavar\u00ebsia nga platforma, duke p\u00ebrfshir\u00eb server\u00ebt, laptop\u00ebt dhe pajisjet mobile.<br \/>\nReplikimi i \u00e7do baze t\u00eb dh\u00ebnash n\u00eb \u00e7do baz\u00eb t\u00eb dh\u00ebnash, lokal, n\u00eb WAN ose n\u00eb cloud.<br \/>\nAft\u00ebsia p\u00ebr t\u00eb punuar optimalisht me nj\u00eb \u00e7ift bazash t\u00eb dh\u00ebnash ose me disa mij\u00ebra p\u00ebr replikim t\u00eb leht\u00eb.<br \/>\nVersioni me pages\u00eb me nj\u00eb nd\u00ebrfaqe grafike dhe mb\u00ebshtetje t\u00eb shk\u00eblqyer.<\/p>\n<p><\/p>\n<h3 id=\"nedostatki-symmetricds\">Disavantazhet e SymmetricDS<\/h3>\n<p><\/p>\n<p>Duhet t\u00eb p\u00ebrcaktohen manualisht n\u00eb komand\u00ebn e linj\u00ebs rregullat dhe drejtimi i replikimit p\u00ebrmes operator\u00ebve SQL p\u00ebr t\u00eb ngarkuar tabelat e katalog\u00ebve, q\u00eb ndonj\u00ebher\u00eb \u00ebsht\u00eb e pad\u00ebshirueshme.<br \/>\nT\u00eb konfigurosh shum\u00eb tabela p\u00ebr replikim \u00ebsht\u00eb e lodhshme, n\u00ebse nuk p\u00ebrdoren skriptet p\u00ebr krijimin e operator\u00ebve SQL, t\u00eb cil\u00ebt p\u00ebrcaktojn\u00eb rregullat dhe drejtimin e replikimit.<br \/>\nN\u00eb log shkruhen shum\u00eb informacione dhe ndonj\u00ebher\u00eb \u00ebsht\u00eb e nevojshme t\u00eb rregullohet dosja e logut q\u00eb t\u00eb mos z\u00eb shum\u00eb hap\u00ebsir\u00eb.<\/p>\n<p><\/p>\n<h3 id=\"itogi-po-symmetricds\">P\u00ebrmbledhje p\u00ebr SymmetricDS<\/h3>\n<p><\/p>\n<p>SymmetricDS lejon konfigurimin e replikimit dyansh\u00ebm midis dy, tre dhe madje disa mij\u00ebra nyjesh, p\u00ebr t\u00eb realizuar replikimin dhe sinkronizimin e skedave. Ky \u00ebsht\u00eb nj\u00eb mjet unik q\u00eb kryen vet\u00eb shum\u00eb detyra, p\u00ebr shembull rikuperimin automatik t\u00eb t\u00eb dh\u00ebnave pas nj\u00eb pushimi t\u00eb gjat\u00eb n\u00eb nyje, shk\u00ebmbimi t\u00eb dh\u00ebnash t\u00eb sigurt dhe efikas midis nyjash p\u00ebrmes HTTPS, menaxhimi automatik i konflikteve mbi nj\u00eb grup rregullash etj. SymmetricDS realizon replikimin midis \u00e7do baze t\u00eb dh\u00ebnash, prandaj mund t\u00eb p\u00ebrdoret p\u00ebr skenar\u00eb t\u00eb ndrysh\u00ebm, duke p\u00ebrfshir\u00eb migrimin, kalimin n\u00eb versionin e ri, shp\u00ebrndarjen, filtrimin dhe transformimin e t\u00eb dh\u00ebnave n\u00eb platforma t\u00eb ndryshme.<\/p>\n<p><\/p>\n<p>Shembulli \u00ebsht\u00eb krijuar mbi baz\u00ebn e zyrtar <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/tutorials.html\">udh\u00ebzuesit t\u00eb shkurt\u00ebr<\/a><\/noindex> p\u00ebr SymmetricDS. N\u00eb <noindex><a rel=\"nofollow\" href=\"https:\/\/www.symmetricds.org\/doc\/3.9\/html\/user-guide.html\">udh\u00ebzuesin e p\u00ebrdoruesit<\/a><\/noindex> p\u00ebrshkruhen n\u00eb detaje konceptet e ndryshme q\u00eb lidhen me konfigurimin e replikimit duke p\u00ebrdorur SymmetricDS.<\/p>\n<p>Burimi: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/southbridge\/blog\/467313\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL, \u0430 \u0435\u0449\u0435 \u043e \u043c\u0435\u0442\u043e\u0434\u0430\u0445 \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0438 \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043c\u0435\u0436\u0434\u0443 \u044d\u0442\u0438\u043c\u0438 \u0434\u0432\u0443\u043c\u044f \u0441\u0435\u0440\u0432\u0435\u0440\u0430\u043c\u0438 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u041e\u0431\u044b\u0447\u043d\u043e \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u043f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u043e\u0439 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438 \u043d\u0430\u0437\u044b\u0432\u0430\u044e\u0442\u0441\u044f \u043e\u0434\u043d\u043e\u0440\u043e\u0434\u043d\u044b\u043c\u0438, \u0438 \u044d\u0442\u043e \u0443\u0434\u043e\u0431\u043d\u044b\u0439 \u043c\u0435\u0442\u043e\u0434 \u043f\u0435\u0440\u0435\u0445\u043e\u0434\u0430 \u0441 \u043e\u0434\u043d\u043e\u0433\u043e \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u0421\u0423\u0411\u0414 \u043d\u0430 \u0434\u0440\u0443\u0433\u043e\u0439. \u0411\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 PostgreSQL \u0438 MySQL \u043f\u0440\u0438\u043d\u044f\u0442\u043e \u0441\u0447\u0438\u0442\u0430\u0442\u044c \u0440\u0435\u043b\u044f\u0446\u0438\u043e\u043d\u043d\u044b\u043c\u0438, \u043d\u043e \u0441 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":28635,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-38146","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"sq_AL\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T19:21:54+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:21:54+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Replikimi midis PostgreSQL dhe MySQL | ProHoster","description":"Do t\u00eb flas p\u00ebr m\u00ebnyra t\u00eb p\u00ebrgjithshme.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041f\u0435\u0440\u0435\u043a\u0440\u0435\u0441\u0442\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043c\u0435\u0436\u0434\u0443 PostgreSQL \u0438 MySQL | ProHoster","og:description":"\u042f \u0432 \u043e\u0431\u0449\u0438\u0445 \u0447\u0435\u0440\u0442\u0430\u0445 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443 \u043e.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/perekrestnaya-replikatsiya-mezhdu-postgresql-i-mysql","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T19:21:54+00:00","article:modified_time":"2019-10-31T19:21:54+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"38146","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-23 20:38:22","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:59:41","updated":"2026-01-23 20:38:22","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/38146","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=38146"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/38146\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/28635"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=38146"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=38146"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=38146"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}