Tạo mới và quản trị các thuộc tính của database Postgresql

Có nhiều cách để tạo mới 1 database trong Postgresql như sử dụng psql, pgAdmin…dưới đây là ví dụ

Tạo mới database:

CREATE DATABASE database_name
WITH
[OWNER = role_name]
[TEMPLATE = template]
[ENCODING = encoding]
[LC_COLLATE = collate]
[LC_CTYPE = ctype]
[TABLESPACE = tablespace_name]
[ALLOW_CONNECTIONS = true | false]
[CONNECTION LIMIT = max_concurrent_connection]
[IS_TEMPLATE = true | false ];

ví dụ:

Cách 1:

su postgres
createdb binhtvdb

Cách 2:

su postgres
psql
create database binhtvdb1;

Nếu không để thuộc tính tham số nào thì hệ thống sẽ tạo CSDL mới từ template1

\l => list hết tất cả CSDL hiện có

postgres=# \c

psql (9.2.24, server 15.7)

WARNING: psql version 9.2, server version 15.0.

Some psql features might not work.

You are now connected to database “postgres” as user “postgres”.

postgres=# \q

bash-4.2$ psql -U postgres

psql (9.2.24, server 15.7)

WARNING: psql version 9.2, server version 15.0.

Some psql features might not work.

Type “help” for help.

postgres=# \c

psql (9.2.24, server 15.7)

WARNING: psql version 9.2, server version 15.0.

Some psql features might not work.

You are now connected to database “postgres” as user “postgres”.

postgres=# create database duongdb1;

CREATE DATABASE

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

————+———-+———-+————-+————-+———————–

binhdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

binhdb1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

employees | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

lego | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

mydatabase | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

(8 rows)

Cách 3: tạo CSDL với option:

CREATE DATABASE hr
WITH
ENCODING = 'UTF8'
CONNECTION LIMIT = 100;

=> mã hoá utf8, tối đa 100 kết nối

Cách 4: tạo mới qua pgAdmin

Thay đổi thuộc tính CSDL với ALTER DATABASE:

Thay đổi thuộc tính:

ALTER DATABASE name WITH option;

Option:

• IS_TEMPLATE
• CONNECTION LIMIT
• ALLOW_CONNECTIONS

Đổi DB name: bạn không thể rename tên DB mà đang kết nối đến, muốn rename DB => chúng ta phải kết nối đến DB khác và rename DB từ đó:

ALTER DATABASE database_name RENAME TO new_name;

Đổi owner database:

ALTER DATABASE database_name OWNER TO new_owner | current_user | session_user;

Đổi tablespace:

ALTER DATABASE database_name SET TABLESPACE new_tablespace;

Đổi thông tin cấu hình database:

ALTER DATABASE database_name SET configuration_parameter = value;

Ví dụ:

## tạo mới db

CREATE DATABASE testdb2;

## đổi tên db

ALTER DATABASE testdb2 RENAME TO testdb;

## đổi owner

ALTER DATABASE testdb OWNER TO hr;

## nếu chưa có user hr thì chúng ta tạo nó

CREATE ROLE hr

LOGIN

CREATEDB

PASSWORD ‘abc123@’;

## đổi tablespace cho db

ALTER DATABASE testdb SET TABLESPACE hr_default;

## nếu chưa có tablespace hr thì chúng ta tạo mới

CREATE TABLESPACE hr_default

OWNER hr

LOCATION ‘/pgdata/15/data/pg_tblspc/’;

Xoá Database:

DROP DATABASE [IF EXISTS] database_name
[WITH (FORCE)]

Option: FORCE sẽ tiến hành ngắt hết kết nối tới DB và sau đó xoá DB, nếu không có option này thì DB đang có kết nối đến sẽ không thực hiện xoá được.

Để xoá được DB chúng ta cần phải kết nối vào một DB khác sau đó mới xoá được DB cần xoá, chúng ta không thể xoá DB nếu chúng ta đang kết nối vào chính nó.

Hoặc có thể sử dụng lệnh dropdb bên ngoài khi đang ở user postgres

Khi Drop database được thực thi thành công thì hệ thống sẽ tiến hành xoá hết 100% tất cả những gì liên quan đến DB đó bao gồm: catalog trong postgresql, tablespace, table, index, views, log, các files khác liên quan….

Đổi tên DB:

ALTER DATABASE old_db RENAME TO newdb;

chỉ thực hiện được khi DB không có kết nối nào đến

Kiểm tra xem DB có connection nào đang kết nối đến hay không:

select datid, datname,pid, usename, client_addr, state from pg_stat_activity;

Thực hiện ngắt kết nối tới DB:

SELECT pg_terminate_backend(pid) from pg_stat_activity;
SELECT pg_terminate_backend(32500) from pg_stat_activity;

Kill theo pid của OS level:

ps -ef | grep postgres

[root@localhost ~]# ps -ef | grep postgres

postgres 4418 22582 0 09:43 ? 00:00:00 postgres: postgres lego 192.168.68.222(59597) idle

postgres 13527 22582 0 10:04 ? 00:00:00 postgres: postgres db 192.168.68.222(60151) idle

root 13605 5495 0 10:05 pts/1 00:00:00 grep –color=auto postgres

postgres 15361 22582 0 08:52 ? 00:00:02 postgres: postgres postgres 192.168.68.222(55858) idle

postgres 22582 1 0 06:35 ? 00:00:01 /usr/pgsql-15/bin/postmaster -D /pgdata/15/data

postgres 22591 22582 0 06:35 ? 00:00:00 postgres: logger

postgres 22592 22582 0 06:35 ? 00:00:01 postgres: checkpointer

postgres 22593 22582 0 06:35 ? 00:00:00 postgres: background writer

postgres 22595 22582 0 06:35 ? 00:00:00 postgres: walwriter

postgres 22596 22582 0 06:35 ? 00:00:00 postgres: autovacuum launcher

postgres 22597 22582 0 06:35 ? 00:00:00 postgres: logical replication launcher

root 23746 20785 0 06:38 pts/1 00:00:00 su postgres

postgres 23748 23746 0 06:38 pts/1 00:00:00 bash

postgres 29202 22582 0 08:08 ? 00:00:00 postgres: postgres mydatabase 192.168.68.222(52981) idle

postgres 29223 22582 0 08:08 ? 00:00:03 postgres: postgres mydatabase 192.168.68.222(53007) idle

=> tìm số pid của db đang sử dụng => kill

kill -9

Ví dụ ở đây là database DB đang sử dụng có pid là 13527

kill -9 13527

Copy database cùng server:

CREATE DATABASE targetdb WITH TEMPLATE sourcedb;

Duplicate database sang server mới:

## 1. tạo dump file
pg_dump -U postgres -d sourcedb -f sourcedb.

## 2. copy dump file sang server mới

## 3. tạo database trên server mới
CREATE DATABASE targetdb;

## 4. restore từ dump file
psql -U postgres -d targetdb -f sourcedb.sql

Thông tin kích thước database:

## bảng, không bao gồm index, objects…
select pg_relation_size('pgbench_accounts');
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts')) size;

## bảng bao gồm total (index, objects…)
select pg_total_relation_size('pgbench_accounts');
SELECT pg_size_pretty (pg_total_relation_size('pgbench_accounts')) size;

## database:
SELECT pg_size_pretty (pg_database_size ('mydatabase')) size;
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;

## indexs
SELECT pg_size_pretty (pg_indexes_size ('my_table')) size;

## tablespace
SELECT pg_size_pretty (pg_tablespace_size ('my_tablespace')) size;

## Khác
SELECT
pg_column_size(5 :: smallint) smallint_size,
pg_column_size(5 :: int) int_size,
pg_column_size(5 :: bigint) bigint_size;

Tổng kết:

pg_size_pretty() để định dạng kích thước.

pg_relation_size() để lấy kích thước của một bảng.

pg_total_relation_size() để lấy tổng kích thước của một bảng, bao gồm cả các chỉ mục …

pg_database_size() để lấy kích thước của một cơ sở dữ liệu.

pg_indexes_size() để lấy kích thước của một index.

pg_total_index_size() để lấy kích thước của tất cả các chỉ mục trên một bảng.

pg_tablespace_size() để lấy kích thước của một tablespace.

pg_column_size() để lấy kích thước của một cột với kiểu dữ liệu cụ thể.

=============================
Website không chứa bất kỳ quảng cáo nào, mọi đóng góp để duy trì phát triển cho website (donation) xin vui lòng gửi về STK 90.2142.8888 - Ngân hàng Vietcombank Thăng Long - TRAN VAN BINH
=============================
Nếu bạn không muốn bị AI thay thế và tiết kiệm 3-5 NĂM trên con đường trở thành DBA chuyên nghiệp hay làm chủ Database thì hãy đăng ký ngay KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE, được Coaching trực tiếp từ tôi với toàn bộ bí kíp thực chiến, thủ tục, quy trình của gần 20 năm kinh nghiệm (mà bạn sẽ KHÔNG THỂ tìm kiếm trên Internet/Google) từ đó giúp bạn dễ dàng quản trị mọi hệ thống Core tại Việt Nam và trên thế giới, đỗ OCP.
- CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
- Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
2 khóa học online qua video giúp bạn nhanh chóng có những kiến thức nền tảng về Linux, Oracle, học mọi nơi, chỉ cần có Internet/4G:
- Oracle cơ bản: https://bit.ly/admin_1200
- Linux: https://bit.ly/linux_1200
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
cở sở dữ liệu, cơ sở dữ liệu quốc gia, database, AI, trí tuệ nhân tạo, artificial intelligence, machine learning, deep learning, LLM, ChatGPT, DeepSeek, Grok, 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/21c/23c/23ai, 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, docker, k8s, micro service, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty