Sample set up

In the Examples provided by this document, we shall be using the set up described below.

The following set up is used in the examples provided by this document:

Source (MINE)

• Hostname: oel712csrc
• OS: Oracle Linux Server release 7.1
• Database: Oracle 12.1 (TNS: UNITSRC)

Target (APPLY)

• Hostname: oel712ctrg
• OS: Oracle Linux Server release 7.1
• Database: PostgreSQL 9.2

Steps provided below are for reference only, by no means they should be considered as recommendations for setting up a Production database. For full information on PostgreSQL installation and configuration, refer to the PostgreSQL documentation

Installation and config

Installing PostgreSQL server.

yum install postgresql-server

Then depending on kernel version either:

postgresql-setup initdb
or
service postgresql initdb

To initialise and then to start

chkconfig postgresql on
service postgresql start

If startup fails, the following command shows the startup failure details:

To connect to database for verification:

# su - postgres
$ psql

Edit the configuration file /var/lib/pgsql/data/postgresql.conf to allow non-localhost connections:

sudo su - -s /bin/sh postgres
cd /var/lib/pgsql/data
cp postgresql.conf postgresql.conf.orig
chmod 400 postgresql.conf.orig
sed -i "s/^#\(listen_addresses = '\)localhost'/\1*'\t/" postgresql.conf

Enable connections on non-localhost address:

cp pg_hba.conf pg_hba.conf.orig
chmod 400 pg_hba.conf.orig
echo -e "host\tall\t\tall\t\t0.0.0.0/0\t\tmd5" >> pg_hba.conf

Restart:

service postgresql restart

Setting up for Replicate

The following is assumed:

• It is not allowed to create OS user(s) only for the sake of replication (i.e. OS dbvrep user)

Create an admin user for replication

Main purpose of this action:

• Having the user as powerful as postgres; and
• Not touching any settings for postgres user (as modifying authorization method to allow it to connect externally)

Creating another superuser:

$ sudo su - -s /bin/sh postgres
$ createuser -d -e -E -l -P -r -s dbvrep_admin

When asked, define a secure password.
Output will be something similar to:

$ createuser -d -e -E -l -P -r -s dbvrep_admin
Enter password for new role:
Enter it again:
CREATE ROLE dbvrep_admin ENCRYPTED PASSWORD 'md52043f6bd01b7e83506e09082ec8161b2' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

Set up is now complete.

Next steps

After this, it is possible to execute setup wizard, which is described in the following sections. Click links below to proceed

• PostgreSQL: Simple replication example
• Data Instantiation for PostgreSQL (step-by-step guide)