CODEWITHSUNDEEP
postgresql
Ken - Enough about Monica
Ken - Enough about Monica

Reputation: 1690

Getting a clean backup of my schema from PostgreSQL

I am backing up my schema and stored procedures by running pg_dump -s database. This works, but it seems to show every change I've made (an ALTER for every time I've altered something, each new declaration of stored procedures I've changed over time, etc.)

I'd like just the current schema so that I can restore things should something go wrong. Is this possible?

Upvotes: 0

Views: 72

Answers (1)

redneb
redneb

Reputation: 23840

TL/DR: This doesn't happen. pg_dump produces output that is optimized to make imports faster.

Are you sure about that? postgresql does not store at all the history of schema changes, so it would not be possible for pg_dump to output that. Here are some reasons that might have caused the confusion. Firstly, pg_dump typically breaks up create table statements into multiple statements. For example, consider this create table statement:

CREATE TABLE t (
    id integer primary key,
    q integer not null references q
);

pg_dump will convert it to

CREATE TABLE t (
    id integer NOT NULL,
    q integer NOT NULL
);
ALTER TABLE ONLY t
    ADD CONSTRAINT t_pkey PRIMARY KEY (id);
ALTER TABLE ONLY t
    ADD CONSTRAINT t_q_fkey FOREIGN KEY (q) REFERENCES q(id);

This is equivalent to the original one. Typically pg_dump creates statements for create table that in this order: (1) create the table without the constrains/indexes, (2) import the table data, and finally (3) create the constraints/indexes with alter table/create index. The reason that it does it in that order, is because it is faster to import the table data without indexes and create the indexes afterwards. But this doesn't mean that postgresql remembers the full history of changes to the table. If you add another column and call pg_dump afterwards, you will see the column in the resulting create table. Now, if you use the -s flag, this breaking up might be unnecessary but pg_dump does not change how it outputs the statements to create tables. It simply skips step (2) from above and it does steps (1) & (3).

Finally, the is another issue that might cause confusion. Function in postgresql can be overloaded by providing multiple definitions that have different types of arguments or different numbers of arguments. So if you do

CREATE OR REPLACE FUNCTION foo(x int) ...

and then later on you do

CREATE OR REPLACE FUNCTION foo(x text, y boolean) ...

then second statement will not delete the function created in the first one because the two functions are treated as different functions. So pg_dump will output them both. Again, this does not mean that postgresql remembers your old delete functions.

Upvotes: 1

Related Questions