Laravel DB replicate

DbReplicate.php

<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Arr;
use Symfony\Component\Console\Output\ConsoleSectionOutput;

class DbReplicate extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'db:replicate
    {--host= : another db host}
    {--port= : another db port}
    {--user= : another db user}
    {--password= : another db password}
    {--database= : another db name}
    {--migrate_only : Migrate to another db only}
    {--chunk=100 : Chunk size}
    ';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $another_db = [
            'host'     => $this->option('host'),
            'port'     => $this->option('port'),
            'username' => $this->option('user'),
            'password' => $this->option('password'),
            'database' => $this->option('database'),
        ];
        $chunk = $this->option('chunk');

        if ($another_db['host'] . $another_db['port'] . $another_db['database'] == '') {
            throw new \Exception('Duplicated with current db');
        }

        $another_db = array_filter($another_db);
        $this->setupAnotherConnection($another_db);

//        $count = \DB::connection('another_db')->table('users')->count();
        $table_queues = $this->getTablesQueue();

        if($this->confirm('Run migrate:fresh with another_db ?')){
            \Artisan::call('migrate:fresh', ['--database' => 'another_db'], $this->getOutput());
        }

        foreach ($table_queues as $table){
            $this->replicateTable($table, $chunk);
        }


        return Command::SUCCESS;
    }

    protected function setupAnotherConnection($options)
    {
        $config = config('database.connections.' . config('database.default'));
        $before_hash = md5(json_encode($config));
        $config = array_merge($config, $options);
        if ($before_hash == md5(json_encode($config))) {
            throw new \Exception('Duplicated with current db');
        }
        \Config::set('database.connections.another_db', $config);
    }

    protected function getTablesQueue(): array
    {
        $migrations = app('migration.repository')->getRan();
        $migration_paths = app('migrator')->paths();
        if (!in_array(database_path('migrations'), $migration_paths)) {
            $migration_paths[] = database_path('migrations');
        }
        $migration_files = app('migrator')->getMigrationFiles($migration_paths);
        $tables = ['migrations'];
        foreach ($migrations as $migration) {
            $file = $migration_files[$migration] ?? false;
            if(!$file){
                $this->warn("Not found " . $migration);
                continue;
            }
            $file = file_get_contents($file);
            if (preg_match_all("/Schema\:\:(create|table)\([\'\"]([^\'\"]+)[\'\"]/ui", $file, $matches)) {
                foreach ($matches[2] as $table) {
                    if (!in_array($table, $tables)) {
                        $tables[] = $table;
                    }
                }
            }
        }

        $db_tables = \DB::getDoctrineSchemaManager()->listTableNames();;
        foreach ($db_tables as $table) {
            if (!in_array($table, $tables)) {
                $tables[] = $table;
            }
        }

        return $tables;
    }

    protected function replicateTable($table_name, $page_size = 50){
        $this->info("* Replicating " . $table_name);
        if($table_name == 'migrations'){
            $this->warn("\tIgnored ");
            return 0;
        }

        // Check table exist
        if(!\DB::getDoctrineSchemaManager()->tablesExist($table_name)){
            $this->warn("\tNo table with name " . $table_name . ' in source db');
            return 0;
        }
        if(!\DB::connection('another_db')->getDoctrineSchemaManager()->tablesExist($table_name)){
            $this->warn("\tNo table with name " . $table_name . ' in another db');
            return 0;
        }

        //        $this->info("\tTruncating " . $table_name);
//        \DB::connection('another_db')->table($table_name)->truncate();
//        dd(\DB::connection('another_db')->table($table_name)->first());
        if(\DB::connection('another_db')->table($table_name)->first()){
            $upsert = true;
            $this->warn("\tUsing upsert mode");
        }else{
            $upsert = false;
        }

        $indexes = \DB::getDoctrineSchemaManager()->listTableIndexes($table_name);
        if(count($indexes)){
            $columns = reset($indexes)->getColumns();
        }else{
            $columns = \DB::getDoctrineSchemaManager()->listTableColumns($table_name);
            $columns = array_keys($columns);
        }

        $this->info("\tPrimary : " . implode(",", $columns));

        $this->info("\tCopying " . $table_name);
        $total = 0;
        $copied = 0;
        /** @var ConsoleSectionOutput $section */
        $section = $this->getOutput()->getOutput()->section();
        $cursor = null;
        do{
            $data = \DB::table($table_name)->orderBy($columns[0])->cursorPaginate($page_size, ['*'], 'cursor', $cursor);
            $cursor = $data->nextCursor();
            \DB::connection('another_db')->transaction(function ($db) use ($data, $table_name, $columns, $upsert, &$copied, &$total){
                foreach ($data as $item){
                    $item = (array)$item;
                    if($upsert) {
                        $db->table($table_name)->upsert($item, [Arr::only($item, $columns)]);
                    }else{
                        $db->table($table_name)->insert($item);
                    }
                    $copied++;
                }
                $total += count($data->items());
            });
            $section->overwrite("\t\tCopied " . $copied . "/" . $total);
        }while(count($data->items()) && $cursor);

    }

}

example output

 Run migrate:fresh with another_db ? (yes/no) [no]:
 > no

* Replicating migrations
        Ignored 
* Replicating users
        Using upsert mode
        Primary : id
        Copying users
                Copied 3/3
* Replicating password_resets
        Primary : email
        Copying password_resets
                Copied 0/0
* Replicating documents
        Using upsert mode
        Primary : original_path
        Copying documents
                Copied 6/6
* Replicating jobs
        Primary : queue,reserved,reserved_at
        Copying jobs
                Copied 0/0
* Replicating view
        Using upsert mode
        Primary : id
        Copying view
                Copied 10/10
* Replicating failed_jobs
        Primary : id
        Copying failed_jobs
                Copied 0/0
* Replicating topics
        Using upsert mode
        Primary : id
        Copying topics
                Copied 38/38
* Replicating topics_documents
        Using upsert mode
        Primary : id
        Copying topics_documents
                Copied 3/3
* Replicating settings
        Primary : id
        Copying settings
                Copied 0/0
* Replicating notifications
        Primary : notifiable_type,notifiable_id
        Copying notifications
                Copied 0/0
* Replicating download_history
        Using upsert mode
        Primary : created_at
        Copying download_history
                Copied 1/1
* Replicating recent_activity
        No table with name recent_activity in source db
* Replicating upload_reports
        Primary : id
        Copying upload_reports
                Copied 0/0
* Replicating pinned_documents
        Primary : document_id,user_id
        Copying pinned_documents
                Copied 0/0
* Replicating document_report
        Primary : reportable_type,reportable_id
        Copying document_report
                Copied 0/0
* Replicating verify_social_emails
        Primary : id
        Copying verify_social_emails
                Copied 0/0
* Replicating download
        No table with name download in another db
* Replicating related_documents
        Primary : document_id
        Copying related_documents
                Copied 4/4
* Replicating titles
        Primary : id
        Copying titles
                Copied 0/0
* Replicating download_reports
        Primary : date
        Copying download_reports
                Copied 0/0
* Replicating tag_contents
        Primary : id
        Copying tag_contents
                Copied 0/0
* Replicating tagging_tags
        No table with name tagging_tags in source db
* Replicating tagging_tagged
        No table with name tagging_tagged in source db
* Replicating taggable_tags
        Primary : id
        Copying taggable_tags
                Copied 21/21
* Replicating taggable_taggables
        Primary : tag_id,taggable_id
        Copying taggable_taggables
                Copied 21/21
* Replicating figures
        Primary : document_id
        Copying figures
                Copied 0/0
* Replicating references
        Primary : document_id
        Copying references
                Copied 0/0
* Replicating seo_keywords
        Primary : id
        Copying seo_keywords
                Copied 0/0
* Replicating seo_keywords_rel
        Primary : keyword1_id
        Copying seo_keywords_rel
                Copied 0/0
* Replicating seo_keywords_document
        Primary : document_code
        Copying seo_keywords_document
                Copied 0/0
* Replicating link_exchanges
        Primary : document_code
        Copying link_exchanges
                Copied 7/7
* Replicating social_posts
        Primary : id
        Copying social_posts
                Copied 0/0
* Replicating seo_titles
        Primary : id
        Copying seo_titles
                Copied 0/0
* Replicating seo_title_document
        Primary : document_code
        Copying seo_title_document
                Copied 0/0
* Replicating document_queues
        No table with name document_queues in source db
* Replicating seo_titles_rel
        Primary : title1_id
        Copying seo_titles_rel
                Copied 0/0
* Replicating seo_parts
        Primary : id
        Copying seo_parts
                Copied 0/0
* Replicating posts
        Primary : slug
        Copying posts
                Copied 0/0
* Replicating post_categories
        Primary : name_normalized
        Copying post_categories
                Copied 0/0
* Replicating post_category
        Primary : category_id
        Copying post_category
                Copied 0/0
* Replicating backlinks
        Primary : order
        Copying backlinks
                Copied 0/0
* Replicating user_documents
        Primary : id
        Copying user_documents
                Copied 1/1
* Replicating user_documents_topics
        Primary : id
        Copying user_documents_topics
                Copied 0/0
* Replicating download_app_histories
        Primary : app_uuid
        Copying download_app_histories
                Copied 0/0
* Replicating internal_links
        Primary : achieved_at
        Copying internal_links
                Copied 0/0
* Replicating inject_link_logs
        Primary : created_at
        Copying inject_link_logs
                Copied 0/0
* Replicating document_statistics
        Primary : backlinks_count
        Copying document_statistics
                Copied 6/6
* Replicating roles
        Primary : id
        Copying roles
                Copied 0/0
* Replicating role_users
        Primary : user_id,role_id
        Copying role_users
                Copied 0/0
* Replicating attachments
        Primary : id
        Copying attachments
                Copied 0/0
* Replicating attachmentable
        Primary : attachment_id
        Copying attachmentable
                Copied 0/0
* Replicating elastic_migrations
        Primary : migration,batch
        Copying elastic_migrations
                Copied 0/0
* Replicating email_webhooks
        Primary : email_id
        Copying email_webhooks
                Copied 0/0
* Replicating emails
        Primary : emailable_type,emailable_id
        Copying emails
                Copied 0/0
* Replicating recent_activities
        Primary : id
        Copying recent_activities
                Copied 1/1
* Replicating tagging_tag_groups
        No table with name tagging_tag_groups in another db