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.

PosgreSQL - Bulk Updating PostgreSQL Table Using CSV and Temporary Table

-------------- Bulk Updating PostgreSQL Table Using CSV and Temporary Table-----------------------------

Introduction

In this document, we walk through a practical approach to bulk update records in a PostgreSQL table using a CSV file. This method is particularly useful when dealing with large datasets (millions of rows) where direct updates may be inefficient or error-prone.

 The scenario involves updating columns in a target table using data provided in a CSV file.

Step 1: Create a Temporary Staging Table

We first create a temporary table to load the CSV data. Temporary tables are session-specific and automatically dropped when the session ends, making them safe for intermediate operations.

 CREATE TEMP TABLE tmp_target_table (

    id bigint,

    column_a varchar(45),

    column_b bigint

);

Step 2: Load CSV Data Using \copy Command

Since the CSV file resides on the client machine, we use the \copy command in psql, which reads the file from the client side.

 \copy tmp_target_table

FROM 'C:/path/to/your/file.csv'

DELIMITER ','

CSV HEADER;

Step 3: Validate Loaded Data

Always verify the data count after loading.

 

SELECT count(*) FROM tmp_target_table;

Step 4: Verify Matching Records with Target Table

Before performing updates, ensure that all IDs exist in the target table.

 

SELECT count(*)

FROM tmp_target_table s

JOIN schema_name.target_table t

ON t.id = s.id;

 

If needed, check for any missing records:

 

SELECT s.*

FROM tmp_target_table s

LEFT JOIN schema_name.target_table t

ON t.id = s.id

WHERE t.id IS NULL;

Step 5: Perform Bulk Update

Now update the target table using the staging table.

 

UPDATE schema_name.target_table t

SET

    column_a = s.column_a,

    column_b = s.column_b

FROM tmp_target_table s

WHERE t.id = s.id;

Step 6: Post-Update Validation

Finally, confirm that updates were applied correctly.

 

SELECT count(*)

FROM schema_name.target_table t

JOIN tmp_target_table s

ON t.id = s.id;

Key Benefits of This Approach

• Efficient for millions of records

• Minimal locking compared to row-by-row updates

• Easy validation before applying changes

• Safe rollback if wrapped inside a transaction

Best Practices

Always test in a lower environment first.

Wrap the update inside a transaction for safety:

 BEGIN;

 -- update statement

 COMMIT;

 Consider indexing the staging table if join performance is slow:

 CREATE INDEX idx_tmp_target_table_id ON tmp_target_table(id);

 Run VACUUM ANALYZE after large updates if needed.

Conclusion

Using a temporary table with the \copy command is one of the most reliable and performant ways to perform bulk updates in PostgreSQL. This method ensures data integrity while handling large volumes efficiently.