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