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
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: <x.y.z>/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.
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).
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 -pchown postgres /usr/local/pgsql/datasu - postgres -
Initialize Postgres
pg_ctl init -D /usr/local/pgsql/data -
Start Postgres
pg_ctl start -D /usr/local/pgsql/data -l logfile
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/
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
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
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.
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 -pchown postgres /usr/local/pgsql/datasu - postgres -
Initialize Postgres
pg_ctl init -D /usr/local/pgsql/data -
Start Postgres
pg_ctl start -D /usr/local/pgsql/data -l logfile
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/
-
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
Follow the same steps as configuring the staging host, but with the IP address 10.0.1.40.
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"
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.