PostgreSQL - Table Migration Using CSV (Legacy to Current Environment)

 Overview

This document describes the step-by-step process to extract data from a legacy PostgreSQL environment, transfer it to a new production environment, and load it into an audit table. The migration includes handling a range datatype (tstzrange) for a time period column.

Source Requirement

Extract the following columns from the legacy table:

• id

• modified_date

• created_date

• organization_id

• template_id

• time_template_id

• time_period

Step 1: Export Required Columns to CSV

\copy (SELECT id, modified_date, created_date, organization_id, template_id, time_template_id, time_period FROM schema_name.source_table) TO '/opt/data_backup/data_audit.csv' CSV HEADER;

Step 2: Compress the Export File

tar -czf /opt/data_backup/data_audit.tar.gz -C /opt/data_backup data_audit.csv

Step 3: Copy File to New Production Server

Copy the compressed file from the legacy server to the new production server using a secure file transfer method such as scp or rsync.

Step 4: Unzip the File on the Target Server

tar -xzf /opt/data_backup/data_audit.tar.gz -C /opt/data_backup

Step 5: Validate Column Data Types in Source Table

Before creating the target table, verify the column data types in the source database to ensure compatibility during import.

Step 6: Create Audit Table in Target Environment

CREATE TABLE generic_tstzrange_audit (

    id BIGINT,

    modified_date TIMESTAMPTZ,

    created_date TIMESTAMPTZ,

    organization_id BIGINT,

    template_id BIGINT,

    time_template_id BIGINT,

    time_period tstzrange

);

Step 7: Import CSV Data into Audit Table

\copy generic_tstzrange_audit FROM '/opt/data_backup/data_audit.csv' CSV HEADER;

Best Practices

• Ensure sufficient disk space before export.

• Validate row counts before and after migration.

• Use transactions where applicable.

• Confirm timezone consistency when working with TIMESTAMPTZ and tstzrange types.

• Perform the activity during a maintenance window if the dataset is large.

Conclusion

This approach provides a reliable way to audit and migrate data between PostgreSQL environments while preserving complex datatypes such as tstzrange. Proper validation at each step ensures data integrity and minimizes risk.