How to synchronize tables in remote (production) and local databases in case if the structure of local database's table has been modified

[Vitaly Isaev]

[Sergey Konoplev]

I am trying to figure out how to dump the contents of several selected
tables from server in order to update the tables on development
workstations. The biggest challenge is that the tables I'm trying to
synchronize may be diverged (developers may add - but not delete - new
fields to the tables through the Django ORM, while schema of the production
database remains unchanged for a long time).

The COPY trick will probably help you. Note that I specify a column
list in the last COPY statement.

skonoplev@[local]:5432 ~=#
create table t (i integer);
CREATE TABLE

skonoplev@[local]:5432 ~=#
insert into t select * from generate_series(1, 5);
INSERT 0 5

skonoplev@[local]:5432 ~=#
copy t to '/tmp/t.dump';
COPY 5

skonoplev@[local]:5432 ~=#
truncate t;
TRUNCATE TABLE

skonoplev@[local]:5432 ~=#
alter table t add s text;
ALTER TABLE

skonoplev@[local]:5432 ~=#
copy t(i) from '/tmp/t.dump';
COPY 5

skonoplev@[local]:5432 ~=#
select * from t;
i | s
---+---
1 |
2 |
3 |
4 |
5 |
(5 rows)
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
[phone], [phone], [phone]
***@gmail.com

[Jim Nasby]

This post might be inappropriate. Click to display it.