• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $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
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $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

      Databases

      • Home
      • All courses
      • Databases
      • Greenplum Database
      CoursesIT & SoftwareDatabasesGreenplum Database
      • Greenplum Tutorial
        9
        • Lecture1.1
          Download the Greenplum Sandbox VM 30 min
        • Lecture1.2
          Greenplum Database Architecture 30 min
        • Lecture1.3
          Greenplum Configuration Files ( pg_hba.conf , postgresql.conf ) 30 min
        • Lecture1.4
          Greenplum Database Installation 30 min
        • Lecture1.5
          Greenplum psql Command Line Client 30 min
        • Lecture1.6
          Greenplum Create Users and Roles 30 min
        • Lecture1.7
          Greenplum Create and Prepare Database 30 min
        • Lecture1.8
          Greenplum Create Tables 30 min
        • Lecture1.9
          Greenplum Data Loading 30 min
      • Greenplum Performance Tuning
        7
        • Lecture2.1
          Greenplum Performance Tuning Overview 30 min
        • Lecture2.2
          Greenplum Analyze the tables 30 min
        • Lecture2.3
          Greenplum explain plans 30 min
        • Lecture2.4
          Greenplum Indexes and performance 30 min
        • Lecture2.5
          Greenplum Row vs. column orientation 30 min
        • Lecture2.6
          Greenplum – Check for even data distribution on segments 30 min
        • Lecture2.7
          Greenplum Partitioning 30 min
      • Greenplum Database Analytics
        1
        • Lecture3.1
          Greenplum Database analytics 30 min
      • Greenplum Backup and Recovery Operations
        2
        • Lecture4.1
          Greenplum gpcrondump 30 min
        • Lecture4.2
          Greenplum gpdbrestore 30 min
      • Importing GPDB Sandbox
        1
        • Lecture5.1
          Importing GPDB Sandbox into VMware Fusion 30 min
      • Greenplum Database Books
        1
        • Lecture6.1
          Data Warehousing with Greenplum PDF 30 min
      • Basic Questions and Answer of Greenplum Database
        1
        • Lecture7.1
          Some basic Questions and answer about Greenplum database 30 min

        Greenplum Create and Prepare Database

        Create a new database with the CREATE DATABASE SQL command in psql or the createdb utility command in a terminal. The new database is a copy of the template1 database, unless you specify a different template. To use the CREATE DATABASE command, you must be connected to a database. With a newly installed Greenplum Database system, you can connect to the template1 database to create your first user database. The createdb utility, entered at a shell prompt, is a wrapper around the CREATE DATABASE command. In this exercise you will drop the tutorial database if it exists and then create it new with the createdb utility.

        Create Database

        1. Enter these commands to drop the tutorial database if it exists:
          $ dropdb tutorial
        2. Enter the createdb command to create the tutorial database, with the defaults:
          $ createdb tutorial
        3. Verify that the database was created using the psql -l command:
          [gpadmin@gpdb-sandbox ~]$ psql -l
                            List of databases
             Name    |  Owner  | Encoding |  Access privileges
          -----------+---------+----------+---------------------
           gpadmin   | gpadmin | UTF8     |
           gpperfmon | gpadmin | UTF8     | gpadmin=CTc/gpadmin
                                          : =c/gpadmin
           postgres  | gpadmin | UTF8     |
           template0 | gpadmin | UTF8     | =c/gpadmin
                                          : gpadmin=CTc/gpadmin
           template1 | gpadmin | UTF8     | =c/gpadmin
                                          : gpadmin=CTc/gpadmin
           tutorial  | gpadmin | UTF8     |
          (6 rows)
        4. Connect to the tutorial database as user1, entering the password you created for user1 when prompted:
          psql -U user1 tutorial

        Grant database privileges to users

        In a production database, you should grant users the minimum permissions required to do their work. For example, a user may need SELECT permissions on a table to view data, but not UPDATE, INSERT, or DELETE to modify the data. To complete the exercises in this guide, the database users will require permissions to create and manipulate objects in the tutorial database.

        1. Connect to the tutorial database as gpadmin.
          $ psql -U gpadmin tutorial
        2. Grant user1 and user2 all privileges on the tutorial database.
          tutorial=# GRANT ALL PRIVILEGES ON DATABASE tutorial TO user1, user2;
        3. Log out of psql and perform the next steps as the user1 role.
          tutorial=# \q

        Create a schema and set a search path

        A database schema is a named container for a set of database objects, including tables, data types, and functions. A database can have multiple schemas. Objects within the schema are referenced by prefixing the object name with the schema name, separated with a period. For example, the person table in the employee schema is written employee.person.

        The schema provides a namespace for the objects it contains. If the database is used for multiple applications, each with its own schema, the same table name can be used in each schema employee.person is a different table than customer.person. Both tables could be accessed in the same query as long as they are qualified with the schema name.

        The database contains a schema search path, which is a list of schemas to search for objects names that are not qualified with a schema name. The first schema in the search path is also the schema where new objects are created when no schema is specified. The default search path is user,public, so by default, each object you create belongs to a schema associated with your login name. In this exercise, you create an faa schema and set the search path so that it is the default schema.

        1. Change to the directory containing the FAA data and scripts:
          $ cd ~/gpdb-sandbox-tutorials/faa
        2. Connect to the tutorial database with psql:
          $ psql -U user1 tutorial
        3. Create the faa schema:
          tutorial=# DROP SCHEMA IF EXISTS faa CASCADE;
          tutorial=# CREATE SCHEMA faa;
        4. Add the faa schema to the search path:
          tutorial=# SET SEARCH_PATH TO faa, public, pg_catalog, gp_toolkit;
        5. View the search path:
          tutorial=# SHOW search_path;
                       search_path
          -------------------------------------
           faa, public, pg_catalog, gp_toolkit
          (1 row)
        6. The search path you set above is not persistent; you have to set it each time you connect to the database. You can associate a search path with the user role by using the ALTER ROLE command, so that each time you connect to the database with that role, the search path is restored:
          tutorial=# ALTER ROLE user1 SET search_path TO faa, public, pg_catalog, gp_toolkit;
        7. Exit out of the psql shell:
        tutorial=# \q

        Prev Greenplum Create Users and Roles
        Next Greenplum Create Tables

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • thought behind whiteboard activity
        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres

        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

        Modal title

        Message modal