-
-
Save vaporic/7948069fae17197409975612c61b0e25 to your computer and use it in GitHub Desktop.
Laravel command for production database replication
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DB_CONNECTION=pgsql | |
| DB_HOST=127.0.0.1 | |
| DB_DATABASE=guesture_stage | |
| DB_USERNAME=dev_user | |
| DB_PASSWORD=**** | |
| DB_HOST_2=1.1.1.1#ip of production database server | |
| DB_PORT_2=1234 #port | |
| DB_DATABASE_2=guesture_pro #production database name | |
| DB_USERNAME_2=prod_user #production database username | |
| DB_PASSWORD_2=**** #production database username |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| return [ | |
| 'connections' => [ | |
| // Open config/database.php file and add this item within the connections array index | |
| 'pgsql_2' => [ | |
| 'driver' => 'pgsql', | |
| 'host' => env('DB_HOST_2', '127.0.0.1'), | |
| 'port' => env('DB_PORT_2', '5432'), | |
| 'database' => env('DB_DATABASE_2', 'forge'), | |
| 'username' => env('DB_USERNAME_2', 'forge'), | |
| 'password' => env('DB_PASSWORD_2', ''), | |
| 'charset' => 'utf8', | |
| 'prefix' => '', | |
| 'schema' => 'public', | |
| 'sslmode' => 'prefer', | |
| ], | |
| ], | |
| ]; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| namespace App\Console\Commands; | |
| use Illuminate\Console\Command; | |
| use Illuminate\Support\Facades\DB; | |
| /** | |
| * Class ReplicationProductionDatabase | |
| * @package App\Console\Commands | |
| */ | |
| class ReplicationProductionDatabase extends Command | |
| { | |
| /** | |
| * The name and signature of the console command. | |
| * | |
| * @var string | |
| */ | |
| protected $signature = 'my-app:replicate-production-database'; | |
| /** | |
| * The console command description. | |
| * | |
| * @var string | |
| */ | |
| protected $description = 'This command will replicate the ACAMIS production database and restore it in staging environment.'; | |
| /** | |
| * @var string | |
| */ | |
| private $localDBConnectionName = 'pgsql'; | |
| /** | |
| * @var string | |
| */ | |
| private $productionDBConnectionName = 'pgsql_2'; | |
| /** | |
| * Create a new command instance. | |
| * | |
| * @return void | |
| */ | |
| public function __construct() | |
| { | |
| parent::__construct(); | |
| } | |
| /** | |
| * Execute the console command. | |
| * | |
| * @return mixed | |
| */ | |
| public function handle() | |
| { | |
| if ( env('APP_ENV') === 'production' ) { | |
| $this->error('Cannot run this command in Production environment.'); | |
| return; | |
| } | |
| $this->info('Replicate Production Database'); | |
| if ( $this->confirm('Start replication?') ) { | |
| // Set current application instance to Maintenance mode | |
| $this->call('down'); | |
| // Make custom seeders | |
| if ( $this->confirm('Create seeders?') ) { | |
| $this->makeSeeders(); | |
| } | |
| // Drop all tables and run migration | |
| if ( $this->confirm('Run fresh migrations?') ) { | |
| $this->cleanDatabase(); | |
| } | |
| // Seed all tables | |
| if ( $this->confirm('Run Database Seeders?') ) { | |
| $this->runSeeders(); | |
| } | |
| // Turn up current application instance | |
| $this->call('up'); | |
| } | |
| return; | |
| } | |
| /** | |
| * Fetch records from all tables and | |
| * store data into JSON files of the same table name | |
| */ | |
| private function makeSeeders() | |
| { | |
| $tables = $this->getTables(); | |
| $this->info('Starting Seeder creation'); | |
| $this->line('Tables found: '.$tables->count()); | |
| $connection = DB::connection($this->productionDBConnectionName); | |
| $tables->each( | |
| function ($table) use ($connection) { | |
| try { | |
| $results = $connection->table($table['table_name'])->get(); | |
| $contents = $results->toJson(); | |
| file_put_contents($this->getFilePath($table['table_name']), $contents); | |
| $this->line('Backup completed for '.$table['table_name']); | |
| } catch ( \Exception $exception ) { | |
| $this->error('Error Occurred for '.$table['table_name']); | |
| } | |
| } | |
| ); | |
| } | |
| /** | |
| * Get the list of tables in the database from a config file. | |
| * Table names are listed in sequential order to avoid foreign key violation | |
| * @return \Illuminate\Support\Collection | |
| */ | |
| public function getTables() | |
| { | |
| return collect(config('spm.tables', [])); | |
| } | |
| /** | |
| * Get file path for JSON file | |
| * | |
| * @param string $tableName | |
| * | |
| * @return string | |
| */ | |
| private function getFilePath(string $tableName) | |
| { | |
| return storage_path("seeders/{$tableName}.json"); | |
| } | |
| /** | |
| * Run table seeding for all tables | |
| */ | |
| private function runSeeders() | |
| { | |
| $this->getTables() | |
| ->filter( | |
| function ($table) { | |
| return !in_array($table['table_name'], ['migrations',]); | |
| } | |
| ) | |
| ->each( | |
| function ($table) { | |
| $this->runTableSeeder($table); | |
| } | |
| ); | |
| } | |
| /** | |
| * Run table seeding for single table | |
| * | |
| * @param $table | |
| */ | |
| private function runTableSeeder($table) | |
| { | |
| $tableName = $table['table_name']; | |
| $connection = DB::connection($this->localDBConnectionName); | |
| $connection = $connection->table($tableName); | |
| $fileName = $this->getFilePath($tableName); | |
| $contents = collect(json_decode(file_get_contents($fileName), true)); | |
| $connection->truncate(); | |
| $contents | |
| ->each( | |
| function ($record) use ($connection) { | |
| $connection->insert($record); | |
| } | |
| ); | |
| if ( array_get($table, 'incrementing', true) ) { | |
| $newIdSequence = $contents->max('id') + 1; | |
| $connection->getConnection() | |
| ->statement("ALTER SEQUENCE {$tableName}_id_seq RESTART WITH {$newIdSequence}"); | |
| } | |
| $this->line("Seeding completed for {$tableName} with {$contents->count()} records."); | |
| } | |
| /** | |
| * Remove all tables from database and re-migrate database table | |
| */ | |
| private function cleanDatabase() | |
| { | |
| $this->call('migrate:fresh'); | |
| } | |
| } |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| return [ | |
| ['table_name' => 'migrations'], | |
| ['table_name' => 'users'], | |
| ['table_name' => 'password_resets', 'incrementing' => false], | |
| ['table_name' => 'blogs'], | |
| ['table_name' => 'comments'], | |
| // add additional tables in sequential of their creation | |
| // add add 'incrementing' => false for table without auto-incrementing primary key | |
| ]; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment