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
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

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL