How to Create Copy of a Database without Backup and Restore.

Products

• VMware Tanzu Greenplum

Issue/Introduction

In this article, we would be discussing a simple steps on how to take replica/copy of a database within the cluster without backup and restore , it very useful if any developers want a replica of the original database for their development and testing or if you want a copy of the database before making major design change in the application tables resting on the database.

Please note that this procedure only works if you want to create a copy of the database within the cluster and not with databases on a different Greenplum cluster.

 

Environment

Resolution

To create a replica or copy of the existing database, you can use the below command:

create database <new_database_name> template <old_database_name>;

Example

Let's take an example to explain if this actual copy's every content from the original database.

• On our original database (testdb), the below sets of objects are there.

  testdb=# \d
               List of relations
   Schema | Name | Type  |  Owner  | Storage 
  --------+------+-------+---------+---------
   public | a1   | table | gpadmin | heap
   public | a2   | table | gpadmin | heap
   public | a3   | table | gpadmin | heap
  (3 rows)
  
  testdb=# select count(*) from a1;
   count 
  -------
   10000
  (1 row)
  
  Time: 106.450 ms
  testdb=# select count(*) from a2;
   count 
  -------
   10000
  (1 row)
  
  Time: 2.462 ms
  testdb=# select count(*) from a3;
   count 
  -------
   10000
  (1 row)
  
  Time: 1.953 ms
  testdb=#

• So, let's create the copy of the database:

  testdb=# create database replica template testdb;
  CREATE DATABASE
  Time: 6272.622 ms

• Cross verification tells us, indeed the replica database is now an exact copy of the testdb:

  testdb=# \c replica 
  You are now connected to database "replica" as user "gpadmin".
  replica=# \d
               List of relations
   Schema | Name | Type  |  Owner  | Storage 
  --------+------+-------+---------+---------
   public | a1   | table | gpadmin | heap
   public | a2   | table | gpadmin | heap
   public | a3   | table | gpadmin | heap
  (3 rows)
  
  replica=# select count(*) from a1;
   count 
  -------
   10000
  (1 row)
  
  Time: 218.771 ms
  replica=# select count(*) from a2;
   count 
  -------
   10000
  (1 row)
  
  Time: 2.646 ms
  replica=# select count(*) from a3;
   count 
  -------
   10000
  (1 row)
  
  Time: 2.615 ms
  replica=#

Additional Information