Adrian Klaver <adrian.klaver@xxxxxxxxx> Thursday 17 March 2011 19:18:25 > On Thursday, March 17, 2011 10:10:49 am Brent Gulanowski wrote: > > We use PG COPY to successfully in PG 8 to copy a database between two > > servers. Works perfectly. > > > > When the target server is PG 9, *some* fields of type timezonetz end up > > garbled. Basically the beginning of the string is wrong: > > > > 152037-01-10 16:53:56.719616-05 > > > > It should be 2011-03-16 or similar. > > > > In this case, the source computer is running Mac OS X 10.6.6 on x86_64 > > (MacBook Pro Core i5), and the destination computer is running Debian > > Lenny on Xeon (Core i7). > > > > I looked at the documentation on the copy command, and the PG9 release > > notes, but I didn't see anything that might explain this problem. > > > > We are using the WITH BINARY option. It has been suggested to disable > > that. What are the down sides of that? I'm guessing just performance > > with binary columns. > > I think the bigger downsides come from using it:) See below for more > information: > > http://www.postgresql.org/docs/9.0/interactive/sql-copy.html > > "Binary Format > The binary format option causes all data to be stored/read as binary format > rather than as text. It is somewhat faster than the text and CSV formats, > but a binary-format file is less portable across machine architectures and > PostgreSQL versions. Also, the binary format is very data type specific; > for example it will not work to output binary data from a smallint column > and read it into an integer column, even though that would work fine in > text format. > The binary file format consists of a file header, zero or more tuples > containing the row data, and a file trailer. Headers and data are in > network byte order. " Actually binary mode is faster in some situations, and slower with other, in any case it should save space in backup files or during transmission (e.g. binary tz takes 8 bytes, text takes more) But this may be due to encoding of timestamptz, you could have 8 version compiled with float timestamps, and 9 with integer tiemstamps or vice versa. Regards, Radek -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
