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 thecreatedb
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 appendinglocal 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 userlily
. 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 asgpadmin
.[gpadmin@mdw ~]$ psql -U gpadmin tutorial
Output:
psql (14.4, server 14.4)
Type "help" for help. -
Grant
lily
all privileges on thetutorial
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 theemployee schema
is written asemployee.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 thancustomer.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 userlily
.[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
, andgp_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.
Other tutorials: