COPY between 7.4.x and 8.3.x
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
On 4:05 pm 07/21/08 Jack Orenstein <jack.orenstein@hds.com> wrote:
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
I believe it should work.
Also, one feature I believe started in the 8.X line (8.2?), is the ability
to have a subquery in the copy command to refine what rows you are getting.
What if we do a binary copy instead?
What do you mean by a binary copy?
pg_dump -Fc?
I think a plain pg_dump and copy will likely be more portable. Specially
going from 8.3 to 7.4.
Why will you keep copying data back and forth?
Not possible to setup a new 8.3, migrate to it and then upgrade the other
7.4 machine to 8.3?
Francisco Reyes wrote:
On 4:05 pm 07/21/08 Jack Orenstein <jack.orenstein@hds.com> wrote:
What if we do a binary copy instead?
What do you mean by a binary copy?
pg_dump -Fc?
No, I mean changing this:
psql -h $SOURCE_HOST ... -c "copy $SOURCE_SCHEMA.$SOURCE_TABLE to stdout" |\
psql ... -c "copy $TARGET_SCHEMA.$TARGET_TABLE from stdin"
to this:
psql -h $SOURCE_HOST ... -c "copy binary $SOURCE_SCHEMA.$SOURCE_TABLE to
stdout" |\
psql ... -c "copy binary $TARGET_SCHEMA.$TARGET_TABLE from stdin"
Why will you keep copying data back and forth?
Not possible to setup a new 8.3, migrate to it and then upgrade the other
7.4 machine to 8.3?
We're migrating a cluster from 7.4 to 8.3. To maintain availability, we need the
7.4 and 8.3 databases up at the same time. We're copying data across versions
for this reason.
Jack
On 6:01 pm 07/21/08 Jack Orenstein <jack.orenstein@hds.com> wrote:
to this:
psql -h $SOURCE_HOST ... -c "copy binary $SOURCE_SCHEMA.$SOURCE_T
ABLE to
stdout" |\
psql ... -c "copy binary $TARGET_SCHEMA.$TARGET_TABLE from stdin"
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html
The BINARY key word causes all data to be stored/read as binary format
rather than as text. It is somewhat faster than the normal text mode, but a
binary-format file is less portable across machine architectures and
PostgreSQL versions.<<
I would suggest to not go that route.
However, you could just test it and see if it works.
If you are doing multiple tables I still think you should consider pg_dump
-Fc. You can restore just the data without the DDL.