Content outline

Site sections (an architecture for organizing information as it is developed):

PostgreSQL Journey

1. Planning
   • Community Postgres or EDB Postgres Advanced Server
   • Reference Architectures
   • Sizing
   • Platforms
   • Availability & Reliability
   • Requirements/prerequisites
   • Planning Checklists
2. Installation/Deployment
   • Platforms

     • (MVP) Linux: postgresql_journey/02_installing/linux.mdx

       Existing materials: Content already exists on new EDBDocs https://edb-docs-advocacy.herokuapp.com/getting-started/installing_postgres/linux

     • (MVP) Windows: postgresql_journey/02_installing/windows.mdx

       Existing materials: Content already exists on new EDBDocs https://edb-docs-advocacy.herokuapp.com/getting-started/installing_postgres/windows

     • (MVP) MacOS: postgresql_journey/02_installing/macos.mdx

       Existing materials: Content already exists on new https://edb-docs-advocacy.herokuapp.com/getting-started/installing_postgres/macos

     • (MVP) Docker: postgresql_journey/02_installing/docker.mdx

       Existing materials: Content already exists on new EDBDocs https://edb-docs-advocacy.herokuapp.com/getting-started/installing_postgres/docker

     • Kubernetes: postgresql_journey/02_installing/kubernetes.mdx

       Existing materials: https://www.enterprisedb.com/blog/postgres-kubernetes-or-vms-guide-framework-running-databases-best-way

   • Deployment Scripts
   • Best Practices
3. Configuration
   • Tuning

     • Windows: postgresql_journey/03_configuration/windows.mdx

       Existing materials: CTO team https://drive.google.com/file/d/1nMu0a8yBY7jVO6IkaWRVTtDPWHyJSyus/view

     • Linux: postgresql_journey/03_configuration/linux.mdx

       Existing materials: CTO Team https://drive.google.com/file/d/1Vt8LpXfRaG7L6Ra1XLwdTwR2dHRU_B9D/view

   • Security

     • ACLs (access control lists): postgresql_journey/03_configuration/acls-access-control-lists.mdx

       Notes: Good for Katacoda examples

   • Replication
4. Development/DevOps
   • Connecting basic Instructions + tutorial for each key language & library

     • (MVP) Python - psycopg2: postgresql_journey/04_developing/python-psycopg2.mdx

       Existing materials: Basic walkthru already available on EDBDocs

     • (MVP) Python - Django: postgresql_journey/04_developing/python-django.mdx

       Existing materials: Basic walkthru already available on EDBDocs

   • Tooling

     • (MVP) psql: postgresql_journey/04_developing/psql.mdx

       Existing materials: Utilize this for training on markdown and how to deploy in EDBDocs

       https://www.postgresql.org/docs/current/app-psql.html

       https://www.enterprisedb.com/postgres-tutorials/connecting-postgresql-using-psql-and-pgadmin

       https://www.enterprisedb.com/postgres-tutorials/how-create-postgresql-database-and-users-using-psql-and-pgadmin

       https://www.enterprisedb.com/postgres-tutorials/10-tools-every-developer-should-have-when-working-postgresql

     • (MVP) pgadmin: postgresql_journey/04_developing/pgadmin.mdx

       Existing materials: installation and usage (katacoda usage example exists)

     • (MVP) django-admin: postgresql_journey/04_developing/django-admin.mdx

   • Concepts from other database systems
     • sqlite: postgresql_journey/04_developing/sqlite.mdx
     • mysql: postgresql_journey/04_developing/mysql.mdx
   • Storing & Querying

     • Window functions: postgresql_journey/04_developing/window-functions.mdx

       Existing materials: Vik's tutorials:

       https://katacoda.com/enterprisedb/scenarios/window-functions-1
       
       https://katacoda.com/enterprisedb/scenarios/window-functions-2
       

       Notes: Good for Katacoda examples

     • CTEs (common table expressions): postgresql_journey/04_developing/ctes-common-table-expressions.mdx

       Notes: Good for Katacoda examples

     • Anonymous functions: postgresql_journey/04_developing/anonymous-functions.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/how-use-block-structure-write-anonymous-blocks-and-divide-larger-blocks-logical

       Notes: Good for Katacoda examples

     • pl/sql functions: postgresql_journey/04_developing/pl-sql-functions.mdx

       Notes: Good for Katacoda examples

     • pl/pgsql functions: postgresql_journey/04_developing/pl-pgsql-functions.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/everything-you-need-know-about-postgres-stored-procedures-and-functions

       https://www.enterprisedb.com/postgres-tutorials/stored-procedures-postgresql-how-create-stored-procedure-and-invoke-it

       https://www.enterprisedb.com/postgres-tutorials/how-develop-user-defined-function-postgresql-stored-procedures

       https://www.enterprisedb.com/postgres-tutorials/10-examples-postgresql-stored-procedures

       https://www.enterprisedb.com/postgres-tutorials/how-declare-variables-plpgsql-stored-procedures

       https://www.enterprisedb.com/postgres-tutorials/how-work-control-structures-postgresql-stored-procedures-using-if-case-and-loop

       https://www.enterprisedb.com/postgres-tutorials/how-raise-errors-and-report-messages-within-stored-procedures-and-functions

       https://www.enterprisedb.com/postgres-tutorials/how-easily-convert-oracle-procedures-postgresql

       Notes: Good for Katacoda examples

     • (MVP) pl/python functions: postgresql_journey/04_developing/pl-python-functions.mdx

       Notes: Good for Katacoda examples

     • pl/perl functions: postgresql_journey/04_developing/pl-perl-functions.mdx

       Notes: Good for Katacoda examples

     • pl/tcl functions: postgresql_journey/04_developing/pl-tcl-functions.mdx

       Notes: Good for Katacoda examples

   • Modeling

     • Views: postgresql_journey/04_developing/views.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/how-create-modify-delete-and-manage-views-postgresql

       https://www.enterprisedb.com/postgres-tutorials/using-materialized-views-and-foreign-data-wrappers-together

       Notes: Good for Katacoda examples

     • Materialized views: postgresql_journey/04_developing/materialized-views.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/how-create-and-refresh-data-materialized-views-postgresql

       Notes: Good for Katacoda examples

     • json/jsonb data: postgresql_journey/04_developing/json-jsonb-data.mdx

       Notes: Good for Katacoda examples

     • FTS (full text search): postgresql_journey/04_developing/fts-full-text-search.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/indexing-documents-full-text-search-postgresql

       https://www.enterprisedb.com/postgres-tutorials/how-implement-faceted-search-django-and-postgresql

       Notes: Good for Katacoda examples

     • FDWs (foreign data wrappers): postgresql_journey/04_developing/fdws-foreign-data-wrappers.mdx

       Existing materials: https://www.enterprisedb.com/postgres-tutorials/using-foreign-data-wrappers-access-remote-postgresql-and-oracle-databases

       https://www.enterprisedb.com/postgres-tutorials/using-materialized-views-and-foreign-data-wrappers-together

       https://www.enterprisedb.com/blog/how-plan-queries-involving-foreign-postgresql-tables

       Notes: Good for Katacoda examples

   • Security considerations during development
     • (MVP) Least-privilege configuration for Django: postgresql_journey/04_developing/least-privilege-configuration-for-django.mdx
   • Testing
   • Debugging
   • Versioning/Schema Migration
     • (MVP) Starting with Django models: postgresql_journey/04_developing/starting-with-django-models.mdx
     • (MVP) Database-first design and Django: postgresql_journey/04_developing/database-first-design-and-django.mdx
   • Potential KataCoda examples

     • Oracle Compatibility - Packages: postgresql_journey/04_developing/oracle-compatibility-packages.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Functions: postgresql_journey/04_developing/oracle-compatibility-functions.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Procedures: postgresql_journey/04_developing/oracle-compatibility-procedures.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Triggers: postgresql_journey/04_developing/oracle-compatibility-triggers.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Support for Oracle Data types: postgresql_journey/04_developing/oracle-compatibility-support-for-oracle-data-types.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Oracle specific SQL syntax and extensions such as use of (+) for outer joins versus ANSI SQL outer join syntax and START WITH ... CONNECT BY for hierarchical queries: postgresql_journey/04_developing/oracle-compatibility-oracle-specific-sql-syntax-and-extensions-such-as-use-of-for-outer-joins-versus-ansi-sql-outer-join-syntax-and-start-with-connect-by-for-hierarchical-queries.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - use of EDB*Plus to demonstrate Oracle SQL*Plus compatibility: postgresql_journey/04_developing/oracle-compatibility-use-of-edb-plus-to-demonstrate-oracle-sql-plus-compatibility.mdx

       Notes: Good for Katacoda examples

     • Oracle Compatibility - Pragma Autonomous Transactions: postgresql_journey/04_developing/oracle-compatibility-pragma-autonomous-transactions.mdx

       Notes: Good for Katacoda examples

     • PostGIS: postgresql_journey/04_developing/postgis.mdx

       Notes: Good for Katacoda examples

     • pg_stat_statements: postgresql_journey/04_developing/pg_stat_statements.mdx

       Notes: Good for Katacoda examples

     • auto_explain: postgresql_journey/04_developing/auto_explain.mdx

       Notes: Good for Katacoda examples

5. Maintenance
   • Monitoring
   • Tuning and Optimization
   • Patching
   • Backup and Recovery
   • Ugrading
6. HA/Failover
   • High Availability/Failover
   • Guide for preparaton, planning and testing
7. Distaster Recovery
   • Distaster Recovery

Key Languages & Framework/Library Platforms

Some tasks are primarily relevant to a subset of platforms: schema migrations are widely applicable, while mapping stored procedure results to objects is specific to ORM platforms. Identifying the root task is therefore essential to defining structure. This is not an exhaustive list, but following language+library platforms are among the most commonly-used when coupled with PostgreSQL.

(listed in rough order of priority based on usage):

1. Java
   1. Hibernate
   2. JDBC
   3. Spring Data
2. Python
   1. Django
   2. SQLAlchemy
   3. psycopg2
   4. Pandas
3. JavaScript
   1. sequelize.js
   2. knex.js
   3. node-postgres
4. Ruby
   1. Rails
5. C#
   1. Npgsql
   2. Entity Framework (Core)
6. PHP
   1. PDO
   2. Laravel
   3. Doctrine