Sau khi install PostgreSQL việc đầu tiên bạn phải làm là tạo ra một database cluster để thực hiện tiếp các truy vấn (SQL) khác. Để tạo một database cluster, ta sử dụng command initdb. Sau đó khởi động PostgreSQL là ta có thể bắt đầu sử dụng PostgreSQL. Bạn có băn khoăn tại sao mình lại có thể thực hiện được truy vấn, mình đang access bằng User nào truy cập vào database nào để thực hiện truy vấn, ... Bài viết này sẽ giới thiệu một số Objects chính trên PostgreSQL, cách tạo ra chúng, và cách nhìn tổng quan về các Objects lưu trữ dữ liệu trên PostgreSQL.
Một database cluster được tạo và khởi động (dựa trên thông tin bạn nhập trên wizard của installer) nếu bạn cài đặt PostgreSQL qua installer trên Windows.
Khái quát về các Objects lưu trữ trên PostgreSQL
Giống như các RDBMS khác, bảng dữ liệu là nơi PostgreSQL thực hiện lưu trữ dữ liệu. Bảng dữ liệu được lưu trữ trong đơn vị lớn hơn là database, các database nằm bên dưới đơn vị lớn nhất của PostgreSQL là database cluster. Như hình minh họa bên dưới ta còn thấy bảng dữ liệu nằm dưới schema và tablespace. Schema là đơn vị tập hợp các bảng (và một số objects khác) theo hình thức logic. Và tablespace là đơn vị của một tập hợp các bảng và indexes theo hình thức vật lý. Xin vui lòng chi xem tiết các Objects lưu trữ như bên dưới.
Tổng quan phân cấp đối tượng trong PostgreSQL.
Các đối tương lưu trữ trong PostgreSQL.
Database cluster
Là đơn vị lưu trữ lớn nhất của một PostgreSQL database server. Database cluster được tạo ra bởi câu lệnh initdb, bao gồm các files config (postgresql.conf, pg_hba.conf, ...), và tất cả các đối tượng lưu trữ đều nằm trong database cluster. Xin vui lòng xem chi tiết trong bài viết Cấu trúc thư mục PostgreSQL.
Có thể cài đặt nhiều PostgreSQL database server trên một hệ điều hành, bằng cách chỉ định các port (thiết lập trong $PGDATA/postgresql.conf) khác nhau cho mỗi server.
Database
Là đơn vị lớn sau Database cluster. Để thực hiện được câu truy vấn, bạn phải truy cập vào một database nào đó. Mặc định sau khi tạo database cluster, PostgreSQL tạo ra 3 database như bên dưới.
$ psql
Timing is on.
psql (10.3)
Type "help" for help.
10300 postgres@postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+---------+-------+-------------------
postgres | bocap | UTF8 | C | C |
template0 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
template1 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
(3 rows)
Tên Database
Mục Đích sử dụng
template0
Là template database. Không thể truy nhập và chỉnh sửa các đối tượng trong đó. Người dùng có thể tạo database mới dựa trên template0 này bằng cách chỉ định TEMPLATE trong câu lệnh "CREATE DATABASE"
template1
Là một template database. Người dùng có thể truy nhập và chỉnh sửa các đối tượng trong đó. Khi thực hiện câu lệnh "CREATE DATABASE", PostgreSQL sẽ copy template1 này để tạo database mới.
postgres
database mặc định của PostgreSQL khi tạo database cluster.
PostgreSQL cũng như các RDBMS khác, để truy nhập một database. Ta cần thông tin: tên database, tên user, password(ở chứng thực trust thì bạn không cần), số port (cho từng database cluster), hostname or IP address.
Để tạo một database ta sử dụng câu lệnh SQL "CREATE DATABASE", hoặc sử dụng createdb từ installer.
Khác với Oracle, PostgreSQL mặc định đối tượng câu lệnh SQL là chữ thường. Nếu muốn chữ in hoa vui lòng viết đối tượng trong dấu "".
10300 postgres@postgres=# CREATE DATABASE testdb TEMPLATE template0;
CREATE DATABASE
Time: 4941.826 ms (00:04.942)
10300 postgres@postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
postgres | bocap | UTF8 | C | C |
template0 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
template1 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
testdb | postgres | UTF8 | C | C |
(4 rows)
10300 postgres@postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
10300 postgres@testdb=# \d
Did not find any relations.
10300 postgres@testdb=# \c postgres
You are now connected to database "postgres" as user "postgres".
10300 postgres@postgres=# DROP DATABASE testdb ;
DROP DATABASE
Để hiện thị thông tin của database trên psql mode, ta sử dụng meta command \l hay \l+ (chi tiết). Muốn access vào database nào đó ta sử dụng meta command "\c database".
Tạo database từ câu lệnh createdb
Cú pháp
$ createdb --help
createdb creates a PostgreSQL database.
Usage:
createdb [OPTION]... [DBNAME] [DESCRIPTION]
Options:
-D, --tablespace=TABLESPACE default tablespace for the database
-e, --echo show the commands being sent to the server
-E, --encoding=ENCODING encoding for the database
-l, --locale=LOCALE locale settings for the database
--lc-collate=LOCALE LC_COLLATE setting for the database
--lc-ctype=LOCALE LC_CTYPE setting for the database
-O, --owner=OWNER database user to own the new database
-T, --template=TEMPLATE template database to copy
-V, --version output version information, then exit
-?, --help show this help, then exit
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port
-U, --username=USERNAME user name to connect as
-w, --no-password never prompt for password
-W, --password force password prompt
--maintenance-db=DBNAME alternate maintenance database
By default, a database with the same name as the current user is created.
Ví dụ
[ ~]$ createdb --template=template0 testdb
[ ~]$ psql -l
Timing is on.
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-------------------
postgres | bocap | UTF8 | C | C |
template0 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
template1 | bocap | UTF8 | C | C | =c/bocap +
| | | | | bocap=CTc/bocap
testdb | postgres | UTF8 | C | C |
(4 rows)
[ ~]$ dropdb testdb
[ ~]$
Schema
Là đơn vị lưu trữ bên dưới database, quản lý dữ liệu dưới dạng logic. Mặc định trong mỗi database có một schema cho người sử dụng, đó là schema public. Ta có thể tạo schema bằng câu lệnh "CREATE SCHEMA ". Đặc điểm của 1 schema như bên dưới.
Có thể sử dụng tên trùng với schema ở database khác but không trùng tên trên cùng database.
Ngoài TABLESPACE và user ra, schema có thể chứa hầu hết các đối tượng còn lại (như table, index, sequence, constraint...)
để truy cập schema ta có thể thêm tên schema vào phía trước đối tượng muốn truy cập hoặc sử dụng tham số search_path để thay đổi schema truy cập hiện tại.
Schema có thể sử dụng với các mục đích như tăng cường security, quản lý dữ liệu dễ dàng hơn.
Khác với Oracle, Schema của PostgreSQL không có liên quan tới database User.
Để tạo schema, ta phải truy cập vào một database nào đó rồi thực hiện lệnh "CREATE SCHEMA " để tạo.
Cú pháp
10300 postgres@postgres=# \h CREATE SCHEMA
Command: CREATE SCHEMA
Description: define a new schema
Syntax:
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION role_specification
where role_specification can be:
user_name
| CURRENT_USER
| SESSION_USER
Ví dụ:
10300 postgres@postgres=# \dn
List of schemas
Name | Owner
--------+----------
public | postgres
(1 row)
10300 postgres@postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
10300 postgres@postgres=# create schema testscm;
CREATE SCHEMA
10300 postgres@postgres=# create table testscm.testtbl();
CREATE TABLE
10300 postgres@postgres=# \d
Did not find any relations.
10300 postgres@postgres=# set search_path to testscm ;
SET
10300 postgres@postgres=# \d
List of relations
Schema | Name | Type | Owner
---------+---------+-------+----------
testscm | testtbl | table | postgres
(1 row)
10300 postgres@postgres=# \dn
List of schemas
Name | Owner
---------+----------
public | postgres
testscm | postgres
(2 rows)
10300 postgres@postgres=#
TABLESPACE
Là đơn vị lưu trữ dữ liệu về phương diện vật lý bên dưới database. Thông thường dữ liệu vật lý được lưu trữ tại thư mục dữ liệu (nơi ta chỉ định lúc ta tạo database cluster initdb -D). Nhưng có một phương pháp lưu trữ dữ liệu ngoài phân vùng này, nhờ sử dụng chức năng TABLESPACE. Khi tạo một TABLESPACE tức là ta đã tạo ra một vùng lưu trữ dữ liệu mới độc lập với dữ liệu bên dưới thư mục dữ liệu. Điều này giảm thiểu được disk I/O cho phân vùng thư mục dữ liệu (nếu trong các hệ thống cấu hình RAID, hay hệ thống có 1 đĩa cứng thì không có hiệu quả).
PostgreSQL tạo một symblic link có tên là oid (Object ID) của tablespace vừa được tạo ở bên dưới pg_tblspc của database cluster. Trỏ tới vùng dữ liệu được chỉ định (LOCATION) trong lúc tạo tablespace.
Để chuyển đổi qua các tablespace để lưu trữ dữ liệu, ta chỉ định thông qua parameter default_tablespace trước khi tạo đối tượng (bảng hoặc index). Ngoài ra đối với bảng tạm thời (temporary table) và index của nó, để chuyển đổi vùng lưu trữ ta thiết lập tablespace cho tham số temp_tablespaces trước khi tạo đối tượng. default_tablespace và temp_tablespaces đều có giá trị mặc định là không thiết lập, tức là sử dụng tablespace mặc định bên dưới database cluster.
ví dụ trên sử dụng meta command ! của psql, meta command này thực hiện các lệnh shell bên ngoài.
Bảng (table)
Bảng là đối tượng lưu trữ dữ liệu từ người dùng. Một bảng bao gồm 0 hoặc nhiều cột (column) tương ứng với từng kiểu dữ liệu khác nhau của PostgreSQL. Tổng quan có 3 kiểu tables mà PostgreSQL support, đó là
unlogged table: là kiểu table mà các thao tác đối với bảng dữ liệu này không được lưu trữ vào WAL. Tức là không có khả năng phục hồi nếu bị corrupt.
temporary table: là kiểu table chỉ được tạo trong phiên làm việc đó. Khi connection bị ngắt, nó sẽ tự động mất đi.
table thông thường: Khác với 2 kiểu table trên, là loại table thông thường để lưu trữ dữ liệu. Có khả năng phục hồi khi bị corrupt và tồn tại vĩnh viễn nếu không có thao tác xóa bỏ nào.
PostgreSQL support nhiều kiểu dữ liệu, bao gồm cả kiểu dữ liệu do người dùng định nghĩa. Để biết thêm chi tiết vui lòng thao khảo ở đây
Cú pháp tạo
postgres=# \h create table
Command: CREATE TABLE
Description: define a new table
Syntax:
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [
{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE source_table [ like_option ... ] }
[, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
OF type_name [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ]
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
PARTITION OF parent_table [ (
{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]
| table_constraint }
[, ... ]
) ] FOR VALUES partition_bound_spec
[ PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]
where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
CHECK ( expression ) [ NO INHERIT ] |
DEFAULT default_expr |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |
UNIQUE index_parameters |
PRIMARY KEY index_parameters |
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and table_constraint is:
[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
UNIQUE ( column_name [, ... ] ) index_parameters |
PRIMARY KEY ( column_name [, ... ] ) index_parameters |
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
and like_option is:
{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }
and partition_bound_spec is:
IN ( { numeric_literal | string_literal | NULL } [, ...] ) |
FROM ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
TO ( { numeric_literal | string_literal | MINVALUE | MAXVALUE } [, ...] )
index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]
exclude_element in an EXCLUDE constraint is:
{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
Ví dụ:
postgres=#
postgres=# create unlogged table test_unlog_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create temporary table test_temp_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# create table test_nomarl_tbl as select generate_series(1,10) as id;
SELECT 10
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------+-----------------+-------+----------
pg_temp_3 | test_temp_tbl | table | postgres
public | test_nomarl_tbl | table | postgres
public | test_unlog_tbl | table | postgres
(3 rows)
postgres=# \q
[postgres@localhost ~]$ psql
psql (10.3)
Type "help" for help.
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | test_nomarl_tbl | table | postgres
public | test_unlog_tbl | table | postgres
(2 rows)
postgres=#
index
Index là đối tượng chỉ mục của một cột nào đó trong bảng, đối tượng này đã được sắp xếp theo một trình tự nào đó khi được sử dụng có thể làm tăng khả năng tìm kiếm. PostgreSQL hiện tại(tới phiên bản 10) support 6 loại index btree, hash, gist, spgist, gin, and brin. Thường thì btree và hash là 2 loại index được sử dụng rộng rãi. Các index còn lại được sử dụng cho từng mục đích khác nhau, ví dụ gist và spgist cho kiểu dữ liệu liên quan tới vị trí địa lý, gin cho kiểu dữ liệu text tìm kiếm với dữ liệu lớn, brin được thiết kế để xử lý các bảng lớn, trong đó các cột nhất định có một số tương quan tự nhiên với vị trí thực của chúng trong bảng.
Cú pháp tạo
postgres=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
postgres=#
Ví dụ
postgres=# create index test_nomarl_tbl_idx on test_nomarl_tbl using btree (id);
CREATE INDEX
postgres=# create index test_nomarl_tbl_hash_idx on test_nomarl_tbl using hash (id);
CREATE INDEX
postgres=# \d test_nomarl_tbl
Table "public.test_nomarl_tbl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
Indexes:
"test_nomarl_tbl_hash_idx" hash (id)
"test_nomarl_tbl_idx" btree (id)
Tablespace: "testtps"
postgres=#
=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, ms sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
