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

On Wed, Oct 8, 2014 at 12:49 AM, Vitaly Isaev <visaev(at)team112(dot)ru> wrote:
> 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(at)[local]:5432 ~=#
create table t (i integer);
CREATE TABLE

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

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

skonoplev(at)[local]:5432 ~=#
truncate t;
TRUNCATE TABLE

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

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

skonoplev(at)[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
+1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979
gray(dot)ru(at)gmail(dot)com