If you want to do some cross database joins or includes across postgresql databases regardless of local or remote databases you can use Postgres FDW. Its an awesome tool for the job when you want to avoid data replication.
Base Postgresql FDW Model:
class PostgresFdwBase < ApplicationRecord
### Article to familiarize with concepts in this models methods - https://thoughtbot.com/blog/postgres-foreign-data-wrapper
self.abstract_class = true
# self.primary_key = :id ### would be nice if this could be added here however this must be specified within each model individually
after_initialize do
### OPTIONAL
@readonly = true ### safeguard to disallow writes by default, as requirements change this may not be desirable
end
def self.fdw_db_config
# base_config = Rails.configuration.database_configuration["my_second_database_#{Rails.env}"]
# ### OR custom base config, example below expects normal database.yml keys update as required
# # base_config = { ... }.with
# base_config = base_config.merge({
# fdw_server_name: "global_data_admin_fdw_server",
# local_fdw_schema_name: "global_data_admin_fdw_schema", ### make sure to use a seperate schema for fdw tables schema otherwise data loss can occur
# remote_schema_name: "public",
# }).merge(Rails.configuration.database_configuration[Rails.env]["global_data_admin"])
# return base_config.with_indifferent_access
raise "Override this method in sub-model, Ie. GlobalDataRecord or AuthAppRecord"
end
def self.sync_all_fdw_tables!
config = self.fdw_db_config.with_indifferent_access
the_sql = <<~SQL
BEGIN;
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
DROP SERVER IF EXISTS #{config.fetch(:fdw_server_name)} CASCADE;
CREATE SERVER #{config.fetch(:fdw_server_name)}
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '#{config.fetch(:host)}', port '#{config.fetch(:port)}', dbname '#{config.fetch(:database)}');
CREATE USER MAPPING FOR CURRENT_USER
SERVER #{config.fetch(:fdw_server_name)}
#{ config.fetch(:username).presence ? ";" : "OPTIONS (user '#{config.fetch(:username)}', password '#{config.fetch(:password)}');" }
DROP SCHEMA IF EXISTS #{config.fetch(:local_fdw_schema_name)};
CREATE SCHEMA #{config.fetch(:local_fdw_schema_name)};
IMPORT FOREIGN SCHEMA #{config.fetch(:remote_schema_name, "public")}
FROM SERVER #{config.fetch(:fdw_server_name)}
INTO #{config.fetch(:local_fdw_schema_name)};
COMMIT;
SQL
self.connection.execute(the_sql)
end
### RESET FDW TABLE FOR JUST THIS MODEL, NOT NEEDED JUST FOR EXAMPLE
def self.sync_this_fdw_table!
config = self.fdw_db_config.with_indifferent_access
the_sql = <<~SQL
DROP FOREIGN TABLE #{table_name} IF EXISTS;
IMPORT FOREIGN SCHEMA #{config.fetch(:remote_schema_name, "public")}
LIMIT TO #{table_name}
FROM SERVER #{config.fetch(:fdw_server_name)}
INTO #{config.fetch(:local_fdw_schema_name)};
SQL
self.connection.execute(the_sql)
end
end
Example Second Database Base Model:
class MySecondDatabaseBaseRecord < PostgresFdwBase
self.abstract_class = true
def self.fdw_db_config
### IF YOU ARE USING RAILS 6+ MULTI-DB config/database.yml
base_config = Rails.configuration.database_configuration["my_second_database_#{Rails.env}"]
### OR custom base config, example below expects normal database.yml keys update as required
# base_config = { ... }
base_config = base_config.merge({
fdw_server_name: "global_data_admin_fdw_server",
local_fdw_schema_name: "global_data_admin_fdw_schema", ### make sure to use a seperate schema for fdw tables schema otherwise data loss can occur
remote_schema_name: "public",
})
return base_config.with_indifferent_access
end
end
Example Model:
class RemotePosts < MySecondDatabaseBaseRecord
self.primary_key = :id ### must be specified for each model
end
Supporting Code:
# config/database.yml.erb
development:
schema_search_path: "public,<%= MySecondDatabaseBaseRecord.fdw_db_config[:local_fdw_schema_name] %>"
# config/initializers/postgresql_fdw.rb
if Rails.env.development? || Rails.env.test?
### Ensure always in sync when dev or test server starts
MySecondDatabaseBaseRecord.sync_all_fdw_tables!
else
### Will be run automatically after db:migrate as per lib/tasks/enhancements.rb
end
# lib/tasks/enhancements.rb
Rake::Task["db:migrate"].enhance do
### (ENHANCEMENTS RUN AFTER TASK COMPLETED)
if !!ENV["SKIP_UPDATE_POSTGRES_FDW_TABLES_AFTER_MIGRATE"]
# Do nothing, skip
else
### SETUP/RESET ALL FDW TABLE DEFINITIONS HERE
MySecondDatabaseBaseRecord.sync_all_fdw_tables!
end
end
Related External Links: