Re: sybase->postgresql

Easiest way? Use perl to pull from Sybase and build a COPY-formatted file.
Then have perl log into postgres and load the file. Seems awkward, but it's
actually *much* faster than other methods. We do this for a 1GB Sybase
database for one client on a nightly basis; takes about 40 minutes.

Thanx Josh. My conceptual difficulty was logging into postgres using perl
since for sybase I have been using the specialized sybperl. But I guess
the standard documentation will help me there.

Cheers,
ashish

Ashish Mahabal, Caltech Astronomy, Pasadena, CA 91125
http://www.astro.caltech.edu/~aam aam@astro.caltech.edu

"One geometry cannot be more true than another; it can only be more
convenient. Geometry is not true, it is advantageous." Robert T. Pirsig

Ashish,

Thanx Josh. My conceptual difficulty was logging into postgres using perl
since for sybase I have been using the specialized sybperl. But I guess
the standard documentation will help me there.

Personally, I use the FreeTDS module:
www.freetds.org
... which may only work for older SyBase servers, though.

--
Josh Berkus
Aglio Database Solutions
San Francisco

You can also use sybase's BCP for some tables, if you know that there's
nothing that needs to be quoted. When I migrated stats.distributed.net
from sybase to pgsql I used both tactics; I bcp'd every table I could,
then used a perl script to copy from sybase to pgsql for tables that had
embedded tabs, linefeeds, etc.

The code I used for our migration is at
http://cvs.distributed.net/viewcvs.cgi/stats-sql/postgresql/. move.pl is
the perl script I used. Since I used BCP for all the really big tables I
just had the perl script insert directly into pgsql, but you could
always have it produce a file that copy could deal with as Josh
mentioned (in fact, you wouldn't even need to write to a file; copy can
read from stdin. This would save time if the amount of downtime for the
migration matters to you.)

On Wed, Feb 11, 2004 at 09:04:14AM -0800, Josh Berkus wrote:

Ashish,

postgresql you said (I saw this on a list):

Also, if you have a *running* Sybase database, conversion is a lot
easier ... you can use Perl::DBI to read directly from sybase to a COPY
file, and then load the COPY file into Postgres.

I am brand new to postgres and do have a running sybase which I wish to
convert. I have used perl's DBlib. However, I am not sure how to get the
output to copy. I will also be sending the question to postgres lists, but
thought I would send it to you too in case you are able to suggest
something.

Easiest way? Use perl to pull from Sybase and build a COPY-formatted file.
Then have perl log into postgres and load the file. Seems awkward, but it's
actually *much* faster than other methods. We do this for a 1GB Sybase
database for one client on a nightly basis; takes about 40 minutes.

--
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

--
Jim C. Nasby, Database Consultant jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

See Also:

UNLOAD [ FROM ] TABLE [ owner. ]table-name TO filename-string
[ unload-option ... ]

unload-option :
DELIMITED BY string
| ESCAPE CHARACTER character
| ESCAPES {ON | OFF}
| FORMAT {ASCII | BCP}
| HEXADECIMAL {ON | OFF}
| ORDER {ON | OFF}
| QUOTES {ON | OFF}

Josh Berkus wrote:

Ashish,

postgresql you said (I saw this on a list):

Also, if you have a *running* Sybase database, conversion is a lot
easier ... you can use Perl::DBI to read directly from sybase to a COPY
file, and then load the COPY file into Postgres.

I am brand new to postgres and do have a running sybase which I wish to
convert. I have used perl's DBlib. However, I am not sure how to get the
output to copy. I will also be sending the question to postgres lists, but
thought I would send it to you too in case you are able to suggest
something.

Easiest way? Use perl to pull from Sybase and build a COPY-formatted file.
Then have perl log into postgres and load the file. Seems awkward, but it's
actually *much* faster than other methods. We do this for a 1GB Sybase
database for one client on a nightly basis; takes about 40 minutes.

--
It is one of the essential features of such incompetence that the person so afflicted is incapable of knowing that he is incompetent. To have such knowledge would already be to remedy a good portion of the offense. ( Miller, 1993 , p. 4)