Pivotal Knowledge Base

Follow

How to Create copy of a database without backup and restore.

Goal

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 wants 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 : This procedure only works if you want to create copy of the database within the cluster and not with databases on a different greenplum cluster.

Solution

To create a replica / copy of the existing database , you can use the below command.

create database <new_database_name> template <old_database_name>;

Example

Lets take a example to explain if this actually copy's every content from the original database.

  • On our original database ( testdb ) i have the below sets of objects with below number of rows.
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 a 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=# 

Related articles

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