Cloning Postgres Database

Here We are cloning postgres database to the name of postgresclone
--connect as postgres database and check the what are the objects you having using \dt command

template1=> \c postgres
You are now connected to database "postgres" as user "u6".
postgres=> \dt
            List of relations
 Schema |    Name     | Type  |  Owner   
--------+-------------+-------+----------
 benz2  | buy         | table | u2
 benz2  | comment_log | table | u2
 benz2  | contable    | table | u2
 benz2  | demo        | table | u2
 benz2  | dept        | table | postgres
 benz2  | item        | table | u2
 benz2  | mytab       | table | u2
 benz2  | mytab2      | table | u2
 benz2  | orders      | table | u2
 benz2  | practis     | table | u2
 benz2  | prod        | table | u2
 benz2  | product     | table | u2
 benz2  | products    | table | u2
 benz2  | sale        | table | u2
 benz2  | stock       | table | u2
 benz2  | t1          | table | u7
 benz2  | t10         | table | u3
 benz2  | t11         | table | u5
 benz2  | t6          | table | u2
 benz2  | t9_renamed  | table | u2
(20 rows)

create a backup directory and give the permission for accessing postgres user

[postgres@r1 ~]$ mkdir  clonedb
[postgres@r1 ~]$ ll
total 12
drwxrwxr-x 2 postgres postgres 4096 Feb 13 07:44 clonedb
drwx------ 3 postgres postgres 4096 Jan 31 05:57 PG_9.3_201306121
drwx------ 3 postgres postgres 4096 Feb  4 11:29 tbslocation

--Go to the bin directory and take the full backup of postgres database 

[postgres@r1 ~]$ cd /opt/PostgreSQL/9.3/
[postgres@r1 bin]$ ./pg_dump -U postgres -d postgres -f /home/postgres/clonedb/postgresfull.dmp 
Password: 
[postgres@r1 bin]$ 

To resote database dump:
--connect as template1 database

[postgres@r1 bin]$ ./psql -d template1
Password: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

--create postgresclone database

template1=# CREATE DATABASE postgresclone WITH  ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-   8' TEMPLATE template0;
CREATE DATABASE

--create a owner for postgresclone database

template1=# CREATE USER  clone WITH PASSWORD 'clone';
CREATE ROLE

template1=# ALTER DATABASE postgresclone OWNER TO clone;
ALTER DATABASE

--Give the createdb privilege to clone user

template1=# ALTER USER clone CREATEDB;                     
ALTER ROLE

--connect as postgresclone database and check the database objects before importing dump file

CTR+D(logout from pgsql console)
 (or)
template1=# \q
[postgres@r1 bin]$ pwd
/opt/PostgreSQL/9.3/bin

[postgres@r1 bin]$ ./psql -d postgresclone -U clone
Password for user clone: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
postgresclone=> \dt
No relations found.
postgresclone=> 0

--Now import the dump file in bin location

./psql -d postgresclone -f /home/postgres/clonedb/postgresfull.dmp

./psql -d postgresclone -f /home/postgres/clonedb/postgresfull.dmp

after imported check the database objects