Nashpush

postgresql-table-seamless-migration

4 min read

Step-by-Step Guide for Seamless Table Migration

This guide outlines the steps for smoothly migrating to a new table in PostgreSQL, with logical replication set up, ensuring no data loss and minimal service disruption.

1. Create the New Table

First, create a new table with the same structure as the old subscribers_subscriber table:

 
CREATE TABLE subscribers_subscriber_new (LIKE subscribers_subscriber INCLUDING ALL);

2. Create Indexes for the New Table

Before copying the data, create all necessary indexes for the new table to ensure optimal performance:

 
CREATE INDEX subscribers_channel_c230ff_idx ON subscribers_subscriber_new (channel_id, is_active);
 
CREATE INDEX subscribers_is_acti_0636ea_idx ON subscribers_subscriber_new (is_active, owner_id);
 
CREATE INDEX subscribers_is_acti_1611fa_idx ON subscribers_subscriber_new (is_active, channel_id, apns_app);
 
CREATE INDEX subscribers_is_acti_731e4f_idx ON subscribers_subscriber_new (is_active, deliveries_idle_days);
 
CREATE INDEX subscribers_is_acti_95aeb0_idx ON subscribers_subscriber_new (is_active, clicks_idle_days);
 
CREATE INDEX subscribers_is_acti_ab1e1c_idx ON subscribers_subscriber_new (is_active, channel_id, city);
 
CREATE INDEX subscribers_is_acti_de05b6_idx ON subscribers_subscriber_new (is_active, channel_id, firebase_app);
 
CREATE INDEX subscribers_is_acti_f9f94e_idx ON subscribers_subscriber_new (is_active, channel_id, country);
 
CREATE INDEX subscribers_subscri_f857df_idx ON subscribers_subscriber_new (subscription_hash_str, channel_id);
 
CREATE INDEX subscribers_subscriber_apns_app_970b22ce ON subscribers_subscriber_new (apns_app);
 
CREATE INDEX subscribers_subscriber_channel_id_27deaa63 ON subscribers_subscriber_new (channel_id);
 
CREATE INDEX subscribers_subscriber_channel_id_is_active_deleted_idx ON subscribers_subscriber_new (channel_id, is_active, deleted);
 
CREATE INDEX subscribers_subscriber_deleted_93c811c4 ON subscribers_subscriber_new (deleted);
 
CREATE INDEX subscribers_subscriber_firebase_app_880e1e53 ON subscribers_subscriber_new (firebase_app);
 
CREATE INDEX subscribers_subscriber_id_channel_id_is_active_idx ON subscribers_subscriber_new (id, channel_id, is_active);
 
CREATE INDEX subscribers_subscriber_is_active_6a4002a0 ON subscribers_subscriber_new (is_active);
 
CREATE INDEX subscribers_subscriber_is_ghost_1bd828e9 ON subscribers_subscriber_new (is_ghost);
 
CREATE INDEX subscribers_subscriber_is_iddle_a73f688c ON subscribers_subscriber_new (is_iddle);
 
CREATE INDEX subscribers_subscriber_owner_id_fc68769f ON subscribers_subscriber_new (owner_id);
 
CREATE INDEX subscribers_subscriber_subscription_hash_769f1d5e ON subscribers_subscriber_new (subscription_hash);
 
CREATE INDEX subscribers_subscriber_subscription_hash_str_c55c81dd ON subscribers_subscriber_new (subscription_hash_str);
 
CREATE INDEX subscribers_subscriber_subscription_hash_str_c55c81dd_like ON subscribers_subscriber_new (subscription_hash_str varchar_pattern_ops);
 
CREATE INDEX subscribers_subscriber_token_58c9e144 ON subscribers_subscriber_new (token);
 
CREATE INDEX subscribers_subscriber_token_58c9e144_like ON subscribers_subscriber_new (token varchar_pattern_ops);
 
CREATE INDEX subscribers_subscriber_unsubscribed_date_768f1d7d ON subscribers_subscriber_new (unsubscribed_date);
 
CREATE INDEX subscribers_subscriber_uuid_b5412fac ON subscribers_subscriber_new (uuid);
 
CREATE INDEX subscribers_token_19b479_idx ON subscribers_subscriber_new (token, channel_id);

3. Initial Data Copying

Copy all records from the old table to the new one, for example, all records after 2024-01-01:

 
INSERT INTO subscribers_subscriber_new
 
SELECT *
 
FROM subscribers_subscriber
 
WHERE subscribed_date >= '2024-01-01';

4. Configure the Sequence for the New Table

Update the sequence for the new table so that it continues from the highest id value in the new table:

 
-- Update the sequence value for the new table
 
SELECT setval('subscribers_subscriber_new_id_seq', (SELECT MAX(id) FROM subscribers_subscriber_new));
 
  
 
-- Link the sequence to the id column in the new table
 
ALTER TABLE subscribers_subscriber_new ALTER COLUMN id SET DEFAULT nextval('subscribers_subscriber_new_id_seq');

5. Synchronization of new records added after the initial copy

Since new records may appear in the old table between the initial copy and the final synchronization, you need to upload them to the new table:

 
INSERT INTO subscribers_subscriber_new
 
SELECT *
 
FROM subscribers_subscriber
 
WHERE subscribed_date >= '2024-01-01'
 
AND id NOT IN (SELECT id FROM subscribers_subscriber_new);

6. Disable Old Services and Perform Final Data Copying with Table Lock

6.1 Disable Old Services:

  • Disable all old services that write to the old table (subscribers_subscriber) to avoid adding new records during the final data copying.

6.2 Perform Final Data Copying with Table Lock:

  • After disabling the old services, lock the old table and perform final synchronization of new records to the new table.

 
BEGIN;
 
  
 
-- Lock the old table to avoid new inserts during the final synchronization
 
LOCK TABLE subscribers_subscriber IN ACCESS EXCLUSIVE MODE;
 
  
 
-- Copy new records from the old table to the new table
 
INSERT INTO subscribers_subscriber_new
 
SELECT *
 
FROM subscribers_subscriber
 
WHERE subscribed_date >= '2024-01-01'
 
AND id NOT IN (SELECT id FROM subscribers_subscriber_new);
 
  
 
-- Commit the transaction, which will automatically unlock the table
 
COMMIT;

7. Rename Tables and Sequences

Once all data is synchronized, rename the new table to the old table’s name for a seamless transition:

  1. Rename the old table to a backup name:
 
ALTER TABLE subscribers_subscriber RENAME TO subscribers_subscriber_old;
  1. Rename the new table to the original name:
 
ALTER TABLE subscribers_subscriber_new RENAME TO subscribers_subscriber;
  1. Rename the sequence (if necessary):

If the old table’s sequence was used for the id column, you can rename the new sequence to match:

 
ALTER SEQUENCE subscribers_subscriber_new_id_seq RENAME TO subscribers_subscriber_id_seq;

8. Verify Replication

After renaming the new table to the old table’s name, verify that logical replication continues working. Since for PostgreSQL this is the same table under the original name, replication should continue without interruption.

9. Archive or Drop the Old Table

  1. Archive the old table as a backup:

If you want to keep a backup of the old table:

 
ALTER TABLE subscribers_subscriber_old RENAME TO subscribers_subscriber_backup;
  1. Drop the old table if no longer needed:

If you no longer need the old table:

 
DROP TABLE subscribers_subscriber_old;

Conclusion:

This plan ensures a seamless transition to the new table with minimal service changes. You lock the old table during the final data copy to avoid data loss, then simply rename the new table to the old name, and everything continues to work as before, including replication.

Graph View