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.

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"
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:
> -- 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;
> --
> -- Data for Name: fake_table; Type: TABLE DATA; Schema: public; Owner: postgres
> --
> COPY fake_table (id) FROM stdin;
> \.
> --
> -- 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.

No comments:

Post a Comment