Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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
[postgres@r1 bin]$
To resote database dump:
--connect as template1 database
[postgres@r1 bin]$ ./psql -d template1
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 a owner for postgresclone database
template1=# CREATE USER  clone WITH PASSWORD 'clone';

template1=# ALTER DATABASE postgresclone OWNER TO clone;
--Give the createdb privilege to clone user
template1=# ALTER USER clone CREATEDB;                     
--connect as postgresclone database and check the database objects before importing dump file
CTR+D(logout from pgsql console)
template1=# \q
[postgres@r1 bin]$ pwd

[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


Popular posts from this blog

Script to find sessions that are blocking other sessions in PostgreSQL

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL pgBadger

How to Return a Result Set from a PostgreSQL Stored Procedure