How to restore the postgresql database dump at real time production environment ?

• Before creating database you have to create tablespace and then you have to give right tablespace name for  database Creation
• And before creating tablespace you have to check with partion or disk space availability it’s a prerequest for Postgresql tablespace creation
• Here given Following steps for creating tablespace & Database in Postgresql environment system.
• You have to follow given steps only for Postgresql database creation & tablespace Creation.
• Here am performed  simple steps of Postgresql Database restoring to some other new server & before that what prerequest want to follow for the postgresql Database restoring &  Everything am mentioned here.

Checking the postgreSQL server status Before restoring :

cd C:\Program Files\edb\enterprincedb\as9.6\bin pg_ctl -D "C:\Program Files\edb\enterprincedb\as9.6\data" status

Connecting the database :

cd C:\Program Files\edb\enterprincedb\as9.6\bin
psql -U enterprisedb -d edb

Checking the tablesapce & database whether already exist with same name in our server :
FOR TABLESPACE :-

SELECT spcname FROM pg_tablespace;

The psql program’s \db meta-command is also useful for listing the existing tablespaces.

FOR DATABASE :-

SELECT datname FROM pg_database;

The psql program’s \l meta-command is also useful for listing the existing databse.

Creating tablespace :

CREATE TABLESPACE "HealthCraft_LC" LOCATION 'D:\HealthCraft_LC';
CREATE TABLESPACE "HealthCraft_DC" LOCATION 'D:\HealthCraft_DC';

Creating Database :

CREATE DATABASE "HealthCraft_LC" TABLESPACE "HealthCraft_LC";
CREATE DATABASE "HealthCraft_DC" TABLESPACE "HealthCraft_DC";

Before Restoring Database you have to restore the roles other wise you will get roles does not exist error

Taking postgres Roles  only backup :

pg_dumpall -h localhost -p 5432 U enterprisedb -v --roles-only -f "/path/to/Preprod_roles_01012019.sql"

Taking Databases backup :

pg_dump -d HealthCraft_LC -U enterprisedb -f "D:\HealthCraft_LC_preprod_01012019.sql"
pg_dump -d HealthCraft_DC -U enterprisedb -f "D:\HealthCraft_DC_preprod_01012019.sql"

Restoring the database roles it is the prerequest :

psql -U enterprisedb  -d edb -f "D:\tutorialdba\Preprod_roles_01012019.sql"

Restoring the postgresql database dump by using psql utility otherwise if your dump is custom format means you have to use pg_restore utility for database restoration :

psql -U enterprisedb  -d "HealthCraft_LC" -f "D:\tutorialdba\HealthCraft_LC_preprod_01012019.sql"
psql -U enterprisedb  -d "HealthCraft_DC" -f "D:\tutorialdba\HealthCraft_DC_preprod_01012019.sql"

else you can use \i at postgres SQL console (if dump is plain format):

psql -U enterprisedb -d HealthCraft_LC
\i D:\HealthCraft_LC_preprod_01012019.sql

Connect the other(HealthCraft_DC) DB the restore the appropriate dump  of HealthCraft_DC database

\c HealthCraft_DC
\i D:\HealthCraft_DC_preprod_01012019.sql

To Dropping the postgres database  :

drop DATABASE "HealthCraft_LC";
ERROR:  database "HealthCraft_LC" is being accessed by other users
DETAIL:  There are 2 other sessions using the database.

First of all you have to kill the connected session by using pg_terminate_backend

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'HealthCraft_LC' AND pid <> pg_backend_pid();
pg_terminate_backend
----------------------
t
t
(2 rows)

after kill the session you can drop the database by using drop command

drop DATABASE "HealthCraft_LC";

Some time session will be connected automatically again and again  at the time you have to issue both command without time delay.

for example 

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'HealthCraft_LC' AND pid <> pg_backend_pid();
drop DATABASE "HealthCraft_LC";
## copy this above two lines paste it to sql prompt

Running the script at background

Windows Server :

START "" psql -U enterprisedb  -d "HealthCraft_LC" -f "D:\HealthCraft_LC_preprod_01012019.sql"

Linux Server :

./psql -U enterprisedb  -d "HealthCraft_LC" -f D:\HealthCraft_LC_preprod_01012019.sql &