Postgresql.md

Huớng các lệnh của psql

Lợi ích của Database schema

1. To allow many users to use one database without interfering with each other.
2. To organize database objects into logical groups to make them more manageable.
3. Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

Does each microservice really need its own database?

1. Private-tables-per-service – each service owns a set of tables that must only be accessed by that service
2. Schema-per-service – each service has a database schema that’s private to that service
3. Database-server-per-service – each service has it’s own database server.

Using a schema per service is appealing since it makes ownership clearer.

Có thể join dữ liệu từ các bảng thuộc schema khác nhau

SELECT u.username, u.hobby, p.text 
FROM blog.posts AS p JOIN public.users AS u
ON p.author_id = u.id;

Để biết xem một role có thể nhìn thấy những đối tượng nào trong schema

\ds+
hoặc
\d

demo=> \d+
                            List of relations
 Schema |      Name      |   Type   |  Owner   |    Size    | Description
--------+----------------+----------+----------+------------+-------------
 blog   | comment        | table    | postgres | 8192 bytes |
 blog   | comment_id_seq | sequence | postgres | 8192 bytes |
 blog   | posts          | table    | postgres | 16 kB      |
 blog   | posts_id_seq   | sequence | postgres | 8192 bytes |
 public | orders         | table    | postgres | 8192 bytes |
 public | orders_id_seq  | sequence | postgres | 8192 bytes |
 public | users          | table    | postgres | 16 kB      |
 public | users_id_seq   | sequence | postgres | 8192 bytes |
(8 rows)

Chạy lệnh này để gán quyền xem schema cho một role

demo=# GRANT USAGE ON SCHEMA auth TO blog;
GRANT

Chạy lại lệnh \d+ sẽ thấy bảng và sequence của schema auth hiện ra

demo=> \d+
                              List of relations
 Schema |       Name        |   Type   |  Owner   |    Size    | Description
--------+-------------------+----------+----------+------------+-------------
 auth   | membership        | table    | postgres | 16 kB      |
 auth   | membership_id_seq | sequence | postgres | 8192 bytes |
 blog   | comment           | table    | postgres | 8192 bytes |
 blog   | comment_id_seq    | sequence | postgres | 8192 bytes |
 blog   | posts             | table    | postgres | 16 kB      |
 blog   | posts_id_seq      | sequence | postgres | 8192 bytes |
 public | orders            | table    | postgres | 8192 bytes |
 public | orders_id_seq     | sequence | postgres | 8192 bytes |
 public | users             | table    | postgres | 16 kB      |
 public | users_id_seq      | sequence | postgres | 8192 bytes |
(10 rows)