Search code examples
dumppostgresql

Updating PostgreSQL schema with schema dump

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?

Solution

  • The best way to do this would be:

    1. Run pgdump on each database, creating a full dump.
    2. 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
    3. Create a new test DB to deploy that script to, and run the script via psql (or whatever tool you use.)
    4. If that script runs successfully, wipe out the target DB and run that script against it.
    5. 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.
    6. 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.