Preparing for PostgreSQL Replication

Version Support

Database	Version	Support Status	Notes
PostgreSQL	9.5, 9.6	Primary platform (applier only)

Configure the source and target hosts following the prerequisites outlined in "Installation Prerequisites" then follow the appropriate steps for the required extractor topology outlined in "Deploying MySQL Extractors".

For replication to PostgreSQL hosts, you must ensure that the networking and user configuration has been configured correctly.

Enable PostgreSQL Networking

Within the PostgreSQL configuration, two changes need to be made:

• Configure the networking so that the listen address for the PostgreSQL server is configured correctly for this edit. Edit the /etc/postgresql/main/postgresql.conf file and edit the listen_address line either to * or to an explicit IP address. For example:

  listen_addresses = '192.168.3.73'

• Edit the /etc/postgresql/main/pg_hba.conf file and ensure that the password properties match the password settings and hostname limitations. In particular, the replicator will communicate over the public IP address, not localhost, and so you must ensure that network-based connections using a user/password combination are allowed. For example, you may want to add a line to the file that provides network-wide access, or at least access for the local network range:

  local   all             all                                     md5

User Configuration

A suitable user must be created with rights and permissions to create databases, as this is required by the replicator to create databases, tables, and other objects. The creatuser command can be used for this purpose. The --createdb adds the CREATEDB permission:

shell> createuser tungsten --createdb

You will be prompted to provide a password for the user.

Alternatively, you can create the user and permissions through the psql interface:

shell> sudo -u postgres psql --port=5433 --user=postgres postgres
Type "help" for help.

postgres=# CREATE ROLE tungsten WITH LOGIN PASSWORD 'password';
postgres=# ALTER ROLE tungsten CREATEDB;

You may also want to grant specific privileges to existing databases which must be done within the psql interface:

shell> sudo -u postgres psql --port=5433 --user=postgres postgres
Type "help" for help.

postgres=# GRANT ALL ON DATABASE postgres TO tungsten;