Postgres: Best practices for replication

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
3 min read

Are you new to Postgres? Below you find some tips how to manage PostgreSQL together with Dbvisit Replicate. 

The article explains the basic concepts; it does not go into depth. This article aims to help work easier with PostgreSQL.

Pre-requisites: basic knowledge and overview of Oracle databases (Oracle 11g and Oracle 12c).

Theory

Before you start creating a replication think about design first. PostgreSQL is similar to Oracle, but it's not Oracle 11g. Oracle 12c with a pluggable database can be considerate as Postgres, but with two eyes closed of course.  

Principal objects in a database are a user, schema, and database. PostgreSQL objects can be likened to a disk in Windows system. In the computer window ("PostgreSQL cluster"), you can see disks C:, D: ... these are databases. Directories could be linked to schemas and files are tables, procedures and so on. Let's compare them to Oracle database too:

  • In Oracle 11g user is equal to a schema. In PostgreSQL, it's not true. It's two different things. The user you're using to connect to the database and collect privileges and settings. It also means PostgreSQL implemented rolesdifferently than Oracle database. 

  • PostgreSQL Server can have 1 .. N databases. It's similar to Oracle 12c EE. 

  • A user can have 0 .. N schemas. A name is unique for a database. A user who creates schema owns it. Other users can work schema too but needs permissions. Also, users need set 'search path' appropriately if they want to work with tables in the schema. 

 

Architecture

Imagine the Oracle 11g as a source database. You have tables in 3 schemas you'd like to replicate. You can design destination database

  1. one database and four schemas (count replicate internal objects too)

  2. one database and one schema only.

Every aspect has it's own pros and cons. The benefit of the first option is your destination architecture is logically identical to the source database. By PostgreSQL security, you'll be able to archive same rules for your applications as you have on the source. The con is management overhead. You'll need to create schemas and add security for additional users (application users). Also, you need to prepare tables with rename clause to a different schema. 

The second option is much more comfortable in management compared to the first one. On the other hand, you won't be able to make any security. Every user with privilege to usage on used schema will able to see whole content. 

I am recommending the first option. 

Basic commands

To make replication management comfortable use in PostgreSQL same user as you're using for DBVisit replication - create a schema, create tables and load data etc.

For Non-Oracle targets, I am recommending to start replication without user data. 

Before you start with setup wizard create a database you'll use for replication. 

PSQL# CREATE DATABASE dbvrepDB;

It's default choose in setup wizard form. A schema for internal Dbvisit Replicate objects will be created during execution of *-all.sh script. Schema you'd like to use for production objects you need to create (unless you go the second option - one schema for all). Login in into new destination database with the user created during setup wizard (setup wizard question: "Enter the user to log into apply database: [dbvrep] dbvrep_apply_user").

  • -all.sh script finished.  Let's start working with Postgres:

psql -h localhost -U dbvrep_apply_user -d dbvrepDB; CREATE SCHEMA schema1;

If you won't be able to connect into PostgreSQL manage your hba.conf file access -  https://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html.

Change search_path to see all schemas and objects in them. 

PSQL# SHOW search_path; 
PSQL# alter role johnny set search_path = "$user", dbvrep, schema1; 
PSQL# select * from pg_user; 

You can instantiate data using ORA2PG tool - Data Instantiation for PostgreSQL (step-by-step guide) .  

For example, you'll mix users. Can happen you'll create a table under the public schema. Below you can see a way how to move the table into the appropriate schema and add permission to dbvrep user:

PSQL# ALTER TABLE dbvisittest SET SCHEMA schema2;
PSQL# grant ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema2.dbvisittest;
PSQL# GRANT USAGE ON SCHEMA schema2 TO dbvrep_apply_user;

 

 

How to prepare a table

To prepare a table using 'rename to' clause. You should define what schema would you like to use. For example, I want to replicate mine objects to SCHEMA schema2. 

dbvrep> pause apply
dbvrep> prepare table msi.dbvisittest rename to schema2.dbvisittest

Continue with ORA2PG tool - https://dbvisit.atlassian.net/wiki/spaces/ugd9/pages/333414402/Data+Instantiation+for+Postgresql+step-by-step+guide. Don't forget to change a able into approrite schema using ORA2PG parameter. 

dbvrep> resume apply