Deployment requires two existing PostgreSQL databases running on ONTAP storage, one as primary DB server and the other as the clone DB server. For reference on PostgreSQL database deployment on ONTAP, referred to TR-4956: Automated PostgreSQL High Availability Deployment and Disaster Recovery in AWS FSx/EC2, looking for the PostgreSQL automated deployment playbook on primary instance.

Provision a Windows server to run the NetApp SnapCenter UI tool with the latest version. Refer to the following link for details: Install the SnapCenter Server.

From SnapCenter Windows server, download and install latest java JDK from Get Java for desktop applications. Turn off Windows firewall.

From SnapCenter Windows server, download and install or update SnapCenter 6.0 Windows prerequisites: PowerShell - PowerShell-7.4.3-win-x64.msi and .Net hosting package - dotnet-hosting-8.0.6-win.

From SnapCenter Windows server, download and install latest version (currently 6.0) of SnapCenter installation executable from NetApp support site: NetApp | Support.

From database DB VMs, enable ssh passwordless authentication for administrator user admin and its sudo privileges without password.

From database DB VMs, stop and disable Linux firewall demon. Install java-11-openjdk.

From SnapCenter Windows server, launch browser to login to SnapCenter with Windows local administrator user or domain user credential via port 8146.

Review Get Started online menu.

In Settings-Global Settings, check Hypervisor Settings and click on Update.

If needed, adjust Session Timeout for SnapCenter UI to the desired interval.

Add additional users to SnapCenter if needed.

The Roles tab list the built-in roles that can be assigned to different SnapCenter users. Custom roles also can be created by admin user with desired privileges.

From Settings-Credential, create credentials for SnapCenter management targets. In this demo use case, they are linux user admin for login to DB server VM and postgres credential for PostgreSQL access.

Reset PostgreSQL user postgres password before creating the credential.

From Storage Systems tab, add ONTAP cluster with ONTAP cluster admin credential. For Azure NetApp Files, you will need to create a specific credential for capacity pool access.

From Hosts tab, add PostgreSQL DB VMs, which installs SnapCenter plugin for PostgreSQL on Linux.

Once host plugin is installed on DB server VM, databases on the host are auto-discovered and visible in Resources tab.

Login to PostgreSQL as postgres user. Create, then drop a test table.

postgres=# \dt
Did not find any relations.


postgres=# create table test (id integer, dt timestamp, event varchar(100));
CREATE TABLE
postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

postgres=# insert into test values (1, now(), 'test PostgreSQL point in time recovery with SnapCenter');
INSERT 0 1

postgres=# select * from test;
 id |             dt             |                         event
----+----------------------------+--------------------------------------------------------
  1 | 2024-10-08 17:55:41.657728 | test PostgreSQL point in time recovery with SnapCenter
(1 row)

postgres=# drop table test;
DROP TABLE
postgres=# \dt
Did not find any relations.

postgres=# select current_time;
    current_time
--------------------
 17:59:20.984144+00

From Resources tab, open the database backup page. Select the SnapShot backup to be restored. Then, click on Restore button to launch database recovery workflow. Note the timestamp of the backup when performing a point-in-time recovery.

Select Restore scope. AT this moment, a complete resource is only option.

For Recovery Scope, choose Recover to point in time and input the timestamp that recovery is rolled forward up to.

The PreOps allows execution of scripts against database before restore/recovery operation or just leave it black.

The PostOps allows execution of scripts against database after restore/recovery operation or just leave it black.

Notification via email if desired.

Review job summary and Finish to start restore job.

Click on running job to open Job Details window. The job status can also be opened and viewed from the Monitor tab.

Login to PostgreSQL as postgres user and validate that the test table has been recovered.

[postgres@psql01 ~]$ psql
psql (14.13)
Type "help" for help.

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | test | table | postgres
(1 row)

postgres=# select * from test;
 id |             dt             |                         event
----+----------------------------+--------------------------------------------------------
  1 | 2024-10-08 17:55:41.657728 | test PostgreSQL point in time recovery with SnapCenter
(1 row)

postgres=# select now();
              now
-------------------------------
 2024-10-08 18:22:33.767208+00
(1 row)

To validate the clone process. Again, insert a row in the test table. Then run a backup to capture the test data.

postgres=# insert into test values (2, now(), 'test PostgreSQL clone to a different DB server host');
INSERT 0 1
postgres=# select * from test;
 id |             dt             |                        event
----+----------------------------+-----------------------------------------------------
  2 | 2024-10-11 20:15:04.252868 | test PostgreSQL clone to a different DB server host
(1 row)

From Resources tab, open the database cluster backup page. Choose the snapshot of database backup that contains the test data. Then, click on clone button to launch database clone workflow.

Select a different DB server host other than the source DB server. Choose an unused TCP port 543x on the target host.

Input any scripts to execute before or after clone operation.

Notification via email if desired.

Review summary and Finish to launch clone process.

Click on running job to open Job Details window. The job status can also be opened and viewed from the Monitor tab.

Cloned database registers with SnapCenter immediately.

Validate cloned database cluster on target DB server host.

[postgres@psql01 ~]$ psql -d postgres -h 10.61.186.7 -U postgres -p 5433
Password for user postgres:
psql (14.13)
Type "help" for help.

postgres=# select * from test;
 id |             dt             |                        event
----+----------------------------+-----------------------------------------------------
  2 | 2024-10-11 20:15:04.252868 | test PostgreSQL clone to a different DB server host
(1 row)

postgres=# select pg_read_file('/etc/hostname') as hostname;
 hostname
----------
 psql02  +

(1 row)