Pivotal Knowledge Base

Follow

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

Environment

Pivotal Greenplum Database (GPDB) all versions

Purpose

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.

Procedure

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 create the copy of the database
testdb=# create database replica template testdb;
CREATE DATABASE
Time: 6272.622 ms
  • Cross verification tell 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

Comments

  • Avatar
    Xavier Bracco

    Not only is this the easiest of replications for databases, but I have been surprised in the past to see how many people, including myself, have asked me the very same question so had to go into the Greenplum manuals which aren't free to find the answer.

Powered by Zendesk