What is the pre request for Database creation and tablespace Creation as well as postgresql restoration to new server or database refreshment .
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 &
