Database Migration - From Sybase ASE To PostgreSQL

Database migrationfrom Sybase ASE to PostgreSQLAchim Eisele and Jens Wilke1&1 Internet AG8.11.2013

AgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

IntroductionAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

IntroductionGoalsWhy we had been dissatisfiedmany DBs on one Systemthe availability was depending on third party Systemsmany downtimesperformance dependenciesOur goalsautonomyperformance improvementshigh availability satisfied customers :-)1&1 Internet AG 2013Database migration1/26

IntroductionWhy PostgreSQLMost advanced Open Source RDBMSBest TCO1&1 Internet AG 2013Database migration2/26

AnalysisAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

Analysis3 Applications250 GB size20 Mio. Queries per dayup to 1.500 transactions per second1&1 Internet AG 2013Database migration3/26

AnalysisArchitectureSybaseall reading and writing operations on one systemmany databases, user and tools on a single systemPostgreSQLHeartbeat / DRBDWAL / Streaming Replication (not synchronous!)SR-Standby for dedicated Statements1&1 Internet AG 2013Database migration4/26

Differences between Sybase ASE and PostgreSQLAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

Differences between Sybase ASE and PostgreSQLLimitations of PostgresSybase offers bi-directional replication using the replicationagenton Sybase it is possible to switch the replication directionSybase offers replication of single tables with few impact,using the transaction logSybase offers a replication solution for Road WarriorsRAW Devices, advantage on modern file systems is doubtableI/O can be audited by processPlans of running queries can be shown1&1 Internet AG 2013Database migration5/26

Differences between Sybase ASE and PostgreSQLAdvantages of Postgres 1/3Postgres’ MVCC vs Sybase’s lockingPostgres is conforming to SQL-standardnested transactions vs savepointsPostgres does know schemesPostgres does support grant for colums, not just revokeon Postgres it is possible to restrict the connection limit peruserLogins can be restricted to IP adresses in Postgres1&1 Internet AG 2013Database migration6/26

Differences between Sybase ASE and PostgreSQLAdvantages of Postgres 2/3On Sybase ASEstrings longer than varchar/char or a page are cropped or faildifferent behaviour on insert and updatedefault ASE pagesize is 2kSybase creates a fixed size container for the DB and thetransaction log.shrinking the DB container is only possible with version 15.71&1 Internet AG 2013Database migration7/26

Differences between Sybase ASE and PostgreSQLAdvantages of Postgres 3/3Postgres’ sequences are not limited to a single column asSybase’s identity columns are and the value can be overwrittenbcp (COPY) is neither capable to escape nor to quotePostgres does support multiple trigger for one eventSybase is planning to implement advisory locks for futureversionsindex concurrently since ASE 15.7 SP 100, May 20131&1 Internet AG 2013Database migration8/26

Porting the applicationAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

Porting the applicationDatabase driverJava (Hibernate)loading JDBC driversetting Dialect and DriverClassPHPOn Debian:apt-get install php5-pgsqlPDO only need another data source name“Plain” PHPsybase connect(), sybase select db() pg connect()sybase query() pg query()sybase fetch object() pg fetch object()1&1 Internet AG 2013Database migration9/26

Porting the applicationSybase vs PostgresSQL Dialect 1/3Date/timeSybaseSELECT getdate();-- Oct 30 2013 03.52PMSELECT convert(varchar(30), getdate(), 109);-- Oct 30 2013 03:52:34:126PMPostgreSQLSELECT current timestamp;-- 2013-10-30 15:52:34.126305 01SELECT to char(current timestamp,’Mon DD YYYY HH12:MI:SS:MSAM’);1&1 Internet AG 2013Database migration10/26

Porting the applicationSybase vs PostgresSQL Dialect 2/3concatenationSybaseSELECT zip ’ ’ city FROM addressPostgreSQLSELECT zip ’ ’ city FROM addressfield lengthSybaseset textsize 512000-- limits the size of a text or image column that is-- returned through a select statement. The default is 32KB.PostgreSQLn/a1&1 Internet AG 2013Database migration11/26

Porting the applicationSybase vs PostgresSQL Dialect 3/3LIMIT TOP, OFFSET is not available in Sybase ASENULL HandlingText-types in Sybase ASENULL empty stringempty string blank-- The empty string, "" or ’’, is stored as a single space-- rather than as NULL. Thus, "abc" "" "def"-- is equivalent to "abc def", not to "abcdef".Sybase’s BIT 0 and 1 only (NULL is not allowed)vs Postgres’ BOOLEAN, TRUE/FALSE and NULL1&1 Internet AG 2013Database migration12/26

Migration strategiesAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

Migration strategiesProceduresMigration strategies:Dump ReloadReplication1&1 Internet AG 2013Database migration13/26

Migration strategiesDump - Reload 1/5adjust and import the DDLstop the application/write accessexport Data, search and replace dates and times, importcreate indexes, foreign keys and set sequencesimport and index/key creation by multiple processes in parallellogical checksre-configure and start the application1&1 Internet AG 2013Database migration14/26

Migration strategiesDump - Reload 2/5Advantagessimpleless effortrollback is also simpleDisadvantageslonger downtimelimits regardiung quoting, escaping and content (date/time)empty strings are converted to NULL1&1 Internet AG 2013Database migration15/26

Migration strategiesDump - Reload 3/5Export with bcpbcp db. tablename out fifo path/ tablename.pipe -Jutf8 -c -t ’;’Sybase bcp is not capable to write in unnamed pipes, just infilesDate and time formats of Sybase are not conforming to anystandard: milliseconds are separated by colons and a whitespace is missing before AM/PM1&1 Internet AG 2013Database migration16/26

Migration strategiesDump - Reload 4/5Import in Postgres using COPY and fix the timestamps:mkfifo fifo path/ tablename.pipecat fifo path/ tablename.pipe seds/\(;\ \ [A-Z]\)\([ ;\]*\):\([0-9]\ \)\(AM\ PM\)\(;\ \)/\1\2.\3 \4\5/g psql -d IMPORTDB -c"COPY tablename from stdin CSV DELIMITER ’;’"alter sequence seq restart with max(id) 1create indexes and foreign keys1&1 Internet AG 2013Database migration17/26

Migration strategiesDump - Reload 5/5the image type is converted to binaryimage is imported into a text field and decoded after thatCREATE TABLE tablename2 as (select id,decode(image, ’hex’) as image,. from tablename);DROP TABLE tablename;ALTER TABLE tablename2 RENAME to tablename;1&1 Internet AG 2013Database migration18/26

Migration strategiesSybase PostgreSQL - ReplicationClosed source replication tool based on transaction logssetup replication, wait until it’s synchronised and restart there-configured application.Advantagesflexible point of time for the switchminimal downtime less organisational overhead :)no impact on the production DBDisadvantagesexpensiveclosed source without warranty1&1 Internet AG 2013Database migration19/26

Migration strategiesPseudo-replicationTop requirement: minimal downtime!should we write our own trigger based replication tool?too much resource consuming on the serveranalysis of the table usagestatic tablesautoincrement tablesnon-static tablestool based migrationOptimizations (indexes, field lengths)1&1 Internet AG 2013Database migration20/26

Migration strategiesPseudo-replication - DDL 1/2Automated Export of DDLsp tablessp columnssp pkeyssp fkeyssp statisticssp helpusersp helprotect1&1 Internet AG 2013Database migration21/26

Migration strategiesPseudo-replication - DDL 2/2// iterate over all tablesfor table in sp tables"CREATE TABLE table- name ("// now iterate over all columns of tablefor column in sp columns table- name" column- name column- type DEFAULT column- default"// add primary key"ALTER TABLE table- name ADD PRIMARY KEY " sp pkeys// grant statementsfor right in sp helprotect"GRANT right- action ON table- name TO right- user"1&1 Internet AG 2013Database migration22/26

Migration strategiesPseudo-replication - Data 1/2Data importto reduce the system load, batch jobs were suspendedimport by 10 processes in parallel(horizontal partition)chunks of 5.000 rows per selectSetup Up-Sync (auto increment)80 tables migrated by triggers (non static tables)using a transitional migration table(table name, primary key, action)1&1 Internet AG 2013Database migration23/26

Migration strategiesPseudo-replication - Data 2/2Finalisationcreate indexesduring downtimecreate sequencescreate foreign keyscontent checksre-configure the applications’ DB connection parametertestsresult: 10 minutes downtime1&1 Internet AG 2013Database migration24/26

RetrospectsAgendaIntroductionAnalysisDifferences between Sybase ASE and PostgreSQLPorting the applicationMigration strategiesRetrospects

Retrospects12 months with PostgreSQLachivement of objectivesautonomy: dedicated hardwarestability: not one single outage in 12 monthsperformance: dedicated hardware, read-only standby1&1 Internet AG 2013Database migration25/26

RetrospectsQuestions?1&1 Internet AG 2013Database migration26/26

Postgres is conforming to SQL-standard nested transactions vs savepoints Postgres does know schemes Postgres does support grant for colums, not just revoke on Postgres it is possible to restrict the connection limit per user Logins can be restricted to IP adresses in Postgres 1&1 Internet AG 2013 Database migration 6/26