Sơ lược về PostgreSQL

PostgreSQL (hay Postgres) là một hệ quản trị cơ sở dữ liệu quan hệ sử dụng ngôn ngữ SQL. Với nhiều ưu điểm và các tiêu chuẩn phù hợp nên nó đã trở thành một sự lựa chọn phổ biến dành cho các dự án khác nhau từ lớn đến nhỏ. PostgreSQL còn có nhiều tính năng nâng cao hữu ích như reliable transactions (giao dịch đáng tin cậy) và concurrency (xử lý đa tác vụ) không cần read locks.

Đầu tiên, bạn cần chuẩn bị một server Ubuntu 18.04 đã được định cấu hình trước khi bắt đầu. Sau khi đã được server Ubuntu được cấu hình, hãy chắc rằng server của bạn có non-root user với quyền sudo và tường lửa cơ bản.

Cài đặt PostgreSQL trên Ubuntu

Trong các kho lưu trữ mặc định của Ubuntu có chứa các gói Postgres, vì vậy bạn có thể dễ dàng cài đặt các gói này bằng cách sử dụng hệ thống apt packaging.

Vì là lần đầu tiên sử dụng apt trong phiên này, hãy làm mới index gói cục bộ của bạn. Sau đó, cài đặt gói Postgres cùng với gói -contrib để bổ sung một số tiện ích và chức năng:

sudo apt update
sudo apt install postgresql postgresql-contrib

Sau khi phần mềm đã được cài đặt, bạn có thể xem qua nó hoạt động như thế nào và nó có thể khác với các hệ thống quản lý cơ sở dữ liệu tương tự mà đã từng sử dụng.

Sử dụng cơ sở dữ liệu và PostgreSQL Role

Theo mặc định, Postgres sử dụng một khái niệm được gọi là role (vai trò) để xác thực. Về mặt nào đó, chúng tương tự như các account kiểu Unix thông thường, nhưng Postgres không phân biệt giữa user và group thay vào đó nó sử dụng một thuật ngữ linh hoạt hơn như “role”.

Sau khi cài đặt, Postgres được thiết lập để sử dụng ident xác thực, nghĩa là nó liên kết các role của Postgres với account Unix / Linux phù hợp. Nếu có một role tồn tại trong Postgres, một username Unix / Linux cùng tên có thể đăng nhập vào role đó.

Quy trình cài đặt đã tạo một user account được gọi là postgres, nó liên kết với role Postgres mặc định. Để sử dụng Postgres, bạn có thể đăng nhập vào account đó.

Dưới đây là một số cách để sử dụng account này truy cập Postgres:

Chuyển sang postgres account

Chuyển sang account postgres trên server của bạn bằng cách nhập:

sudo -i -u postgres

Sau đó, bạn có thể truy cập Postgres prompt ngay lập tức bằng cách nhập:

psql

Thao tác này sẽ đăng nhập vào PostgreSQ Prompt và từ đây bạn có thể tự do tương tác với hệ quản trị cơ sở dữ liệu ngay lập tức.

Thoát khỏi PostgreSQL prompt bằng cách nhập:

q

Thao tác này sẽ đưa bạn trở lại Command Prompt của postgres Linux .

Truy cập Postgres Prompt mà không chuyển account

Trong ví dụ trên, bạn đã được hướng dẫn để truy cập Postgres Prompt bằng cách chuyển sang user Postgres và chạy psql để mở Postgres Prompt. Ngoài ra, bạn có thể thực hiện trong một bước duy nhất bằng cách chạy lệnh psql khi dùng postgres user với sudo, thực hiện như sau:

sudo -u postgres psql

Với lệnh này bạn có thể đăng nhập trực tiếp vào Postgres mà không có bash shell ở giữa.

Bạn có thể thoát phiên Postgres bằng cách nhập:

q

Trong nhiều trường hợp, cần yêu cầu sử dụng nhiều hơn một role Postgres. Vì vậy, bước kế tiếp ta sẽ cùng tìm hiểu cách định cấu hình chúng.

Tạo một vai trò mới

Hiện tại, bạn chỉ có role postgres được định cấu hình trong database. Bạn có thể tạo thêm các role bằng lệnh createrole. Interactive sẽ hiện lời nhắc về tên của role mới cũng như quyền cấp trên user.

Nếu bạn đã đăng nhập bằng account postgres, bạn có thể tạo user mới bằng cách nhập:

createuser --interactive

Thay vào đó, nếu bạn muốn sử dụng sudo cho mỗi lệnh mà không cần chuyển account bình thường của mình, hãy nhập:

sudo -u postgres createuser --interactive

Các script sẽ nhắc một số lựa chọn và dựa trên câu trả lời của bạn, nó sẽ thực hiện các lệnh Postgres chính xác để tạo user đáp ứng các thông số kỹ thuật của bạn.

Đầu tiên, prompt sẽ yêu cầu bạn tên cho role mới. Ví dụ bên dưới đặt tên cho role là sammy nhưng bạn có thể đặt tên cho role của mình bất cứ thứ gì bạn thích

Output
Enter name of role to add: sammy

Tiếp theo, bạn sẽ được hỏi liệu role mới có phải là một superuser hay không. Trong PostgreSQL, một role superuser có các đặc quyền cực kỳ rộng và chúng có thể bỏ qua gần như tất cả các lần kiểm tra quyền.

Ví dụ dưới chỉ định role sammy là một superuser và vì các role superuser có rất nhiều quyền và quyền kiểm soát đối với cơ sở dữ liệu nên bạn không nên cấp cho các role mới trạng thái superuser:

Output
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Lưu ý rằng bạn chỉ có thể tạo các role superuser mới nếu bạn đang tạo chúng như một role đã là một superuser. Theo mặc định, role postgres là superuser.

Bạn có thể kiểm soát nhiều hơn bằng cách chuyển một flag bổ sung. Kiểm tra các tùy chọn bằng cách xem man page.

man createuser

Việc cài đặt Postgres đã có một role mới, nhưng chưa thêm bất kỳ cơ sở dữ liệu nào. Phần tiếp theo, mô tả quá trình tạo cơ sở dữ liệu.

Tạo cơ sở dữ liệu mới

Theo mặc định, một giả định mà hệ thống xác thực Postgres đưa ra là đối với bất kỳ role nào được sử dụng để đăng nhập, role đó sẽ có một cơ sở dữ liệu có cùng tên mà nó có thể truy cập. Điều này có nghĩa là, nếu user bạn đã tạo trong phần trên có tên là sammy, thì role đó sẽ cố gắng kết nối với cơ sở dữ liệu có tên là “sammy” theo mặc định. Bạn có thể tạo cơ sở dữ liệu thích hợp bằng lệnh createdb.

Nếu bạn đăng nhập bằng account postgres, bạn sẽ nhập:

createdb sammy

Thay vào đó, nếu bạn muốn sử dụng sudo cho mỗi lệnh mà không cần chuyển từ tài khoản thông thường, hãy nhập:

sudo -u postgres createdb sammy

Tính linh hoạt này cung cấp nhiều đường dẫn để tạo cơ sở dữ liệu khi cần thiết.

Mở Postgres Prompt với vai trò mới

Để đăng nhập với xác thực dựa trên ident, bạn cần một user Linux có cùng tên với cơ sở dữ liệu và role Postgres.

Nếu bạn không có sẵn user Linux, bạn có thể tạo một user bằng lệnh adduser. Bạn sẽ phải thực hiện việc này từ account non-root với sudo (nghĩa là không đăng nhập với tư cách user postgres):

sudo adduser sammy

Khi account mới khả dụng, bạn có thể chuyển đổi và kết nối với cơ sở dữ liệu bằng cách nhập:

sudo -i -u sammy
psql

Hoặc, bạn có thể làm điều này nội tuyến:

sudo -u sammy psql

Lệnh này sẽ tự động đăng nhập cho bạn, giả sử rằng tất cả các thành phần đã được định cấu hình đúng. Nếu bạn muốn user của mình kết nối với một cơ sở dữ liệu khác, bạn có thểthực hiện bằng cách chỉ định cơ sở dữ liệu như sau:

psql -d postgres

Sau khi đăng nhập, bạn có thể kiểm tra thông tin kết nối hiện tại bằng cách nhập:

conninfo

Output
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

Nó sẽ hữu ích nếu bạn đang kết nối với cơ sở dữ liệu không mặc định hoặc với user không mặc định.

Tạo và xóa bảng

Bây giờ bạn đã biết cách làm thế nào để kết nối với hệ thống cơ sở dữ liệu PostgreSQL, hãy cùng tìm hiểu thêm một số tác vụ quản lý Postgres cơ bản nhé.

Đầu tiên, tạo bảng để lưu trữ dữ liệu. Ví dụ, bảng mô tả một số thiết bị.

Cú pháp cơ bản cho lệnh này như sau:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

Các lệnh này đặt tên cho bảng, sau đó xác định các cột cũng như kiểu cột và độ dài tối đa của dữ liệu. Bạn cũng có thể tùy chọn thêm các điều kiện cho mỗi cột.

Dưới đây là một ví dụ về cách tạo và quản lý bảng trong Postgres mà bạn có thể tham khảo:

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

Các lệnh này sẽ tạo một bảng kiểm kê thiết bị sân chơi. Đầu tiên là ID thiết bị, thuộc kiểu serial. Kiểu dữ liệu này là một số nguyên tự động tăng dần. Bạn cũng đã cung cấp cho cột này điều kiện của primary key, nghĩa là các giá trị phải là duy nhất và không được rỗng.

Đối với hai trong số các cột (Equi_id và install_date), các lệnh không chỉ định độ dài trường. Do một số loại cột không yêu cầu độ dài thế nên độ dài được quyết định bởi kiểu của cột.

Hai lệnh tiếp theo tạo ra các cột cho loại thiết bị và màu sắc tương ứng, mỗi cột không được để trống (NOT NULL được áp dụng cho mỗi). Dòng sau những dòng này là một cột vị trí và thêm một điều kiện yêu cầu các giá trị của cột này là một trong tám giá trị. Cột cuối cùng là cột ngày ghi lại ngày cài đặt các thiết bị.

Lưu ý rằng trong SQL, mọi câu lệnh phải kết thúc bằng dấu chấm phẩy (;).

Nếu bạn đã nhập đúng thao tác CREATE TABLE, nó sẽ trả về kết quả sau:

Output
CREATE TABLE

Bạn có thể tìm danh sách các bảng trong cơ sở dữ liệu bằng cách gõ:

d

Output
                 List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Bảng ở đây, nhưng có một thứ được gọi là yard_equip_id_seq thuộc kiểu sequence. Đây là đại diện của kiểu serial mà đã cung cấp cho cột. Điều này sẽ giúp theo dõi số tiếp theo trong chuỗi và được tạo tự động cho các cột thuộc loại này.

Nếu bạn muốn trả về bảng không có kiểu sequence, bạn có thể nhập:

dt

Output
         List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Thêm, truy vấn và xóa dữ liệu trong bảng

Sau khi tạo một bảng, bạn có thể thêm một số dữ liệu vào bảng đó.

Ví dụ: thêm một slide và một swing bằng cách gọi bảng bạn muốn thêm, đặt tên cho các cột và sau đó cung cấp dữ liệu cho mỗi cột, như sau:

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Bạn cần lưu ý khi nhập dữ liệu để tránh một số trường hợp treo máy. Không đặt tên cột trong dấu ngoặc kép, nhưng giá trị cột mà bạn nhập cần có dấu ngoặc kép.

Một điều cần lưu ý nữa là bạn không nhập giá trị cho cột Equi_id. do cột này được tạo tự động bất cứ khi nào một hàng mới trong bảng được tạo. Truy xuất thông tin bạn đã thêm bằng cách nhập:

SELECT * FROM playground;

Output
equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

Kết quả này cho biết rằng bạn đã điền thành công và tất cả các dữ liệu khác của bạn đã được sắp xếp chính xác. Nếu slide bị hỏng, bạn phải xóa nó, bạn cũng có thể xóa hàng khỏi bảng của mình bằng cách nhập:

DELETE FROM playground WHERE type="slide";

Truy vấn lại bảng:

SELECT * FROM playground;

Output
equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

Thêm và xóa các cột khỏi bảng

Sau khi tạo bảng, bạn có thể sửa đổi nó để thêm hoặc bớt các cột tương đối dễ dàng. Để thêm một cột last_maint nhập:

ALTER TABLE playground ADD last_maint date;

Lần sau khi bạn xem lại thông tin bảng , cột mới sẽ được thêm vào (nhưng không có dữ liệu nào được nhập vào):

SELECT * FROM playground;

Output
equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

Để xóa một cột, bạn có thể nhập câu lệnh SQL rất giống với câu lệnh sử dụng để thêm cột last_maint. Xóa cột bằng cách nhập:

ALTER TABLE playground DROP last_maint;

Thao tác này sẽ xóa cột last_maint và bất kỳ giá trị nào bên trong nó, giữ nguyên tất cả các dữ liệu khác.

Cập nhật dữ liệu trong bảng

Sau khi bạn đã biết cách thêm dữ liệu vào bảng và cách xóa chúng, tiếp theo hãy cũng tìm hiểu về cách sửa đổi các mục nhập hiện có.

Bạn có thể cập nhật các giá trị của dữ liệu hiện có bằng cách truy vấn bản ghi và đặt cột thành giá trị bạn muốn sử dụng. Bạn có thể truy vấn bản ghi “swing” (nó sẽ cho mọi kết quả swing trong bảng) và thay đổi màu của nó thành “red”.

UPDATE playground SET color="red" WHERE type="swing";

Bạn có thể kiểm tra xem thao tác đã thành công chưa bằng cách truy vấn lại dữ liệu:

SELECT * FROM playground;

Output
equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

Như kết quả này cho biết, trang của bạn hiện đã được đăng ký là red.

Lời kết

Và trên đây là tất cả các bước cài đặt Postgresql trên Ubuntu cũng như các thao tác quản trị cơ bản. Chúc bạn thực hiện thành công!