Database And Tablespace Creation in PostgreSQL

In this blogs will be helpful to how to create database in Postgresql environment and how to do restore the database and what are the common errors you will be faced during database restoration  example for xlog full , not having enough disk space for restoration , move data to some other disk partition if data is full.

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 tablespace & 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';

Before creating tablespace you have to check the server available space and available partition this is one of the prerequest as well.

While restoring data if you get disk issue or data partition is full means  you have to go through the following blogs

Postgresql data moving some other partition/Disk

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 &