Preparing the subscription database v7

You must prepare a database to use as a subscription database.

Replicate the tables and views in a given publication to the same database. The database you replicate to is called the subscription database. Create a subscription database user name with the following characteristics:

• The subscription database user can connect to the subscription database.
• The subscription database user has the privileges to create database objects for the replicated tables and views from publications.
• The subscription database user has the privileges needed to execute the TRUNCATE command on the replicated tables.

Postgres subscription database

Choose or create a database passworded user name to serve as the subscription database user. The subscription database user becomes the owner of the replicated database objects.

When creating the subscription database definition, enter the subscription database user name in the Subscription Service – Add Database dialog box (see Adding a subscription database).

The subscription database user must also be able to run the TRUNCATE command on the subscription tables. This requires the following:

• Superuser privileges.
• The ability to modify the system catalog tables to disable foreign key constraints on subscription tables. See Disabling foreign key constraints for snapshot replications for more information on this requirement.

When choosing the subscription database user name, you can either:

• For the subscription database user name, use the Postgres user name postgres created when you installed PostgreSQL (enterprisedb for EDB Postgres Advanced Server installed in Oracle-compatible configuration mode). If you choose this option, skip Step 1 and proceed to Step 2.
• Create a new subscription database user name.

Step 1: Create a superuser as the subscription database user.

CREATE ROLE subuser WITH LOGIN SUPERUSER PASSWORD '<password>';

Step 2: Create or choose the subscription database.

The names of the schemas containing the publication tables and views become the names of the Postgres schemas for the subscription tables. The subscription server creates these schemas in the subscription database when you create the subscription. If schemas with these names already exist in the subscription database, the existing schemas store the subscription tables.

For a SQL Server publication database: If the schema containing the publication tables and views in SQL Server is named dbo, then the subscription server creates a schema named dbo_sql in the Postgres subscription database for the subscription tables. (Schema dbo is a special reserved schema in Postgres.)

The existing schemas can't contain any tables or views with the same names as the publication tables and views. The subscription server returns an error if there are already identically named tables or views. You must delete or rename these tables and views before the subscription is created.

You can create a new subscription database owned by the subscription database user subuser:

CREATE DATABASE subdb OWNER subuser;

Oracle subscription database

Step 1 (Optional): If you don't have an existing database that you want to use as your subscription database, create a new database. This step can be fairly complicated. Refer to the appropriate Oracle documentation for performing this task.

Step 2: Create a database user name for the subscription database user. The subscription database user name must have a password, and it must have the ability to create a database session. The subscription database user becomes the owner of the replicated database objects.

When creating the subscription database definition, enter the subscription database user name in the Subscription Service – Add Database dialog box (see Adding a subscription database).

CREATE USER subuser IDENTIFIED BY password;
GRANT CONNECT TO subuser;

Step 3: Grant the privileges needed to create the replicated database objects.

Create the replicated database objects in the schema owned by and with the same name as the subscription database user.

GRANT RESOURCE TO subuser;

Step 4 (For Oracle 12c only): Grant the privileges required to access tablespaces. Grant the GRANT UNLIMITED TABLESPACE privilege to the subscription database user. This requirement applies to both a pluggable database and a non-container database.

GRANT UNLIMITED TABLESPACE TO subuser;

SQL Server subscription database

Step 1: Create or choose the subscription database.

The names of the schemas containing the publication tables and views become the names of the SQL Server schemas for the subscription tables. The subscription server creates these schemas in the subscription database when you create the subscription. If schemas with these names already exist in the subscription database, the existing schemas store the subscription tables.

The existing schemas can't contain any tables or views with the same names as the publication tables and views. The subscription server returns an error if there are already identically named tables or views. You must delete or rename these tables and views before the subscription is created.

You can create a new subscription database as follows:

USE primary;
GO
CREATE DATABASE subdb;
GO

Step 2: Create a passworded SQL Server login for the subscription database user.

When creating the subscription database definition, enter the SQL Server login in the Subscription Service – Add Database dialog box (see Adding a subscription database).

CREATE LOGIN subuser WITH PASSWORD = '<password>';
GO

Step 3: The subscription database must have a database user who is the creator and owner of the subscription tables. This database user must be mapped to the SQL Server login created in Step 2.

In this example, the database user is given the same name as the SQL Server login subuser.

USE subdb;
GO
CREATE USER subuser FOR LOGIN subuser;
GO

Step 4: Grant the database-level privileges needed by the subscription database user to create the schema and tables for the subscription.

GRANT CREATE SCHEMA TO subuser;
GRANT CREATE TABLE TO subuser;
GO

• On this page
• Postgres subscription database
• Oracle subscription database
• SQL Server subscription database