Skip to content

Create Database Objects

1. Create Database

  • 1.1 Create Database Folder（Change ZSITMCP1 to your actual SID)
su - postgres
mkdir -p /pgdata/ZSITMCP1_tbs/wxalert
chmod 700 /pgdata/ZSITMCP1_tbs/
ll -d /pgdata/ZSITMCP1_tbs/

image-20260308120655774

  • 1.2 Create tablespace（Change SID and DB)
psql -p 5432

create tablespace "wxalert"
owner postgres
location '/pgdata/ZSITMCP1_tbs/wxalert';
\db

Notiche: You must change SID and DB what you need

image-20260308121401659

  • 1.3 Create database and revoke public schema permission（Change SEQAS to your actual DB name)
create database "wxalert"
with
owner = postgres
encoding = 'UTF8'
lc_collate = 'en_US.UTF-8'
lc_ctype = 'en_US.UTF-8'
tablespace = "wxalert"
connection limit = -1;

revoke all on schema public from public;

\l

image-20260308121651842

2. Create User and schema

  • 2.1 Create User
create user "wxmp" with password 'password';
\du

Users are global, and each instance can only have one account with the same name. Can Creating on all DB

image-20260308122113501

  • 2.2 Create Schema
\c wxalert
create schema "wxmp" authorization "wxmp";
grant all on schema "wxmp" to "wxmp";
\dn

image-20260308122645249

  • 2.3 Create readonly User
create user "usernamereadonly" WITH PASSWORD 'passwd';
grant USAGE ON SCHEMA "schemaname" TO "usernamereadonly";  ###Must exist
grant SELECT ON SCHEMA "schemaname" TO "usernamereadonly";
grant SELECT ON "schemaname".TABLENAME TO "usernamereadonly";
\du

The schema is local, and different DBs can have multiple schemas with the same name. Creating them requires switching to the corresponding DB

Notiche: Create readonly user must be chose one If the Role&Schema name are not consistent, you may need to set search_path Example: Database Level： DB=# ALTER ROLE Username IN DATABASE "Databasename" SET search_path TO "schemaname, public";

Cluster Level： DB=# ALTER ROLE Username SET search_path TO "schemaname, public";