How to migrate schemas in postgresql from local server to production server?

Question

I create a database, some tables and stored procedures on my local postgres server. Now I want to automatically migrate these things to my production server without creating everything from scratch again. This should be something quite straight-forward, however I did not find any books trying to explain this. So I wonder how could it be achieved? Can someone please provide some link of tutorials? Thanks!

You mean to create tables and procedure in your local DB to the production DB and no need to copy data in tables, right? and Juts to confirm both DBs are PostgreSQL,isn't it? — Vivek S.
– Vivek S., Commented Jan 5, 2016 at 6:07
You are using the wrong process. Put each change that you make in a (SQL) script, then store those scripts in a version control system. When you deploy to production, run those migration scripts in production. Anything else will get you in a lot of trouble in the long run. You might want to check out tools like Liquibase or Flyway that help (a lot) to manage schema migration steps — user330315
– user330315, Commented Jan 5, 2016 at 9:20
@a_horse_with_no_name: I thought of using VCS to manage all the scripts. But will it be troublesome to track the changes and make the changes manually? It is especially so when when there are several commits, each with several changes, before I pull on server side. — Shaohua Huang
– Shaohua Huang, Commented Jan 5, 2016 at 9:53
That's why I recommend using Liquibase (or Flyway). Once you are past the initial release in production your current solution is going to fail at some point. — user330315
– user330315, Commented Jan 5, 2016 at 10:06

Dzmitry Savinkou · Accepted Answer · 2016-01-05 07:43:36Z

6

Use pg_dump for this purpose, Postgres has great tutorial. For single schema instruction may be the following:

pg_dump -o -h hostname -U db_username -d db_name -n schema_name > your_schema.dump

Sample restore command:

psql -h hostname -U db_username db_name < your_schema.dump

answered Jan 5, 2016 at 7:43

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

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

1 Comment

Edhowler Over a year ago

I would add -s in the first command, since the question was asking to migrate only the schema, not the data.

Collectives™ on Stack Overflow

How to migrate schemas in postgresql from local server to production server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related