Обсуждение: Migrating database(s) from Sybase ASE 15.7 to PostgreSQL 10.6 onLinux
Hello, We're investigating the migration of our LMS (Library Managment System) from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have around 400 columns, some of them are also containing BLOB (bytea) data. The DB size vary upto 20 GByte. The interfaces contain any kind of language one could imagine :-) ESQL/C, JDBC, Perl DBD, ... Re/ the migration of the data itself, are there any use case studies which could we keep in mind? We plan to export the tables with our own tool which produces CSV with delimiter '|' (and \| if the char | is in char columns too) and with hex representation of the BLOB data. This seems to fit nicely with PostgreSQL's COPY command. Any known pitfalls? Btw: We're investigating MySQL too, but this seems to be from the list now for not having an ESQL/C interface. Regards matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
On 5/3/19 6:56 AM, Matthias Apitz wrote: > Hello, > > We're investigating the migration of our LMS (Library Managment System) > from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have > around 400 columns, some of them are also containing BLOB (bytea) data. > The DB size vary upto 20 GByte. The interfaces contain any kind of > language one could imagine :-) ESQL/C, JDBC, Perl DBD, ... > > Re/ the migration of the data itself, are there any use case studies > which could we keep in mind? We plan to export the tables with our own > tool which produces CSV with delimiter '|' (and \| if the char | is in > char columns too) and with hex representation of the BLOB data. This seems > to fit nicely with PostgreSQL's COPY command. > > Any known pitfalls? Do you have many stored procedures, functions, etc? -- Angular momentum makes the world go 'round.
Re: Migrating database(s) from Sybase ASE 15.7 to PostgreSQL 10.6 onLinux
От
Christoph Moench-Tegeder
Дата:
## Matthias Apitz (guru@unixarea.de): > Re/ the migration of the data itself, are there any use case studies > which could we keep in mind? https://wiki.postgresql.org/images/e/e7/Pgconfeu_2013_-_Jens_Wilke_-_Sybase_to_PostgreSQL.pdf Regards, Christoph -- Spare Space
El día Friday, May 03, 2019 a las 07:38:23AM -0500, Ron escribió: > On 5/3/19 6:56 AM, Matthias Apitz wrote: > >Hello, > > > >We're investigating the migration of our LMS (Library Managment System) > >from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have > >around 400 columns, some of them are also containing BLOB (bytea) data. > >The DB size vary upto 20 GByte. The interfaces contain any kind of > >language one could imagine :-) ESQL/C, JDBC, Perl DBD, ... > > > >Re/ the migration of the data itself, are there any use case studies > >which could we keep in mind? We plan to export the tables with our own > >tool which produces CSV with delimiter '|' (and \| if the char | is in > >char columns too) and with hex representation of the BLOB data. This seems > >to fit nicely with PostgreSQL's COPY command. > > > >Any known pitfalls? > > Do you have many stored procedures, functions, etc? We have in Sybase triggers on some tables calculating the next value for an integer "serial" based on helper tables because Sybase does not know (or did not know in 11.9) about serials. But, these will be replaced by native "serial" on PG. Sybase also has a so called SYB_IDENTITY_COLUMN in each table with a unique number for each row (may have gaps) and as Sybase does not know SCROLLED CURSOR we simulated these in our DB layer reading-in all SYB_IDENTITY_COLUMN numbers of a hit list after SELECT and can read backwards in this in memory list presenting the requested row with a new SELECT based on the SYB_IDENTITY_COLUMN number. This is somewhat clumsy but certain features in upper layers want to read backwards (and we came from INFORMIX-SE, later INFORMIX-ONL some 30 years ago). I was deeply impressed by the COPY command, loading ~35000 rows in the time one needs to close and open the eyes. As well a SELECT returns in a table with ~35000 without any INDEX in very short time. How PG does this? matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub 70 years of NATO - 70 years of wars (Jugoslavia, Afghanistan, Syria, ...) and 70 years of war preparation against Russia. -- PEACE instead of NATO !
On 5/3/19 6:09 AM, Matthias Apitz wrote: > El día Friday, May 03, 2019 a las 07:38:23AM -0500, Ron escribió: > >> On 5/3/19 6:56 AM, Matthias Apitz wrote: >>> Hello, >>> >>> We're investigating the migration of our LMS (Library Managment System) >> >from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have >>> around 400 columns, some of them are also containing BLOB (bytea) data. >>> The DB size vary upto 20 GByte. The interfaces contain any kind of >>> language one could imagine :-) ESQL/C, JDBC, Perl DBD, ... >>> >>> Re/ the migration of the data itself, are there any use case studies >>> which could we keep in mind? We plan to export the tables with our own >>> tool which produces CSV with delimiter '|' (and \| if the char | is in >>> char columns too) and with hex representation of the BLOB data. This seems >>> to fit nicely with PostgreSQL's COPY command. >>> >>> Any known pitfalls? >> >> Do you have many stored procedures, functions, etc? > > We have in Sybase triggers on some tables calculating the next value for > an integer "serial" based on helper tables because Sybase does not know > (or did not know in 11.9) about serials. But, these will be replaced by native > "serial" on PG. > > Sybase also has a so called SYB_IDENTITY_COLUMN in each table with a > unique number for each row (may have gaps) and as Sybase does not know > SCROLLED CURSOR we simulated these in our DB layer reading-in all > SYB_IDENTITY_COLUMN numbers of a hit list after SELECT and can read > backwards in this in memory list presenting the requested row with a new > SELECT based on the SYB_IDENTITY_COLUMN number. This is somewhat > clumsy but certain features in upper layers want to read backwards (and > we came from INFORMIX-SE, later INFORMIX-ONL some 30 years ago). > > I was deeply impressed by the COPY command, loading ~35000 rows in the > time one needs to close and open the eyes. As well a SELECT returns in > a table with ~35000 without any INDEX in very short time. How PG does this? In the COPY case the entire thing is done in a single transaction. The downside to this is that a single error in the data will roll back everything. As to SELECT you are seeing the query planner at work. See the sections below for more info: 14.1 https://www.postgresql.org/docs/11/using-explain.html 14.2 https://www.postgresql.org/docs/11/planner-stats.html Indexes are still important. > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com
On 5/3/19 4:56 AM, Matthias Apitz wrote: > > Hello, > > We're investigating the migration of our LMS (Library Managment System) > from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have > around 400 columns, some of them are also containing BLOB (bytea) data. > The DB size vary upto 20 GByte. The interfaces contain any kind of > language one could imagine :-) ESQL/C, JDBC, Perl DBD, ... > > Re/ the migration of the data itself, are there any use case studies > which could we keep in mind? We plan to export the tables with our own > tool which produces CSV with delimiter '|' (and \| if the char | is in > char columns too) and with hex representation of the BLOB data. This seems > to fit nicely with PostgreSQL's COPY command. You might want to also take a look at: A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server) https://github.com/tds-fdw/tds_fdw > > Any known pitfalls? > > Btw: We're investigating MySQL too, but this seems to be from the list > now for not having an ESQL/C interface. > > Regards > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com
If you could use foreign data wrapper to connect https://github.com/tds-fdw/tds_fdw then you can skip the migration back and for to CSV.
You could even do partial migrations if needed (it could impact some queries' speed though).
Pablo
On Fri, May 3, 2019 at 6:37 AM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 5/3/19 4:56 AM, Matthias Apitz wrote:
>
> Hello,
>
> We're investigating the migration of our LMS (Library Managment System)
> from Sybase ASE 15.7 to PostgreSQL 10.6. The used database in field have
> around 400 columns, some of them are also containing BLOB (bytea) data.
> The DB size vary upto 20 GByte. The interfaces contain any kind of
> language one could imagine :-) ESQL/C, JDBC, Perl DBD, ...
>
> Re/ the migration of the data itself, are there any use case studies
> which could we keep in mind? We plan to export the tables with our own
> tool which produces CSV with delimiter '|' (and \| if the char | is in
> char columns too) and with hex representation of the BLOB data. This seems
> to fit nicely with PostgreSQL's COPY command.
You might want to also take a look at:
A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase
and Microsoft SQL Server)
https://github.com/tds-fdw/tds_fdw
>
> Any known pitfalls?
>
> Btw: We're investigating MySQL too, but this seems to be from the list
> now for not having an ESQL/C interface.
>
> Regards
>
> matthias
>
--
Adrian Klaver
adrian.klaver@aklaver.com