3

I've local PostgreSQL database with tables, constraints, relations etc.

How can I migrate it to a production server?

Daulet Nurgali
  • 167
  • 1
  • 3
  • 8
  • 2
    why not pg_dump and restore?.. – Vao Tsun Feb 08 '18 at 14:55
  • http://liquibase.org/ –  Feb 08 '18 at 15:12
  • @VaoTsun I get following error while using pg_dump: `pg_dump: server version: 9.6.5; pg_dump version: 9.5.10` `pg_dump: aborting because of server version mismatch` – Daulet Nurgali Feb 08 '18 at 16:03
  • @a_horse_with_no_name `liquibase` or `flyway` is good approach, but I'am at the end of developing my app. These tools must be used from the start of developing an app (put each sql script into file and store them ) – Daulet Nurgali Feb 08 '18 at 16:05
  • You can introduce Liquibase for an existing database: http://www.liquibase.org/documentation/existing_project.html I don't use Flyway but I'm sure it can do something similar. –  Feb 08 '18 at 16:06
  • 3
    @DauletNurgali you try to use old pg_dump with new server - while you should do the opposite. try `locate bin/pg_dump` to find pg_dump for 9.6 version – Vao Tsun Feb 08 '18 at 16:13

2 Answers2

1

Try using flyway. It does exactly this. Dump your schema into an sql file and migrate using flyway.

sindhu_sp
  • 749
  • 5
  • 12
1

From pg_dump manual page you can try with pg_dump and psql, you can also check other flags for data, schema or table specific tasks. I personally sometime do this kind of job using Navicat or pgAdmin client.

To dump a database called mydb into a SQL-script file:

$ pg_dump mydb > db.sql

To reload such a script into a (freshly created) database named newdb:

$ psql -d newdb -f db.sql
A l w a y s S u n n y
  • 36,497
  • 8
  • 60
  • 103