A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://cloud.google.com/database-migration/docs/postgresql-to-alloydb/configure-source-database below:

Configure your source | Database Migration Service

Skip to main content Configure your source

Stay organized with collections Save and categorize content based on your preferences.


Overview

Database Migration Service supports continuous migrations from source databases to AlloyDB destination databases.

Supported source databases for PostgreSQL include:

Configuring your source requires configuring both the source instance and underlying source databases.

Configure your source instance

To configure your source instance, follow the steps below:

  1. Your source instance must include the postgres database. If you don't have this database, then create it.
  2. Install the pglogical package on the source instance and make sure that it's included in the shared_preload_libraries variable.
    1. See Install the pglogical package on the source instance for your environment.
Configure your source databases

Database Migration Service migrates all databases under your source instance other than the following databases:

Do the following on each database in your source instance that isn't mentioned above:

  1. For PostgreSQL version 9.4 sources only, install the following pglogical extensions on each database in your source instance:

  2. For all other versions, install only the pglogical extension on each database in your source instance: CREATE EXTENSION IF NOT EXISTS pglogical.

  3. For tables that don't have primary keys, Database Migration Service supports migration of the initial snapshot and INSERT statements during the CDC phase. You should migrate UPDATE and DELETE statements manually.

    See Debugging and other tools to learn how to generate a query to list tables in a PostgreSQL database without primary keys. For each database table that you find that doesn't have a primary key and the table isn't part of the databases that aren't migrated, you should alter the table so that it has a primary key or follow this process. For tables that don't have primary keys, only the initial snapshot and INSERT statements are migrated.

  4. The USER you're using to connect to the source instance (which will be configured as the user in the Connection Profiles page) must have certain privileges on each of the migrated databases, as well as the default postgres database. You can create a new user or reuse an existing one. To set these privileges, connect to the instance and run the following commands:

    1. GRANT USAGE on SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
    2. GRANT USAGE on SCHEMA pglogical to PUBLIC; on each database to migrate.
    3. GRANT SELECT on ALL TABLES in SCHEMA pglogical to USER on all databases to get replication information from source databases.
    4. GRANT SELECT on ALL TABLES in SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
    5. GRANT SELECT on ALL SEQUENCES in SCHEMA SCHEMA to USER on all schemas (aside from the information schema and schemas starting with "pg_") on each database to migrate.
    6. If your source is Amazon RDS, then run the following command:
      1. GRANT rds_replication to USER
    7. If your source isn't Amazon RDS, then run the following command:
      1. ALTER USER USER with REPLICATION role
Install the pglogical package on the source instance

This section describes how to configure the pglogical package, including configuration for the max_replication_slots, max_wal_senders, and max_worker_processes parameters. You can also get the correct values for these parameters by running a migration job test when you create the migration job. During this test, Database Migration Service can verify your settings and suggest the correct values.

On-premise or self-managed PostgreSQL
  1. Install the pglogical package on the server.
  2. Connect to the instance and set the following parameters, as needed:

    The parameters that you're setting in this step apply to a PostgreSQL database server that's running. You can also make these changes persistent by adding the following lines of code to the postgresql.conf file:

  3. To apply the configuration changes, restart the source instance.
Amazon RDS PostgreSQL
  1. Install the pglogical extension on your source database. For more information, see Using PostgreSQL extensions with Amazon RDS for PostgreSQL in the Amazon RDS documentation.

  2. Configure the source instance using parameter groups.

  3. Attach the parameter group to the instance. If you're creating a new instance, then you can find this option under Additional Configuration. Otherwise, modify the instance to attach the parameter group.

  4. To apply the configuration changes, restart the source instance.

Note: The pglogical extension can log credentials in plain text on the source instance. This behavior is caused by the extension itself, and is unrelated to either Database Migration Service or Cloud SQL. Cloud SQL for PostgreSQL

Enable logical replication and decoding for the source database by configuring the following flags.

All settings in this section are configured using flags in Cloud SQL. To enable flags in Cloud SQL, see Configure database flags.
  1. Set the cloudsql.logical_decoding and cloudsql.enable_pglogical flags to on.

  2. Set the max_replication_slots flag. This flag defines the maximum number of replication slots the source instance can support. It must be set to at least the number of subscriptions expected to connect, plus some reserves for table synchronization.

    Database Migration Service requires one slot for each database that's migrated (which is all of the databases under the source instance).

    For example, if there are 5 databases on the source instance and if there will be 2 migration jobs created for the source, then the number of replication slots must be at least 5 * 2 = 10, plus the number of replication slots already used by you. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of replication slots and verify your configuration by running the migration job test when you create the migration job.

    The default value for this flag is 10.

  3. Set the max_wal_senders flag to at least the same as max_replication_slots, plus the number of senders already used on your instance.

    For example, if the max_replication_slots flag is set to 10, and you're already using 2 senders, then the number of WAL sender processes running at the same time would be 10 + 2 = 12. If you plan to use adjusted data dump parallelism settings, make sure to increase the number of senders and verify your configuration by running the migration job test when you create the migration job.

    The default value for this flag is 10.

  4. Set the max_worker_processes source flag to at least the same number of databases that Database Migration Service is going to migrate (which is all of the databases under the source instance), plus the number of max_worker_processes already used on your instance. If you plan to use adjusted data dump parallelism settings, account for two additional worker processes per connection (up to a maximum of 20 workers).

    The default value for this flag is 8.

  5. Restart your source instance so that the configuration changes that you made to the flags can take effect.
Enable replication delay monitoring for PostgreSQL version below 9.6

If you're migrating from a PostgreSQL version earlier than 9.6, then the replication delay metric isn't available by default. There are three alternatives to allow you to track this metric to ensure minimal downtime when you promote the database:

  1. Option 1: Enable Database Migration Service to track the replication delay by granting access to a specific query. Using a user with the SUPERUSER privilege, perform the following:

    1. Define the following function to allow Database Migration Service to query for the replication delay.

      CREATE OR REPLACE FUNCTION pg_stat_replication_user()
RETURNS TABLE (
pid               integer                  ,
usesysid          oid                      ,
username          name                    ,
application_name  text                     ,
client_addr       inet                     ,
client_hostname   text                     ,
client_port       integer                  ,
backend_start     timestamp with time zone ,
backend_xmin      xid                      ,
state             text                     ,
sent_location     pg_lsn                   ,
write_location    pg_lsn                   ,
flush_location    pg_lsn                   ,
replay_location   pg_lsn                   ,
sync_priority     integer                  ,
sync_state        text
)
LANGUAGE SQL
SECURITY DEFINER
AS $$
 SELECT *
 FROM pg_catalog.pg_stat_replication;
$$;

    2. Grant the EXECUTE permission to the USER by running the following commands:

      1. REVOKE EXECUTE ON FUNCTION pg_stat_replication_user() FROM public;
      2. GRANT EXECUTE ON FUNCTION pg_stat_replication_user() to {replication_user};

  2. Option 2: Grant the SUPERUSER privilege directly to the USER used to connect to the source instance. This will allow Database Migration Service to read the replication delay directly.

  3. Option 3: Track the replication delay independently by using the following query:

    For PostgreSQL versions earlier than 10, run this command as a superuser. 
        SELECT current_timestamp, application_name,
    pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.sent_location) AS sent_location_lag,
    pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.write_location) AS write_location_lag,
    pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.flush_location) AS flush_location_lag,
    pg_xlog_location_diff(pg_current_xlog_location(), pg_stat_replication.replay_location) AS replay_location_lag
    FROM pg_stat_replication
    WHERE application_name like 'cloudsql%';

    In this option, Database Migration Service won't reflect the replication delay metric in the graphs or API responses.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-07-02 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-07-02 UTC."],[[["Database Migration Service facilitates continuous migrations from various PostgreSQL source databases, including Amazon RDS, Amazon Aurora, self-managed PostgreSQL, and Cloud SQL, to AlloyDB."],["Configuring a source instance involves ensuring the presence of the `postgres` database and installing the `pglogical` package, along with making sure it is included in the shared_preload_libraries variable."],["For database configuration, all databases under the source instance are migrated, excluding certain template or system databases like `template0`, `template1`, and `rdsadmin` depending on the type of source."],["The user connecting to the source instance needs specific privileges on each migrated database, including schema usage and table selection rights, as well as the appropriate replication privileges for their source type (Amazon RDS or not)."],["When setting parameters for the `pglogical` package, `max_replication_slots`, `max_wal_senders`, and `max_worker_processes` should be configured to accommodate the number of databases being migrated and the number of migration jobs that will be executed."]]],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4