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
--connect as template1 database
after imported check the database objects
--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 databasetemplate1=# 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 databasetemplate1=# CREATE USER clone WITH PASSWORD 'clone';
CREATE ROLE
template1=# ALTER DATABASE postgresclone OWNER TO clone;
ALTER DATABASE
--Give the createdb privilege to clone usertemplate1=# ALTER USER clone CREATEDB;
ALTER ROLE
--connect as postgresclone database and check the database objects before importing dump fileCTR+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.dmpafter imported check the database objects
Comments
Post a Comment