Yandex.Cloud
  • Services
  • Why Yandex.Cloud
  • Pricing
  • Documentation
  • Contact us
Get started
Yandex Managed Service for PostgreSQL
  • Getting started
  • Step-by-step instructions
    • All instructions
    • Information about existing clusters
    • Creating clusters
    • Connecting to databases
    • Stop and start the cluster
    • SQL queries in the management console
    • Changing cluster and database settings
    • Connecting to DataLens
    • Managing PostgreSQL hosts
    • Database management
    • How to manage PostgreSQL extensions
    • Managing database users
    • Granting permissions and roles
    • Managing backups
    • Deleting clusters
    • Database migration to the Cloud
    • Creating a logical replica of Amazon RDS for PostgreSQL in Managed Service for PostgreSQL
  • Solutions
    • Creating a PostgreSQL cluster for 1C
  • Concepts
    • Relationship between service resources
    • Host classes
    • Network in Yandex Managed Service for PostgreSQL
    • Quotas and limits
    • Storage types
    • Backups
    • Assigning roles
    • Replication
    • Supported clients
  • Access management
  • Pricing policy
    • Current pricing policy
    • Archive
      • Before January 1, 2019
      • From January 1 to March 1, 2019
      • From March 1, 2019 to February 1, 2020
  • API reference
    • Authentication in the API
    • gRPC
      • Overview
      • BackupService
      • ClusterService
      • DatabaseService
      • ResourcePresetService
      • UserService
      • OperationService
    • REST
      • Overview
      • Backup
        • Overview
        • get
        • list
      • Cluster
        • Overview
        • addHosts
        • backup
        • create
        • delete
        • deleteHosts
        • get
        • list
        • listBackups
        • listHosts
        • listLogs
        • listOperations
        • move
        • rescheduleMaintenance
        • restore
        • start
        • startFailover
        • stop
        • streamLogs
        • update
        • updateHosts
      • Database
        • Overview
        • create
        • delete
        • get
        • list
        • update
      • ResourcePreset
        • Overview
        • get
        • list
      • User
        • Overview
        • create
        • delete
        • get
        • grantPermission
        • list
        • revokePermission
        • update
      • Operation
        • Overview
        • get
  • Questions and answers
    • General questions
    • Questions about PostgreSQL
    • All questions on the same page
  1. Step-by-step instructions
  2. Creating a logical replica of Amazon RDS for PostgreSQL in Managed Service for PostgreSQL

Creating a logical replica of Amazon RDS for PostgreSQL in Managed Service for PostgreSQL

  • Settings for Amazon RDS for PostgreSQL
  • Settings for Managed Service for PostgreSQL
  • Using logical replication

If you use Amazon RDS for PostgreSQL, you can create a copy of your data in Managed Service for PostgreSQL.

Settings for Amazon RDS for PostgreSQL

Available for Amazon RDS for PostgreSQL version 10.4 and higher.

Warning

The DB instance must have public access: Public accessibility = yes.

  1. Set up logical replication.

    1. Set the parameter to parameter group of your DB instance:

      rds.logical_replication = 1
      
    2. Restart the cluster to apply the changes.

  2. Create a separate user with the role rds_replication. To do this, execute the following statements on behalf of the user with the rds_superuser role:

    CREATE ROLE <user> WITH LOGIN PASSWORD <password>;
    GRANT rds_replication TO <user>;
    
  3. Grant the SELECT privilege to all the replicated tables:

    GRANT SELECT ON <table>, <table2> TO <user>;
    
  4. Create a publication:

    CREATE PUBLICATION pub FOR TABLE <table>, <table2>;
    

Note

We don't recommend using FOR ALL TABLES publications as you won't be able to edit the table list later.

  1. Add an inbound rule to the VPC security groups.
    For example:

    protocol: tcp, port: 5432, source: 84.201.175.90/32
    

    Where

    • 84.201.175.90 is a public IP address.

Settings for Managed Service for PostgreSQL

Warning

The hosts must have public access.

  1. To create a subscription, grant the mdb_admin role to the user:

    yc managed-postgresql user update {user_name} --grants mdb_admin --cluster-id {cluster_id}
    
  2. On the Managed Service for PostgreSQL cluster host, create a subscription with the Amazon RDS connection string.

    CREATE SUBSCRIPTION s_data_migration CONNECTION 'host=<Amazon RDS server address> port=<port> user=<username> sslmode=prefer dbname=<database name>' PUBLICATION pub;
    

Learn more about creating subscriptions in the PostgreSQL documentation.

Using logical replication

  • Changes to the database schema and DDL are not replicated.

    First apply the new schema changes on the subscriber side and then on the publication side.

  • Sequences are not replicated.

    When the table is replicated, the data in the serial columns or ID columns that is generated using sequences are also replicated. However, the sequence on the subscriber side will preserve its starting value.

    If you switch to the subscriber database, restart the sequence using the latest value:

    ALTER SEQUENCE serial RESTART WITH <new value>;
    
  • When you create a subscription, data from the source tables is fully copied.

    To accelerate the copy process, only create the primary key indexes. Create all the other indexes after the copying is complete.

  • If a table doesn't have the primary key, you'll get errors like:

    ERROR:  55000: cannot update table "t2" because it does not have a replica identity and publishes updates
    HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
    

    To run UPDATE and DELETE replications on tables without the primary key, change REPLICA IDENTITY:

    alter table t2 replica identity full;
    
  • In PostgreSQL version 10, the TRUNCATE command is not replicated.

    You can use DELETE instead.

  • External tables are not replicated.

  • To view errors related to logical replication, see the Managed Service for PostgreSQL logs.

  • If you need to re-create a subscription, delete all data from the tables on the subscriber side to prevent primary key restriction errors.

In this article:
  • Settings for Amazon RDS for PostgreSQL
  • Settings for Managed Service for PostgreSQL
  • Using logical replication
Language
Careers
Privacy policy
Terms of use
© 2021 Yandex.Cloud LLC