2

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" (
    origin_id serial,
    name text
)
    SERVER localdb
    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.

1 Answer 1

0

I think I'd try to get a list of tables from the .sql file (or directly from the server to which you applied the .sql file), then feed that list of names to IMPORT FOREIGN SCHEMA ... LIMIT TO (...). That seems less error prone than what you are envisioning.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.