Mengonfigurasi database PostgreSQL yang dikelola sendiri untuk CDC
Tetap teratur dengan koleksi
Simpan dan kategorikan konten berdasarkan preferensi Anda.
Halaman ini menjelaskan cara mengonfigurasi change data capture (CDC) untuk
mengalirkan data dari database PostgreSQL yang dikelola sendiri ke tujuan yang didukung,
seperti BigQuery atau Cloud Storage.
Mengaktifkan replikasi logis di database
Tetapkan parameter wal_level di database Anda dengan menambahkan wal_level=logical ke file postgresql.conf.
Mulai ulang server.
Buat publikasi dan slot replikasi
Login ke konsol PostgreSQL sebagai superuser.
Buat publikasi. Sebaiknya buat publikasi hanya untuk tabel yang ingin direplikasi. Hal ini memungkinkan Datastream membaca saja data yang relevan, dan mengurangi beban pada database dan Datastream:
CREATE PUBLICATION PUBLICATION_NAME
FOR TABLE SCHEMA1.TABLE1, SCHEMA2.TABLE2;
Ganti kode berikut:
PUBLICATION_NAME: Nama publikasi Anda. Anda
harus memberikan nama ini saat membuat aliran data di wizard pembuatan aliran
Datastream.
SCHEMA: Nama skema yang berisi tabel.
TABLE: Nama tabel yang ingin Anda replikasi.
Anda dapat membuat publikasi untuk semua tabel dalam skema. Pendekatan ini memungkinkan
Anda mereplikasi perubahan untuk tabel dalam daftar skema yang ditentukan, termasuk
tabel yang Anda buat di masa mendatang:
CREATE PUBLICATION PUBLICATION_NAME
FOR TABLES IN SCHEMA SCHEMA1, SCHEMA2;
Anda juga dapat membuat publikasi untuk semua tabel di database Anda. Perhatikan bahwa pendekatan ini meningkatkan beban pada database sumber dan Datastream:
CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;
Buat slot replikasi dengan memasukkan perintah SQL berikut:
REPLICATION_SLOT_NAME: Nama slot replikasi Anda. Anda
harus memberikan nama ini saat membuat stream di wizard pembuatan stream
Datastream.
Membuat pengguna Datastream
Untuk membuat pengguna Datastream, masukkan perintah SQL berikut:
CREATE USER USER_NAME WITH ENCRYPTED PASSWORD 'USER_PASSWORD';
Ganti kode berikut:
USER_NAME: Nama pengguna Datastream yang ingin Anda buat.
USER_PASSWORD: Sandi login untuk pengguna Datastream
yang ingin Anda buat.
Berikan hak istimewa berikut kepada pengguna yang Anda buat:
ALTER ROLE USER_NAME WITH REPLICATION;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME
GRANT SELECT ON TABLES TO USER_NAME;
Ganti kode berikut:
USER_NAME: Pengguna yang ingin Anda beri hak istimewa.
SCHEMA_NAME: Nama skema yang ingin Anda beri
hak istimewa.
Langkah berikutnya
Pelajari lebih lanjut cara kerja Datastream dengan sumber PostgreSQL.
[[["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-10 UTC."],[[["\u003cp\u003eThis guide explains the process of configuring a self-managed PostgreSQL database, which also applies to AlloyDB Omni, for use with Datastream.\u003c/p\u003e\n"],["\u003cp\u003eEnabling logical replication requires setting the \u003ccode\u003ewal_level\u003c/code\u003e parameter to \u003ccode\u003elogical\u003c/code\u003e in the \u003ccode\u003epostgresql.conf\u003c/code\u003e file and restarting the server.\u003c/p\u003e\n"],["\u003cp\u003eCreating a publication allows you to specify which tables or schemas will be replicated, and you can also create a publication for all tables in the database.\u003c/p\u003e\n"],["\u003cp\u003eA replication slot is created using the \u003ccode\u003ePG_CREATE_LOGICAL_REPLICATION_SLOT\u003c/code\u003e function, and its name must be unique for each Datastream stream.\u003c/p\u003e\n"],["\u003cp\u003eA Datastream user must be created with a secure password, and it should be granted replication privileges and select permissions on the desired tables and schemas.\u003c/p\u003e\n"]]],[],null,["# Configure a self-managed PostgreSQL database\n\nThe following sections cover how to configure a self-managed PostgreSQL database.\n| **Note:** These instructions also work for a source AlloyDB Omni database.\n\nEnable logical replication on the database\n------------------------------------------\n\n1. Set the `wal_level` parameter in your database by adding `wal_level=logical` to the `postgresql.conf` file.\n\n2. Restart the server.\n\nCreate a publication and a replication slot\n-------------------------------------------\n\n1. Sign in to a PostgreSQL console as a superuser.\n\n2. Create a publication. We recommend that you create a publication only for the\n tables that you want to replicate. This allows Datastream to read-only\n the relevant data, and lowers the load on the database and Datastream:\n\n ```\n CREATE PUBLICATION PUBLICATION_NAME\n FOR TABLE SCHEMA1.TABLE1, SCHEMA2.TABLE2;\n ```\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003ePUBLICATION_NAME\u003c/var\u003e: The name of your publication. You'll need to provide this name when you create a stream in the Datastream stream creation wizard.\n - \u003cvar translate=\"no\"\u003eSCHEMA\u003c/var\u003e: The name of the schema that contains the table.\n - \u003cvar translate=\"no\"\u003eTABLE\u003c/var\u003e: The name of the table that you want to replicate.\n\n You can create a publication for all tables in a schema. This approach lets\n you replicate changes for tables in the specified list of schemas, including\n tables that you create in the future: \n\n ```\n CREATE PUBLICATION PUBLICATION_NAME\n FOR TABLES IN SCHEMA SCHEMA1, SCHEMA2;\n ```\n\n You can also create a publication for all tables in your database. Note that\n this approach increases the load on both the source database and Datastream: \n\n ```\n CREATE PUBLICATION PUBLICATION_NAME FOR ALL TABLES;\n ```\n3. Create a replication slot by entering the following SQL command:\n\n ```\n SELECT PG_CREATE_LOGICAL_REPLICATION_SLOT('REPLICATION_SLOT_NAME', 'pgoutput');\n ```\n\n \u003cbr /\u003e\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003eREPLICATION_SLOT_NAME\u003c/var\u003e: The name of your replication slot. You will need to provide this name when you create a stream in the Datastream stream creation wizard.\n\n | The replication slot name must be unique for each stream that's replicating from this database.\n\n### Create a Datastream user\n\n1. To create a Datastream user, enter the following SQL commands:\n\n ```\n CREATE USER USER_NAME WITH ENCRYPTED PASSWORD 'USER_PASSWORD';\n ```\n\n \u003cbr /\u003e\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003eUSER_NAME\u003c/var\u003e: The name of the Datastream user that you want to create.\n - \u003cvar translate=\"no\"\u003eUSER_PASSWORD\u003c/var\u003e: The login password for the Datastream user that you want to create.\n2. Grant the following privileges to the user you created:\n\n ```\n ALTER ROLE USER_NAME WITH REPLICATION;\n GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;\n GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;\n ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA_NAME\n GRANT SELECT ON TABLES TO USER_NAME;\n ```\n\n Replace the following:\n - \u003cvar translate=\"no\"\u003eUSER_NAME\u003c/var\u003e: The user to whom you want to grant the privileges.\n - \u003cvar translate=\"no\"\u003eSCHEMA_NAME\u003c/var\u003e: The name of the schema to which you want to grant the privileges.\n\nWhat's next\n-----------\n\n- Learn more about how Datastream works with [PostgreSQL sources](/datastream/docs/sources-postgresql)."]]
