We have a set of 7.4.x databases, and will occasionally copy data between like so:
psql -h $SOURCE_HOST ... -c "copy $SOURCE_SCHEMA.$SOURCE_TABLE to stdout" |\
psql ... -c "copy $TARGET_SCHEMA.$TARGET_TABLE from stdin"
This is always run on the host containing the target table.
We will now be adding 8.3.x databases to the mix, and will need to copy between
7.4.x and 8.3.x in both directions. The datatypes we use are:
- bigint
- bytea
- int
- timestamp with time zone
- varchar
Will our copying technique work between 7.4.x and 8.3.x databases in both
directions?
What if we do a binary copy instead? (We're going to investigate BINARY to see
if there is a performance improvement.)
Jack Orenstein