Normal development flow requires continuous patching the production database with local changes normally made automatically by the orm software, this method is not perect but deceptively simple, all we'll use is standard Unix commands, and is good enough for us. You will not see a database name or user name because we'll use the convention to use an operating system account owning the database in local and production and both databases local and remote are named as the user, all this with automatic ssh login in remote. The diff command and pg_dump The first aproach is to compare schemas, suppose you have your local dev version in localhost and the production version in other server called remote-server. 1. Obtain backup of db structure in production server ssh remote- pg_dump -s -Ox  > remote1. server sql Note: We obtain only the schema (-s) without any permissions and grant sql comands (-Ox). 2. Obtain backup of local database pg_dump -s -Ox  > . local sql 3. Compare the two schemas Nexts command compare the two files and filter the lines needed in remote to be like local using standard unix filters grep and sed . diff remote2. . | grep | sed sql local sql '^>' 's/^> //' Difference in database versions If the Postgres versions are different, chances are that will be a lot of differences not essential in nature, but in syntax, i.e, schema prefix usages. To be bullet proof we should make this comparison using the same version of postgres, for this you should create a local database with the remote schema and then make the comparison: temp1
psql -f remote1. temp1
pg_dump -s -Ox temp1  > remote2. createdb sql sql Now, compare again schemas same server version diff remote2 local .sql .sql Again to show the sql needed to patch remote to be like local use: diff remote2. . | grep | sed sql local sql '^>' 's/^> //' The only catch is if there are only field differences the sql generated could be invalid, so check it out before proceed. Finally, this script makes all [ -ne 1 ] Please give remote server arg Usage: postgres-diff remote-server-addr -1 SERVER= TEMPDB= REMOTE1= REMOTE2= LOCAL= Geeting schema from remote server
ssh pg_dump -s -Ox  > .sql Geeting schema from server
pg_dump -s -Ox  > .sql Restoring remote createdb psql -f .sql > /dev/null Getting diffs, this SQL could be invalid
pg_dump -s -Ox > .sql
diff .sql .sql  | grep | sed dropdb #!/bin/bash # Compare a remote database with its local counterpart if $# then echo echo exit fi $1 "tempdb- " $RANDOM "/tmp/r1- " $RANDOM "/tmp/r2- " $RANDOM "/tmp/loc- " $RANDOM echo $SERVER $REMOTE1 echo local $LOCAL echo in local $TEMPDB $REMOTE1 $TEMPDB echo $TEMPDB $REMOTE2 $REMOTE2 $LOCAL '^>' 's/^> //' $TEMPDB This code is available in github: https://github.com/hanspoo/postgres-diff

Flow

Live free or die


Read My Stories

Too Long; Didn't Read

In your car, at home, or at work — Bosch technology shapes many areas of life.

PostgreSQL diff Explained

Too Long; Didn't Read

Coin Mentioned

[email protected]

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

PostgreSQL diff Explained

Too Long; Didn't Read

Coin Mentioned

[email protected]

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES