Query a postgres database for the SQL which generates the tables

Question

We want to replicate our local test DB automatically, given the structure of the production DB, on the fly / on demand.

What would be nice, would be to query the prod DB and retrieve the SQL that generates the tables/views and then run that SQL against a cleaned out local DB.

Or perhaps there is a better way to replicate a production DB on a local machine?

What is the best way to do this?

Possible duplicate of How to generate create script of table using SQL query in SQL Server — johan855
– johan855, Commented Oct 13, 2016 at 22:21
You are approaching this from the wrong end. Your production database should have been created from SQL scripts stored in a version control system - including all schema changes. If you need to setup a test database, run those scripts from the version control system. Tools like Liquibase or Flyway will help you organize and track the execution of those scripts — user330315
– user330315, Commented Oct 14, 2016 at 5:58
@horse i sort of disagree - the truth ultimate lies in the DB itself not in VCS, there is no need to put SQL in VCS, that might be overkill? — Alexander Mills
– Alexander Mills, Commented Oct 14, 2016 at 8:25

Iurii Tkachenko · Accepted Answer · 2016-10-14 05:55:49Z

2

If I understood your question correct, you need to copy DB schema. You can use pg_dump with --schema-only parameter to do this. It'll only dump schema of the database, and you can import it locally.

$ pg_dump mydb --schema-only > mydb-schema.sql

answered Oct 14, 2016 at 5:55

Iurii Tkachenko

3,19931 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query a postgres database for the SQL which generates the tables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related