Apache Cloudberry (Incubating) | Apache Cloudberry (Incubating)

To create a new database in Apache Cloudberry, you can either use the CREATE DATABASE SQL command in the psql client, or use the createdb utility. The createdb utility is a wrapper around the CREATE DATABASE command.

Quick-start operations

In the following operations, you will be guided to create a new database using the createdb utility, to create a schema, and to set search path for schemas. You will also learn how to create a user and grant privileges to the user.

Before moving on to the operations, make sure that you have completed the previous tutorial Lesson 1: Create Users and Roles. You will connect to the tutorial database as the user lily with password set up in the previous tutorial.

Create database

Log into Apache Cloudberry in Docker. Before creating the tutorial database, make sure that this database does not exist.
```
[gpadmin@mdw ~]$ dropdb tutorial
```
Output:
```
dropdb: error: database removal failed: ERROR:  database "tutorial" does not exist
```

Create the tutorial database using the createdb utility.

[gpadmin@mdw ~]$ createdb tutorial

[gpadmin@mdw ~]$ psql -l  # Verifies that this database has been created.
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 gpadmin   | gpadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | gpadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | gpadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/gpadmin         +
           |         |          |             |             | gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/gpadmin         +
           |         |          |             |             | gpadmin=CTc/gpadmin
 tutorial  | gpadmin | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

Info:

Unless you specify a different database, the newly created database is a copy of the template1 database.

Create an entry in the pg_hba.conf configuration file by appending local tutorial lily md5 to /data0/database/master/gpseg-1/pg_hba.conf.
```
[gpadmin@mdw ~]$ echo "local tutorial lily md5" >> /data0/database/master/gpseg-1/pg_hba.conf
```
Info:
- pg_hba.conf is the configuration file for client access control in Apache Cloudberry.
- md5 is the authentication methods, which means that the user needs to enter the password to log in.

Reload the configuration file to populate the change.

[gpadmin@mdw ~]$ gpstop -u

20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Starting gpstop with args: -u
20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Gathering information and validating the environment...
20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Obtaining Cloudberry Coordinator catalog information
20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Obtaining Segment details from coordinator...
20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Cloudberry Version: 'postgres (Apache Cloudberry) 1.0.0 build dev'
20230818:14:18:45:003733 gpstop:mdw:gpadmin-[INFO]:-Signalling all postmaster processes to reload

Connect to the tutorial database as the user lily. You need to enter the password set up in the previous tutorial.

[gpadmin@mdw ~]$ psql -U lily tutorial

Password for user lily:  # changeme
psql (14.4, server 14.4)
Type "help" for help.

tutorial=> \q    -- Exits the database.

Grant database privileges to users

For database users to properly do their works, you need to grant them the minimum permissions required. For example, a user might need SELECT permissions on a table to view data, and need UPDATE, INSERT, or DELETE to modify the data.

In the following operations, the database user lily will require permissions to create and manipulate objects in the tutorial database.

Connect to the tutorial database as gpadmin.

[gpadmin@mdw ~]$ psql -U gpadmin tutorial

Output:

psql (14.4, server 14.4)
Type "help" for help.

Grant lily all privileges on the tutorial database.

tutorial=# GRANT ALL PRIVILEGES ON DATABASE tutorial TO lily;

Output:

GRANT

tutorial=# \q    -- Exits the database.

Create schema and set search path

In this section, you will be guided to create a faa schema and set the search path to make faa the default schema.

Info:

Database schema is a named container for a set of database objects, including tables, data types, and functions. One database can have multiple schemas. Objects in 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 as 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. For example, employee.person is a different table than customer.person. Both tables can be accessed in the same query as long as they are with accordingly schema name.

The database contains a schema search path including a list of schema names. 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.

Connect to the tutorial database as the user lily.

[gpadmin@mdw ~]$ psql -U lily tutorial

Password for user lily:  # changeme

psql (14.4, server 14.4)
Type "help" for help.

Create the faa schema.

tutorial=> DROP SCHEMA IF EXISTS faa CASCADE;
tutorial=> CREATE SCHEMA faa;

Set the search path to faa, public, pg_catalog, and gp_toolkit schemas.

tutorial=> SET SEARCH_PATH TO faa, public, pg_catalog, gp_toolkit;

Output:

SET

Verify that the search path is set correctly.

tutorial=> SHOW search_path;

Output:

search_path
-------------------------------------
faa, public, pg_catalog, gp_toolkit
(1 row)

Associate a search path with the user role lily.

The search path you have set in the previous step is not persistent. You need 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 lily SET search_path TO faa, public, pg_catalog, gp_toolkit;
```
Output:
```
ALTER ROLE
```

What's next

After creating and preparing the database, you can start to create tables in the database. See Lesson 3: Create Tables for more information.

Quick-start operations​

Create database​

Grant database privileges to users​

Create schema and set search path​

What's next​

Quick-start operations

Create database

Grant database privileges to users

Create schema and set search path

What's next