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://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();
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;
إرسال تعليق