Moving data Postgres to MySQL using PHP/Laravel5

Question

I'm using:

• A local Postgres DB
• Laravel 5 with a MySQL DB also local
• Postgres DB contains the table structure from OTRS - with over 91.000 rows on the table "tickets"

I needed to move the data from one DB to the other. What I did was compile a big query that united all the data I needed and run it in a PHP script I created inside the Laravel structure. Then I iterated through the results using Eloquent to insert into MySQL. I know it's terrible but I had to remove the memory and time limits for PHP in order to do this and though it took a really long time, it worked!

Right now I realized I missed something, I need to rerun a similar query (results in the same number of rows), but this time to just add one field on the MySQL DB.

My question is, how can I optimize this process? I'm thinking of using chunks but I don't know how to that.

To clarify:

• MySql's Tickets table contains 91397 rows and 5 columns
• Postgres Tickets table also has 91397 rows and 6 columns
• I created a migration on Laravel (MySQL) that added the extra column (though it's empty)

It's probably easier if I show you the code I have.

link

Answer 1

You can use eloquent to do this. Here's how I'd do (untested, may need some tuning):

Set up both DB connections in the config\database.php. Make two models for the same resource (you can use only one model but I rather not), I'll name them M1 and M2, with different connection attribute. To do this create an attribute inside the model: protected $connection = 'connection_name'.

So basically:

class M1 extends Model {
   protected $connection = 'connection_name';
   protected $table = 'table_name';
   protected $guarded = [];

Same for M2 with a different $connection.

Now in your controller method or wherever you're executing your code, you can chunk queries using eloquent:

//assuming you are transferring M1's db to M2's

M1::chunk(200, function($m1s)  //if you wanna use only one model, this should be M1::on('connection-name')->chunk(...
{
   foreach ($m1s as $m1)
   {
      $m2 = new M2();
      //this does not copy model-specific attributes like $connection
      $m2->fill( $m1->getAttributes() );  
      $m2->save();
   }
});

I think this does the job, can you test this?