• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • CoWorking/Virtual Office Space Solution
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • CoWorking/Virtual Office Space Solution
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

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

      Home › Forums › PostgreSQL Database › How to create database and restore the postgres database dump ? › Reply To: How to create database and restore the postgres database dump ?

      January 2, 2019 at 5:09 pm #10617
      2ndnijam
      Keymaster

      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, 3 months ago by 2ndnijam.

      Search

      ADVERTISEMENT

      Latest Posts

      Rayafeel Business proposal
      18Apr2021
      24 Best Coworking Spaces in Chennai You Should Know
      13Apr2021
      Easy to get a Virtual Address in Chennai
      12Apr2021
      Easy to get a Virtual Office in Chennai
      12Apr2021
      What is a Virtual Office? And How to get in chennai ?
      11Apr2021

      Latest Courses

      PostgreSQL

      PostgreSQL

      $600.00 $500.00
      Greenplum

      Greenplum

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • please let me know pre requirements to increase work_mem
      • how to copy some data in one table to another table in postgres
      • postgres script for finding queries more than 1 hours ?
      • How to take the tables backup in postgres
      • what are the extensions required to be in instance for performance

      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Contact
      • Our Team
      • Blog

      COURSES

      • List Of Course
      • Become An Instructor
      • Events
      • Postgres Support Blog

      Support

      • DBA Support
      • Consultancy Services
      • Postgres Migration Blogs
      • Forum

      Recommend

      • Groups
      • Login
      • FAQs
      • SignUp

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Login with:

      Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

      Login with your site account

      Lost your password?

      Not a member yet? Register now

      Register a new account

      Are you a member? Login now