Moving Tables Across Postgresql Instances

Posted2 months agoActiveabout 2 months ago

ananthakumaran

85 points

5 comments

ananthakumaran.inTechstory

calmpositive

Debate

20/100

PostgresqlDatabase MigrationLogical Replication

Key topics

Postgresql

Database Migration

Logical Replication

The article discusses using logical replication to move tables across PostgreSQL instances, and the discussion highlights the importance of dropping indexes before replication and explores alternative methods such as importing data from S3 and using Foreign Data Wrappers.

Snapshot generated from the HN discussion

Discussion Activity

Light discussion

First comment

Peak period

78-84h

Avg / period

2.5

Key moments

01Story posted
Nov 2, 2025 at 1:47 AM EDT
2 months ago
Step 01
02First comment
Nov 5, 2025 at 10:25 AM EST
3d after posting
Step 02
03Peak activity
3 comments in 78-84h
Hottest window of the conversation
Step 03
04Latest activity
Nov 6, 2025 at 6:39 AM EST
about 2 months ago
Step 04

Generating AI Summary...

Analyzing up to 500 comments to identify key contributors and discussion patterns

Discussion (5 comments)

Showing 5 comments

fourseventy

about 2 months ago

2 replies

I'm in the middle of doing a major version upgrade of postgres from pg15 to pg18 using the same logical replication techniques that this article talks about. As this article mentions, dropping the indexes on the new database before replication is key. Otherwise the replication takes forever because each insert needs to update the index as well.

hinkley

about 2 months ago

Dropping indexes and constraints because constraint checks can cause table scans if there are no indexes.

Our old “drop/create” scripts would go 5-10x faster that way, setting up sample data for dev environments.

dspillett

about 2 months ago

This is the same even if doing bulk or batched inserts. MS's process of importing an exported DB (when using that method, i.e. with Azure SQL rather than an on-prem restore to on-prem where a page-level backup is of course much more efficient) doesn't create indexes until after the full data import has completed.

vbilopav

about 2 months ago

Why not Foreign Data Wrappers?

Zaheer

about 2 months ago

I recently learned that on RDS you can import data from S3. Handy feature for accomplishing similar goal: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...

View full discussion on Hacker News

ID: 45788086Type: storyLast synced: 11/20/2025, 12:50:41 PM

Want the full context?

Jump to the original sources

Read the primary article or dive into the live Hacker News thread when you're ready.

Open link View on HN