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 duongdb

Cách 2:

su postgres
psql
create database duongdb1;

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

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

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

duongdb1 | 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ể.