Trying to fix a dump file ... cat backup.sql | sed -e "s/uniqueidentifier/uuid/g" > backup_fixed.sql
... gives me a dump that won't import. It is hard digging through 30+ gigs of text data to find where sed ate a field delimiter, so I'm going to give Tom's idea a try. I didn't even know the ALTER TYPE x RENAME TO y; was even available, and I probably wouldn't have tried it if Tom hadn't suggested it. It takes a certan amount of chutzpah to make that kind of change before diving into the lengthy process of database upgrading. ________________________________ From: Arndt Lehmann <arndt.lehm...@gmail.com> To: pgsql-general@postgresql.org Sent: Thursday, July 16, 2009 5:22:26 AM Subject: Re: [GENERAL] best practice transitioning from one datatype to another On Jul 16, 5:46 pm, a.w...@netzmeister-st-pauli.de (Andreas Wenk) wrote: > Arndt Lehmann schrieb: > > > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: > >> CG <cgg...@yahoo.com> writes: > >>> While transitioning from 8.1 to 8.4, I need to transition to the internal > >>> UUID type in place of the contrib/uniqueidentifier module. I've built the > >>> database around uniqueidentifier, so nearly every table has one column of > >>> that data type. It's going to be tedious to > >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; > >>> ...repeat 600 times... > >>> I'll also have to drop and reload the views and the rules on tables. > >>> It'll be tedious even if the tables have no data in them. > >>> Can anyone recommend a better/faster way to make the transition? > >> Couldn't you rename the type to uuid in the 8.1 database before you > >> dump? > > >> regards, tom lane > > >> -- > >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > >> To make changes to your > >> subscription:http://www.postgresql.org/mailpref/pgsql-general > > > Just an idea - don't know if it works, first try on a test server: > > 1. Dump the complete database into text format (pg_dump --format=t) > > 2. Do a search and replace from "uniqueidentifier" to "uuid" > > 3. Reimport > > > Best Regards, > > Arndt Lehmann > > uh - --format=t means tar format. --format=p means plain text ... > > Or am I missing something? > > $pg_dump --help > Usage: > pg_dump [OPTION]... [DBNAME] > > General options: > ... > -F, --format=c|t|p output file format (custom, tar, plain text) > ... > > Cheers > > Andy > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Andy, You are perfectly right. My oversight. Best Regards, Arndt Lehmannn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general