SQL Server to PostgreSQL Migration

This project restores a SQL Server .bak backup and migrates its contents to PostgreSQL with pgloader. The entire environment runs through Docker Compose and produces a summary for every migration.

Requirements

• Docker Engine with the Docker Compose plugin.
• Enough disk space for the source backup, both database volumes, and the resulting dump.
• A full SQL Server backup accessible through any local path.

Components

• docker-compose.yml: defines the SQL Server, PostgreSQL, and pgloader services, including local credentials.
• Dockerfile.pgloader: builds pgloader with an 8 GiB heap for large databases.
• freetds.conf: configures TDS 7.4, UTF-8, and large text fields.
• restore_and_migrate.sh: restores the .bak file and runs the migration.
• export_postgres.sh: exports and verifies a PostgreSQL database.
• backups/: contains PostgreSQL exports generated by the scripts.
• logs/: contains summaries generated by pgloader.

The backups/ and logs/ directories contain local artifacts. The scripts create them when needed, Git ignores them completely, and they are excluded from the Docker build context.

Configuration

The images and passwords are declared directly in docker-compose.yml because this project is intended for a controlled local environment. Update the image and environment properties in that file when they need to change.

SQL Server is published on port 1433, and PostgreSQL is published on port 5432.

Connecting from external clients

Both database engines publish their ports on the Docker host. Use localhost from a client running on the same machine. From another machine on the network, use the Docker host's IP address and allow the corresponding port through the firewall.

SQL Server

Parameter	Value
Host	localhost or <DOCKER_HOST_IP>
Port	1433
User	sa
Password	S3rv3r_Docker
Database	sqlserver_db or the second migration argument
Authentication	SQL Server Authentication

The container uses a local certificate. If the client requires encryption, enable the option equivalent to Trust server certificate (TrustServerCertificate=True). Example connection string:

Server=localhost,1433;Database=sqlserver_db;User Id=sa;Password=S3rv3r_Docker;TrustServerCertificate=True;

PostgreSQL

Parameter	Value
Host	localhost or <DOCKER_HOST_IP>
Port	5432
User	postgres
Password	postgres
Database	postgres_db or the third migration argument

Example connection URI:

postgresql://postgres:postgres@localhost:5432/postgres_db

These credentials are intended exclusively for a controlled local environment. Do not expose ports 1433 or 5432 to the Internet, and change the passwords before using this configuration on an untrusted network.

Running a migration

Validate and build the environment:

docker compose config --quiet
bash -n restore_and_migrate.sh export_postgres.sh
docker compose build pgloader

Run a complete migration:

./restore_and_migrate.sh /path/to/source.bak sqlserver_db postgres_db

The arguments are, in order: the source backup path, the name of the restored SQL Server database, and the name of the destination PostgreSQL database. The backup path may be absolute or relative to the working directory. The script mounts the directory containing the .bak file inside the container as read-only; it does not move or copy the file into backups/. The restore replaces an existing SQL Server database with the same name. Use an empty PostgreSQL database when repeating a migration.

When the migration finishes, the script prints the pgloader statistics table and asks whether to create a PostgreSQL dump. Answer y to save a timestamped dump in backups/. For non-interactive runs, set EXPORT_POSTGRES_DUMP=y to create it or EXPORT_POSTGRES_DUMP=n to skip it.

Inspect service status and logs:

docker compose ps
docker compose logs -f mssql postgres
tail -n 40 logs/pgloader-summary-*.log

The migration summary is also displayed automatically when the migration finishes. It should end with Total import time and a success mark. Isolated FreeTDS connection warnings do not indicate a failed migration when the summary reports zero errors.

Exporting and restoring PostgreSQL

Create a compressed dump with an automatically generated timestamped filename:

./export_postgres.sh postgres_db

You may also provide an explicit output path:

./export_postgres.sh postgres_db backups/copy.dump

The script verifies that the database exists, refuses to overwrite an existing file, validates the dump catalog, and displays its size and SHA-256 checksum.

To restore the dump into a new database:

RESTORE_DATABASE=postgres_restore
DUMP_FILE=backups/copy.dump
docker exec -u postgres postgres createdb "$RESTORE_DATABASE"
docker exec -i postgres pg_restore -U postgres -d "$RESTORE_DATABASE" \
  --no-owner --no-privileges < "$DUMP_FILE"

Stopping the environment

docker compose down

This command preserves the named volumes. docker compose down -v also deletes the restored databases and must only be used when all local database data should be discarded.

License

This project is licensed under the MIT License.