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 Installation On Linux

PostgreSQL Sequence

How to configure Replication Manager (repmgr) ?

Postgresql maximum size

PostgreSQL pgBadger