Hi,
We have an oracle deployment running in 300+ TB. Apart from migration challenges, do we foresee any operational challenges?
Of course. There are always operational challenges in ultra-large databases... 😉
I have not heard of something huge prior. This would be highly helpful in planning further.
Few challenges which caught my attention include,
- Backup, restore of such a large database
- Recovery of a broken database (A lost replica)
- Fixing a standby that lags a lot
- Triggering a PITR with any backup tools
- Maintenance at times
- Finding equivalents of oracle items (which is common during any oracle to postgres migration)
Postgresql has tablespaces, which obviously is important for you.
Convert all those NUMERIC(38,0) types to BIGINT!! ora2pg can do that for you.
ora2pg can also export sections of a database, and also views. This allowed me to migrate 95% (all the old, "read only" records) of an 8TB before cutover day.
The PG database (which is heavy on BLOBs converted to PG's bytea type) was half the size of the 8TB Oracle database. Your Mileage Will Vary, of course, but don't be surprised if the resulting DB is only 150TB.
PgBackRest:
- is multi-threaded.
- has delta restores, which means restoring just the changed files on top of an existing (problematic) instance.
- can be the source of replication. Doing a delta restore makes it even faster.
- is quite fast. With just 8 threads, we back up a 3.75TB db to a NAS in 2 hours (656MB/sec). More threads might make it faster, but really is bottlenecked by the whole hardware stack.
- Is very efficient at handling small tables and indices (which is important, since each PG object is (at least) its own file, and additional files are added after 1GiB.
- can only write to one directory (i.e. each thread cannot write to a separate mount point), which is a bottleneck.
- has zst compression, and also AES-256-CBC compression.
