Các lệnh thường dùng trên PostgreSQL

Các lệnh thường dùng trên PostgreSQL

1./ Import &​​ Export Database trong PostgreSQL

1.1/ Import DB

psql -U username dbname < dbexport.pgsql

Import DB trên Docker container

cat your_dump.sql | docker exec -i ID_container psql -U postgres

Lưu ý:

Cần tạo dbname trước khi Import.

psql

create database dbname;

1.2/ Export DB

pg_dump -U username dbname > dbexport.pgsql

ExportDB trên Docker container

docker exec -t ID_container pg_dump -U postgres dbname > /opt/backup/dump_`date +%d-%m-%Y"_"%H_%M_%S`.psql

Tham khảo:

https://www.a2hosting.com/kb/developer-corner/postgresql/import-and-export-a-postgresql-database#:~:text=can%20use%20phpPgAdmin.-,Method%20%231%3A%20Use%20the%20pg_dump%20program,to%20the%20account%20using%20SSH.

https://stackoverflow.com/questions/24718706/backup-restore-a-dockerized-postgresql-database

2./ Kiểm tra Database Size & Table Size trong PostgreSQL

Kiểm tra dung lượng của Database

psql

\l+

 

#hoặc show dung lượng của 1 DB

SELECT pg_size_pretty( pg_database_size('dbname') );

Kiểm tra dung lượng của Table

psql

#truy cập vào database dbname

\c dbname;

# show toàn bộ​​ tables

\d

\dt

#Để​​ kiểm tra dung lượng của tables​​ 

\dt+

Hoặc có thể​​ show từng tables

\c dbname;

SELECT pg_size_pretty( pg_total_relation_size('tablename') );

 

3./ Drop sessions connect to PostgreSQL Database

Trường hợp bạn muốn xoá 1 Database thì gặp lỗi​​ sau:

drop database mydb;

Lỗi

ERROR: ​​ database "mydb" is being accessed by other users

DETAIL: ​​ There are 2 other sessions using the database.

Kiểm tra số​​ lượng sessions đang connect tới Database

select​​ pid​​ as​​ process_id,​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ usename​​ as​​ username,​​ 

 ​​ ​​ ​​ ​​​​  ​​​​ datname​​ as​​ database_name,​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ client_addr​​ as​​ client_address,​​ 

 ​​ ​​ ​​ ​​ ​​ ​​​​ application_name,

 ​​ ​​ ​​ ​​ ​​ ​​​​ backend_start,

 ​​ ​​ ​​ ​​ ​​ ​​​​ state,

 ​​ ​​ ​​ ​​ ​​ ​​​​ state_change

from​​ pg_stat_activity;

 

Với các phiên bản PostgreSQL 9.2 trở​​ lên​​ 

SELECT​​ pg_terminate_backend(pg_stat_activity.pid)

FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'mydb' -- ← change this to your DB

 ​​​​ AND pid <> pg_backend_pid();

Kết quả:

pg_terminate_backend​​ 

----------------------

​​ t

​​ t

(2 rows)

Với các phiên bản PostgreSQL từ​​ 9.1 trở​​ xuống

SELECT pg_terminate_backend(pg_stat_activity.procpid)

FROM pg_stat_activity

WHERE pg_stat_activity.datname = 'TARGET_DB' -- ← change this to your DB

 ​​​​ AND procpid <> pg_backend_pid();

#​​ https://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it

 

Sau đó tiến hành Drop

drop database mydb;  ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​ ​​​​  ​​​​ 

Kết quả ​​ ​​ ​​ ​​ ​​ ​​​​ 

DROP DATABASE

Kiểm tra lại

\l+

4./ Đi tên PostgreSQL Database

ALTER DATABASE db_old_name RENAME TO db_new_name;

5./ Upgrade user thành Super User trong PostgreSQL

ALTER USER myuser WITH SUPERUSER;

\du+

 

Note:

Tạo DB từ​​ 1 template

createdb -U postgres -T template1 pgdb_production

Change password user PostgreSQL

ALTER USER user_name WITH PASSWORD 'new_password';

#https://stackoverflow.com/questions/12720967/postgresql-how-to-change-postgresql-user-password

6./ Kiểm tra tất cả​​ các quyền trên Table của DB của 1 user

select * from information_schema.role_table_grants

where table_catalog='DB_Name' and grantee='user_name'

order by table_name asc;

hoặc​​ 

/c DB_Name

SELECT * FROM information_schema.role_table_grants ​​ WHERE table_name='table_name';

SELECT * FROM information_schema.role_table_grants order by table_name asc;

7./ Grant all table cho 1 user

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_name;

8./ List Connection on PostgreSQL

SELECT sum(numbackends) FROM pg_stat_database;

​​ Kết quả:

sum​​ 

-----

 ​​​​ 63

(1 row)

 

9./ Kiểm tra chính xác user sử​​ dụng bao nhiêu connection

Liệt kê tất cả​​ connection của user

SELECT usesysid, usename FROM pg_stat_activity WHERE usename = 'user_need_check;

Count số​​ connection của user

SELECT COUNT​​ (*) FROM pg_stat_activity WHERE usename = 'user_need_check;

 

10./ Liệt kê câu lệnh querry được thực hiện bởi 1 user

Khi xác định được user cần check kiểm tra như sau:

SELECT pid, datname, usename, query FROM pg_stat_activity​​ WHERE usename='user_need_check';

 

11./ Giới hạn connection của user trong postgresql

lệnh kiểm tra maxconnection của toàn bộ hệ thống

SHOW max_connections;

limit 1 user với connection không được vượt quá 10

ALTER USER​​ user_need_check​​ WITH CONNECTION LIMIT 10;

Kiểm tra những user đang limit

SELECT rolname, rolconnlimit

FROM pg_roles

WHERE rolconnlimit <> -1;

Xoá bỏ limit số lượt connection của 1 user

ALTER USER​​ user_need_check​​ WITH CONNECTION LIMIT -1;

 

Chúc các bạn thành công.

SaKuRai

Xin chào, Mình là Sakurai. Blog này là nơi để note lại và chia sẻ những kiến thức, kinh nghiệm mà mình và anh em trong Team. Cảm ơn các bạn đã quan tâm theo dõi!

You may also like...

Leave a Reply

Your email address will not be published.