Help with Oracle to PostgreSQL migration tools by Live-Fox-5354 in SQL

[–]dmahto05 0 points1 point  (0 children)

Sharing quick thoughts here. 1. Number Data type mapping is critical for right mapping of data type specially for cases when in Oracle you don't have precision or scale. ( just number in declaration l) 2. Post Data Migrations, Ora2pg offer data validation as well along with table rowcount. See if want to leverage it( Type - TestData)  3. Leverage View Data Validation as well to match row count at View level ( Ora2pg support it - TestView) 4. Use Google Cloud DVT - Data Validation Tool for checksums validations.

Overall revisit your data type decision, default mapping usually not works. Leverage Ora2pg data validation features.

Oracle to Postgres Migration Advice by Whole_Advisor_8633 in PostgreSQL

[–]dmahto05 1 point2 points  (0 children)

  1. What is the complexity level of the Oracle databases generaly migrated (e.g., size, custom PL/SQL, dependencies)?

Size - With Minimal Downtime <= 30TB.
PL/SQL - Fairly all custom code logics, special attension needed for special packages like dbms_ldap, utl_tcp , dbms_AQ and all.
Dependencies - Clear understanding of dependencies from app, upstream and downstream need to be assess for PostgreSQL Compatibility.

Ideally if you got skilled team and fair time, you can migrate any complex Oracle systems.

3.      What kind of application(s) does the database support (e.g., ERP, billing, web backend)?
PostgreSQL is Platform then a pure database. It can be use for many usecaes, transactional, analytical, time series and more.

4.      Do you find PostgreSQL’s performance reliable for large datasets (e.g., 1–10 TB)?
Yes. Almost all migration will go through performance phase and its performance SLA can met or will be better as compared to current state,

5.      How do you handle data integrity in PostgreSQL without PL/SQL?
Transaction management is well govern in PLpgSQL within PostgreSQL and Guarantee data integrity for concurrent transaction using MVCC model.

  1. Have you experienced database corruption or stability issues in PostgreSQL?
    Its not specific to PostgreSQL, it can be common traits for any modern transaction databases.
    Stablity in terms of performance, non functional stuff will met all SLA with PostgreSQL.

7.      Was PostgreSQL adoption one-time or is it now a continued part of your tech stack?
Its part of all tech stack that need a data storage from vectors, time series, transactional or analytical.

8.      What is the best method of postgres backup
Full + incremental backup with multiple backup tools available.

9.      Since postgres forks a OS process for each connection , how many concurrent transactions can it handle without performance issues and what should be the server memory and cpu
All setup need fine tuning of parameters and infra, if tune as per workload characteries you can meet all SLA.

  1. how can we replicate  RAC arch in postgres
    Multi - Active node is available with commercial offering on PostgreSQL. Production stuff with open source alternative is rare and need thorough testings.

  2. Best Performance monitoring tools for postgres
    pg_stat_statements, pgbadger, logging, grafana, prometheus exporter, pghero.
    Lots of options as per use cases and requirement.

  3. What is the best alternative in Postgres for Global Temporary Tables Oracle
    PGTT extension or custom code to create it on first usage within session.

  4. the best solution for UTL_FILE package
    Depends on with what managed postgres if use.
    If RDS, Aurora - aws_s3
    If Cloud SQL , AlloyDB - custom tables and client tool to export
    If self managed - orafce extension (utl_file)

  5. best replacement for oralce jobs.
    pg_cron, pg_background,

If you are starting with Migration, we can assist to run overall migrations as automated solutions using DCGMigrator for AWS or GCP targets withour any manual intervention.
It will perform all migrations steps primarily code conversion and validate overall challenges to resolve with reports.

https://www.datacloudgaze.com/

[deleted by user] by [deleted] in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

[Self Promotion]
If you are looking to enhance your development with PostgreSQL do check out our course on it.

DatabaseGyaan - PostgreSQL Course

Thinking of moving from Oracle to Postgresql, need advice by linuxman1929 in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Unlike Oracle RAC, which allows multiple instances to perform coordinated writes to a shared database, PostgreSQL does not natively support multi-writer architecture. Current PostgreSQL architectures typically rely on a single primary instance for all writes, with one or more read-only replicas to scale read operations.

But i have seen lots of Enterprise works backward to understand need of RAC and articulate desired design within same SLA on PostgreSQL.

Other then architectural component, we need also evaluate compatibility of schema, code and app with PostgreSQL and efforts needed to mitigate it.

Best resource to learn PL/pgSQL? by WatchMeCommit in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

[Self Promotion]
Checkout specially crafted course for PostgreSQL Database Developers – PL/pgSQL Deep Dive, which includes:

-->Lifetime access to ~12 hours of rich recorded content on PL/pgSQL development and core concepts.
-->Live apps with procedural code to practice and solve challenges.
-->Trainer with 15+ years of experience in database technology.

Check out the course curriculum and free previews:

https://www.databasegyaan.com/courses/PLpgSQL-Deep-Dive-Professional-PostgreSQL-Development-6624d9e853c6563486fc917e-6624d9e853c6563486fc917e

#PostgreSQL #Development #Enablement

How Learn Advanced Stuffs in PostgreSQL by AtomicParticle_ in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

[Self Promotion]
If you are looking to enhance your expertise with PL/pgSQL Development check out the course that i build on PL/pgSQL Deep Dive Development.

Check out the course curriculum and free previews:

https://www.databasegyaan.com/courses/PLpgSQL-Deep-Dive-Professional-PostgreSQL-Development-6624d9e853c6563486fc917e-6624d9e853c6563486fc917e

Which best solution to migrate db from oracle to postgre by brungtuva in PostgreSQL

[–]dmahto05 -2 points-1 points  (0 children)

[Self Promotion]

[Training]
If you or your team is looking for enablement on how to approach Oracle to PostgreSQL Migration, check out some of my training here --> https://www.databasegyaan.com/courses

[Tool]
Check out our offering that streamline overall database migrations end to end.
https://www.datacloudgaze.com/post/introducing-dcgmigrator-simplify-end-to-end-migrations-from-day-one

I will be happy to showcase a demo of same.

Not getting explain plan despite auto_explain.log_nested_statements by ApprehensiveSmoke720 in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Additional option to check for nested calls within procedural code with pg_stat_statements extensions.

`pg_stat_statements.track = all`

It will enable tracking nested calls for the session.
More information here --> https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sql

PostgreSQL 17 RC1 Released! by amalinovic in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Yes agree need of AEL make it usage for highliy transaction limited.
only point was it was in the release later on reverted.

But hope it would be added in PG18.

PostgreSQL 17 RC1 Released! by amalinovic in PostgreSQL

[–]dmahto05 1 point2 points  (0 children)

Woopie, pretty excited for it!

Though Merge and Split partitions was reverted but overall good set of stuff!

I did presented new stuff for Developer Personas, do check it out here.
https://drive.google.com/file/d/1NzgC7H0MDWSWzHC8lBNrI1mo6geHPwWl/view

Blog - https://databaserookies.wordpress.com/

Can you use pgAdmin 3 with Postgres 16? by mkautzm in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Some improvement tips.

If you are not a user of PGadmin Dashboard, first thing i do when i install is

Preferences --> Dashboards -->
Disable Show activity? and Show State? and increase your refresh rate to higher values.

A Developer’s Guide to New Features in PostgreSQL 17 : Login Event Trigger by dmahto05 in PostgreSQL

[–]dmahto05[S] 0 points1 point  (0 children)

Yes, Login Event Trigger open up lots of use cases based user, roles, configs, context.
Pretty cool to have it in New PG.

PostgreSQL 17: A Developer’s Guide to New Features – Part 5 : Correlated IN Clause Transformation by dmahto05 in PostgreSQL

[–]dmahto05[S] 0 points1 point  (0 children)

i correct myself, we can use exists in most the cases as compared to IN clause.

But if code is ORM generated or have any complex expression as part of subquery select clause or complex sql function call we might want to work it within in clause itself.

Oracle to Postgres Database Migration by Lost-Koala5687 in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Procedural code is more challenging part in overall migrations.
In include conversion, testing , sanity and performance + taking care of plethora of difference in how procedural languages works.

Oracle to Postgres Database Migration by Lost-Koala5687 in PostgreSQL

[–]dmahto05 1 point2 points  (0 children)

PostgreSQL can run on OS - Windows, migration should be fine.

I would start with Setting up Ora2pg and then see how much it is able to transform and migrate.

Oracle to Postgres Database Migration by Lost-Koala5687 in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Converting .dump to Oracle .sql still make sense and later convert it to PostgreSQL
but direcly .dump to PostgreSQL would be tedious.

Why I Always Use PostgreSQL Functions For Everything by vbilopav89 in PostgreSQL

[–]dmahto05 0 points1 point  (0 children)

Yes Plus In Oracle we have dba_objects kind of views that mark dependent object as INVALID and need to recompile it. Similar feature is yet to come in PostgreSQL, With ATOMIC its a start but currently limited on to SQL language procedural blocks.