-2

I need to migrate a PostgreSQL database (~5TB in size) from version 13 running on Oracle Linux 7.9 to version 17 running on Ubuntu 24. The database includes critical data and downtime must be minimized.

Details:

Source: PostgreSQL 13 on Oracle Linux 7.9

Target: PostgreSQL 17 on Ubuntu 24

Approx. size: 5TB

Minimal downtime is preferred.

What would be the best approach to ensure a smooth migration? Should I use pg_dump/pg_restore, logical replication, or another method? Any tips for handling large data volumes and avoiding issues would be greatly appreciated!

Improve this question
4
  • 2
    This is a question for DBA but if you are responsible for maintaining a 5TB database with critical data, you likely have the expertise to research possible methods, evaluate them and plan a migration, including strategies for minimizing downtime Commented Jan 16 at 17:37
  • Thank you for your input, Hakim00. While I understand that managing a 5TB critical database implies some expertise, every migration scenario is unique, especially when dealing with different OS versions, PostgreSQL versions, and downtime constraints. I'm looking for community insight on best practices, specific tools, or challenges others may have faced in similar scenarios. If you’ve dealt with large-scale PostgreSQL migrations, your advice would be valuable. Commented Jan 16 at 17:42
  • 1
    This is a question for DBA Adminstrators. Commented Jan 16 at 17:58
  • Just start the logical replication, since this can work on different PostgreSQL versions: postgresql.org/docs/13/logical-replication.html Commented Jan 16 at 17:59

1 Answer 1

Reset to default
1

From your mention of Oracle Linux I assume you are using cloud hosting. Which implies your new version on a different OS will be on a different virtual machine.

If that is the case the only real option you have is logical replication. If you can't afford to have downtime and your database is being written to constantly, copying 5TB of data is going to take too long.

You should be able to use the built-in logical replication I think. It was available in PostgreSQL 13 although there have been a lot of changes since then. If not, check out the pglogical extension from 2nd quadrant.

I would recommend taking the time to set up a proper test environment with a copy of your production system, but much smaller. Start with 5MB of data, then go through 50MB and 500MB - get a feeling for how the logical replication setup works. That should give you an estimate for how long the process will take too.

Add in scripts to check a couple of frequently-updated tables to make sure your new DB is completely up-to-date. If you can automate preventing writes to your old DB, and switching traffic to the new one, add that to your test setup too.

The key things that might trip you up are differences in installed extension versions (postgis etc) or in locale settings/libraries or timezones. Make sure your smaller test-data can exercise those corners.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, Richard, for your detailed response. Your insights about logical replication and setting up a test environment are invaluable, especially for a database of this size. I’ll explore built-in logical replication and pglogical to determine the best fit for our use case. Testing with scaled-down data and automating the traffic switch are excellent suggestions that I’ll incorporate. I’ll also pay close attention to extensions, locale settings, and timezones, as you mentioned. Thanks again for your thorough advice—it’s much appreciated!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.