Updating PostgreSQL schema with schema dump

Question

I have two servers: production and testing. Both of them run PostgreSQL. I made a lot of alterations on testing server, like ALTER table, CREATE INDEX etc. and want to transfer these alterations to the production server as easy as possible. What is the most convenient way for this?

This is PostgreSql, amirite? ... No, I think you mean software.fionet.com/pSQL ? — user1086498
– user1086498, Commented Apr 5, 2012 at 15:25

user1086498 · Accepted Answer · 2012-04-06 12:25:17Z

2

The best way to do this would be:

Run pgdump on each database, creating a full dump.
Use a comparison tool like Beyond Compare to run through the two files side by side and create a merge; given that you've said there are only 'alter' changes and data changes the two should be similar enough that it can line them up
Create a new test DB to deploy that script to, and run the script via psql (or whatever tool you use.)
If that script runs successfully, wipe out the target DB and run that script against it.
Make sure the application(s) that the DB backs are put offline during this process, at least during the time when you drop the DB and redeploy.
If there are additional data changes, you may wish to pgdump the target DB before dropping it. You can then merge back in the data added during the time between the first pgdump on the target DB and the second one.

Other notes:

Pgdump is a command line tool, usually run via ssh (like PuTTY)
It outputs ASCII text SQL scripts in proper creation order, since PostGre can have object interdependencies.
psql, the command line interface to PostGre should allow you to execute a script from file, if you don't have a GUI program that can handle a SQL script as large as your entire dump.
There are options to dump data and structure separately.

answered Apr 6, 2012 at 12:25

user1086498

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

2 Comments

Dmitriy Berkutov Over a year ago

Thank you for guidelines. I've come over the special posgtresql diff tool: apgdiff.startnet.biz/diff_online.php

Tomasz Waszczyk Over a year ago

@RiverC How do create full dump on Postgresql ?

Collectives™ on Stack Overflow

Updating PostgreSQL schema with schema dump

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related