Ekstensi pglogical adalah alat replikasi logis
yang tangguh dan fleksibel yang dirancang untuk PostgreSQL, dan juga mendukung
ketersediaan tinggi (HA) dan pemulihan dari bencana (DR).
Replikasi biner tradisional, yang umumnya dikenal sebagai replikasi fisik, mereplikasi
perubahan di tingkat sistem file dan blok, sehingga menghasilkan mirror fisik di
sistem target. Meskipun replikasi fisik bersifat andal dan melindungi seluruh cluster database, replikasi ini hanya bersifat satu arah dan memerlukan akses ke file data database pokok dan file log tulis-sebelum (WAL).
Sementara itu, ekstensi pglogical mengekstrak perubahan SQL dari database
penyedia dan mereplikasinya, lalu memutarnya kembali terhadap satu atau beberapa database
pelanggan. Replikasi ini dikenal sebagai replikasi logis.
Dengan menggunakan ekstensi pglogical, Anda dapat melakukan hal berikut:
Mereplikasi data di antara beberapa database AlloyDB Omni.
Mereplikasi data antara AlloyDB Omni dan Google Cloud AlloyDB.
Mereplikasi data antara AlloyDB Omni dan distribusi PostgreSQL lainnya yang mencakup banyak layanan cloud pihak ketiga.
Manfaat
Replikasi logis dengan ekstensi pglogical menawarkan manfaat berikut:
Replikasi selektif: memberikan fleksibilitas untuk menetapkan filter dan aturan
guna menentukan data yang ingin direplikasi dan tujuannya. Anda dapat memilih tabel yang disertakan dan cara tabel baru ditangani, baik disertakan maupun tidak.
Anda juga dapat menambahkan filter kolom dan baris. apply delay
opsional dapat ditambahkan untuk situasi saat Anda ingin pelanggan merepresentasikan beberapa
titik waktu di belakang dari penyedia.
Replikasi dua arah dan multi-primer: semua database anggota terbuka
dalam status baca/tulis dan dapat digunakan sepenuhnya. Setiap database endpoint bertindak sebagai penyedia dan pelanggan, sehingga memungkinkan pembuatan skenario replikasi lanjutan, dan memungkinkan kemungkinan pembaruan data yang dilakukan di endpoint yang berbeda.
Dukungan penyedia cloud: Penyedia cloud seperti Google menyadari nilai ekstensi pglogical dan mengintegrasikannya ke dalam layanan Cloud mereka, seperti Google Cloud SQL untuk PostgreSQL dan AlloyDB. Penyedia cloud
lain juga menyertakan ekstensi pglogical sebagai opsi, yang memungkinkan konfigurasi multi-cloud
atau hybrid cloud.
Replikasi lintas versi: karena pglogical mereplikasi pernyataan SQL yang sebenarnya, pglogical memungkinkan replikasi antara versi utama PostgreSQL. Terutama jika database sumber penyedia memiliki versi yang lebih rendah daripada database target pelanggan, replikasi lintas versi dapat diterapkan dengan keandalan.
Ekstensi pglogical menawarkan dukungan untuk banyak versi PostgreSQL sebelumnya, seperti versi 9.4 dan yang lebih tinggi. Hal ini menjadikannya pilihan yang optimal untuk skenario
saat Anda berurusan dengan sistem lama dan ingin mereplikasi data ke versi PostgreSQL yang lebih modern seperti yang digunakan di AlloyDB Omni dan AlloyDB. Google Cloud
Singkatnya, ekstensi pglogical menyediakan solusi replikasi logis yang kaya fitur, dengan kompatibilitas untuk versi PostgreSQL yang lebih lama dan layanan yang dikelola Cloud yang mencakup Google Cloud SQL untuk PostgreSQL dan AlloyDB.
Batasan replikasi logis
Semua teknologi replikasi logis, termasuk yang digunakan dengan platform database relasional lainnya, memiliki beberapa batasan, dan kesalahan pengelolaan dapat menghentikan proses replikasi.
Pertimbangkan poin-poin berikut untuk penerapan yang andal:
Pertimbangan tentang cara menangani objek cakupan database dan cakupan cluster yang berada di luar cakupan replikasi. Ekstensi pglogical berfungsi di tingkat database dan hanya terhadap sekumpulan tabel dan urutan tertentu.
Jenis objek lain, seperti fungsi dan prosedur, harus direplikasi menggunakan metode lain.
Sebaiknya semua tabel replikasi harus memiliki kunci utama.
Anda dapat menggunakan fitur REPLICA IDENTITY tabel untuk memberi tahu ekstensi pglogical tentang kolom yang mengidentifikasi baris secara unik. Hal ini
harus dihindari jika memungkinkan. Tabel yang tidak memiliki kunci utama, bersifat statis, dan tidak pernah UPDATED atau DELETED, serta hanya mendukung INSERTS.
Jenis tabel ini tidak memerlukan kunci utama.
Pengelolaan pemicu dan urutan dalam database pelanggan. Secara default, pemicu
ditentukan sebagai pemicu ORIGIN atau LOCAL, dan tidak diaktifkan di database pelanggan
saat baris direplikasi. Semua pemicu harus diperiksa untuk memastikan
bahwa opsi REPLICA ditetapkan untuk pemicu apa pun sehingga tidak diaktifkan di
akhir pelanggan kecuali jika diperlukan.
Menangani penyelesaian konflik secara manual atau otomatis melalui who wins
aturan.
Mereplikasi perintah Data Definition Language (DDL) dengan menerapkan secara manual di semua endpoint, atau mereplikasi DDL secara otomatis ke database pelanggan menggunakan fungsi API pglogical yang sesuai di database penyedia.
Memastikan bahwa tabel dan urutan yang baru dibuat ditambahkan secara manual atau otomatis ke set replikasi di database utama.
Memastikan bahwa jaringan TCP yang andal, berperforma tinggi, tepercaya, dan aman ada di antara semua endpoint dalam topologi replikasi.
Pembatasan dan batasan tambahan ekstensi pglogical mencakup hal-hal berikut:
Izin superuser diperlukan untuk pglogical versi 2.4.3.
Meskipun sebagian besar tabel dan urutan dapat direplikasi, jenis objek lain tidak direplikasi oleh ekstensi pglogical, dan tabel TEMPORARY serta UNLOGGED tidak direplikasi.
Untuk mereplikasi DDL, fungsi API pglogical harus digunakan. Perintah DDL
asli tidak direplikasi, kecuali perintah TRUNCATE.
Beroperasi di tingkat objek per tabel dan per urutan, serta di-deploy
per database. Artinya, beberapa objek, termasuk objek cakupan cluster
seperti users dan roles, dikecualikan dari replikasi dan harus
dikelola secara terpisah.
[[["Mudah dipahami","easyToUnderstand","thumb-up"],["Memecahkan masalah saya","solvedMyProblem","thumb-up"],["Lainnya","otherUp","thumb-up"]],[["Sulit dipahami","hardToUnderstand","thumb-down"],["Informasi atau kode contoh salah","incorrectInformationOrSampleCode","thumb-down"],["Informasi/contoh yang saya butuhkan tidak ada","missingTheInformationSamplesINeed","thumb-down"],["Masalah terjemahan","translationIssue","thumb-down"],["Lainnya","otherDown","thumb-down"]],["Terakhir diperbarui pada 2025-09-05 UTC."],[[["\u003cp\u003eThe \u003ccode\u003epglogical\u003c/code\u003e extension is a logical replication tool for PostgreSQL that supports high availability, disaster recovery, and allows for replication between multiple AlloyDB Omni databases, AlloyDB Omni and Google Cloud AlloyDB, and AlloyDB Omni and other PostgreSQL distributions.\u003c/p\u003e\n"],["\u003cp\u003e\u003ccode\u003epglogical\u003c/code\u003e offers benefits like selective replication, bi-directional and multi-primary replication, cloud provider support, and cross-version replication between different major versions of PostgreSQL.\u003c/p\u003e\n"],["\u003cp\u003eLogical replication with \u003ccode\u003epglogical\u003c/code\u003e has limitations, including the need for manual handling of database and cluster-scoped objects, consideration of primary keys or \u003ccode\u003eREPLICA IDENTITY\u003c/code\u003e for tables, and managing triggers and sequences in subscriber databases.\u003c/p\u003e\n"],["\u003cp\u003eThe \u003ccode\u003epglogical\u003c/code\u003e extension operates on a per-table and per-sequence basis within a database, meaning that cluster-scoped objects like users and roles are not replicated and require separate management.\u003c/p\u003e\n"],["\u003cp\u003eSuperuser permissions are required for pglogical version 2.4.3.\u003c/p\u003e\n"]]],[],null,["# About the pglogical extension\n\nSelect a documentation version: Current (16.8.0)keyboard_arrow_down\n\n- [Current (16.8.0)](/alloydb/omni/current/docs/about-pglogical)\n- [16.8.0](/alloydb/omni/16.8.0/docs/about-pglogical)\n- [16.3.0](/alloydb/omni/16.3.0/docs/about-pglogical)\n- [15.12.0](/alloydb/omni/15.12.0/docs/about-pglogical)\n- [15.7.1](/alloydb/omni/15.7.1/docs/about-pglogical)\n- [15.7.0](/alloydb/omni/15.7.0/docs/about-pglogical)\n- [15.5.5](/alloydb/omni/15.5.5/docs/about-pglogical)\n- [15.5.4](/alloydb/omni/15.5.4/docs/about-pglogical)\n- [15.5.2](/alloydb/omni/15.5.2/docs/about-pglogical)\n\n\u003cbr /\u003e\n\nThis page provides an overview of the `pglogical` extension, its benefits, and limitations.\n\n\u003cbr /\u003e\n\n\n| The information on this page applies only to AlloyDB Omni for containers. It does not apply to AlloyDB Omni for Kubernetes.\n\n\u003cbr /\u003e\n\nOverview\n--------\n\nThe [`pglogical` extension](https://github.com/2ndQuadrant/pglogical) is a robust and flexible\nlogical replication tool designed for PostgreSQL, and it also supports\nhigh availability (HA) and disaster recovery (DR).\n\nTraditional binary replication, commonly known as *physical replication*, replicates\nchanges at the file system and block level, resulting in a physical mirror in the\ntarget system. Even though the physical replication is robust and protects the\nentire database cluster, it is unidirectional only and requires access to the\nunderlying database data file and write-ahead log (WAL) files.\n\nWhereas, the `pglogical` extension extracts SQL changes from a provider\ndatabase and replicates them, and then replays them against one or more subscriber\ndatabases. This replication is known as *logical replication*.\n| **Note:** The `pglogical` extension uses the terms `provider` and `subscriber` to describe the endpoints that are used in a replication topology. These terms are synonyms for terms such as source/target, publisher/subscriber, and primary/standby.\n\nBy using the `pglogical` extension, you can do the following:\n\n- Replicate data between multiple AlloyDB Omni databases.\n- Replicate data between AlloyDB Omni and Google Cloud AlloyDB.\n- Replicate data between AlloyDB Omni and other PostgreSQL distributions that include many in third-party cloud services.\n\nBenefits\n--------\n\nLogical replication with the `pglogical` extension offers the following benefits:\n\n- **Selective replication:** provides the flexibility to set filters and rules\n to determine what data you want to replicate and where to. You can choose which\n tables are included and how new tables are handled whether they're included or not.\n You can also add column and row filters. An optional `apply delay`\n can be added for situations where you want the subscriber to represent some\n trailing point in time from the provider.\n\n- **Bi-directional and multi-primary replication:** all member databases are open\n in a read/write state and are fully usable. Each endpoint database acts as both\n provider and subscriber, allowing the creation of advanced replication scenarios,\n and enabling the possibility of data updates that are made at different endpoints.\n\n- **Cloud provider support:** Cloud providers such as Google recognize the value\n of the `pglogical` extension and integrate it into their Cloud services,\n such as Google Cloud SQL for PostgreSQL and AlloyDB. Other cloud\n providers also include the `pglogical` extension as an option, allowing multi-cloud\n or hybrid-cloud configurations.\n\n- **Cross-version replication:** as pglogical replicates the actual SQL statements,\n it allows replication between major versions of PostgreSQL. Especially when the\n provider source database is a lower version than the subscriber target database,\n cross-version replication can be implemented with reliability.\n\n The `pglogical` extension offers support for many earlier versions of PostgreSQL\n such as version 9.4 and higher. This makes it an optimal choice for scenarios\n where you are dealing with legacy systems and want to replicate data into more\n modern versions of PostgreSQL such as those used in AlloyDB Omni\n and Google Cloud AlloyDB.\n\nIn summary, the `pglogical` extension provides a feature-rich logical replication\nsolution, with compatibility for older versions of PostgreSQL and Cloud-managed\nservices that include Google Cloud SQL for PostgreSQL and AlloyDB.\n\nLimitations of logical replication\n----------------------------------\n\nAll logical replication technologies, including those used with other\nrelational database platforms, have some limitations, and any mismanagement can\nbreak the replication process.\n\nConsider the following points for a reliable implementation:\n\n- Consideration on how to handle database-scoped and cluster-scoped objects that are outside of the replication scope. The `pglogical` extension works at the database level and against a specified set of tables and sequences only. Other object types, such as functions and procedures, must be replicated using some other method.\n- It is recommended that all replication tables must have a primary key. It is possible to utilize the table `REPLICA IDENTITY` feature to inform the `pglogical` extension about which columns uniquely identify the rows. This must be avoided where possible. Tables that do not have primary keys, are static in nature, and are never `UPDATED` or `DELETED`, and supports only `INSERTS`. These types of tables do not need primary keys.\n- Management of triggers and sequences in subscriber databases. By default, triggers are defined as `ORIGIN` or `LOCAL` triggers, and do not fire on the subscriber database when the rows are replicated. All triggers should be checked to ensure that the `REPLICA` option is set for any trigger so that it does not fire on the subscriber end unless it is required.\n- Dealing with conflict resolution either manually or automatically through `who wins` rules.\n- Replication of Data Definition Language (DDL) commands by either manually implementing on all endpoints, or automatically replicating DDL to subscriber databases using the appropriate `pglogical` API function on the provider database.\n- Ensuring that newly created tables and sequences are manually or automatically added to replication sets on primary databases.\n- Ensuring that a robust, performant, reliable, and secured TCP network exists between all endpoints in the replication topology.\n\nAdditional restrictions and limitations of the `pglogical` extension include the\nfollowing:\n\n- Superuser permissions are required for `pglogical` version 2.4.3.\n- While most tables and sequences can be replicated, other object types are not replicated by the `pglogical` extension, and `TEMPORARY` and `UNLOGGED` tables are not replicated.\n- To replicate DDL, the pglogical API function must be used. Native DDL commands are not replicated, except for the `TRUNCATE` command.\n- Operates on an object level per table and per sequence, and is deployed per database. This means that some objects, including cluster-scoped objects such as `users` and `roles`, are excluded from the replication and must be managed separately.\n\nWhat's next\n-----------\n\n- [Replicate data between AlloyDB for PostgreSQL and AlloyDB Omni](/alloydb/omni/current/docs/replicate-data-alloydb-omni).\n- [Replicate data between AlloyDB Omni and other databases](/alloydb/omni/current/docs/replicate-data-omni-other-db)."]]
