Must Know Postgres Extension for DBA and Developer during Migration

Mydbops Opensource Database Meetup 16

Topic: Must-Know PostgreSQL Extensions for Developers and DBAs During Migration
Speaker: Deepak Mahto, Founder of DataCloudGaze Consulting
Date & Time: 8th June | 10 AM - 1 PM IST
Venue: Bangalore International Centre, Bangalore

Abstract: Discover how PostgreSQL extensions can be your secret weapon! This talk explores how key extensions enhance database capabilities and streamline the migration process for users moving from other relational databases like Oracle.

Key Takeaways:

* Learn about crucial extensions like oracle_fdw, pgtt, and pg_audit that ease migration complexities.
* Gain valuable strategies for implementing these extensions in PostgreSQL to achieve license freedom.
* Discover how these key extensions can empower both developers and DBAs during the migration process.
* Don't miss this chance to gain practical knowledge from an industry expert and stay updated on the latest open-source database trends.

Mydbops Managed Services specializes in taking the pain out of database management while optimizing performance. Since 2015, we have been providing top-notch support and assistance for the top three open-source databases: MySQL, MongoDB, and PostgreSQL.

Our team offers a wide range of services, including assistance, support, consulting, 24/7 operations, and expertise in all relevant technologies. We help organizations improve their database's performance, scalability, efficiency, and availability.

Contact us: [email protected]

Visit: https://www.mydbops.com/

Follow us on LinkedIn: https://in.linkedin.com/company/mydbops

For more details and updates, please follow up the below links.

Meetup Page : https://www.meetup.com/mydbops-databa...

​​Twitter: https://twitter.com/mydbopsofficial

Blogs: https://www.mydbops.com/blog/
​
​Facebook(Meta): https://www.facebook.com/mydbops/

1. Must-Know Postgres Extensions
for DBAs and Developers During
Migration
Deepak Mahto
DataCloudGaze Consulting

2. © 2024 All Rights Reserved
About Me
I am Deepak Mahto, and I like to call myself a
Database Guy.
●Founder of DataCloudGaze Consulting.
●I have 15+ years of database experience,
with more than 7 years in cloud and
migrations.
●I have published 150+ technical blogs on
databases.
●I live in Mumbai and have a 3-year-old child.
●Loves to explore street food.

3. © 2024 All Rights Reserved
Agenda
●What are Postgres Extensions and Their Importance
●Some Fun Facts About Extensions
●List of Extensions to Aid in Migration from Heterogeneous Sources Like
Oracle and MSSQL

4. © 2024 All Rights Reserved
What is extension?
PostgreSQL's extensibility allows for seamless integration of extensions,
making them function like built-in features, thus enhancing its capabilities
and flexibility.

5. © 2024 All Rights Reserved
Extensive versatility of Extension’s
Reference -https://cloud.tembo.io

6. © 2024 All Rights Reserved
Create Extension in Postgres
CREATE EXTENSION loads and manages new extensions, requiring appropriate
privileges.

7. © 2024 All Rights Reserved
Postgres fun fact -default extension.
PL/pgSQL is a loadable procedural language for the PostgreSQL database
system that is default extension created with each new database.

8. © 2024 All Rights Reserved
More supported Procedural Language as extensions
●PL/R -PostgreSQL support for R as a procedural language (PL)
●PL/V8 -A procedural language in JavaScript powered by V8
●PL/Tcl -Tcl procedural language for PostgreSQL.
●PL/Perl -The Perl procedural language for PostgreSQL.
●PL/Rust -Procedural language in the Rust programming.
●PL/Python -Untrusted procedural language for PostgreSQL.
More..

9. pg_stat_statements
Track statistics of SQL planning and execution

10. pg_stat_statements -toplevel to track nested calls.
Problematic SQL!

11. pg_stat_statements -toplevel to track nested calls.
*pg_stat_statements.track = 'all' (only for non prod or specific session)
Identify problematic sql within procedural
block.

12. © 2024 All Rights Reserved
pg_hint_plan -Influence sql performance
Makes it possible to tweak PostgreSQL execution plans using so-called
"hints" in SQL comments
Influence access
path and
Join method

13. hypopg -Invisible Indexes
PostgreSQL extension adding support for hypothetical indexes
Validate and test indexes
before actually creating
it.

14. orafce -Oracle’s compatibility functions and packages
Emulate a subset of functions and packages from the Oracle RDBMS.

15. oracle_fdw -Foreign Data Wrapper for Oracle
Facilitate seamless data migration and integration between Oracle databases
and PostgreSQL.

16. pgtt -Temporary Table across schema
Use Oracle-style Global Temporary Tables and the others RDBMS.
Session 1
Session 2

17. pgaudit -Compliance and Regulatory
provides detailed session and/or object audit logging via the standard
PostgreSQL logging facility.

18. pgaudit
Configuring custom logging based on compliance requirement.

19. Table Partitioning Maintenance –
pg_partman/pg_cron

20. © 2024 All Rights Reserved
PL/pgSQL Conversion -Challenge
postgres=# SELECT func_demo1();
ERROR: column "col1" does not exist
LINE 1: SELECT 1 WHERE COL1 =
1
QUERY: SELECT 1
WHERE COL1 = 1
CONTEXT: PL/pgSQL function
func_demo1() line 4 at SQL statement
PL/pgSQL functions aren't syntax-checked until executed.

21. © 2024 All Rights Reserved
plpgsql_check extension to rescue.
Extension serves as a comprehensive linter for plpgsql in Postgres
●Utilizes the internal PostgreSQL parser/evaluator to display runtime errors.
●Parses SQL inside routines to identify errors not typically found during
"CREATE PROCEDURE/FUNCTION" commands.
●The plpgsql_check extension detects issues in PL/pgSQL code:
undefined/unused variables, type mismatches, control flow errors, incorrect
function calls, trigger problems, and SQL statement errors.

22. © 2024 All Rights Reserved
Some more essential extensions
-pg_repack -lets you remove bloat from tables and indexes as online.
-pglogical -PostgreSQL Logical Replication -Change Data Capture.
-hydra_columnar -Analytics-Columnar storage for Postgres
-passwordcheck* -Checks and rejects weak passwords.
-postgresql_anonymizer -Anonymization & Data Masking.
-auto_explain -logging execution plans of slow statements
automatically.

23. © 2024 All Rights Reserved
Thank you!
https://www.datacloudgaze.com/
https://www.linkedin.com/in/mahtodeepak/
https://x.com/mahtodeepak05
https://databaserookies.wordpress.com/