postgresqlliquibaseliquibase-hibernateliquibase-sql

How to copy a database schema from database A to database B


I have created a Postgresql database A using liquibase changesets. Now, I'm creating an application that allows creating a new database B and copies the schema from database A in real-time including the liquibase changesets as the database can still be updated later. Note that at the time of the copied schema in database A could already be updated, making the base changesets outdated.

My main question would be:

  1. How to copy PostgreSQL schema x from database a (dynamically generated at run-time) to b using liquibase? Database b could be on another server.
  2. If it's not possible with liquibase, what other tools or approaches would make this possible?

--

Let me add more context:

  1. We initialize a new database a schema using liquibase changeset.
  2. We can add a new table and field to the database an at run-time. Or during the time when the application is running. For example, we add a new table people to the schema of database a, which is not originally in the changeset. This is done using liquibase classes too. So changeset is added to databasechangelog table.
  3. Now, we create a new database b.
  4. We want to import the schema of the database a to b, with people table.

I hope that is clear.

Thanks.


Solution

  • Here's how I solved this problem of mine using the Liquibase Java library:

    1.) Export the changelog from the source database into a temporary file (XML).

    Liquibase liquibase = new Liquibase(liquibaseOutFile.getAbsolutePath(), new FileSystemResourceAccessor(), sourceDatabase);
    liquibase.generateChangeLog(catalogAndSchema, changeLogWriter, new PrintStream(liquibaseOutFile.getAbsolutePath()), null);
    

    2.) Execute the temporary file to the new data source.

    Liquibase targetLiquibase = new Liquibase(liquibaseOutFile.getAbsolutePath(), new FileSystemResourceAccessor(), targetDatabase);
    Contexts context = new Contexts();
    targetLiquibase.update(context);
    

    Here's the complete code: https://czetsuya-tech.blogspot.com/2019/12/generate-postgresql-schema-using-java.html