PostgreSQL to Tacnode Migration Guide

This guide provides a comprehensive, step-by-step process for migrating your data and applications from PostgreSQL to Tacnode. It covers environment setup, permissions, migration steps, schema adaptation, application refactoring, best practices, and troubleshooting. All content is rewritten in native English for clarity and usability.

Overview

Tacnode supports migration from various PostgreSQL sources, including cloud platforms and self-hosted databases. The process leverages Tacnode's distributed, relational, and semi-structured data capabilities.

Preparation

Environment Requirements

• Source PostgreSQL database (version 9.6 or newer)
• Tacnode database instance (admin access)
• Network connectivity between source and target (preferably private network)
• Data migration tool (Tacnode Data Sync, ETL, or custom scripts)

Permission Setup

Create a dedicated PostgreSQL user for migration:

CREATE USER tacnode_cdc WITH REPLICATION ENCRYPTED PASSWORD 'your_password';
GRANT SELECT ON ALL TABLES IN SCHEMA schema1, schema2 TO tacnode_cdc;
GRANT USAGE ON SCHEMA schema1, schema2 TO tacnode_cdc;

Create a target user in Tacnode:

CREATE USER tacnode_app WITH PASSWORD 'secure_password';
GRANT ALL PRIVILEGES ON SCHEMA sales, inventory, customers TO tacnode_app;

Network Configuration

• Ensure Tacnode can reach PostgreSQL (configure firewall/security groups).
• For public access, whitelist IPs and use secure connections.

Migration Steps

1. Create Publication in PostgreSQL

• For all tables:

  CREATE PUBLICATION datasync_pub FOR ALL TABLES;

• Or for selected tables:

  CREATE PUBLICATION datasync_pub FOR TABLE schema1.table1, schema2.table2;

2. Set WAL Level

• Set wal_level = logical in postgresql.conf and restart PostgreSQL.

3. Configure Data Sync

• In Tacnode Data Sync:
  1. Log in to Tacnode dashboard.
  2. Go to Data Sync > Data Import.
  3. Create a new sync task.
  4. Configure source PostgreSQL connection (host, port, db, user, password).
  5. Configure target Tacnode connection.
  6. Select tables to sync.
  7. Map source to target schemas/tables.
  8. Set sync mode (full + incremental) and dirty data policy.

4. Start and Monitor Migration

• Start the sync task in Data Sync.
• Monitor progress and status.

5. Validate Data Consistency

• Compare row counts and sample data between source and target:

  SELECT COUNT(*) FROM customers.users;
  SELECT COUNT(*) FROM inventory.products;
  SELECT COUNT(*) FROM sales.orders;
  SELECT COUNT(*) FROM sales.order_items;
  SELECT * FROM customers.users LIMIT 5;
  SELECT * FROM inventory.products ORDER BY product_id DESC LIMIT 5;

6. Application Cutover

• Update connection strings:

  # Old PostgreSQL
  jdbc:postgresql://old-postgres-host:5432/ecommerce
  # Tacnode
  jdbc:postgresql://tacnode-host:5432/ecommerce

• Test application features (user registration, product queries, order creation).

Application Refactoring

Update Connection Configuration

Update connection configuration in your app. SQL syntax and feature compatibility is high, but test for:

• Sequence operations: SELECT nextval('user_id_seq');
• Array operations: SELECT ARRAY[1,2,3]; SELECT array_append(ARRAY[1,2], 3);

Example Java (Spring Boot) Repository:

@Repository
public class UserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
 
    public User findById(Long id) {
        String sql = "SELECT user_id, username, email FROM customers.users WHERE user_id = ?";
        return jdbcTemplate.queryForObject(sql, new Object[]{id}, new UserRowMapper());
    }
 
    public List<User> findAll() {
        String sql = "SELECT user_id, username, email FROM customers.users ORDER BY user_id";
        return jdbcTemplate.query(sql, new UserRowMapper());
    }
 
    public void save(User user) {
        String sql = "INSERT INTO customers.users (username, email) VALUES (?, ?)";
        jdbcTemplate.update(sql, user.getUsername(), user.getEmail());
    }
}

Best Practices

Pre-Migration

• Assess data volume, custom functions, extensions, and SQL compatibility.
• Plan migration windows and rollback strategies.
• Train your team on Tacnode.

During Migration

• Migrate non-critical data first, then core business data.
• Monitor migration progress and validate data consistency.
• Document issues and solutions.

Post-Migration

• Optimize indexes and storage post-migration.
• Harden security (IP whitelist, user permissions, audit logs).

Troubleshooting

Publication Creation Errors

• Check user permissions.

Data Sync Delays

• Check WAL settings and replication slots.

Data Type Incompatibility

• Use compatible types (e.g., replace arrays with JSONB, custom types with VARCHAR + CHECK).