This quick start guide, which is excerpted from the larger User Guide, is intended to provide you with a quick overview of working with PostgreSQL database objects in the Delphix Engine. It does not cover any advanced configuration options or best practices, which can have a significant impact on performance. It assumes that you are working in a Lab/Dev setting and attempting to quickly test Delphix functionality. It assumes you will use the VMware Hypervisor.
In this guide, we will walk through deploying a Delphix Engine, starting with configuring Source and Target database environment. We will then create a dSource, and provision a VDB.
For purposes of the QuickStart, you can ignore any references to Replication or Masking, such as the engines shown in the diagram below.
Deploy OVA on VMware
Use the Delphix-supplied OVA file to install the Delphix Engine. The OVA file is configured with many of the minimum system requirements. The underlying storage for the install is assumed to be redundant SAN storage.
- Download the OVA file from https://download.delphix.com. You will need a support login from your sales team or a welcome letter.
- Navigate to the Delphix Product Releases/<Current Version>/Appliance Images page.
- Login using the vSphere client to the vSphere server (or vCenter Server) where you want to install the Delphix Engine.
- In the vSphere Client, click File.
- Select Deploy OVA Template.
- Browse to the OVA file.
- Click Next.
- Select a hostname for the Delphix Engine.
This hostname will also be used in configuring the Delphix Engine network.
- Select the data center where the Delphix Engine will be located.
- Select the cluster and the ESX host.
- Select one (1) data store for the Delphix OS. This datastore can be thin-provisioned and must have enough free space to accommodate the 300GB comprising the Delphix operating system.
- Select four (4) or more data stores for Database Storage for the Delphix Engine. The Delphix Engine will stripe all of the Database Storage across these VMDKs, so for optimal I/O performance, each VMDK must be equal in size and be configured Thick Provisioned - Eager Zeroed. Additionally, these VMDKs should be distributed as evenly as possible across all four SCSI I/O controllers.
- Select the virtual network you want to use.
If using multiple physical NICs for link aggregation, you must use vSphere NIC teaming. Do not add multiple virtual NICs to the Delphix Engine itself. The Delphix Engine should use a single virtual network. For more information, see Optimal Network Architecture for the Delphix Engine.
- Click Finish.
The installation will begin and the Delphix Engine will be created in the location you specified.
- Once the installation has completed, power on the Delphix Engine and proceed with the initial system configuration as described in Setting Up Network Access to the Delphix Engine.
If your source database is 4 TB, you probably need 4 TB of storage for the Delphix Engine. Add at least 4 data disks of similar size for the Delphix VM. For example: for a source database of 4 TB, create 4 VMDKs of 1 TB each.
For a full list of requirements and best practice recommendations, see Virtual Machine Requirements for VMware Platform.
Setup Network Access to the Delphix Engine
- Power on the Delphix Engine and open the Console.
- Wait for the Delphix Management Service and Delphix Boot Service to come online.
This might take up to 10 minutes during the first boot. Wait for the large orange box to turn green.
- Press any key to access the sysadmin console.
sysadmin@SYSTEMfor the username and
sysadminfor the password.
You will be presented with a description of available network settings and instructions for editing.
Delphix Engine Network Setup To access the system setup through the browser, the system must first be configured for networking in your environment. From here, you can configure the primary interface, DNS, hostname, and default route. When DHCP is configured, all other properties are derived from DHCP settings. To see the current settings, run "get." To change a property, run "set <property>=<value>." To commit your changes, run "commit." To exit this setup and return to the standard CLI, run "discard." defaultRoute IP address of the gateway for the default route -- for example, "18.104.22.168." dhcp Boolean value indicating whether DHCP should be used for the primary interface. Setting this value to "true" will cause all other properties (address, hostname, and DNS) to be derived from the DHCP response dnsDomain DNS Domain -- for example, "delphix.com" dnsServers DNS server(s) as a list of IP addresses -- for example, "22.214.171.124,126.96.36.199." hostname Canonical system hostname, used in alert and other logs -- for example, "myserver" primaryAddress Static address for the primary interface in CIDR notation -- for example, "188.8.131.52/22" Current settings: defaultRoute: 192.168.1.1 dhcp: false dnsDomain: example.com dnsServers: 192.168.1.1 hostname: Delphix primaryAddress: 192.168.1.100/24
hostname. If you are using DHCP, you can skip this step.
delphix network setup update *> set hostname=<hostname>
Use the same
hostnameyou entered during the server installation.
Configure DNS. If you are using DHCP, you can skip this step.
delphix network setup update *> set dnsDomain=<domain> delphix network setup update *> set dnsServers=<server1-ip>[,<server2-ip>,...]
Configure either a static or DHCP address.
delphix network setup update *> set dhcp=true
delphix network setup update *> set dhcp=false delphix network setup update *> set primaryAddress=<address>/<prefix-len>
The static IP address must be specified in CIDR notation (for example,
Configure a default gateway. If you are using DHCP, you can skip this step.
delphix network setup update *> set defaultRoute=<gateway-ip>
Commit your changes. Note that you can use the
getcommand prior to committing to verify your desired configuration.
delphix network setup update *> commit Successfully committed network settings. Further setup can be done through the browser at: http://<address> Type "exit" to disconnect, or any other commands to continue using the CLI.
- Check that you can now access the Delphix Engine through a Web browser by navigating to the displayed IP address, or hostname if using DNS.
Setting Up the Delphix Engine
- Network Security
- Outbound Connectivity
- Network Authorization
- Connect to the Delphix Engine at
The Delphix Setup application will launch when you connect to the server.
Enter your sysadmin login credentials, which initially defaults to the username sysadmin, with the initial default password of sysadmin. On first login, you will be prompted to change the initial default password.
- Click Next.
The Delphix Engine supports two types of administrators:
- System Administrator (sysadmin) - this is the engine system administrator. The sysadmin password is defined here.
- Engine Administrator (admin) - this is typically a DBA who will administer all the data managed by the engine.
On the Administrators tab, you set up the sysadmin password by entering an email address and password. The details for the admin are displayed for reference.
The default domain user created on Delphix Engines from 5.3.1 is known as admin instead of delphix_admin. When engines created before 5.3.1 are upgraded to 5.3.1 or later they will retain their old username 'delphix_admin'. To avoid complications Delphix recommends creating users with an admin role and then Disabling delphix_admin.
The engine time is used as the baseline for setting policies that coordinate between virtual databases and external applications
Choose your option to set up system time in this section. For a Quick Start, simply set the time and your timezone. You can change this later.
The initial out-of-the-box network configuration in the OVA file is set to use a single VMXNET3 network adapter.
You have already configured this in the initial configuration. Delphix supports more advanced configurations, but you can enable those later.
You should see the data storage VMDKs or RDMs you created during the OVA installation. Click Next to configure these for data storage.
Choose your options to configure serviceability settings.
For a Quick Start, accept the defaults. You can change this later.
Choose your options to configure authentication services.
For a Quick Start, accept the defaults. You can change this later.
- Enter your Support Username and Support Password.
- Click Register.
If external connectivity is not immediately available, you must perform manual registration.
- Copy the Delphix Engine registration code in one of two ways:
- Manually highlight the registration code and copy it to clipboard. Or,
- Click Copy Registration Code to Clipboard.
- Transfer the Delphix Engine's registration code to a workstation with access to the external network Internet. For example, you could e-mail the registration code to an externally accessible e-mail account.
- On a machine with access to the external Internet, please use your browser to navigate to the Delphix Registration Portal at http://register.delphix.com.
- Login with your Delphix support credentials (username and password).
- Paste the Registration Code.
- Click Register.
Although your Delphix Engine will work without registration, we strongly recommend that you register each engine as part of the setup. Failing to register the Delphix Engine will impact its supportability and security in future versions.
To regenerate the registration code for a Delphix Engine please refer to, Regenerating the Delphix Engine Registration Code. Delphix strongly recommends that you regenerate this code and re-register the engine regularly to maximize the Support Security of the Delphix Engine. Delphix recommends doing this every six months.
The final summary tab will enable you to review your configurations for System Time, Network, Storage, Serviceability, and Authentication.
- Click the Back button to go back and to change the configuration for any of these server settings.
- If you are ready to proceed, then click Submit.
- Click Yes to confirm that you want to save the configuration.
- Click Setup to acknowledge the successful configuration.
- There will be a wait of several minutes as the Delphix Engine completes the configuration.
Requirements for PostgreSQL Source Databases
Source Database Requirements
- The Delphix source database can be in READ-WRITE or READ only mode. It can be a standby database(streaming site) as well.
The Staging host must have access to a PostgreSQL role on the source side that has replication, and login privileges. This can be the built-in PostgreSQL role or a newly-created role (for example, delphix).
To create a new role for use with the Delphix Engine, use the following command: SQL> CREATE USER delphix WITH REPLICATION ENCRYPTED [ PASSWORD 'password'];
- You must make the following changes to postgresql.conf (for more information, see the Server Configuration chapter in the PostgreSQL documentation):
TCP/IP connectivity must be configured to allow the role mentioned above to connect to the source database from the Delphix Engine and from the standby DBMS instance set up by the Delphix Engine on the staging environment. This can be done by modifying the parameterlisten_addresses, which specifies the TCP/IP addresses on which the DBMS is to listen for connections from client applications.
listen_addresses = '*' # defaults to 'localhost'; use '*' for all
The value of max_wal_senders, which specifies the maximum number of concurrent connections from standby servers or streaming base backup clients, must be increased from its desired value by four. That is, in addition to the allowance of connections for consumers other than the Delphix Engine, there must be an allowance for four additional connections from consumers set up by the Delphix Engine.
The default value max_wal_senders is zero, meaning replication is disabled. In this configuration, the value ofmax_wal_senders must be increased to two for the Delphix Engine: max_wal_senders = 4 # Default is 0
The value of, wal_level which determines how much information is written to the write-ahead log (WAL), must be set to archive or hot_standby to allow connections from standby servers. The valuelogicalwal_level (introduced in PostgreSQL 9.4) is also supported.
The default value of wal_level is minimal, which writes only the information needed to recover from a crash or immediate shutdown to the WAL archives. In this configuration, you must add the logging required for WAL archiving as follows: wal_level = archive # Default is minimal
- In releases prior to PostgreSQL 9.6.x, parameter 'wal_level' allowed the values 'archive' and 'hot_standby'. These values are still accepted but mapped to 'replica'.
The plugin will work with ALTER SYSTEM SET wal_level TO 'replica' and ALTER SYSTEM SET wal_level TO 'hot_standby'.
You must configure PostgreSQL to allow PostgreSQL client connections and replication client connections from the staging target environment. To configure appropriately, add the following entries to pg_hba.conf:
host all <role> <ip-address_of_delphix_engine>/32 <auth-method> host all <role> <ip-address_of_staging_target>/32 <auth-method> host replication <role> <ip-address_of_staging_target>/32 <auth-method>
- <auth-method> can be set to trust, scram-sha-256, or md5 on source. Delphix inherits the same authentication method from the source while dSource creation and VDB provisioning. For more information on how to configure, pg_hba.conf see the Client Authentication chapter in the PostgreSQL documentation.
wal_keep_segmentsparameter in postgresql.conf file on the Source environment should be large enough to support the WAL sync process once the dSource is building up.
Postgres 13 onwards, the configuration parameter
wal_keep_segmentsis changed to
wal_keep_size. It is specified in megabytes rather than the number of files as with the
wal_keep_segmentsparameter. If you previously used
wal_keep_segments, the following formula will give you an approximate equivalent setting:
- It is mandatory to have "port" (can be commented or not commented) in postgresql.conf file.
Requirements for PostgreSQL Target Hosts and Databases
Target Environment Requirements
The operating system and architecture of the target environment must match those of the source environment. It is recommended that the source and the target environments should be identical and hardware configurations should match.
For supported OS version and DBMS version, see PostgreSQL Matrix. The underlying Operating System for both the Source and Staging environment should be amongst these two versions.
There must be an installation of PostgreSQL on the target environment that is compatible with the installation of PostgreSQL on the source environment. Two installations of PostgreSQL are compatible if and only if:
They share the same vendor (for example, PostgreSQL is incompatible with EnterpriseDB Postgres Plus Advanced Server).
They share the same major version number (for example, 9.5.4 is compatible with 9.5, 9.5.3; however, it is incompatible with 9.3, 9.3.24, or 9.2).
They are compiled against the same architecture (in other words, 32-bit and 64-bit installations of Postgres are incompatible).
They are compiled with the same WAL segment size. The default WAL segment size of 16 MB is rarely changed in practice, so almost all installations of PostgreSQL are compatible with each other in terms of WAL segment size.
There must be an operating system user (e.g. PostgreSQL) with the following privileges:
The Delphix Engine must be able to make an SSH connection to the target environment using the operating system user.
The operating system user must have read and execute privileges on the PostgreSQL binaries installed on the target environment.
The operating system user must have permission to run mount and umount as the superuser via sudo with neither a password nor a TTY. See Sudo Privilege Requirements for PostgreSQL Environments for examples of the /etc/sudoers file on different operating systems.
There must be a directory on the target environment where the Delphix Engine Plugin can be installed (for example, /var/tmp ) with the following properties:
The Plugin directory must be writable by the operating system user mentioned above.
The Plugin directory must have at least 1.5 GB of available storage.
There must be a mount point directory (for example, /mnt) that will be used as the base for mount points that are created when provisioning a VDB with the following properties:
The mount point directory must be writable by the operating system user mentioned above.
The mount point directory should be empty.
TCP/IP connectivity to and from the source environment must be configured as described in General Network and Connectivity Requirements.
Hostname and IP must be correctly set in /etc/hosts file, for example [postgres@source ~]$ hostname -i
The output of “hostname -i” command should produce the correct result as the IP address of the server. For example:
[postgres@source postgres]$ hostname -i
- When using the External Backup method for ingestion the directories being used for keeping the Backup file and WAL file should be accessible by OS User.
- Zip must be installed on the Staging/Target Host for External Backup Support.
- The backup command and File should be in the below format:
PostgreSQL_T<YYYYMMDD>.zip where YYYY denotes Year, MM denotes month, DD denotes date. For example PostgreSQL_T20190314.zip.
The above zip file should contain a Database Full Backup from the Source created in tar format. The below flags can be used with pg_basebackup for taking Source Database backup:
For PostgreSQL versions 9.4.x, 9.5.x, and 9.6.x
<PATH_TO_PG_BIN>/pg_basebackup -p<PORT>-D<EMPTY_BACKUP_DIR>-F t -v -w -P -x
For PostgreSQL versions 10.x and 11.x
<PATH_TO_PG_BIN>/pg_basebackup -p <PORT> -D <EMPTY_BACKUP_DIR> -F t -v -w -P -Xs
If there's no PostgreSQL instance running on the Target host, however, we have a PostgreSQL installation, even then the plugin relies on the "DELPHIX_PG_PATH" variable to discover the environment.
In the absence of "DELPHIX_PG_PATH" variable or if the value of "DELPHIX_PG_PATH" variable is NULL then Linux "find" command will be used for the Environment discovery which may impact the overall performance. Hence, it is preferred to create this Environment Variable with correct entries.
In order to optimize the performance, it is preferred to create an Environment Variable "DELPHIX_PG_PATH" which should be accessible by OS users.
Below should be the syntax for "DELPHIX_PG_PATH" variable:
DELPHIX_PG_PATH="/usr/pgsql-9.6/bin:/var/lib/pgsql/9.6/data;/opt/edb/as9.6/bin:/opt/edb/as9.6/data;/usr/pgsql-9.6/bin:/tmp/TESING/data;"The variable "DELPHIX_PG_PATH" must be available to the environment user in a non-interactive way. You can test this variable for non-interactive logins using
ssh firstname.lastname@example.org "env | grep DELPHIX_PG_PATH".
Adding a PostgreSQL Environment
Make sure that the staging environment in question meets the requirements described in Requirements for PostgreSQL Hosts and Databases
Login to the Delphix Management application.
Next, to Environments, click the Actions (...) menu and select Add Environment.
In the Add Environment dialog, select Unix/Linux.
Select Standalone Host.
Enter Name for the Environment.
Enter the Host IP address or hostname.
Enter the SSH port. The default value is 22.
Enter an OS Username for the Environment which is currently ‘postgres’.
Select Login Type.
— Username and Password - enter the OS username and password
— Username and Public Key - enter the OS username.
— Password Vault - select from an existing Enterprise Password Vault
Using Public Key Authentication
If you want to use public-key authentication for logging into your Unix-based environment, there are two options: use the engine's key pair or provide a key pair for this environment.
To use the engine's key pair:
- Select Public Key for the Login Type.
- Click View Public Key.
- Copy the public key that is displayed, and append it to the end of your
~/.ssh/authorized_keysfile. If this file does not exist, you will need to create it.
Run chmod 600 ~/.ssh/authorized_keys to allow only the file's owner to read and write to it (make sure the file is owned by the user).
Run chmod 755 ~ to restrict access to the user's home directory so no other user may write to it.
Run chmod 700 ~/.ssh so that others cannot write to it. The ~/.ssh directory cannot be writable by group or other users. Otherwise, authentication will fail.
As an alternative, you can provide a key pair specific for this environment via the API or CLI. See Option 2 in this CLI Cookbook article for instructions.
For Password, enter the password associated with the user in step 11.
If you want to use Public Key Encryption for logging into your environment:
a. Select Public Key for the Login Type.
b. Click View Public Key.
c. Copy the public key that is displayed, and append it to the end of your
~/.ssh/authorized_keysfile. If this file does not exist, you will need to create it.
i. Run chmod 600 ~/.ssh/authorized_keys to allow only the file's owner to read and write to it (make sure the file is owned by the user).
ii. Run chmod 755 ~ to restrict access to the user's home directory so no other user may write to it.
iii. Run chmod 700 ~/.ssh so that others cannot write to it. The ~/.ssh directory cannot be writable by group or other users. Otherwise, authentication will fail.
The public key needs to be added only once per user and per environment.
You can also add public key authentication to an environment user's profile by using the command-line interface, as explained in the topic CLI Cookbook: Setting Up SSH Key Authentication for UNIX Environment Users.
For Password Login, click Verify Credentials to test the username and password.
Enter Toolkit Path (make sure Toolkit path does not have spaces).
- To provide your own Oracle Java select the Provide my own JDK checkbox and click Next.
- In the Java Development Kit tab enter the absolute path to your Oracle JDK and click Next.
As the new environment is added, you will see two jobs running in the Delphix platform Job History, one to Create and Discover an environment, and another to create an environment. When the jobs are complete, you will see the new environment added to the list in the Environments tab. If you do not see it, click the Refresh icon in your browser.
18. Once the environment is discovered, further linking would require adding a source config to the above-discovered installation. Please refer to Linking a PostgreSQL dSource for more information.
Linking a PostgreSQL Data Source
The source and staging instances must meet the host requirements
Databases must meet container requirements
For more information refer to Requirements for PostgreSQL Source Hosts and Databases
It is not possible to access the staging server with PostgreSQL 9.4 and PostgreSQL 9.5 versions.
- Login to the Delphix Management application.
- Select Manage > Environments.
- From the Databases tab, select a repository from which you want to create your dSource and click the icon.
- In the Add Database dialog window enter the Name for your source config and click Add.
- Select your source config and click the Add dSource link located to the right.
In the Source tab click on the +Add icon located next to Delphix Initiated Backup Parameter.The Add Button provided for Delphix Initiated Backup/External Backup - Streaming Replication is clickable multiple times but should be used ONLY once since the solution is built only to support a single backup path. Providing Multiple Paths will error out the linking process.
The following fields are mandatory for this process :
- Delphix Initiated Backup Flag - Checkbox should be selected.
- PostgresDB Replication User
- PostgresDB Replication User Password
- Source Host Address
- Source Instance Port Number
- Staging Instance Port Number - must be unique for a given instance
Optionally, database configuration parameters can be defined during the linking operation in the Config Settings section.
After dSource creation, users can now configure the parameters and their values in the
postgresql.conffile through the "Config Settings" section on the Delphix Engine UI.
For example, to support Replication Slots through UI, users can now provide the "primary_slot_name" parameter and its value through the UI. This will update the already existing parameter value in the
postgresql.conffile with the value provided by the user.
Similarly, if a user wants to disable a parameter then they can provide the parameter name and select the check box Comment Property. This will comment out the parameter in the
- In the dSource Configuration tab, enter a dSource name and select a group for your dSource.
Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots. See the topics under Users and Groups for more information.
- In the Data Management tab specify your Staging Environment, User, and Mount Base, then click Next.
- If required apply policy details to the dSource, then click Next.
- Users can provide bash scripts as Plugin defined hooks. These hooks are executed after data ingestion but before snapshot operation, click Next.
- Review the dSource Configuration and Data Management information in the Summary section.
The Delphix Engine initiates two jobs to create the dSource: DB_Link and DB_Sync. You can monitor these jobs by clicking Active Jobs from the top menu bar, or by selecting System > Event Viewer. When the jobs have successfully completed, the database icon will change to a dSource icon on the Environments > Host > Databases screen, and the dSource will also appear in the list of Datasets under its assigned group.
The dSource Configuration Screen
Provisioning a PostgreSQL VDB
You will need to have linked a dSource from a staging instance, as described in Linking a PostgreSQL dSource or have created a VDB from which you want to provision another VDB
You should have set up the POSTGRES target environment with necessary requirements as described in PostgreSQL Support and Requirements
Make sure you have the required Instance Owner permissions on the target instance and environment
The method for Database Permissions for Provisioned PostgreSQL VDBs is decided before the provisioning
- Navigate to Manage, and select Datasets.
- Select a dSource and a snapshot from which you want to provision. Click the provision VDB icon to open the provision VDB wizard.
Select a target environment from the left pane, and an Installation to use from the dropdown list of available PostgreSQL instances on that environment.
Set the Environment User to be the Instance Owner. Note: The picking of instance owner is only possible if you have multiple environment users set on that host.
You will see the Target Configuration section where you need to specify Mount Path.
Optionally, set the database configuration parameters for the VDB.
Users can disable a configuration parameter by selecting the check box Comment Property. This will comment out the parameter in the
On the configuration page enter the PostgreSQL database name as the vFiles Name value.
Select a Target Group for the VDB and click the green Plus icon to add a new group, if necessary.
Select a Snapshot Policy for the VDB then click Next.
- Specify any desired hook operations.
- Review the Provisioning Configuration and Data Management information.
Once the VDB provisioning has successfully completed, if the source and target instance ids are not the same, you may want to grant secadm and dbadm on the database to the target instance id. Please refer to Database Permissions for Provisioned PostgreSQL VDBs for more information.
Congratulations! You have provisioned your first virtual database!
Now, perform some simple functional tests with your application. You can connect your app to the VDB using standard TNS/JDBC techniques. Delphix has already registered the VDB for you on the target listener.
We suggest the following next steps:
- Drop a table and use the VDB Rewind feature to test the recovery of your VDB.
- Take a snapshot of your dSource and refresh your VDB to quickly get fresh production data.
- Provision a new VDB from your VDB to test the sharing data quickly with other teams.
- Mask your new VDB to protect sensitive data. Provision new VDBs from that masked VDB to quickly provide safe data to development and QA teams.