Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: SQL Server 2k migration to PostgreSQL

    Hi all

    I hope you can help me in some way.

    Basically for the last 5 months i've developed a SQL 2k db for my company.

    All of a sudden they quite fancy using Linux on the server, therefore i would be required to use PostgreSQL instead of SQL Server 2k.

    I could do with some advice about how PostgreSQL and SQL Server compare? and how much work would be involved in migrating from one to the other.

    Points i've found myself and may be an issue are:

    I've used some of SQL Servers XML capabilites and as far as i can tell PostgreSQL doesn't support XML.

    I've made extensive use of functions that return table variables etc... is there a similar idea i can use in PostgreSQL?

    Extensive use of Stored Procedures and T-SQL etc...

    DTS packages. Any equivalent or similar idea?

  2. #2
    Join Date
    Apr 2003

    Arrow Complete Howto

    Follow the steps below to convert your SQL Data to PostgreSQL. Any think following a “#” is the exact command to type at the shell prompt

    From Your Linux Machine:
    1.As root, create a postgres user. # adduser postgres
    2.Install the PostgreSQL RPM’s on your machine.
    3.Install PhpPgAdmin
    4.Start Postgre SQL for the first time. # service PostgreSQL start
    5.Set PostgreSQL to run at system Startup. # chkconfig --add PostgreSQL
    6.Login as the Postgres User # su postgres
    7.Create a Blank Database # createdb Databasename

    From your SQL 7 Server or a machine with the SQL administration tools installed:
    1.Install the PostgreSQL ODBC Drivers
    2.Open the Import and Export Data tool. (Part of SQL 7)
    3.On the Source Screen pick your SQL Server and the database you wish to export from.
    4.On the Destination Screen
    a.Select “Other (ODBC Data Source)” as the destination
    b.Create a new User/System DSN
    i.Type of data source: Either a System or User Data source
    ii.Driver: PostgreSQL
    c.Driver Setup Screen.
    i.Enter the database name from step 6
    ii.Enter the name or IP of the PostgreSQL server
    iii.Do not enter a username or password on this screen
    d.On the Destination screen enter “Postgres” as the username and enter a Space in the Password field
    e.Pick the table(s) you wish to export
    f.Export the tables. You will receive an error about relationship not existing. Just Press Ok.
    5.Now you need to make corrections to the table(s) structure.
    a.Open the PhpPgAdmin site in your browser
    b.Look at the Properties for the table you just imported.
    c.Check for Boolean fields, they were most likely imported as char (1).
    d.Check all other fields to make sure the correct data type was imported
    e.View the dump Structure
    f.Copy the Select statement to the clipboard
    g.Drop the table from the database
    h.Paste your select statement in to the “Run SQL Code” Box
    i.Correct any data types that were wrong
    j.Run the Select Statement
    k.You should know have a correctly structured table
    6.Now you need to Export all your data to a Delimited text File.
    a.Open the Import and Export Data tool. (Part of SQL 7)
    b.On the Source Screen pick your SQL Server and the database you wish to export from.
    c.On the Destination Screen choose Text File, and enter a file name
    d.Pick the table you wish to export
    e.Change the Row Delimiter to {LF} and change the column delimiter to Tab and change quoting to none.
    f.Export the data
    7.You now need to remove any embedded carriage returns.
    8.Open your text file in the Hex Editor (If prompted to convert the file to Dos mode press NO)
    9.View the file in Hex Mode (CTRL+H if you are using UltraEdit32)
    10.You find all existences of “0D 0A” with “20” This will Replace all the embedded carriage returns with a space.
    11.Next you need to add a NULL Value Search for the values “09 09” and then replace with “09 7C 09” This finds any blank fields and places a | character there.
    12.Save the file
    13.Copy the file to your PostgreSQL server
    14.Use VI or another text editor to add the following line to the beginning of the text file. “Copy TableName from stdin with null as ‘|’;”
    15.At the end of the file add. “\.”
    16.Save the file and exit the editor
    17.Login as the postgres user. # Su Postgres
    18.Import the file. # Psql tablename > filename
    19.You should now have your table correctly imported.
    Open up
    Take a look to my Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts