Delphix Postgres LabGuide

Intro
Engine Configuration
Configure the Source
Configure the Staging Host
Configure the Target
Create the dSource and VDB
- Create the dSource
- Provision a VDB

Intro

At the end of this lab exercise, you will have a fully functional system for creating masked copies of Postgres data. This lab starts from scratch, which is perhaps a bit unrealistic but allows for covering Delphix and Postgres in a sufficient level of detail. There are some exercises, of course, that are done for the sake of this seeming like a realistic system. These would be left out when a customer already has a running system.

Assumptions

You're using LabAlchemy
Your LabAlchemy course is running
You have a source, target and staging server
Your Delphix Engine is on 5.3.5 or greater

Relevant Host Access Info

Target Environment	IP Address	User	Purpose
Source	10.0.1.20	centos	Source database
Staging	10.0.1.30	centos	Staging server and masking
Target	10.0.1.40	centos	Target/copy databases

Relevant Network Info

Delphix Engine: 10.0.1.10
Jumpbox: 10.0.1.5

Engine Configuration

To begin, please do the following:

Engine Version

Ensure the engine is running the latest version of Delphix. The latest can be found at download.delphix.com.

Install Plugin

Support for Postgres requires the installation of the latest plugin. This plugin tells the Delphix Engine how to discover Postgres instances and provide virtual copies.

From download.delphix.com, go to the latest engine download folder (matching what you installed above)
Find the Postgres plugin: /Plugins/PostgreSQL and download. Note: Delphix plugins will have different versioning from that of the Delphix Engine.
Extracting the downloaded file will give a JSON file.
Log in to the Engine and go to Manage->Plugins and click the + icon
Drag (or upload) the downloaded JSON file

This will now show "PostgresDB" as an additional supported plugin.

Configure the Source

Real deployments of Delphix will have an existing source and source database. Since we're setting up a demo system, we need to configure the source system (like our customers), the source database (somewhat like customers) and create data (not like customers).

Basic Host Configuration

Ensure the system is configured correctly.

Access the source environment, this IP is matching what you see above:

ssh -i ~/internal/dxkey centos@10.0.1.20
Check that the OS version is supported. Supported versions can be found on the Postgres Support Matrix on docs.delphix.com. Assuming you're using CentOS:

cat /etc/centos-release

CentOS must be 7.4 or greater, depending on the specific Postgres version (below)
Check to see if Postgres is running:

ps -A | grep postgres
(Assuming Postgres is not running, if it is, skip ahead). Switch to root and either add the postgres user or reset its password to "postgres". Linux will complain about the password choice, but ignore it.

sudo su -

passwd postgres(set password as "postgres")
Set up directories and change users

mkdir /usr/local/pgsql/data -p

chown postgres /usr/local/pgsql/data

su - postgres
Initialize Postgres

pg_ctl init -D /usr/local/pgsql/data
Start Postgres

pg_ctl start -D /usr/local/pgsql/data -l logfile

Prepping the Source for Delphix

Make Toolkit Directory

As the centos user (if you're still using the "postgres" user, you may use "exit" to transition), create the directory for the Delphix toolkit

sudo mkdir /opt/delphix
Assign ownership to the "postgres" user

sudo chown postgres /opt/delphix/

sudo chmod 770 /opt/delphix/

Configure Postgres

Create a role

If you are not already using the "postgres" user, switch to the "postgres" user (password, set before, is "postgres").

su - postgres
Start using psql

psql
Review existing roles. The role "delphix" should not exist.

select rolname from pg_roles;

Will return something like:
Create a delphix role:

create role delphix superuser login replication password 'delphix';
Validate that the role exists now:

select rolname from pg_roles;
Exit the client

exit

Edit the Postgres Configuration File

Edit the postgres.conf file: Here we will enable remote listeners. As the root user:

vi /usr/local/pgsql/data/postgresql.conf
Enable remote connections. Find the line that starts with "listen_addresses" and set this to all by:

listen_addresses='*';
Ensure "port" is in the file (may remain commented)

#port = 5432
Set the amount of data that is recorded in the write-ahead logs. In the Write-ahead log section, set wal_level to logical

wal_level = logical
If the number is low, Increase concurrent connections by four. In the replication section, set "max_wal_senders" by four. Setting to 10 should be sufficient.

max_wal_senders = 10
Save the file

Edit the PG_HBA file

Edit the pg_hba.conf file to allow client connections from the Delphix Engine, the staging

vi /usr/local/pgsql/data/pg_hba.conf

Add five lines to the bottom of the file. This allows connectivity from the Delphix Engine, the staging server and the jump box on lines 2-4.

#Delphix Connections
 host   all            delphix      10.0.1.10/32   trust
 host   all            delphix      10.0.1.30/32   trust
 host   all            delphix      10.0.1.5/32    trust
 host   replication    delphix      10.0.1.30/32   trust

Restart Postgres

Switch to the postgres user

su - postgres
As the user "postgres"

pg_ctl restart -D /usr/local/pgsql/data -l logfile

Ensure Postgres Starts Upon Restart

We may choose to start and stop our lab. Rather than going through the process of starting Postgres each time, we'll create a rule to ensure that Postgres is started when the system does.

As centos, change directories:

cd /etc/systemd/system
Create a new file for our rule:

sudo touch postgresql.service
Set the proper permissions

sudo chmod 644 postgresql.service
Edit the file:

sudo vi postgresql.service

Enter the following information and save the file:

[Unit]
Description=PostgreSQL Database Server
Documentation=man:postgres(1)
    
[Service]
Type=notify
User=postgres
ExecStart=/usr/pgsql-11/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

Reload systemctl

sudo systemctl daemon-reload
Enable our script to run at startup:

sudo systemctl enable postgresql
If you wish this prove this works, restart your lab class and check that Postgres is running by running:

ps -A | grep postgres

Add Data

For this exercise to be interesting, we must have data in our source database. The following scripts will create two tables of data in the default Postgres database. This is assuming that you've kept the standard names and passwords.

From the terminal on the jumpbox, change directory to /home/delphix/Scripts
In that folder, you'll find dbmanager.jar
Execute these statements in order (opening this on the jump box and copying pasting is your friend):

java -jar dbmanager.jar setupdb jdbc:postgresql://10.0.1.20:5432/postgres?username=delphix?password=delphix --table DEMO_TABLE_1?rows=5?orderby=id:ASCENDING --columns DEMO_TABLE_1:SEQUENCE?name=id,FIRST_NAME,LAST_NAME,SSN --table DEMO_TABLE_2?rows=5 --columns DEMO_TABLE_2:CREDIT_CARD,TIMESTAMP?name=birthday?sequence=RANDOM --outputdb demo.cfg

java -jar dbmanager.jar createtables jdbc:postgresql://10.0.1.20:5432/postgres?username=delphix?password=delphix --inputdb ./demo.cfg

java -jar dbmanager.jar insertrows jdbc:postgresql://10.0.1.20:5432/postgres?username=delphix?password=delphix --inputdb ./demo.cfg --setrows 10000000

Note, the last step, inserting ten million rows into two tables will take a few minutes. Time for coffee.

Configure the Staging Host

Basic Host Configuration

The basic configuration for the staging host is the same as the source.

Access the environment, this IP is matching what you see above:

ssh -i ~/internal/dxkey centos@10.0.1.30
Check OS version is supported. Assuming CentOS:

cat /etc/centos-release

CentOS must be 7.4 or greater, depending on the specific Postgres version (below)
Check to see if Postgres is running:

ps -A | grep postgres
(Assuming Postgres is not running, if it is, skip ahead). Switch to root and either add the "postgres" user or reset its password to "postgres". Linux will complain about the password choice but ignore it.

sudo su -

passwd postgres (set password as "postgres")
Set up directories and change users

mkdir /usr/local/pgsql/data -p

chown postgres /usr/local/pgsql/data

su - postgres
Initialize Postgres

pg_ctl init -D /usr/local/pgsql/data
Start Postgres

pg_ctl start -D /usr/local/pgsql/data -l logfile

Prepping the Staging Host for Delphix

Make Toolkit Directory

As the centos user, create the directory for the Delphix toolkit

sudo mkdir /opt/delphix
Assign ownership to the "postgres" user

sudo chown postgres /opt/delphix/

sudo chmod 770 /opt/delphix/

sudo chgrp postgres /opt/delphix/
(Perhaps finish this later) SET Environment Variables

Validate Permissions

The operating system user must have read and execute privileges on the PostgreSQL binaries installed on the target environment. Run the fully query and ensure the last three letters match (r-x):

ls -ld /usr/pgsql-11/bin
The "postgres" user must have permission to run mount and umount as the superuser via sudo with neither a password nor a TTY. Edit the sudoers file and uncomment the line allowing people in group "wheel" to run all commands

sudo visudo
Add "postgres" to the wheel group:

sudo usermod -aG wheel postgres
Ensure the direction /mnt exists and has read/write/execute access for others:

sudo chmod o+w /mnt
Ensure the "postgres" user has access to the toolkit directory

sudo chmod o+rwx /opt/delphix/

Create the Staging Environment on the Delphix Engine

On the Delphix Engine as an administrator, go to Manage->Environments
Click Add Environment
Add an environment name "Postgres Staging Host"
Add the host address "10.0.1.30" and the OS username
Select "username and public key"
Click view public key
Copy the public key
As the "postgres" user on the staging server, edit the ~/.ssh/authorized_keys file and paste the key from the engine:

vi ~/.ssh/authorized_keys

It should look like:

Run the following to allow only the file's owner to read and write to it:

chmod 600 ~/.ssh/authorized_keys
Run the following to restrict access to the user's home directory:

chmod 755 ~
Back on the Delphix Engine, enter the toolkit path (whatever you specified above)

/opt/delphix/
Click Next
Wait for the process to complete.
On the staging server, validate that in the toolkit path, there are now files

Configure the Target

Follow the same steps as configuring the staging host, but with the IP address 10.0.1.40.

Create the dSource and VDB

Create the dSource

Before creating (one or more) copies of the source database, we need to create the Delphix-managed version, which we call the staging copy. This is managed on the staging host and will get data from the source via replication.

Establish the Installation

On the Delphix Engine as an administrator, navigate the Manage->Environments
Select "Postgres Staging Host"
Click "Databases"
In the "Postgres" Installation Click the + to add a new Database
Name it "Postgres"

Add a dSource

Navigate to Manage->Datasets
Click the + and "Add dSource"
Click "Next"
On the "Source" window select "Postgres"
Check the "Delphix Initiated Backup Flag"
Under "Delphix Initiated Backup/External Backup - Streaming Replication" click "Add"
Set the "PostgresDB Replication User" and password to be "delphix"
Set the source host address to "10.0.1.20"
Leave the source port as "5432"
Leave the staging port as "5433" Since we have an instance of Postgres already running on this instance, we must provide a new port for the second instance.
Click Next
On the dSource Configuration page name the dSource name as "Postgres Source"
Create a group "Postgres"
Click Next
Leave the "staging environment" and "user" as they are. Set "Mount Base" to "/mnt"
No changes to policies
No hooks
Review the summary and click "Submit"

Provision a VDB

Context

The objective of Delphix virtualization is to provide easy access to virtualized databases that resemble production and other data systems. Here, we provision a Virtual Database (VDB). VDBs are fully functional database images that can be created from dSources.

Go to Manage -> Datasets, select the most recent Snapshot, and click on the leftmost icon to provision a database (you can hover over each icon to see what it does)
In the Wizard, select “Postgres Target Host” as the environment and ensure the user = “postgres”
Select “Add Dataset Group” and name this target group “DB Target”, and set name = “devdb”
Accept all other defaults and click through the Wizard.
Review the summary page, click Submit, and watch in the Actions tab to confirm it runs successfully.