Synchronize unicode data using copy or pg_dump
Hi,
I need to synchronize some tables from a database (master) to another
one (slave).
Both servers are running Debian Woody with PostgreSQL 7.2.1 (postgresql
7.2.1-2woody4).
The databases are in unicode and doesn't contain any binary data.
The tables have primary/foreign key constraints, sequences and indexes,
but no triggers/rules. There are OIDs but these are different on the 2
databases. In fact they are not used by the application.
I need to do that ASAP, and hopefully only once (there are already some
kind of home-made replication scripts, that are not the origin of my
problem).
Later I would like to set up pgreplication or something... any advice on
that is welcomed too ;-)
What would be the fastest way to achieve that?
On the master:
For each table, use COPY TO or pg_dump?
On the slave:
- delete everything from these tables
- drop the FK constraints
- (drop indexes?)
- vacuum (or later?)
- import from files (using COPY FROM or pg_restore)
- recreate the FK constraints
- update the sequences
- reindex (or recreate them)
Does that sound good?
Thanks for you help,
Pascal Polleunus
Pascal Polleunus wrote:
Hi,
I need to synchronize some tables from a database (master) to another
one (slave).
Both servers are running Debian Woody with PostgreSQL 7.2.1 (postgresql
7.2.1-2woody4).
The databases are in unicode and doesn't contain any binary data.
The tables have primary/foreign key constraints, sequences and indexes,
but no triggers/rules. There are OIDs but these are different on the 2
databases. In fact they are not used by the application.I need to do that ASAP, and hopefully only once (there are already some
kind of home-made replication scripts, that are not the origin of my
problem).
Later I would like to set up pgreplication or something... any advice on
that is welcomed too ;-)What would be the fastest way to achieve that?
Wouldn't it be easier to copy directly the files from the filesystem?
Are there particular precautions to take before doing that (except
stopping pg)?
Show quoted text
On the master:
For each table, use COPY TO or pg_dump?On the slave:
- delete everything from these tables
- drop the FK constraints
- (drop indexes?)
- vacuum (or later?)
- import from files (using COPY FROM or pg_restore)
- recreate the FK constraints
- update the sequences
- reindex (or recreate them)Does that sound good?
Thanks for you help,
Pascal Polleunus