There must be an installation of PostgreSQL binaries/RPMs on the staging environment/server that is compatible with the installation of PostgreSQL on the source environment. A single Postgres server can only listen on a single port. Here Postgres server is referred to Postgres Cluster. In simple words, consider Postgres cluster as multi-tenant database which is running on a port(say 5432, it can be of your choice as well) and there are databases(tenants or individual databases) running under that cluster(multi-tenant) using the common PORT . If end user wants to connect to individual database, he/she has to use the port which Postgres cluster is using.
Now coming to query that you asked - Dsource's Postgres Data files, config files etc. will reside on Delphix engine storage.
Note - Dsource & VDB will always use Delphix engine storage.
It is always recommended that dsource should be on a different server than source.
We also support mixed-version within the same MAJOR version of Postgres like source is on 12.7, staging(dsource) can be on 12.8 and Target(VDB) can be on 12.9
Mixed version support helps customers in any ways like testing the upgrade on VDB before applying it on source side. Testing new features and many more.
Major version of PostgreSQL should match between Source, Staging & Target environment.
For example, in the version number 10.1, the 10 is the major version number and the 1 is the minor version number, meaning this would be the first minor release of the major release 10. For releases before PostgreSQL version 10.0, version numbers consist of three numbers, for example, 9.5.3. In those cases, the major version consists of the first two digit groups of the version number, e.g., 9.5, and the minor version is the third number, e.g., 3, meaning this would be the third minor release of the major release 9.5.
------------------------------
Prabhjot Singh
Development Engineer
Delphix
------------------------------
Original Message:
Sent: 12-17-2021 11:30:40 PM
From: Rajat Solanki
Subject: Virtualization for Postgres
Hi Prabhjot,
Thank you for the detailed explanation.
I just have one follow-up question. In the three different ways provided, it is mentioned that replication will be required between Source and dsource/staging.
I would like to understand if "dsource/staging" is another server with a physical Postgres database or is it a file residing inside delphix engine (and hence another physical postgres db is not required).
Regards,
------------------------------
Rajat Solanki
TCS
Delphix Community Members
Original Message:
Sent: 12-17-2021 11:59:11 AM
From: Prabhjot Singh
Subject: Virtualization for Postgres
WAL log(Transaction Log) is simply a record of all changes(insert, delete, update, DDL etc.) that have been made in the database and is stored on disk. Treat WAL log as archive log(Archive log is offline copy of redo log, used in oracle database terminology). WAL prevents data loss since the WAL log can be used to restore the proper state of the database.
It is not necessary that WAL logs are generated only when a user sets up replication between source and target. WAL logs are used to increase resilience and restore data after crash.
My understanding says PostgreSQL doesn't support generating no WAL. I mean we can't disable WAL generation. However, we can disable logging for user custom tables. so that if end user performs any DML/DDL operation on those tables, the transaction logs will not be written to WAL logs.
We have 3 different ways of ingesting postgres data into Delphix engine.
- Delphix engine will do all the stuff, initiate the backup on source, setup streaming replication between source/sender & dsource/staging. It is real time replication.
- Customer provides the backup(which happens usually when SOURCE DB size is quite in TBs, so customer takes backup in advance before trigger dsource workflow for the first time), DE setup the replication using that backup. It is similar to approach number 1. It is real world/live replication.
- Customer provides the backup, Here WAL logs(archive logs) are copied by customer from source to staging(manually, one can use hooks as well to copy). WAL logs are only applied to staging dsource when they are made available on staging server side. It is not a live streaming replication as option1 & 2. It is kind of cold replication.
We only support pg_basebackup backup strategy(at this moment). All the above methods take backup leveraging pg_basebackup utility.
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 source side/site that has replication, and login privileges.
https://docs.delphix.com/docs/datasets/postgresql-environments-and-data-sources/quick-start-guide-for-postgresql
------------------------------
Prabhjot Singh
Development Engineer
Delphix
Original Message:
Sent: 12-17-2021 03:52:39 AM
From: Rajat Solanki
Subject: Virtualization for Postgres
Hi Team,
We have a requirement to implement Delphix for Postgres Database. As per the documentation, Delphix uses WAL logs, which are generated only when we have replication protocol setup between two Postgres DBs.
We feel that having replication protocol and a replica DB of a production database could be risky and resource-consuming. Could you please advise if there is any alternate way to do this, or if our understanding is incorrect ?
Thanks,
------------------------------
Rajat Solanki
TCS
Delphix Community Members
------------------------------