Friday, January 6, 2012

Postgresql - Spotting differences between schemas

How do you tell the difference between database schemas in an automatic fashion?

One approach is to make a sql dump from both schemas and apply the diff unix command.

http://linux.101hacks.com/linux-commands/diff-command-examples/

I'll use a toy database called switch_books

First make the first backup (the "before" backup)
-bash-4.2$ pg_dump switch_books > switch_books_before.sql
-bash-4.2$ psql switch_books

Then I create a dummy table:
switch_books=# create table fake_table (id integer primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fake_table_pkey" for table "fake_table"
CREATE TABLE
switch_books=# \q

And take a new shot (the after backup)
-bash-4.2$ pg_dump switch_books > switch_books_after.sql

# generate the diff file
-bash-4.2$ diff switch_books_before.sql switch_books_after.sql > switch_books_diff.sql

-bash-4.2$ cat switch_books_diff.sql | less


And reviewing the results:
164a165,175
> -- Name: fake_table; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
> --
>
> CREATE TABLE fake_table (
>     id integer NOT NULL
> );
>
>
> ALTER TABLE public.fake_table OWNER TO postgres;
>
> --
358a370,377
> -- Data for Name: fake_table; Type: TABLE DATA; Schema: public; Owner: postgres
> --
>
> COPY fake_table (id) FROM stdin;
> \.
>
>
> --
583a603,610
> -- Name: fake_table_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
> --
>
> ALTER TABLE ONLY fake_table
>     ADD CONSTRAINT fake_table_pkey PRIMARY KEY (id);
>
>

RAF mentioned today this utility in a conversation.


There are other tools that go one step beyond by generating a sql script that will make the differences go away.
http://apgdiff.startnet.biz/

No comments:

Post a Comment