Search Postgresql Archives

Re: best practice transitioning from one datatype to another

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.lehmann@xxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
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...@xxxxxxxxxxxxxxxxxxxxxxx (Andreas Wenk)
wrote:
> Arndt Lehmann schrieb:
>
> > On Jul 16, 8:05 am, t...@xxxxxxxxxxxxx (Tom Lane) wrote:
> >> CG <cgg...@xxxxxxxxx> 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...@xxxxxxxxxxxxxx)
> >> 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...@xxxxxxxxxxxxxx)
> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux