How to make a copy of greenplum database without backup and restore

Post date: Jan 09, 2015 12:45:45 AM

Here is the quick way to make a duplicate copy of your database in Greenplum

[gpadmin@sachi ~]$ psql -d sachi

psql (8.2.15)

Type "help" for help.

sachi=# \l+

                                             List of databases

   Name    |  Owner  | Encoding |   Access privileges    |  Size   | Tablespace |        Description        

-----------+---------+----------+------------------------+---------+------------+---------------------------

 bd_dev    | gpadmin | UTF8     | =Tc/gpadmin            | 29 MB   | pg_default | 

                                : gpadmin=CTc/gpadmin                             

                                : w8xpb=c/gpadmin                                 

                                : bdacc_capsvc=c/gpadmin                          

                                : tev_bdasvc=c/gpadmin                            

 bd_prd    | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 bd_tst    | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 gpadmin   | gpadmin | UTF8     |                        | 29 MB   | pg_default | 

 gpperfmon | gpadmin | UTF8     | gpadmin=CTc/gpadmin    | 284 MB  | pg_default | 

                                : =c/gpadmin                                      

 himanshu  | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 postgres  | gpadmin | UTF8     |                        | 29 MB   | pg_default | 

 sachi     | gpadmin | UTF8     | =Tc/gpadmin            | 5453 MB | pg_default | 

                                : gpadmin=CTc/gpadmin                             

                                : sachi=CTc/gpadmin                               

                                : gpuser=CTc/gpadmin                              

 template0 | gpadmin | UTF8     | =c/gpadmin             | 27 MB   | pg_default | 

                                : gpadmin=CTc/gpadmin                             

 template1 | gpadmin | UTF8     | =c/gpadmin             | 28 MB   | pg_default | Default template database

                                : gpadmin=CTc/gpadmin                             

(10 rows)

sachi=# create database copyofsachi template sachi;

ERROR:  source database "sachi" is being accessed by other users

sachi=# \q

[gpadmin@sachi ~]$ psql -d template1

psql (8.2.15)

Type "help" for help.

template1=# create database copyofsachi template sachi;

CREATE DATABASE

template1=# sachi=# \l+

                                              List of databases

    Name     |  Owner  | Encoding |   Access privileges    |  Size   | Tablespace |        Description        

-------------+---------+----------+------------------------+---------+------------+---------------------------

 bd_dev      | gpadmin | UTF8     | =Tc/gpadmin            | 29 MB   | pg_default | 

                                  : gpadmin=CTc/gpadmin                             

                                  : w8xpb=c/gpadmin                                 

                                  : bdacc_capsvc=c/gpadmin                          

                                  : tev_bdasvc=c/gpadmin                            

 bd_prd      | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 bd_tst      | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 copyofsachi | gpadmin | UTF8     |                        | 5452 MB | pg_default | 

 gpadmin     | gpadmin | UTF8     |                        | 29 MB   | pg_default | 

 gpperfmon   | gpadmin | UTF8     | gpadmin=CTc/gpadmin    | 284 MB  | pg_default | 

                                  : =c/gpadmin                                      

 himanshu    | gpadmin | UTF8     |                        | 28 MB   | pg_default | 

 postgres    | gpadmin | UTF8     |                        | 29 MB   | pg_default | 

 sachi       | gpadmin | UTF8     | =Tc/gpadmin            | 5453 MB | pg_default | 

                                  : gpadmin=CTc/gpadmin                             

                                  : sachi=CTc/gpadmin                               

                                  : gpuser=CTc/gpadmin                              

 template0   | gpadmin | UTF8     | =c/gpadmin             | 27 MB   | pg_default | 

                                  : gpadmin=CTc/gpadmin                             

 template1   | gpadmin | UTF8     | =c/gpadmin             | 28 MB   | pg_default | Default template database

                                  : gpadmin=CTc/gpadmin                             

(11 rows)

template1-#