Migrate SQL Server repository to PostgreSQL

warning

PostgreSQL repositories will not work with Dataedo Desktop, move the repository only if you plan to use only Dataedo Portal

If your Dataedo repository currently runs on SQL Server and you want to move it to PostgreSQL, you do not have to set everything up from scratch and re-import your data sources. Dataedo CLI comes with a copy-to-postgres command that copies all data from your SQL Server repository into a PostgreSQL repository.

Before you start

• you need an existing SQL Server repository. If you have not set up a repository yet and want it to be on PostgreSQL, read this article instead
• you need a PostgreSQL repository already created (with dataedo create) on the same Dataedo version as the source repository
• stop the Web Portal and Agent before running the command
• the migration is a safe, non-destructive process. However, your SQL Server repository will be set to read only mode during creation, so you can't use it or add new information to it during the export

Copy to PostgreSQL using Dataedo CLI

The copy runs through Dataedo CLI, which ships with the Dataedo Editor and Agent installers. If you are not sure whether it is on your machine, run dataedo --help in a terminal — if it is installed, you will get a list of available commands, copy-to-postgres among them. You can also run dataedo copy-to-postgres --help at any point to see the options below without leaving the terminal.

copy-to-postgres options

Running dataedo copy-to-postgres --help lists the options the command accepts:

Option	Description
--help	Show help information.
-tsn, --targetServerName	Target PostgreSQL server name
-tp, --targetPort	Target PostgreSQL port (default: 5432)
-tl, --targetLogin	Target PostgreSQL login
-tpass, --targetPassword	Target PostgreSQL password
-tdn, --targetDatabaseName	Target PostgreSQL database name
-uc, --updateConfig	After a successful copy, point the Web Portal configuration at the PostgreSQL repository. Default: False.

Migration — basic configuration

For a basic run, you do not need to pass any of the options above — just call the command and answer the prompts. This is the quickest way to run the migration and works for most cases.

1. Stop the Web Portal and Agent.

2. Open a terminal on the machine where Dataedo CLI is installed and run:

dataedo copy-to-postgres

3. You will be prompted for the target repository's connection details, one at a time:

Target server name: 
Target login: 
Target password: 
Target database name: 

4. Dataedo will connect to both repositories and start copying data. Existing data in the target repository is cleared first (this includes the data that was seeded when the repository was created), then tables are copied over one by one, with progress printed to the terminal as it goes.

5. Once every table is copied, Dataedo resets the PostgreSQL sequences and verifies the row counts between the source and the target. If everything matches, you will see a summary line and a success message:

Resetting sequences...
Verifying row counts...
Copied XYZ rows across XYZ tables in XYZ.
Command successfully completed: copy-to-postgres
Done

If the row counts don't match, the command fails with details on which table didn't come across correctly - re-run the copy once the issue is resolved.

After the copy

Once the copy finishes, point your Web Portal and Agent configuration at the PostgreSQL repository (or pass -uc / --updateConfig when you run the command to have this done for you), then start the Web Portal and Agent back up.