Connecting Azure PostgreSQL to Oracle Autonomous with ORACLE_FDW
When migrating databases to PostgreSQL, not all data transitions happen immediately. Applications often need to keep accessing legacy databases for reading or writing, whether during a phased migration, for reporting, or because certain data still lives in another system. PostgreSQL implements parts of the SQL/MED standard via Foreign Data Wrappers (FDW), enabling queries against tables stored in external systems as if they were local. Instead of building ETL pipelines or copying data periodically, PostgreSQL can access remote data directly and let the query planner decide what to execute remotely. In this article, I'll connect Azure Database for PostgreSQL Flexible Server to an Oracle Autonomous Database using oracle_fdw, a Foreign Data Wrapper maintained by Laurenz Albe. I'll show how to: connect PostgreSQL to Oracle over TLS (Transport Layer Security) discover the outbound IP address used by Azure Database for PostgreSQL import Oracle tables as PostgreSQL foreign tables examine how filters and join operations are pushed down to Oracle perform INSERT, UPDATE, and DELETE operations on Oracle tables from PostgreSQL migrate from Oracle to PostgreSQL with a Create Table As Select over FDW run hybrid queries to compare remote and local tables after migration The goal is not to build a distributed database or a distributed transaction system. The goal is to access Oracle data from PostgreSQL with minimal setup while letting each database do the work it can do most efficiently. Enable the Foreign Data Wrapper To enable ORACLE_FDW, I selected it in the server parameters allowed extensions: Another option is to include it in the ServerParameter entry of an ARM template. In both cases, it is easy to check: postgres=> \dconfig azure.extensions List of configuration parameters Parameter | Value ------------------+------------------------------------------------- azure.extensions | ORACLE_FDW (1 row) Open the firewall and get the connection string I will connect to the Oracle Autonomous Database over the public internet. The security measures include user-password authentication, an encryption certificate, and a firewall with an IP whitelist. Since I am unsure which IP address Azure Database for PostgreSQL will use when connecting, I currently permit connections from any IP to my Oracle Autonomous Database: I didn’t select “Secure access from everywhere” because it requires mutual TLS (mTLS) with client certificates stored in a wallet on the client device. Since the client is an Azure PostgreSQL managed service and I can’t install a custom wallet, I chose one-way TLS (encryption without a client wallet), which is a practical option in this setup, as Oracle server certificates chain to public CAs trusted by Azure/PostgreSQL trust stores. This approach requires whitelisting IP addresses or a CIDR range. For testing, I temporarily allowed 0.0.0.0/0 to connect and capture the real source IP address for future adjustments. I did this only to identify the source IP used by Azure Database for PostgreSQL. In sensitive database environments, avoid allowing 0.0.0.0/0 even for a short time. Instead, use an ephemeral Oracle Autonomous test instance to identify the PostgreSQL server’s source IP address. You will need a username and password to connect, along with the connection string shown in the database connection details: I recommend using the TP or LOW services because MEDIUM and HIGH are intended for data warehouse workloads, which can cause unexpected locking behavior or resource usage. In my case, the connection string is: (description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-madrid-1.oraclecloud.com))(connect_data=(service_name=g230b6bb64a62e6_mad_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes))) That's everything I need on the Oracle side. I have all the information needed to connect from PostgreSQL. Declare the Foreign Data Wrapper server I connect to the Azure PostgreSQL server with psql and enable the ORACLE_FDW extension: postgres=> create extension oracle_fdw; CREATE EXTENSION I declare the connection string for the foreign data wrapper server: postgres=> create server oracle_autonomous foreign data wrapper oracle_fdw options (dbserver '(description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-madrid-1.oraclecloud.com))(connect_data=(service_name=g230b6bb64a62e6_mad_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'); CREATE SERVER I declare the Oracle username and password to be used by my current user: postgres=> create user mapping for current_user server oracle_autonomous options (user 'ADMIN', password '<password on Oracle Autonomous>'); CREATE USER MAPPING To check if the connection is correct, I call oracle_diag(), which shows the client-side versions as well as the major version of the server: postgres=> select oracle_diag('oracle_autonomous') ; oracle_diag ---------------------------------------------------------------------------------------- oracle_fdw 2.8.0, PostgreSQL 18.4, Oracle client 23.26.0.0.0, Oracle server 23.0.0.0.0 (1 row) As the server version is returned, I know that I'm connected. If it cannot connect, you must verify the connection string, the IP allow list, and the credentials. Discover the public outbound IP address Since I don't want to keep the firewall open to 0.0.0.0/0 indefinitely, and rely solely on password-based protection, I first identify the IP address I'm connected from to narrow down the allowed IP list: postgres=> select oracle_execute('oracle_autonomous',$$ begin -- PL/SQL block to raise an exception that carries the IP address information raise_application_error( -20999, 'Hello ' || user||'@'||sys_context('userenv','ip_address') ); end; $$); ERROR: error executing statement: OCIStmtExecute failed to execute query DETAIL: ORA-20999: Hello ADMIN@4.203.152.223 ORA-06512: at line 3 The error message is expected because I used raise_application_error() to return a message to oracle_execute(), which does not accept statements that return a result. Now I can replace CIDR 0.0.0.0/0 with the IP address of the Azure PostgreSQL server I'm connecting from: Note that Azure does not guarantee that this public address remains static, so this rule may need updating after maintenance or failover. For production where such FDW access is long-term and highly available, use an architecture that provides controlled egress IPs (for example via NAT) that you can allowlist, or private connectivity where available. Since ORACLE_FDW allows me to connect and perform DML on the Oracle side, I have no other tasks on the Oracle side. PostgreSQL is my Oracle client. Import Oracle table metadata Back at my psql prompt, I can import the metadata for the Oracle schema "SH" into the PostgreSQL schema "fdw_sh": postgres=> create schema fdw_sh ; CREATE SCHEMA postgres=> import foreign schema "SH" from server oracle_autonomous into fdw_sh ; IMPORT FOREIGN SCHEMA I can describe what is imported: postgres=> set search_path TO fdw_sh, public ; postgres=> \d List of relations Schema | Name | Type | Owner --------+----------------------------+---------------+-------- fdw_sh | channels | foreign table | franck fdw_sh | costs | foreign table | franck fdw_sh | countries | foreign table | franck fdw_sh | customers | foreign table | franck fdw_sh | products | foreign table | franck fdw_sh | promotions | foreign table | franck fdw_sh | sales | foreign table | franck fdw_sh | supplementary_demographics | foreign table | franck fdw_sh | times | foreign table | franck (9 rows) Those are the tables imported from the "SH" schema of the Oracle database. ORACLE_FDW has automatically mapped the data types to PostgreSQL data types: postgres=> \d fdw_sh.countries Foreign table "fdw_sh.countries" Column | Type | Collation | Nullable | Default | FDW options ----------------------+-----------------------+-----------+----------+---------+-------------- country_id | numeric | | not null | | (key 'true') country_iso_code | character(2) | | not null | | country_name | character varying(40) | | not null | | country_subregion | character varying(30) | | not null | | country_subregion_id | numeric | | not null | | country_region | character varying(20) | | not null | | country_region_id | numeric | | not null | | country_total | character varying(11) | | not null | | country_total_id | numeric | | not null | | country_name_hist | character varying(40) | | | | Server: oracle_autonomous FDW options: (schema 'SH', "table" 'COUNTRIES') It is recommended to run ANALYZE to ensure the PostgreSQL query planner knows the cardinalities. Note that foreign tables are not automatically analyzed by auto-analyze. postgres=> select format('analyze verbose %I.%I;', table_schema, table_name) from information_schema.tables where table_schema = 'fdw_sh' \gexec PostgreSQL doesn't automatically collect statistics on foreign tables. Without running ANALYZE, the optimizer might misjudge row counts, resulting in suboptimal join plans and fewer pushdown opportunities. By default, ANALYZE reads 100% of the table but it can be lowered for large table by setting the sample percentage beforehand: postgres=> alter foreign table fdw_sh.sales options (set sample_percent '5') ; ALTER FOREIGN TABLE Query the foreign tables I can query foreign tables just like local ones. For example, to find the total customer credit exposure by country for certain regions, I run the following: postgres=> -- explain (analyze, verbose, buffers) select co.country_name, sum(cu.cust_credit_limit) as total_credit from fdw_sh.customers cu join fdw_sh.countries co on co.country_id = cu.country_id where co.country_region in ('Europe') group by co.country_name having sum(cu.cust_credit_limit) > 1e7 order by total_credit desc ; country_name | total_credit ----------------+-------------- Germany | 49579000 United Kingdom | 45205500 Italy | 44844500 France | 23987000 Spain | 12170000 (5 rows) The execution plan indicates the operations that have been delegated to the foreign database: Sort (cost=253237.64..253237.64 rows=3 width=41) (actual time=10348.498..10348.500 rows=5.00 loops=1) Output: co.country_name, (sum(cu.cust_credit_limit)) Sort Key: (sum(cu.cust_credit_limit)) DESC Sort Method: quicksort Memory: 25kB -> GroupAggregate (cost=253056.49..253237.61 rows=3 width=41) (actual time=10343.320..10348.489 rows=5.00 loops=1) Output: co.country_name, sum(cu.cust_credit_limit) Group Key: co.country_name Filter: (sum(cu.cust_credit_limit) > '10000000'::numeric) Rows Removed by Filter: 3 -> Sort (cost=253056.49..253116.81 rows=24130 width=14) (actual time=10342.473..10343.918 rows=30564.00 loops=1) Output: co.country_name, cu.cust_credit_limit Sort Key: co.country_name Sort Method: quicksort Memory: 1783kB -> Foreign Scan (cost=10000.00..251300.00 rows=24130 width=14) (actual time=51.461..10333.852 rows=30564.00 loops=1) Output: co.country_name, cu.cust_credit_limit Oracle query: SELECT /*47caada6fd16dcb0*/ r2."COUNTRY_NAME", r1."CUST_CREDIT_LIMIT" FROM ("SH"."CUSTOMERS" r1 INNER JOIN "SH"."COUNTRIES" r2 ON (r1."COUNTRY_ID" = r2."COUNTRY_ID") AND (r2."COUNTRY_REGION" = 'Europe')) Oracle plan: SELECT STATEMENT Oracle plan: HASH JOIN (condition "R1"."COUNTRY_ID"="R2"."COUNTRY_ID") Oracle plan: TABLE ACCESS FULL COUNTRIES (filter "R2"."COUNTRY_REGION"='Europe') Oracle plan: TABLE ACCESS FULL CUSTOMERS Query Identifier: -2654414372183047898 Planning Time: 152.202 ms Execution Time: 10348.595 ms The most important line in the execution plan is the generated Oracle query. It shows exactly which operations PostgreSQL delegated to Oracle and how many rows were returned across the network. In this example, the join and filter were pushed down: it executed a hash join with the COUNTRIES table as the build table and the CUSTOMERS table as the probe table, returning 30564 rows. The aggregation happened in PostgreSQL. Here is the visualization in the VS Code extension for PostgreSQL: Checking the execution plan is essential because remote calls introduce latency. We should minimize roundtrips and avoid reading excessive rows that will be discarded later. Execute DML (read and write) Unlike many federation technologies, oracle_fdw supports direct INSERT, UPDATE, and DELETE operations on Oracle tables from PostgreSQL. I use oracle_execute() to create a new table on the remote Oracle Database: postgres=> select oracle_execute( 'oracle_autonomous', $$ create table "REGIONS" ( ID number primary key, NAME varchar2(100) unique ) $$ ); oracle_execute ---------------- (1 row) postgres=> select oracle_close_connections() ; oracle_close_connections -------------------------- (1 row) After executing DDL through oracle_execute(), I close the cached Oracle connection because, in my tests, Oracle’s implicit DDL commit left oracle_fdw’s transaction state out of sync, causing subsequent queries on the same remote session to fail with ORA-08177 ("can't serialize access for this transaction"). I am able to declare the foreign table and insert rows through it: postgres=> create foreign table regions ( id numeric options (key 'true'), name text ) server oracle_autonomous options (schema 'ADMIN', table 'REGIONS') ; CREATE FOREIGN TABLE postgres=> insert into regions (name, id) select distinct country_region, country_region_id from fdw_sh.countries ; INSERT 0 6 To demonstrate that DML occurs on the Oracle Database, I attempt to insert a duplicate, which results in an Oracle error: postgres=> insert into regions values (1,'Europe') ; ERROR: error executing query: OCIStmtExecute failed to execute remote query DETAIL: ORA-00001: unique constraint (ADMIN.SYS_C0035974) violated on table ADMIN.REGIONS columns (NAME) ORA-03301: (ORA-00001 details) row with column values (NAME:'Europe') already exists Help: https://docs.oracle.com/error-help/db/ora-0000 Remote queries are supported for transactions: postgres=> begin; BEGIN postgres=*> delete from regions; DELETE 6 postgres=*> select * from regions; id | name ----+------ (0 rows) postgres=*> rollback; ROLLBACK postgres=> select * from regions; id | name -------+------------- 52800 | Africa 52801 | Americas 52802 | Asia 52803 | Europe 52804 | Middle East 52805 | Oceania (6 rows) The remote delete was executed but later reversed through a rollback in the local transaction. You can check when the remote transaction starts and ends by setting client_min_messages to debug. You can query both local and remote tables within a single local transaction (without two-phase commit or distributed transaction guarantees). However, this does not provide consistent guarantees for distributed transactions. Hybrid queries An SQL statement can involve local and remote tables. Here is an easy way to import data from Oracle to PostgreSQL: postgres=> create table local_customers as select * from fdw_sh.customers ; CREATE TABLE postgres=> alter table local_customers add primary key (cust_id) ; ALTER TABLE postgres=> vacuum analyze local_customers ; VACUUM A SQL statement can join local and remote tables. Here is an easy way to compare two tables: postgres=> select l.cust_id, r.cust_id -- full outer join to read all rows from both tables from local_customers l -- push down order by to favor merge join full outer join ( select * from fdw_sh.customers order by cust_id ) r using (cust_id) -- eliminate the same rows where -- one cust_id row doesn't exist in the other l.cust_id is null or r.cust_id is null -- or it exists in both but with difference values or l is distinct from r ; To compare them, all rows must be read, but this execution plan is efficient, using a sort-merge join that compares rows without buffering them into a temporary table. This comparison is long and may produce false positives if concurrent DML operations occur while logical replication runs during the migration. Nevertheless, since both databases utilize multi-version concurrency control snapshots and their transactions started nearly simultaneously when using autocommit or serializable transactions, the likelihood of false positives is low. To deal with transient differences, you can quiesce writes, compare only a known past time window, or recheck reported rows after replication catches up. Limitations The foreign data wrapper is not a distributed query engine. It pushes only certain operations when it improves performance. For example, in the previous case, Oracle performed the join and filter, but PostgreSQL executed the GROUP BY. Operation Pushdown WHERE ✅ Yes (only expressions that can be safely translated) JOIN ✔️ Yes, between two foreign tables on the same foreign server when the join conditions and filters can be translated ORDER BY ✔️ Yes, except for string-based sort and when a join is pushed down GROUP BY ❌ (no aggregation pushdown in oracle_fdw 2.8.0) INSERT/UPDATE/DELETE ✅ Joins over 3 foreign tables ❌ No PostgreSQL functions ❌ No, except now(), transaction_timestamp(), current_timestamp, current_date, localtimestamp which are translated and pushed down Queries continue to be transmitted over the network. If pushdown isn't feasible, large result sets can slow down performance. Foreign tables are not automatically analyzed. Cross-database transactions are not managed as distributed transactions. oracle_fdw is ideal for access, reporting, and migration, but it does not substitute for physically transferring heavily used data into PostgreSQL. Conclusion ORACLE_FDW is simple to enable on Azure Database for PostgreSQL Flexible Server and provides a simple way to access Oracle data from PostgreSQL without introducing a separate replication or ETL layer. Oracle tables appear as PostgreSQL foreign tables, can join with local tables, and support read-write operations. The key feature is visibility. PostgreSQL's execution plan shows not only local operations but also the SQL sent to Oracle, along with the Oracle execution plan. This helps users easily see which parts are executed remotely and which stay on PostgreSQL. As with all Foreign Data Wrappers, performance depends on how much work you delegate to the remote database. Pushdown candidates typically include filters, joins, and sorting. PostgreSQL executes aggregation locally rather than delegating it to Oracle. Network latency and data transfer costs also play a significant role. For migrations, reporting, data validation, or gradual application modernization, oracle_fdw provides an effective solution to connect PostgreSQL and Oracle while using standard SQL on both platforms. It does not try to treat multiple databases as a single distributed system. If you used the Oracle Foreign Data Wrapper, please share your questions, comments, and feedback in the PostgreSQL Hub Developer Forum.
More Performance, Same Price: Azure Postgres V3 & V5 Compute Compared
Azure customers can select newer compute options and scale resources in real time with minimal disruption to business operations. This flexibility allows you to scale capacity as demand changes, match compute and memory profiles to each workload, and test new hardware configurations before moving production workloads. Using this opportunity to improve workload performance and cost efficiency over time results in real improvements to both price and performance. New Compute Can Change Workload Economics A newer compute generation is not merely a different SKU name. Changes in processor architecture, clock speed, memory bandwidth, storage throughput, and virtualization can materially affect application performance. Azure Database for PostgreSQL offers multiple options across General Purpose and Memory Optimized tiers. Customers can change the compute size and move between hardware generations without rebuilding the database platform. Reviewing these options regularly ensures that workloads are optimized and able to maximize performance and cost investments. A workload that was appropriately sized when deployed may no longer be running on the most cost-effective infrastructure. Periodic evaluation of newer compute generations can reveal opportunities to improve throughput, latency, or capacity without increasing spend. The opportunity to upgrade Azure Postgres workloads while maintaining the same operating costs presents a valuable option for anyone currently consuming V3 family compute. Take advantage of these capabilities by scaling your Azure Postgres workloads today: Scale Compute in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn Benchmarking V3 and V5 PostgreSQL Compute To measure the potential impact, we compared two Azure Database for PostgreSQL servers. Each server was provisioned with 4 General Purpose vCores, 16 GiB memory, and SSD Storage with 7500 IOPS. We ran the same CPU-intensive workload under identical test conditions with increasingly concurrent client workloads. Across repeated test runs, the V5 server processed approximately 40% more transactions than the comparable V3 configuration at effectively the same price. Benchmark resources and provisioning steps are included in the appendix. (Higher is better) The result represents approximately 40% more transaction throughput for the same spend in this specific benchmark. (Lower is better) The V5 configuration completed the workload in less time, indicating lower overall execution latency in this benchmark. For CPU-intensive workloads, this improvement translates to higher transaction volumes, reduced processing backlogs and latency, and provides additional capacity for future growth at approximately the same cost. Database performance also depends on memory, storage, I/O latency, concurrency, query design, indexing, PostgreSQL configuration, and application behavior. This result should therefore be treated as a workload-specific reference benchmark rather than a universal performance claim. The most meaningful comparison is one performed with a representative version of your own workload. Infrastructure should be reviewed continuously Cloud optimization is not a one-time sizing exercise. A server selected several years ago may continue to operate reliably while missing newer price-performance improvements. Regular infrastructure reviews help teams identify opportunities before older choices become unnecessary cost or capacity constraints. Teams should periodically review: Available compute family options in their Azure regions CPU, memory, storage, and I/O utilization Current and projected workload demands Transactions or queries completed per unit of cost Performance under representative load Migration requirements and expected downtime For additional guidance on optimizing Azure Database for PostgreSQL workloads, see Plan Azure Database for PostgreSQL flexible server deployments for operational performance on Microsoft Learn. Azure’s continued investment in regions, datacenters, and compute infrastructure gives customers new ways to improve their workloads. Realizing that value requires regularly reviewing what has become available, measuring it against real application behavior, and adopting it where the business case makes sense. The combination of continued platform investment from Microsoft and your active optimization becomes an ongoing partnership focused on helping your businesses perform, scale, and succeed. Appendix This appendix provides the resources and provisioning steps used for the benchmark. Benchmark Resources The benchmark was deployed using the following bicep file definition, named “postgres-flex-compute-benchmarks.bicep”: param administratorLogin string = 'benchAdmin' @secure() param administratorLoginPassword string = '' param serverEdition string = 'GeneralPurpose' type serverConfiguration = { serverName: string skuName: string } param storageSizeGB int = 32 param storageTier string = 'P40' //7500 IOPS param location string = 'canadacentral' param haMode string = 'Disabled' param availabilityZone string = '2' param serverConfigs serverConfiguration[] = [ { serverName: 'bench-standard-d4s-v3' skuName: 'Standard_D4s_v3' // 4 vCores, 16 GiB memory, 6400 Max IOPS } { serverName: 'bench-standard-d4s-v5' skuName: 'Standard_D4s_v5' // 4 vCores, 16 GiB memory, 6400 Max IOPS } ] resource servers 'Microsoft.DBforPostgreSQL/flexibleServers@2025-08-01' = [for serverConfig in serverConfigs: { location: location name: serverConfig.serverName properties: { createMode: 'Default' version: '18' administratorLogin: administratorLogin administratorLoginPassword: administratorLoginPassword availabilityZone: availabilityZone storage: { storageSizeGB: storageSizeGB autoGrow: 'Disabled' type: 'Premium' tier: storageTier } network: { publicNetworkAccess: 'Enabled' } backup: { backupRetentionDays: 7 geoRedundantBackup: 'Disabled' } highAvailability: { mode: haMode } } sku: { name: serverConfig.skuName tier: serverEdition } }] // Create the firewall rule on every server. resource serverFirewallRules 'Microsoft.DBforPostgreSQL/flexibleServers/firewallRules@2025-08-01' = [ for (serverConfig, i) in serverConfigs: { name: 'AllowAll' parent: servers[i] properties: { startIpAddress: '0.0.0.0' endIpAddress: '255.255.255.255' } } ] The following plpgsql function was created to prioritize CPU operations: CREATE OR REPLACE FUNCTION leibniz_pi(iterations integer) RETURNS double precision LANGUAGE plpgsql AS $$ DECLARE i integer; result double precision := 0; sign double precision := 1; BEGIN FOR i IN 0..iterations - 1 LOOP result := result + sign / (2 * i + 1); sign := -sign; END LOOP; RETURN 4 * result; END; $$; Provisioning Steps Provision two Azure Database for PostgreSQL flexible servers using comparable V3 and V5 compute configurations using the following CLI command: $password = Read-Host "Password" -MaskInput az deployment group create ` --resource-group <your_resource_group_name> ` --template-file ./postgres-flex-compute-benchmarks.bicep ` --parameters administratorLoginPassword="$password" Once the servers have been provisioned, create the “leibniz_pi” function on each server. Use containerized environments to execute a pgbench while passing in the custom plpgsql function: 'SELECT leibniz_pi(10000000);' | docker run --rm -i ` -e PGPASSWORD="<YOUR_PG_PASSWORD>" ` postgres:18 ` pgbench -n -c 8 -j 8 -T 300 -f - ` "host=<V3_OR_V5_SERVER_NAME>.postgres.database.azure.com port=5432 dbname=postgres user=benchAdmin sslmode=require" Repeat the test runs and record transaction throughput, execution time, and relevant resource metrics. Compare the results while accounting for workload variability and any differences in the underlying compute architecture.July 2026 Recap: Azure Database for PostgreSQL
Features PgBouncer: Update to 𝘃𝗲𝗿𝘀𝗶𝗼𝗻 𝟭.𝟮𝟱.𝟮 Azure Database for PostgreSQL Flexible Server now supports PgBouncer 1.25.2, keeping the built-in connection pooler aligned with the latest community release. PgBouncer helps applications efficiently manage large numbers of idle and short-lived connections with low overhead. This update includes the latest community security and stability fixes, including fixes for multiple CVEs affecting network packet parsing, SCRAM authentication, error handling, and admin command authorization - strengthening the reliability and security of the managed connection pooling experience. Documentation: PgBouncer in Azure Database for PostgreSQL Maintenance Events: Programmatic control through Rest APIs and Azure CLI Azure Database for PostgreSQL Flexible Server now supports new Maintenance Events REST APIs and Azure CLI commands, giving you more ways to programmatically view and manage planned maintenance. You can view upcoming maintenance, review maintenance history, reschedule eligible maintenance for up to 14 days, and apply maintenance on demand through REST APIs and Azure CLI. These capabilities make it easier to into automation integrate maintenance management, scripts, internal tooling, and operational workflows. The REST APIs are available starting with the 2026-04-01-preview API version, while the same maintenance capabilities are available in Azure CLI version 2.88.0 and later. Documentation: Maintenance Events REST API Documentation: Azure CLI Maintenance Event Commands India South Central now generally available We’re excited to announce that Azure Database for PostgreSQL Flexible Server is now generally available in the India South Central region. You can now build and run production-ready cloud applications closer to your users, with the flexibility and control of a fully managed PostgreSQL service. Expanding PostgreSQL Extensibility with pgPointCloud, RDKit, and plpgsql_check Azure Database for PostgreSQL Flexible Server now supports three additional PostgreSQL extensions, expanding the range of specialized workloads you can run on a fully managed PostgreSQL service. pgPointCloud: Enables you to store, compress, and query large-scale LiDAR and 3D point-cloud data directly in PostgreSQL, supporting spatial workloads across areas such as geospatial analytics, autonomous systems, agriculture, research, and other data-intensive scenarios. RDKit: Brings cheminformatics capabilities to PostgreSQL, helping pharmaceutical, chemical, and research teams work with molecular data, fingerprints, similarity searches, and indexing support. plpgsql_check: Helps developers validate PL/pgSQL code and improve code quality, especially for teams modernizing database applications or migrating procedural SQL workloads to Azure Database for PostgreSQL Flexible Server. Together, these extensions make it easier to bring advanced data types, domain-specific analytics, and developer tooling closer to your PostgreSQL applications on Azure. See the full list of all available extensions in Azure Database for PostgreSQL in our learn documentation. Azure PostgreSQL Learning Bytes Take Control of PostgreSQL Maintenance Azure Database for PostgreSQL Flexible Server now gives you more control over planned maintenance events. With self-service maintenance controls in the Azure portal, you can view upcoming maintenance, reschedule eligible maintenance to a more convenient time, apply updates when you're ready, and review maintenance history after completion. These capabilities help reduce operational risk and make it easier to align maintenance with your business schedule. Whether you're managing production workloads, preparing for a major release, or avoiding peak business periods, these controls provide greater flexibility and visibility so you can plan maintenance with confidence. Learn more: Read the full blog post, Take Control of Your PostgreSQL Maintenance, for details on the maintenance experience and how to use it.Oracle to PostgreSQL in VS Code: Assessment, Conversion, and Validation
This article explores the possibilities opened by the schema conversion workflow. It is an early evaluation: I used HorizonDB as a target, which is in preview and not a supported target yet, and Oracle Database 26ai as a source which has not been validated yet. Early tests and feedbacks are welcome. If you encounter any issue, please open an issue (https://github.com/microsoft/vscode-pgsql/issues) or a discussion (https://github.com/Azure-Samples/postgres-hub/discussions) The goal is to evaluate how AI assistance changes the first phases of a migration: assessing the effort, creating a first working prototype, and making the remaining technical gaps visible. This is where an integrated development environment is particularly effective. Source artifacts, generated PostgreSQL code, reports, database connections, tests, findings, and repairs can remain in one reproducible VS Code workspace. Migrating a database is not finished when the tables compile. A useful test must also answer three harder questions: Was the reference data copied correctly? Were the application-facing stored routines actually deployed? Do those routines produce the same business effects under a realistic load? This is particularly important when business logic is embedded in proprietary database constructs such as PL/SQL packages, which are in scope for this migration. Swingbench Order Entry is a good example. It has two versions: one uses pure JDBC, with the business logic in Java, and the other places the logic in a PL/SQL package that uses advanced Oracle Database features. The server-side version illustrates the migration challenges found in many critical legacy applications. In this article, I walk through those questions using Swingbench's Sales Order Entry (SOE) schema. I ran Oracle Database and Swingbench in Docker containers and used the PostgreSQL extension for Visual Studio Code to convert the schema against an Azure HorizonDB (preview) scratch database. I then used GitHub Copilot to build the data-copy, workload-emulation, and validation programs needed to test PostgreSQL. This article follows the migration end to end. Readers can use individual sections independently, but I keep the complete sequence because each stage exposed issues that schema conversion alone could not reveal. Throughout this project, I made the architectural decisions, directed the investigation, and validated results. The migration extension performed the model-assisted schema and PL/SQL conversion, while GitHub Copilot generated supporting code and tests under my direction. PostgreSQL compilation and the executable tests provided the evidence. Instead of replacing deterministic extraction, dependency analysis, and type mappings, AI participated in the iterations that traditionally follow them: contextual translation, review, diagnosis, repair, and validation. GitHub Copilot also produced the first draft of this article, which I reviewed and refined so it accurately describes what I ran, observed, and concluded. What was migrated Swingbench SOE is a compact but realistic migration target. It includes: customers, addresses, cards, products, inventory, orders, and order items primary keys, foreign keys, checks, indexes, and sequences views and analytical queries the Oracle SOE.ORDERENTRY PL/SQL package used by Swingbench, with all the application logic, leaving only the presentation layer to the frontend package state, collection types, helper routines, random choices, sleeps, transaction control, and business transactions The test environment was: Component Version or image Oracle Database 26ai gvenzl/oracle-free:slim (23.26.2) Swingbench domgiles/swingbench:latest (2.6.1118) PostgreSQL Azure HorizonDB (PostgreSQL 17.9) PostgreSQL extension for Visual Studio Code 1.27.3 Foundry model gpt-5.2 1. Build the Oracle source environment I used the following docker-compose.yaml to start Oracle and keep a Swingbench container available for schema creation and testing: services: oracle: image: gvenzl/oracle-free:slim environment: ORACLE_PASSWORD: "<oracle-system-password>" ports: - "1521:1521" healthcheck: test: ["CMD", "healthcheck.sh"] interval: 10s timeout: 5s retries: 30 swingbench: image: domgiles/swingbench:latest platform: linux/amd64 depends_on: oracle: condition: service_healthy entrypoint: ["sleep", "infinity"] I started both containers with: docker compose up -d Once Oracle was healthy, I created a small SOE database with Swingbench's command-line wizard. I chose scale 0.1 : large enough to exercise the relationships while remaining convenient for repeated migrations. docker compose exec swingbench oewizard -cl -create \ -cs //oracle:1521/FREEPDB1 \ -dba "sys as sysdba" -dbap '<oracle-system-password>' \ -df /opt/oracle/oradata/FREE/FREEPDB1/soe01.dbf \ -u soe -p '"<soe-password>"' \ -scale 0.1 -tc 1 -v Swingbench substitutes the SOE password into CREATE USER ... IDENTIFIED BY . A password containing characters that require Oracle's quoted-password syntax must be passed with the literal double quotes expected by that SQL statement. Before migrating anything, I compiled the source package and checked for Oracle errors: docker compose exec -T oracle \ sqlplus -s / as sysdba <<'SQL' alter session set container=FREEPDB1; alter package SOE.ORDERENTRY compile; alter package SOE.ORDERENTRY compile body; show errors package body SOE.ORDERENTRY SQL I then tested the environment to be migrated by running the original Swingbench application for one minute: docker compose exec -it swingbench charbench \ -cs //oracle:1521/FREEPDB1 \ -dbau system -dbap '<oracle-system-password>' \ -u soe -p '<soe-password>' \ -rt 00:01:00 -c ../configs/SOE_Server_Side_V2.xml 2. Prepare VS Code, HorizonDB, and Foundry I installed VS Code and the PostgreSQL extension for Visual Studio Code, authored by Microsoft. The migration workflow is exposed under Migrations (Preview) in the PostgreSQL view: I opened Migrations (Preview) and created a project. The first step was to name the migration project. I generated all screenshots while reviewing this article and replaying the walkthrough, so the project identifier for this run was walkthrough-validation : The second step was to connect to the source Oracle Database as a user with the DBA role and select the schema: The third step was to define the destination PostgreSQL database: The migration tool executes generated DDL there, so this must not be an application or production database. At the time of this test, the migration workflow expected an Azure PostgreSQL connection for compilation, either Flexible Server or HorizonDB. I used an Azure HorizonDB (preview) database as a dedicated scratch target. This was an exploratory use of the preview workflow, not a statement of official HorizonDB support. The generated code targets PostgreSQL rather than HorizonDB-specific APIs; portability to another PostgreSQL deployment still depends on its PostgreSQL version, available features, and managed-service restrictions. I created the database service beforehand from VS Code: The conversion also needed a Microsoft Foundry resource and a deployed model. I created the resource, opened Foundry, deployed gpt-5.2 , and recorded the resource endpoint and deployment name. I created the resource from the Azure portal: I noted its endpoint and key: From VS Code, I opened the Foundry portal: I selected GPT-5.2: I chose to use the selected model: I deployed the model (I used the model name as the deployment name here): The fourth configuration step was to select the model deployment using its name, endpoint, and API key: The migration project was created: All files, configuration, logs, and reports were stored under .github/postgres-migrations. 3. Run the migration project The migration workflow extracted Oracle metadata, created a dependency graph, divided the objects into dependency-aware chunks, converted each chunk, reviewed the generated SQL, compiled it against PostgreSQL, and assembled deployment artifacts. The process resembles a compiler pipeline more than a text translator: It started by extracting the metadata into artifacts/oracle/SOE/extract/ddl: A report provides all details when completed: It then started the conversion, one chunk at a time: The conversion log shows the cycle of conversion, compilation, review, test, and validation using the LLM. Here is an example showing the level of detail the log provides: 16:50:16 [INFO] ossdbtoolsservice.conversion_v2.pipeline.chunk_converter.package_converter: Package ORDERENTRY: member processOrders -> converted in 80.0s (tokens=5232, notes=Oracle ROWNUM < 10 translated to LIMIT 9 (no ORDER BY in source, so row choice remains arbitrary).; Oracle (+) outer joi) This is a good example because it goes beyond syntax, where ROWNUM < 10 translates to LIMIT 9 . It warns that a LIMIT without ORDER BY has a nondeterministic result that can differ between Oracle and PostgreSQL. Applications should not rely on physical row order, but a migration must still consider behavior validated by years of production use, even when that behavior originated as a side effect of an application bug. The PostgreSQL DDL goes into artifacts/oracle/SOE/convert/sessions. A report provides all details: Simple objects can follow deterministic paths, while complex PL/SQL receives model-assisted conversion and review. PostgreSQL compilation, rather than the model, is intended to be the final syntax and dependency check. Once the migration is complete, it lists the review tasks. The counts and classifications shown here are observations from version 1.27.3 of an evolving preview workflow. Future versions may present fewer review tasks as their focus evolves in response to feedback, including a stronger emphasis on syntax and conversion completeness: The summary also lists extensions that should be installed in the target database: The orafce extension implements many functions familiar to Oracle users, including functions from Oracle DBMS packages. It can reduce the amount of code that must change during a migration. Extensions such as orafce and plpgsql_check can improve conversion and validation when they are available. For this experiment, however, I deliberately used a vanilla PostgreSQL target without them. I wanted to see what the workflow could produce without tying the converted application to extensions that may not be available from every managed PostgreSQL service. This makes portability a design goal of the prototype, not a claim that every PostgreSQL deployment is identical or that omitting extensions always produces the most accurate conversion. Before proceeding to the manual review, I inspected the fresh conversion log to separate what the pipeline had repaired automatically from what it had only flagged for attention. 4. What the migration workflow corrected, and what it could not prove I used GitHub Copilot to parse the conversion log and distinguish initial conversion, model review, compiler-driven repair, and warnings left for application review. The analysis found 37 automatic correction events: 33 during the second-pass review and four after PostgreSQL rejected generated SQL during compilation. The run finished with zero failed objects and zero fallback objects. The second-pass reviewer corrected 20 of the 35 generated package artifacts. The interesting changes included: guarding current_setting() results against missing or empty values before casting them changing the DML counter helpers from transaction-local to session-level GUC writes so their state can survive a commit on the same connection replacing invalid Oracle %TYPE references in PostgreSQL signatures and expressions replacing invalid PERFORM * FROM and Oracle cursor syntax schema-qualifying table references and limiting a SELECT INTO that could otherwise return multiple rows repairing an incomplete generated type-creation block These were code changes, not only warnings. This is where model-assisted conversion goes beyond a fixed syntax rulebook: it reviews generated code in context and revises interactions between types, state, queries, and control flow. PostgreSQL compilation then found four remaining defects. The repair loop removed unsupported DEFERRABLE clauses from two check constraints and repaired two routines before recompiling them. This is an important distinction: model review can anticipate defects, while the target database provides the exact SQLSTATE and failing statement. The pipeline then fixes and retries, much like a developer working until the code compiles. Chunking did not leave dependencies to the user. When a referenced table, index, or routine was produced by another chunk, the pipeline deferred the dependent operation and retried it after the required object existed. All such operations completed automatically in this run. Structural translations The conversion itself also made deliberate structural changes that were not compiler repairs: Oracle behavior PostgreSQL translation Package members Schema routines named orderentry$<member> (PostgreSQL has no packages) Package records and collections Composite types, array domains, and set-returning functions Package variables Custom settings accessed through set_config() and current_setting() DBMS_RANDOM.VALUE random() with range arithmetic (without the DBMS_RANDOM emulation provided by orafce ) DBMS_LOCK.SLEEP pg_sleep() DBMS_APPLICATION_INFO application_name or custom settings BULK COLLECT RETURNS TABLE , SETOF , or RETURN QUERY ROWNUM < n LIMIT n - 1 or row_number() CONNECT BY row generation Recursive CTE FORALL inventory updates Row-by-row PL/pgSQL loop (PostgreSQL runs PL and SQL in the same engine) SYSDATE and SYSTIMESTAMP CURRENT_TIMESTAMP , clock_timestamp() , and date_trunc() Package procedures Functions returning void Package overloads PostgreSQL overloads with the same flattened name What still requires attention Successful compilation proves syntax and dependency consistency, not behavioral equivalence. The fresh review report classifies 34 of 78 objects as auto-approved and leaves 44 non-blocking behavioral divergences for application review. The most important are: Oracle ENABLE NOVALIDATE primary keys do not validate existing rows, while PostgreSQL primary-key creation does. Eight tables therefore require clean source data before deployment. Five Oracle reverse-key indexes became normal PostgreSQL btree indexes, which can change insertion hot spots and access behavior. Package state is stored as text in custom GUCs. Although the reviewer changed the counter helpers to session-level writes, the emitted routines still use different key prefixes and a mixture of session-level and transaction-local settings. Initialization, pooling, commits, and casts need runtime tests. PostgreSQL functions cannot reproduce Oracle package-side COMMIT . The caller must own transaction boundaries. ROWNUM conversions without ORDER BY preserve nondeterministic selection, but they do not guarantee that Oracle and PostgreSQL choose the same rows. BULK COLLECT collections became row sets, and FORALL became a loop. Empty collection behavior, ordering, locking, and performance can differ. Random-number boundaries, numeric casts, exception behavior, and SYSDATE / SYSTIMESTAMP timing and timezone semantics remain database-specific. None of these 44 findings blocked deployment in this run. They define the work for the runtime validation in the following sections rather than automatic fixes that the migration report can prove. 5. Examples from the generated ORDERENTRY package The package conversion shows why this is more than syntax replacement. The pipeline emitted all 34 executable package-body members, including private helpers, and represented Oracle procedures as PostgreSQL functions returning void . Because PostgreSQL has no equivalent of package visibility, every member became a schema routine named orderentry$<member> . Applications should keep calling only the former public API, with privileges used to preserve the old visibility boundary. The harder transformations preserved the shape of the business logic without pretending that Oracle and PostgreSQL use the same programming model. For example, BULK COLLECT collections became set-returning functions. Indexed collection access became operations over those row sets. The FORALL inventory update in orderentry$neworder became a PL/pgSQL loop, and the CONNECT BY row generator used for warehouse activity became a recursive CTE. Package helpers using DBMS_RANDOM , DBMS_LOCK.SLEEP , and DBMS_APPLICATION_INFO became calls to random() , pg_sleep() , and PostgreSQL settings. A small conversion with a large consequence I verified that from_mills_to_secs was already a private Oracle function rather than a helper invented during migration: function from_mills_to_secs(value integer) return float is real_value float := 0; begin real_value := value/1000; return real_value; exception when zero_divide then real_value := 0; return real_value; end from_mills_to_secs; Despite the shortened name, “mills” means milliseconds here. The ZERO_DIVIDE branch is defensive but cannot normally be reached because the divisor is the constant 1000. I compared it with the generated PostgreSQL function, which keeps the operation but explicitly converts to floating point to avoid integer division: CREATE OR REPLACE FUNCTION soe.orderentry$from_mills_to_secs(value integer) RETURNS double precision LANGUAGE plpgsql IMMUTABLE AS $$ DECLARE real_value double precision := 0; BEGIN real_value := value::double precision / 1000.0; RETURN real_value; EXCEPTION WHEN division_by_zero THEN real_value := 0; RETURN real_value; END; $$; orderentry$sleep calls this helper for the fixed or randomly selected delay, maps DBMS_LOCK.SLEEP to pg_sleep , and updates a package counter. Here the review found a subtle semantic issue: the generated helper calculates elapsed milliseconds but adds the selected delay in seconds to the counter. The supplied workload uses zero delays, so it did not affect this run. With nonzero delays, the code could compile and execute while returning the wrong value. This is the kind of small conversion detail that is easy to miss and appears much later as an application error. Transaction control changes ownership The largest architectural difference is transaction control. In Oracle Database, oecommit can issue COMMIT from inside the package when the session-level PLSQLCOMMIT flag is true. The package therefore decides when its changes become durable and increments its own commit counter at that point. The converter represented the package procedures as PostgreSQL functions to preserve their callable API, but a PostgreSQL function cannot commit or roll back its surrounding transaction. The generated oecommit can preserve the instrumentation, not the transaction boundary. PostgreSQL procedures invoked with CALL can perform transaction control in specific contexts, but changing these package entry points into procedures would also change how the application calls them and how return values are handled. For this migration, I chose to disable package-side commits. The Python driver calls orderentry$setplsqlcommit('false') , commits each successful mutating call, and rolls back a failed call. Transaction ownership therefore moved from the stored package to the application. That is an intentional adaptation, not an equivalent syntax translation. Multi-call atomicity, retries, error handling, and connection-pool behavior must be reviewed with that new boundary in mind. 6. Copy the reference data separately Without leaving VS Code, I first checked the migrated schema, including its tables, views, and converted ORDERENTRY routines: The migration extension converted the schema and code but did not copy the Swingbench data. For this small, specific task, it was faster to guide GitHub Copilot to generate a Python program than to switch to another migration tool. The script stayed in the repository with the connection setup and commands, making the copy reproducible and documented rather than a one-time manual operation. I installed its dependencies (Python 3.10 or later required) and exposed connection information through environment variables rather than embedding credentials: python3 -m pip install -r scripts-postgres/requirements.txt export ORACLE_DSN='localhost:1521/FREEPDB1' export ORACLE_USER='soe' export ORACLE_PASSWORD='<soe-password>' export PG_DSN='host=<host> port=5432 dbname=<db> user=<user> sslmode=require' export PG_SCHEMA='_mig_scratch_soe' The PG_SCHEMA variable names the PostgreSQL schema created by the migration extension during validation. The scripts set search_path to this schema automatically before executing any SQL. I ran a clean copy with: bash scripts-postgres/copy_soe_data.sh --truncate The program uses dependency order and PostgreSQL binary COPY . It also handles Oracle intervals and numeric values, preserves the intent of ENABLE NOVALIDATE checks, and advances the PostgreSQL sequences after loading. I then verified that the Oracle and PostgreSQL row counts matched: The complete implementation is available in copy_soe_data.py. 7. Recover and reproduce the Swingbench workload Swingbench is a free load generator, but it is not open source. It is distributed as compiled Java, so porting its client to PostgreSQL was neither practical nor the goal. For this server-side workload, the business logic is in the SOE.ORDERENTRY package. The Java client primarily chooses transactions, generates parameters, and calls the package. Calling the migrated routines with arbitrary values would test invocation, but not the real workload. I guided GitHub Copilot to inspect the files in the Swingbench container. The XML configuration provided the enabled transactions, weights, user count, delays, and timeout. I used javap to confirm the externally observable call signatures and runtime behavior needed to build a compatible validation workload. GitHub Copilot documented those findings under my direction in scripts-postgres/swingbench_calls.md. I then guided GitHub Copilot to create run_swingbench_workload.py. It follows the same weighted transaction selection with four persistent user connections, a start barrier, copied customer data, and the configured timeout. It reports throughput, latency, and errors without trying to reproduce the Swingbench GUI or Oracle-specific connection pool. As described in Section 5, transaction ownership had to change. The driver disables package-side commits, commits successful mutating calls through Psycopg, and rolls back failed calls. I ran it with: export PG_DSN='host=<host> port=5432 dbname=<db> user=<user> sslmode=require' export PG_SCHEMA='_mig_scratch_soe' bash scripts-postgres/run_swingbench_workload.sh --duration 300 Before creating load, the program verifies that the public workload API is complete. It exits early rather than report misleading performance results for an incomplete deployment. 8. Validate in layers Validation is part of the migration, not a final smoke test. Compilation proves that PostgreSQL accepts the generated objects, but not that they return the right results or preserve business effects. I directed GitHub Copilot to build four validation layers so each failure could be isolated before adding more workload complexity. Layer 1: catalog and query validation The rollback-safe test harness begins with a preflight that checks the PostgreSQL version, required tables, five sequences, and all public workload routines. It also submits the two analytical queries to PostgreSQL with EXPLAIN before proceeding to routine calls. I separately ran those two analytical queries with EXPLAIN ANALYZE against 106,583 customers, 160,684 orders, and 482,752 order items. Both executed successfully. The top-customer query completed in 424 ms. Its sequential scans were reasonable because it consumed almost all orders and items, although the aggregate spilled to temporary disk: Sort (actual time=423.274..423.277 rows=20 loops=1) Sort Key: (rank() OVER (?)) Sort Method: quicksort Memory: 26kB Buffers: shared hit=11220, temp read=1792 written=1953 -> WindowAgg (actual time=423.242..423.255 rows=20 loops=1) Run Condition: (rank() OVER (?) <= 20) -> Sort (actual time=423.236..423.240 rows=21 loops=1) Sort Key: sum(oi.quantity * oi.unit_price) DESC Sort Method: quicksort Memory: 1929kB -> HashAggregate (actual time=402.740..415.533 rows=24968 loops=1) Group Key: c.customer_id Batches: 9 Memory Usage: 8273kB Disk Usage: 1480kB -> Hash Join (actual time=60.363..279.582 rows=482718 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Hash Join (actual time=34.192..176.799 rows=482752 loops=1) Hash Cond: (oi.order_id = o.order_id) -> Seq Scan on order_items oi (rows=482752 loops=1) -> Hash -> Seq Scan on orders o (rows=160684 loops=1) -> Hash -> Seq Scan on customers c (rows=106583 loops=1) Planning Time: 0.742 ms Execution Time: 423.895 ms VS Code with the PostgreSQL extension can visualize the plan, including buffers read by each node and the flow of rows between nodes: The monthly revenue query completed in 627 ms. Its aggregate spilled into 33 batches before two in-memory window sorts, making work_mem the first tuning candidate rather than an index: WindowAgg (actual time=615.702..626.031 rows=27472 loops=1) Buffers: shared hit=11211, temp read=1965 written=2208 -> Sort (actual time=615.698..617.253 rows=27472 loops=1) Sort Key: customer_sales.customer_id, customer_sales.month_start Sort Method: quicksort Memory: 2352kB -> WindowAgg (actual time=600.739..610.404 rows=27472 loops=1) -> Sort (actual time=600.708..602.149 rows=27472 loops=1) Sort Key: customer_sales.month_start, customer_sales.revenue Sort Method: quicksort Memory: 2137kB -> Subquery Scan on customer_sales (actual time=570.611..587.861 rows=27472 loops=1) -> HashAggregate (actual time=570.610..586.090 rows=27472 loops=1) Group Key: c.customer_id, date_trunc('month', o.order_date) Batches: 33 Memory Usage: 8209kB Disk Usage: 2040kB -> Hash Join (actual time=54.823..392.870 rows=482718 loops=1) Hash Cond: (o.customer_id = c.customer_id) -> Hash Join (actual time=31.672..208.840 rows=482752 loops=1) Hash Cond: (oi.order_id = o.order_id) -> Seq Scan on order_items oi (rows=482752) -> Hash -> Seq Scan on orders o (rows=160684) -> Hash -> Seq Scan on customers c (rows=106583) Planning Time: 0.266 ms Execution Time: 627.065 ms These execution plans establish a PostgreSQL baseline and identify possible tuning work, such as aggregate spills and work_mem pressure. I kept those observations for the physical-design review after correctness and concurrent workload validation rather than tuning from isolated queries too early. Layer 2: call every public workload routine After the preflight, the same harness discovers viable values and calls all nine workload transactions plus setPLSQLCOMMIT : bash scripts-postgres/run_orderentry_tests.sh Each call is isolated and reports its SQLSTATE on failure. The first run exposed defects that compilation could not: composite return types did not match some rows, internal and public customer-ID types differed, package settings lacked defaults, and newOrder retained an Oracle %TYPE cast. GitHub Copilot generated schema-relative repairs, which I reviewed and applied. The implementation is available in apply_orderentry_runtime_fixes.py: bash scripts-postgres/apply_orderentry_runtime_fixes.sh I then ran the harness again, and all ten checks passed. Layer 3: verify business effects A successful call does not prove the correct business effect. GitHub Copilot generated a rollback-safe order workflow: bash scripts-postgres/run_swingbench_workload.sh --verify-only It creates two orders for a copied customer, verifies their fields and item rows, then checks that browseandupdateorders changes one item quantity and the matching order total together. It rolls back, reconnects, and proves that both orders are absent. Only sequence values may advance because PostgreSQL sequences are nontransactional. Layer 4: weighted concurrent load Only after the first three layers passed did I run concurrent load. Four users executed 287 calls across all nine transaction types with zero errors. This ordering separated migration defects from performance observations and avoided assuming that copied customer IDs were contiguous. After validation: review physical design After validating the migration and ensuring data accuracy, it is time to look at the physical layer, using the execution plans captured in Layer 1 together with observations from the concurrent workload. Oracle and PostgreSQL handle data patterns differently, so blindly copying Oracle optimizations can degrade performance. PostgreSQL's MVCC architecture adds write overhead to updates, so the table FILLFACTOR strategy needs a fresh look compared with Oracle's PCTFREE . PostgreSQL also provides different tuning options, including partial indexes, specialized index types, and incremental sorting. Reproduce the complete validation At the end of my run, the test setup contained: all ten workload tables populated all five required sequences deployed and advanced two analytical queries accepted by PostgreSQL under EXPLAIN a complete invocation harness a weighted Swingbench-like concurrent driver a rollback-safe semantic order test After deploying the converted schema and its ORDERENTRY routines, I applied the reviewed runtime fixes and ran the checks in increasing order of scope: bash scripts-postgres/apply_orderentry_runtime_fixes.sh bash scripts-postgres/run_orderentry_tests.sh bash scripts-postgres/run_swingbench_workload.sh --verify-only bash scripts-postgres/run_swingbench_workload.sh --duration 300 Conclusion AI did not make this migration a one-click operation. The Oracle application combined packages, private helpers, collections, package state, bulk operations, transaction control, and behavior that only appears under realistic calls. A credible migration had to understand those interactions rather than only produce PostgreSQL syntax. The useful change is that the migration can be approached as an integrated project from one place. In VS Code, the migration extension converted and compiled the schema. I inspected the generated code and reports, directed GitHub Copilot to generate the supporting data-copy and workload programs, reviewed and applied runtime fixes, and validated data, API calls, business effects, and concurrent load. The model accelerated code conversion and the creation of focused tools, while PostgreSQL, executable tests, and my knowledge of the application provided the evidence. This is the method I would carry from assessment into a real migration: use AI to drive more of the conversion, diagnosis, repair, and validation iterations, not to hide complexity. Keep source behavior, converted code, data movement, test programs, findings, and fixes together in a reproducible workspace. This makes the first prototype useful evidence for estimating the remaining effort. A migration report measures one pipeline stage; the application is migrated only when its data, transactions, business effects, and workload work together on PostgreSQL. This validation is necessary but not sufficient for production. A real go-live also requires review of edge cases, collation, NULL handling, error paths, security, and sustained performance under representative data volumes. Because this walkthrough exercises preview functionality, experience reports are especially valuable. Readers trying it on other Oracle applications or PostgreSQL targets can share what converts well, what requires intervention, and which findings would make the assessment more useful in the PostgreSQL Hub Developer ForumAI-assisted Oracle-to-PostgreSQL schema conversion in Visual Studio Code
By AI Omar Rajawat, Pranay Lohia, Gautam Juneja, Vikas Nimmagadda, Anil Dogra, Aditya Duvuri We’re seeing significant interest in migrating database workloads from Oracle to Azure Database for PostgreSQL. Historically, schema conversion has been one of the most technically challenging and expensive steps in that journey, demanding specialist knowledge of both engines and stretching migration timelines before a single row of data moves. Recent advances in AI-assisted schema conversion are changing that, turning what used to be a long, manual effort into a faster, more accessible, and lower-cost proposition. This post looks at what that shift means in practice for teams moving to Azure Database for PostgreSQL flexible server. Oracle schema conversion is where the complexity of translating schema and code objects to PostgreSQL becomes visible. Packages, procedures, triggers, custom types, and dependencies built up over years must be mapped to PostgreSQL-compatible definitions while preserving the relationships that make the schema work. Generally available since May 2026, the feature is built into the PostgreSQL extension for Visual Studio Code, published by Microsoft. It helps teams convert Oracle schema and code objects — tables, views, constraints, packages, procedures, functions, and triggers — into PostgreSQL-compatible definitions for Azure Database for PostgreSQL flexible server, with no separate conversion utility to install and no disconnected workflow to manage. It brings schema discovery, conversion, compile validation, and review into one project-based experience. Teams can connect to Oracle, select schemas, and configure a Microsoft Foundry connection in the same project. The extension then translates Oracle-specific constructs, compiles and syntax-checks converted DDL into scratch schemas on Azure Database for PostgreSQL flexible server and surfaces unresolved items as review tasks that teams can work through with GitHub Copilot agent mode. Why schema conversion deserves a better workflow Traditional conversion tools can produce a useful first pass, but the long tail of the process is rarely solved by generating replacement DDL alone. Teams still need clear answers to practical questions: What converted successfully? What needs attention? Which Oracle constructs require a PostgreSQL design decision? Which items should be reviewed first? We designed the schema conversion experience around those questions. The goal is not to hide complexity behind a single score. It is to help teams make steady progress while keeping the work visible and reviewable. What the schema conversion experience provides The experience guides teams through a schema conversion project rather than a collection of separate scripts. It discovers the selected Oracle schemas and converts both the relational model and the code that runs on it: tables, indexes, sequences, primary key, unique, check and foreign key constraints, views and materialized views, synonyms, and Oracle object types — along with the PL/SQL that is usually the hardest part of the migration. Packages and package bodies, package-level state, standalone procedures and functions, and triggers are translated into PostgreSQL functions, procedures, and trigger functions. Oracle-specific constructs are mapped to PostgreSQL equivalents rather than dropped or stubbed out. REF CURSOR and SYS_REFCURSOR become PostgreSQL refcursor; CLOB and BLOB columns become text and bytea ; and NUMBER and VARCHAR2 are mapped by precision and length to their closest PostgreSQL types. Oracle date functions such as ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, and TRUNC are resolved through the orafce extension, which the project detects and flags for you before deployment. Every converted definition is compiled against scratch schemas on Azure Database for PostgreSQL flexible server, so the deployment script you end up with is an organized, dependency-ordered set of PostgreSQL SQL artifacts that has already been proven to build. Objects that still require human judgment are surfaced as review tasks. Teams can inspect the source and converted definitions side by side, work through the remaining items, and use GitHub Copilot agent mode for guided assistance — keeping automation and human review in the same workflow. How it works: the system architecture Under the hood, the conversion engine follows one principle — the language model is a single, bounded stage; it never has the first or the last word. Deterministic steps decide what the model sees and what it is allowed to produce. Deterministic in. Rule-based extraction reads the Oracle DDL and metadata, then a dependency-graph decomposition splits the estate into bounded, dependency-ordered chunks — so every object is converted in the context that keeps it correct. Bounded conversion. A tiered model strategy through the Microsoft Foundry connection translates each chunk with structured, contract-wrapped input and output. Even very large PL/SQL packages are split and converted member by member, so nothing is trusted as a monolith. Deterministic out. Converted objects pass through review, then compile-and-verify against scratch schemas on Azure Database for PostgreSQL flexible server, and finally dependency-ordered deploy assembly. Unresolved items become review tasks, and every object carries a per-object audit trail. A continuous-improvement loop closes the system: the engineering team maintains a versioned regression suite of supported conversion patterns, and an executable benchmark tracks regressions as the pipeline evolves. Proven in production The approach has been exercised on real enterprise estate. Across representative production runs totaling more than 60,000 schema objects; conversion reached roughly 98% overall — with several schemas converting at a full 100%. The hardest tail, PL/SQL package members, now compiles at 96% across more than 20,000 members thanks to targeted coverage and a resilient compile stage. Conversion outcome and review status are separate measures. Objects that convert and compile cleanly are safe to deploy as they are; the rest are deliberately routed into a prioritized review queue rather than silently accepted. In a representative single-schema run, no object ended in a hard conversion failure, and a cleanly generated object can still involve a PostgreSQL design decision. That is the workflow operating as intended: automation absorbs the volume, and review tasks to keep the remaining judgment calls visible, ordered, and auditable. Measured, not asserted: the SchemaBench eval Quality is verified by running it. SchemaBench, the evaluation framework, deploys each converted schema to a live PostgreSQL database and probes real behavior — whether constraints still fire and whether objects still resolve — rather than comparing DDL text. It scores seven weighted dimensions: semantic fidelity, structure, constraints, completeness, performance, target idioms, and maintainability, behind hard gates. On the e-commerce benchmark, the strongest model scored 96.1 overall with 100% semantic fidelity and a ~98% behavioral-probe pass rate. Every failure a migration hits becomes a permanent regression test the next run has to pass. Learn more: Oracle to Azure Database for PostgreSQL schema conversion overviewFaster, Safer Version Upgrades for Databases with Large Objects
By Varun Dhawan, Ilan Benschikovski, and Alexander Kukushkin - Azure PostgreSQL, Microsoft Faster, Safer Upgrades for Databases with Large Objects TL;DR: We improved major version upgrades for PostgreSQL databases with very high large-object counts. For upgrades targeting PostgreSQL 15 and later, large-object metadata is now handled more efficiently, reducing memory/temp-space pressure and helping previously risky upgrades complete more reliably. Why this matters Some PostgreSQL workloads store documents, images, PDFs, scanned files, or attachments as large objects (LOBs). In normal operations this is fine. But during a major version upgrade, very high LOB counts could make the schema dump step slow, memory-heavy, or fail. This improvement is about making that upgrade path safer and more predictable for Azure Database for PostgreSQL flexible server customers. What changed? During a major version upgrade, PostgreSQL uses pg_upgrade , which internally runs pg_dump to move schema and metadata into the new version. For databases with millions of large objects, the older upgrade path handled large-object metadata one object at a time. That created high memory and temporary-space pressure during the schema dump phase. This fix changes the upgrade path. Instead of processing large-object metadata one object at a time, PostgreSQL now transfers that metadata in bulk. The actual large-object data is not changed; only the upgrade metadata handling is improved. Why this is different This improvement builds on upstream PostgreSQL work that makes large-object metadata handling more efficient during upgrades. We brought that benefit into Azure Database for PostgreSQL flexible server for supported PostgreSQL 15+ upgrade targets, so customers with large-object-heavy workloads can benefit without waiting for a future PostgreSQL major version. Before vs after Area Before After Metadata handling One operation per large object Bulk metadata transfer Memory/temp pressure Grew heavily with LOB count Much flatter and more predictable High LOB counts Risk of OOM or temp-space failure Completes more reliably for PostgreSQL 15+ targets Customer workaround vacuumlo + scale-up often needed Less reliance on LOB-specific workarounds for PostgreSQL 15+ targets In plain English: the upgrade no longer has to carry paperwork for every large object one by one. It moves the metadata in bulk, which makes the upgrade faster, safer, and less likely to fail at very high LOB counts. The numbers We tested upgrades from PostgreSQL 13 with large-object counts ranging from 10M to 500M. The older path is represented by PostgreSQL 13 → 14. The improved path is represented by PostgreSQL 13 → 15. Note: These figures come from a multi-database test where large objects were spread across 100 databases. Because pg_dump runs per database, single-database workloads with the same total large-object count may see different runtimes. Cap Outcome summary Large objects Older path Improved path What changed 10M 48 min 16 min 3x faster 20M 1h 02m 18 min 3.4x faster 30M 2h 41m 21 min 7.6x faster 50M Failed at higher scale 26-30 min Now completes 100M Failed 54 min Now completes 500M Failed 4h 01m Now completes Key takeaway: this is not just faster. At higher LOB counts, the improvement changes the outcome from upgrade fails to upgrade completes. Who benefits from this? You should care if your database stores large binary content using PostgreSQL large objects. Workload pattern Why it matters Document management PDFs, contracts, scans, and archived files Attachment-heavy apps Files stored inside PostgreSQL instead of external storage Legacy apps using lo APIs LOBs may have accumulated for years Image/archive systems Millions of binary objects can build up quietly Previous upgrade failures Failures during schema dump may map to this scenario Copy/paste: check your large-object count Run these checks in each database you plan to upgrade. 1. Count large objects in the current database -- Count PostgreSQL large objects in the current database SELECT current_database() AS database_name, count(*) AS large_object_count FROM pg_largeobject_metadata; 2. Check large-object storage footprint -- Estimate large-object data and metadata size SELECT pg_size_pretty(pg_total_relation_size('pg_largeobject'::regclass)) AS large_object_data_size, pg_size_pretty(pg_total_relation_size('pg_largeobject_metadata'::regclass)) AS large_object_metadata_size; 3. Understand ownership and ACL shape -- Inspect large-object metadata shape SELECT count(*) AS total_large_objects, count(lomacl) AS large_objects_with_custom_acl, count(DISTINCT lomowner) AS distinct_large_object_owners FROM pg_largeobject_metadata; 4. Find top large-object owners -- Top large-object owners SELECT lomowner::regrole AS owner, count(*) AS large_object_count FROM pg_largeobject_metadata GROUP BY lomowner ORDER BY large_object_count DESC LIMIT 10; What should I do before my next major version upgrade? If your situation is... Recommended action Target is PostgreSQL 15 or later Target PostgreSQL 15 or later to benefit from improved large-object metadata handling. Target is PostgreSQL 14 or earlier Prefer PostgreSQL 15+ where possible; very high LOB counts may still hit older-path limitations. Very large or unusual database Restore a copy and rehearse the upgrade before production. Suspected orphan LOBs Consider vacuumlo only after testing. It can delete valid LOBs if your app uses custom references. Any major version upgrade Keep healthy free space and leverage pre-upgrade validation checks to validate extension/schema compatibility first. Bottom line If large objects were making your PostgreSQL upgrade risky, this improvement makes the upgrade path safer and more predictable. For large-object-heavy databases, upgrades targeting PostgreSQL 15 and later now show faster runtime, lower memory/temp-space pressure, and successful validation up to 500M large objects. Learn more Major version upgrades in Azure Database for PostgreSQL flexible server How to perform a major version upgrade PostgreSQL vacuumlo documentationTop 10 Performance Optimization Techniques for Azure Database for PostgreSQL Flexible Server
Introduction Performance optimization is one of the most common challenges faced by organizations running business-critical workloads on Azure Database for PostgreSQL flexible server. As your workloads grow it’s common to encounter high CPU utilization, storage bottlenecks, autovacuum issues, excessive temporary file generation, and connection saturation. The good news is that Azure PostgreSQL flexible server provides several built-in capabilities to help optimize performance, improve scalability, and reduce operational overhead. This article explores ten practical techniques that can significantly improve database performance and reliability. 1. Choose the Right Compute SKU Performance starts with selecting the appropriate compute tier. Azure PostgreSQL flexible server offers: Pricing tier Target workloads Burstable Designed for workloads that don't require full CPU performance continuously. Best suited for proof-of-concept environments, and development builds. Not recommended for production workloads. General Purpose Provides a balance between CPU and memory with scalable I/O throughput, making it suitable for most production workloads. Examples include servers for hosting web applications, mobile apps, and enterprise applications. Memory Optimized Suitable for high-performance database workloads that require in-memory performance for larger buffer cache sets, and higher concurrency. Examples include servers for processing real-time data and high-performance transactional or analytical apps. Learn more about Compute Tiers here. 2.Enable and Use Query Store Query Store is one of the most powerful performance tools available. Query Store automatically captures the following and keeps them available for review: Query execution statistics Runtime metrics Wait event information Historical execution trends It organizes the data into time windows, so you can spot database usage patterns. Data for all users, databases, and queries is stored in a database named azure_sys in the Azure Database for PostgreSQL instance. It’s generally recommended to monitor query store from Azure tools, KQL, etc. Learn more about Query store here. You can also view some useful scenario for query store and some Best Practices for Query store 3.Leverage Built-In PgBouncer Connection Pooling PostgreSQL uses a process-per-connection model, which means every connection consumes memory and CPU resources. Azure PostgreSQL flexible server provides built-in PgBouncer support for eligible SKUs . PgBouncer allows multiple application sessions to reuse open backend connections and significantly reduces overhead. Benefits include: Lower memory consumption Faster connection handling Improved application scalability Reduced CPU overhead Learn more about PgBouncer here 4.Use Azure Troubleshooting Guides One underutilized feature is the built-in troubleshooting experience available directly in the Azure portal. Guides are available for: CPU troubleshooting Memory troubleshooting IOPS analysis Temporary files Autovacuum monitoring Autovacuum blockers These tools provide actionable recommendations and visualizations without requiring external monitoring solutions. Learn more about Troubleshooting Guides here. 5. Monitor and Tune Autovacuum Autovacuum is critical for maintaining PostgreSQL performance. Without proper vacuuming: Dead tuples accumulate Table bloat increases Statistics are not refreshed regularly Query performance degrades Transaction ID wraparound risks emerge Use Azure's built-in Autovacuum Monitoring TroubleshootingGuides to identify: Vacuum lag Blocked autovacuums Table bloat Inefficient cleanup operations Azure now also offers adaptive tuning capabilities to optimize maintenance behavior. Learn more about Autovacuum tuning here 6.Optimize Storage and IOPS Planning Many performance incidents originate from insufficient storage planning rather than inefficient SQL. In Azure PostgreSQL flexible server: Storage and baseline IOPS are closely related. Learn more here. Larger storage allocations provide higher baseline IOPS. Auto-grow prevents storage-related outages Note: Storage can only be scaled up and will always be double in size. SSDv2 auto-grow will allow customized growth settings in future release. For write-heavy workloads, monitoring storage utilization and IOPS is essential. Best practice: Enable Storage Auto-Grow Monitor Read/Write IOPS regularly Scale storage proactively 7.Investigate Temporary File Generation Large sorts and hash operations that exceed available memory spill to disk and generate temporary files. Symptoms include: Sudden Latency Spikes Increased IOPS Slower query execution Azure TroubleshootingGuides provide dedicated temporary-file analysis capabilities that help identify offending queries. Frequent temp file generation often indicates: Missing indexes Undersized work_mem Large sorting operations 8.Use Intelligent Tuning Azure PostgreSQL flexible server includes Intelligent Tuning capabilities. The service continuously observes workload behavior and automatically optimizes parameters related to write operations. Examples of tuning include: checkpoint_completion_target max_wal_size min_wal_size bgwriter settings This reduces administrative effort while helping maintain consistent performance. Learn more about Intelligent Tuning here. 9.Optimize Checkpoints and Write Workloads Checkpoint spikes frequently appear in escalations involving high IOPS and latency. Aggressive checkpoint activity can: Generate excessive disk writes Increase latency Consume IOPS capacity Monitoring checkpoint behavior and ensuring WAL parameters are properly configured can significantly improve write-intensive workloads. Azure intelligent tuning can assist in this area as well. 10.Metric Monitoring Optimization should always be data-driven. You should track the following: CPU utilization Memory pressure Active Connections Oldest Query IOPS consumption Combining Azure Metrics, Query Store, and PostgreSQL statistic views allows teams to distinguish between normal workload spikes and true performance degradation. PostgreSQL statistics views provide valuable workload insights. For example, pg_stat_activity can be used to identify long-running or blocking queries, pg_stat_user_tables helps track dead tuples, vacuum activity, and statistics refreshes, while pg_stat_statements (if enabled) help identify the most resource-intensive queries by execution time and frequency. Learn more about Metric here Conclusion Performance optimization in Azure Database for PostgreSQL flexible server is not just about changing a few parameters and hoping for better results. It requires a structured approach that combines workload understanding, proactive monitoring, proper sizing, query optimization, and platform-native capabilities. By leveraging Query Store, PgBouncer, Intelligent Tuning, Autovacuum Monitoring, Azure Metrics, and Troubleshooting Guides, you can significantly improve database efficiency while reducing operational effort. References Compute Options - Azure Database for PostgreSQL | Microsoft Learn Query Store in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn PgBouncer in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft Learn Autovacuum Tuning - Azure Database for PostgreSQL | Microsoft Learn Intelligent Tuning in Azure Database for PostgreSQL Flexible Server - Azure Database for PostgreSQL | Microsoft LearnTLS Certificate Pinning and Best Practices in Azure Database for PostgreSQL
TLS certificate pinning in Azure Database for PostgreSQL Transport Layer Security (TLS) encrypts data in transit between client applications and the server and authenticates the service endpoint in client-server authentication. Azure Database server certificates are issued by well-known trusted public Certificate Authorities (CAs), including Microsoft-issued certificates, and are validated by clients during the TLS handshake. Customers do not manage certificates on the server side. Certificate pinning is a client-side security technique where an application restricts trust to a specific certificate, for example by thumbprint, public key, or CA, rather than relying solely on the default OS or platform trust store. The trust store contains pre-installed root CAs and may also include additional certificates configured by the client. During standard TLS validation, the client will trust any server certificate that chains to one of those root CAs. Why detecting TLS certificate pinning is not possible by design Certificate pinning is entirely client-side logic. The server has no visibility into whether pinning is configured on the client. From the server’s perspective, the client either completes the TLS handshake or aborts it. The server never sees: Which certificate(s) the client trusts Whether the client is comparing root CA, intermediate CA, leaf certificate or SPKI hash Whether the trust decision was static or dynamic What the server can see is TLS handshake failure patterns, TLS protocol, and cipher negotiation. Why certificate pinning is risky While certificate pinning was historically used to reduce the risk of man-in-the-middle attacks, it introduces significant operational fragility in cloud environments, particularly during certificate rotations. Server certificates and certificate authorities (CAs) must be rotated periodically to maintain security and compliance. In Azure Database for PostgreSQL, when certificate pinning is used, clients bind trust to a specific certificate or CA. As a result, any change to the server certificate chain—including CA updates—can cause connection failures, even when the new certificates are fully valid and secure. One of the most common complications during certificate rotations is certificate pinning. Recommended TLS certificate trust model for Azure PostgreSQL Instead of pinning, adopt a CA‑based trust model that allows certificates to change safely. Trust root CAs, not individual certificates. Configure clients to use standard TLS validation against Azure-documented root CAs, rather than restricting trust to specific certificates or a narrowly scoped set of certificate authorities. Avoid configurations that effectively implement certificate pinning—such as trusting only a single certificate, public key, or limited CA set—unless explicitly required. Maintain a flexible and up-to-date trust store Clients rely on a trust store, key store, or equivalent certificate bundle to validate server certificates during TLS negotiation. Include the appropriate root and intermediate certificate authorities (CAs) required to validate the server certificate chain Ensure that trust stores are periodically reviewed and updated in line with provider guidance and announced certificate authority changes For the current TLS certificates visit the Azure Database for PostgreSQL documentation. Use certificate validation modes that rely on standard CA-based trust rather than pinning For PostgreSQL client configurations, prefer: sslmode=verify-ca Validates the server certificate chain against trusted CAs sslmode=verify-full Verifies CA and hostname match These modes ensure that clients validate the server certificate chain against trusted CAs, and in stricter modes, verify hostname identity. They do not imply certificate pinning by themselves. They rely on standard CA-based trust. Configurations only become rigid when trust is narrowly restricted, such as to a single certificate or limited CA set, often through custom or overly constrained trust stores. This effectively introduces certificate pinning. When properly configured, these modes authenticate the service endpoint and protect against spoofing, while remaining resilient to certificate rotations. Maintain a combined CA during certificate rotations Azure may rotate root or intermediate CAs over time. When Azure announces a CA rotation: Add newly required root CAs to the client trust store before the rotation begins. Retain existing trusted root CAs until the transition is fully complete. Avoid removing older root certificates prematurely. If specific rotation guidance includes updates related to intermediate CAs, follow the service-specific instructions provided for that rotation. This combined CA approach, using both the current and upcoming certificate authorities during the transition window, allows clients to continue validating the server certificate chain without interruption. As you review your current client configurations, ensure your applications rely on CA-based trust, avoid overly restrictive certificate configurations such as certificate pinning, and are prepared to handle routine certificate rotations without disruption. For a deeper dive, see the full article: TLS Certificate Pinning in PostgreSQL and MySQL: Risks, Rotations, and Best Practices.Monitoring and using pg_repack in Azure Database for PostgreSQL flexible server
In this post: we will walk through how to configure and use the pg_repack extension in Azure Database for PostgreSQL flexible server. We will also cover how to run pg_repack on a table, monitor the progress during execution, and validate the results after the repack operation is completed. Why Monitor pg_repack During Execution? While running pg_repack is straightforward, administrators often need visibility into what is happening behind the scenes, especially when working with large tables in production environments. Monitoring the operation provides several benefits: Verify that pg_repack is actively running and has not stalled. Identify the current phase of the operation, such as table copying, index rebuilding, or final table swap. Understand resource usage and the impact on the database. Before you start: Before performing this lab, ensure the following prerequisites are met: Azure Resources: An active Azure subscription An Azure Database for PostgreSQL flexible server instance Database Requirements: A table with a PRIMARY KEY or UNIQUE NOT NULL index (required by pg_repack) Linux Machine: I have used an Ubuntu Linux Virtual Machine SSH connectivity to the VM using PuTTY Configuring and using pg_repack in Azure Database for PostgreSQL Step 1: Allow list and create the pg_repack Extension Before using pg_repack, the extension must be allowlisted and created in the target database. Navigate to your Azure Database for PostgreSQL flexible server and add pg_repack to the allow list of extensions. Once the server configuration is updated, connect to the database and create the extension. Step 2: Create and connect to a Linux Virtual Machine Since pg_repack is a client-side utility, a Linux virtual machine was created to install and run the pg_repack client against Azure Database for PostgreSQL flexible server. Step 3: Connect to the Linux Virtual Machine Before running pg_repack, I connected to the Linux virtual machine that would be used to install and execute the pg_repack client. In the Azure portal, navigate to the Linux Virtual Machine. Open PuTTY and enter the VM's IP Address. Select SSH (Port 22) as the connection type and click Open. Enter the VM username and password when prompted. After successful authentication, a terminal session is established The following output confirms that the connection was successful and that the Ubuntu operating system is ready for further configuration. Step 4: Update Package Repositories on the Linux Virtual Machine Before installing the pg_repack client, update the package repositories on the Ubuntu virtual machine to ensure the latest package information is available. Please run the following command and provide the password for the linux virtual machine when prompted. sudo apt update Step 5: Download the pg_repack Important: If you face any version mismatch issue or errors then you can use the below command to resolve After preparing the test environment and generating table bloat, the next step was to download the pg_repack source code to the Linux virtual machine. The git clone command downloads the pg_repack source code from the official GitHub repository to the Linux virtual machine. This source code is later used to build and install the pg_repack client utility required to perform table reorganization operation. After downloading the repository, the cd pg_repack command changes the current directory to the downloaded project folder. git clone https://github.com/reorg/pg_repack.git cd pg_repack Step 6: Install PostgreSQL Client Packages After updating the package repositories, the next step was to install the PostgreSQL client packages on the Linux virtual machine. The PostgreSQL package installs the PostgreSQL client tools, including psql, which is used to connect to Azure Database for PostgreSQL flexible server. sudo apt install postgresql postgresql-contrib When the command is executed, Ubuntu displays a summary of the packages that will be installed along with their dependencies. To proceed with the installation, type Y and press Enter. Step 7: Connect to Azure Database for PostgreSQL flexible server After installing the PostgreSQL client packages on the Linux virtual machine, the next step is to establish a connection to the Azure Database for PostgreSQL flexible server using the psql client. Please update the following command with your server details and execute it and enter your PostgreSQL server user password to establish the connection. Note: Make sure your Linux machine network is allowed to connect on your Azure Database for PostgreSQL flexible server. psql -h <Hostname> -p 5432 -U <username> postgres Step 8: Create a Test Database After successfully connecting to Azure Database for PostgreSQL flexible server, a dedicated database was created to perform the pg_repack lab activities as shown below: Step 9: Connect to the Newly Created Database After creating the repack_lab database, connect to it before proceeding with the pg_repack activities. \c repack_lab Step 10: Create a Sample Table for pg_repack Testing After connecting to the repack_lab database, I created a sample table that would be used throughout the lab to test the functionality of pg_repack. CREATE TABLE test_table ( id SERIAL PRIMARY KEY, name TEXT, created_at TIMESTAMP DEFAULT NOW() ); Step 11: Insert Sample Data into the Test Table After creating the test_table, the next step was to populate it with sample data. This helps simulate a realistic workload and provides enough records to demonstrate how pg_repack works. The following command was used to insert 100,000 rows into the table: INSERT INTO test_table(name) SELECT md5(random()::text) FROM generate_series(1,100000); Step 12: Create an Index on the Test Table After populating the test_table with 100,000 records, an index was created as shown below: CREATE INDEX idx_test_name ON test_table(name); Step 13: Check the Initial Table Size Before generating table, bloat and running pg_repack, it is useful to capture the current size of the table. This serves as a baseline for comparing storage consumption before and after the repack operation. SELECT pg_size_pretty(pg_total_relation_size('test_table')); Step 14: Generate Table Bloat Using UPDATE Operations To demonstrate how pg_repack reorganizes a table and reclaims unused space, the next step was to generate table bloat by repeatedly updating all rows in the table. The following command was executed multiple times: UPDATE test_table SET name = md5(random()::text); Step 15: Disable Autovacuum on the Test Table To clearly observe table bloat and demonstrate the effectiveness of pg_repack, autovacuum was temporarily disabled on the test table. This prevents Azure Database for PostgreSQL flexible server from automatically cleaning up dead tuples generated by the previous UPDATE and DELETE operations. ALTER TABLE test_table SET (autovacuum_enabled = false); Step 16: Analyze Live and Dead Tuples Before Running pg_repack After generating table bloat through multiple UPDATE and DELETE operations and disabling autovacuum, the next step was to measure the number of live and dead tuples in the table. Step 17: Execute pg_repack to Reorganize the Table The pg_repack utility was executed against the test table to reclaim unused space and reorganize the table structure. The pg_repack utility reorganizes tables and indexes online while minimizing locking and application downtime. Unlike VACUUM FULL, pg_repack performs the reorganization in the background and requires only a brief lock during the final table swap operation. Command executed: pg_repack \ --host=myflexibleserver.postgres.database.azure.com \ --port=5432 \ --username=dbadmin \ --dbname=repack_lab \ --table=test_table \ --jobs=2 \ --no-kill-backend \ --no-superuser-check Monitoring pg_repack Execution Once the pg_repack operation was initiated, the next step was to monitor its execution and identify the activities being performed by the utility in the background: To track active pg_repack sessions, the following query was executed: SELECT pid, usename, application_name, state, wait_event_type, wait_event, now() - query_start AS running_for, query FROM pg_stat_activity WHERE application_name ILIKE '%repack%' OR query ILIKE '%repack%' ORDER BY query_start; After starting the pg_repack operation, I monitored the active sessions by querying the pg_stat_activity system view. This helped me understand the current stage of the operation and verify that the process was executing successfully. The query returned multiple sessions created by pg_repack, indicating that the utility was actively processing the table. Session 1 - Lock Acquisition LOCK TABLE public.test_table IN SHARE UPDATE EXCLUSIVE MODE This session acquired a SHARE UPDATE EXCLUSIVE lock on the target table. This lock prevents conflicting schema changes while still allowing normal read and write operations during most of the repack process. Session 2 - Temporary Repack Table Creation SELECT 'repack.table_24861'::regclass::oid At this stage, pg_repack was working with an internal temporary table created to hold the reorganized data. This table acts as a replacement for the original table during the repack operation. Session 3 - Creating Primary Key Index CREATE UNIQUE INDEX index_24869 ON repack.table_24861 USING btree(id) This session shows pg_repack rebuilding the primary key index on the new table structure. Session 4 - Creating Secondary Index CREATE INDEX index_24873 ON repack.table_24861 USING btree(name) This indicates that additional indexes are being recreated on the temporary table to match the original table definition. Based on the output, the operation had successfully moved past the initialization phase and was actively rebuilding indexes on the temporary table. This is one of the final stages before pg_repack performs the table swap and completes the reorganization process. Conclusion In summary, monitoring pg_repack execution is essential for ensuring a smooth and efficient table reorganization process. Proper visibility into progress and resource consumption helps administrator complete maintenance tasks confidently while maintaining optimal database performance and availability. References Optimize by using pg_repack - Azure Database for PostgreSQL | Microsoft Learn PostgreSQL: Documentation: 18: 27.4. Progress Reporting pg_repack 1.5.3 -- Reorganize tables in PostgreSQL databases with minimal locksMicrosoft Defender CSPM Assessments for Azure Database for PostgreSQL Flexible Server - GA
As security and regulatory requirements evolve, proactively monitoring and assessing database security posture becomes just as important as detecting active threats. Maintaining a secure and compliant database environment requires continuous visibility into security gaps and configuration drift from established security baselines. We're excited to announce the general availability of Microsoft Defender for Cloud Security Posture Management (Defender CSPM) assessments for Azure Database for PostgreSQL Flexible Server. These built-in assessments continuously evaluate PostgreSQL server configurations against PostgreSQL-specific security best practices, helping organizations identify vulnerabilities and misconfigurations and prioritize them based on the risk they pose. The assessments provide actionable recommendations to help customers strengthen their security baseline, prioritize remediation efforts, and support compliance requirements. Findings are surfaced directly in Microsoft Defender for Cloud, enabling security and operations teams to proactively improve the security posture of their PostgreSQL workloads. An initial set of PostgreSQL-focused assessments is included at launch, covering areas such as network security, auditing controls, and operational resilience. Additional assessment coverage is planned for future releases. If you already have Microsoft Defender CSPM enabled on subscriptions that contain Azure Database for PostgreSQL flexible servers, no additional setup is required. Assessments are automatically available, provided a risk score and integrated into the existing Defender experience, making it easier to continuously monitor security posture and maintain alignment with organizational and industry security standards. You can view assessment recommendations in the Azure portal on the resource blade of your Azure Database for PostgreSQL flexible server or the main Defender for Cloud experience, and the Microsoft Defender portal. Learn more Microsoft Defender CSPM assessments for Azure Database for PostgreSQL Flexible Server. What is Microsoft Defender Cloud Security Posture Management? Enable Defender CSPM Microsoft Defender Azure Data Security Recommendations
