I want to build a set of PostgreSQL databases that derive from a separate structure definition. The structure is defined as a set of
.sql files that will be imported using
docker-entrypoint-initdb.d during start.
One of the databases will have regular tables that are created e.g. like this:
CREATE TABLE data."origin" (
origin_id serial PRIMARY KEY,
name text UNIQUE
Another database needs to be connected to this one using foreign tables that should have the same structure, e.g.
CREATE FOREIGN TABLE data."origin" (
OPTIONS (schema_name 'data', table_name 'origin');
So the question is, whether there is an elegant way to use the original
.sql files from the first database to construct the second one as well.
The only way that we have in mind is to fiddle with
sed or similar magic during the image build to add the
FOREIGN and the
SERVER … stuff in there, remove the
PRIMARY KEY etc. This will most likely break sooner or later.