Reply To: How to create database and restore the postgres database dump ?

[2ndnijam]

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 &

• This reply was modified 2 years, 9 months ago by 2ndnijam.