Setting up the pglogical extension (original) (raw)

To set up the pglogical extension on your RDS for PostgreSQL DB instance , you add pglogical to the shared libraries on the custom DB parameter group for your RDS for PostgreSQL DB instance. You also need to set the value of the rds.logical_replication parameter to 1, to turn on logical decoding. Finally, you create the extension in the database. You can use the AWS Management Console or the AWS CLI for these tasks.

You must have permissions as the rds_superuser role to perform these tasks.

The steps following assume that your RDS for PostgreSQL DB instance is associated with a customDB parameter group. For information about creating a custom DB parameter group, see Parameter groups for Amazon RDS.

To set up the pglogical extension

1. Sign in to the AWS Management Console and open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
2. In the navigation pane, choose your RDS for PostgreSQL DB instance.
3. Open the Configuration tab for your RDS for PostgreSQL DB instance. Among the Instance details, find the Parameter group link.
4. Choose the link to open the custom parameters associated with your RDS for PostgreSQL DB instance.
5. In the Parameters search field, type shared_pre to find the shared_preload_libraries parameter.
6. Choose Edit parameters to access the property values.
7. Add pglogical to the list in the Values field. Use a comma to separate items in the list of values.
   
8. Find the rds.logical_replication parameter and set it to1, to turn on logical replication.
9. Reboot the RDS for PostgreSQL DB instance so that your changes take effect.
10. When the instance is available, you can use psql (or pgAdmin) to connect to the RDS for PostgreSQL DB instance.

psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb  

1. To verify that pglogical is initialized, run the following command.

SHOW shared_preload_libraries;  
shared_preload_libraries  
--------------------------  
rdsutils,pglogical  
(1 row)  

1. Verify the setting that enables logical decoding, as follows.

SHOW wal_level;  
wal_level  
-----------  
 logical  
(1 row)  

1. Create the extension, as follows.

CREATE EXTENSION pglogical;  
EXTENSION CREATED  

1. Choose Save changes.
2. Open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
3. Choose your RDS for PostgreSQL DB instance from the Databases list to select it, and then choose Reboot from the Actions menu.

To setup the pglogical extension

To setup pglogical using the AWS CLI, you call the modify-db-parameter-group operation to modify certain parameters in your custom parameter group as shown in the following procedure.

1. Use the following AWS CLI command to add pglogical to theshared_preload_libraries parameter.

aws rds modify-db-parameter-group \  
   --db-parameter-group-name custom-param-group-name \  
   --parameters "ParameterName=shared_preload_libraries,ParameterValue=pglogical,ApplyMethod=pending-reboot" \  
   --region aws-region  

1. Use the following AWS CLI command to set rds.logical_replication to1 to turn on the logical decoding capability for the RDS for PostgreSQL DB instance.

aws rds modify-db-parameter-group \  
   --db-parameter-group-name custom-param-group-name \  
   --parameters "ParameterName=rds.logical_replication,ParameterValue=1,ApplyMethod=pending-reboot" \  
   --region aws-region  

1. Use the following AWS CLI command to reboot the RDS for PostgreSQL DB instance so that the pglogical library is initialized.

aws rds reboot-db-instance \  
    --db-instance-identifier your-instance \  
    --region aws-region  

1. When the instance is available, use psql to connect to the RDS for PostgreSQL DB instance.

psql --host=111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb  

1. Create the extension, as follows.

CREATE EXTENSION pglogical;  
EXTENSION CREATED  

1. Reboot the RDS for PostgreSQL DB instance using the following AWS CLI command.

aws rds reboot-db-instance \  
    --db-instance-identifier your-instance \  
    --region aws-region  

Using pglogical to synchronize data

Setting up logical replication

Did this page help you? - Yes

Thanks for letting us know we're doing a good job!

If you've got a moment, please tell us what we did right so we can do more of it.

Did this page help you? - No

Thanks for letting us know this page needs work. We're sorry we let you down.

If you've got a moment, please tell us how we can make the documentation better.