0

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?

Alexander Mills
  • 90,741
  • 139
  • 482
  • 817
  • 1
    Possible duplicate of [How to generate create script of table using SQL query in SQL Server](http://stackoverflow.com/questions/4213687/how-to-generate-create-script-of-table-using-sql-query-in-sql-server) – johan855 Oct 13 '16 at 22:21
  • @johan855: Not a duplicate. Postgres is not SQL Server . –  Oct 14 '16 at 05:57
  • 1
    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 –  Oct 14 '16 at 05: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 Oct 14 '16 at 08:25

1 Answers1

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
Iurii Tkachenko
  • 3,106
  • 29
  • 34