Mục đích: Chia sẻ những câu lệnh thường dùng để quản trị PostgreSQL
- Stop/Start:
service postgresql-12 status
service postgresql-12 start
- Connect:
psql -h localhost -p 5432
psql -h localhost -p 9999
psql -h localhost -p 9999 -c "show
pool_nodes;"
- Backup/Restore:
pg_basebackup -h 192.168.254.31 -p 5432 -U replication
-D /data01/pgdata/ -Fp -Xs -P -R -T '/data01/pgdata/sea_tblspc_api=/data01/pgdata/sea_tblspc_api_stb'
-T '/data01/pgdata/sea_tblspc_swib=/data01/pgdata/sea_tblspc_swib_stb'
Tạo job crontab trên OS định kỳ 00h00 backup hàng
ngày.
- Clean archive log:
pg_archivecleanup -d /data01/pgdata/pg_wal
00000002000000110000009C
/usr/pgsql-12/bin/pg_archivecleanup -d /pgdata01/wal
000000020000001F000000B1
- List user:
postgres=# \du
SELECT usename AS role_name, CASE WHEN usesuper AND
usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text) WHEN
usesuper THEN CAST('superuser' AS pg_catalog.text) WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text) ELSE CAST('' AS pg_catalog.text) END
role_attributes FROM pg_catalog.pg_user ORDER BY role_name desc;
- Create user:
CREATE USER binhtv4 WITH PASSWORD 'Cognac#12345';
CREATE USER pcidss_user WITH PASSWORD 'ank#123' VALID
UNTIL '2050-01-01';
GRANT CONNECT ON DATABASE API TO
pcidss_user;
- List db:
postgres=# \l
- Connect db:
postgres=# \c API
- List table:
postgres=# \dt
- Create role:
CREATE ROLE grp_ewallet WITH
NOLOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
VALID UNTIL 'infinity';
- Alter user binhtv4 with superuser;
- ALTER GROUP grp_superadmin ADD USER binhtv4;
-- Check lock:
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity
psa
ON pl.pid =
psa.pid where usename='siem';
SELECT blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS
blocked_user,
blocking_locks.pid AS
blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS
blocked_statement,
blocking_activity.query AS
current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN
pg_catalog.pg_stat_activity blocked_activity
ON blocked_activity.pid = blocked_locks.pid
JOIN
pg_catalog.pg_locks
blocking_locks
ON
blocking_locks.locktype = blocked_locks.locktype
AND
blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
AND
blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND
blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND
blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND
blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND
blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND
blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND
blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND
blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND
blocking_locks.pid != blocked_locks.pid
JOIN
pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid =
blocking_locks.pid
WHERE NOT
blocked_locks.granted;
--Check câu lệnh chạy lâu:
SELECT pid, now() - pg_stat_activity.query_start AS
duration, state,
query FROM pg_stat_activity WHERE state ='active' AND
query NOT ILIKE '%pg_stat_activity%'
AND (now() - pg_stat_activity.query_start) >
interval '30 seconds'
ORDER BY duration desc;
-- Kill:
select pg_cancel_backend(9722);
-- Check replicat:
On master:
select * from pg_stat_replication;
On replica:
select * from pg_stat_wal_receiver;
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
postgres=# select pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
0/540C1DB8
postgres=# select pg_last_xlog_replay_location();
pg_last_xlog_replay_location
------------------------------
0/540C1DB8
(1 row)
postgres=# SELECT
CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
THEN 0
ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
END AS log_delay;
log_delay
-----------
0
(1 row)
---------------------------------------------------------
-- export:
time pg_dumpall -r -l API >
/backupfs/dump/role_api.sql
time pg_dumpall -t -l API >
/backupfs/dump/tablespace_api.sql
time pg_dump -Fc API -n api_ewallet > /backupfs/dump/api.dump
-- import:
time psql -f /tmp/role_api.sql postgres
time psql -f tablespace_api.sql postgres
time pg_restore -d API /tmp/api.dump
-- authen md5 pgpool:
# cd /data01/pgpool/bin/
# ./pg_md5 --md5auth --username=u_ewallet Ewalljet@2720
--------------------------------------------------------------------------
Golive chuyển db lên cloud:
--------------------------------------------------------------------------
time pg_dumpall -r -l API > /tmp/role_api.sql
time pg_dumpall -t -l API >
/tmp/tablespace_api.sql
pg_dump -s -t _api.auth_request_in_new4_id_seq -t
_api.auth_request_out_new4_id_seq -t _api.auth_request_in -t
_api.auth_request_out_new4_id_seq -t _api.auth_request_out -t
_api.sms_tbl_command_id_seq -t _api.sms_tbl_command -t
_api.sms_tbl_error_id_seq -t _api.sms_tbl_error -t
_api.sms_tbl_request_in_id_new1_seq -t _api.sms_tbl_request_in -f
/tmp/sequence.sql API
pg_dump -s -t
_api.auth_context -t _api.auth_context_activity -t _api.auth_location_activity
-t _api.auth_locking_user -t _api.auth_login_fail -t _api.auth_otp -t
_api.auth_request_arc -t _api.auth_request_error -t _api.auth_request_in -t
_api.auth_request_out -t _api.auth_reset_pass -t _api.auth_session -t
_api.cust_info_account -t _api.cust_info_account_addinfo -t
_api.cust_info_contracts -t _api.cust_info_customer -t
_api.cust_info_customer_corp_addinfo -t _api.cust_info_customer_corp_session -t
_api.cust_info_customer_ind_addinfo -t _api.cust_info_customer_ind_session -t
_api.cust_info_loan_products -t _api.cust_info_loan_products_addinfo -t
_api.cust_info_request_arc -t _api.cust_info_request_error -t
_api.cust_info_request_in -t _api.cust_info_request_out -t
_api.cust_info_request_out_20210701 -t _api.cust_info_user_profile -t
_api.sms_tbl_command -t _api.sms_tbl_error -t _api.sms_tbl_request_arc -t _api.sms_tbl_request_arc_old
-t _api.sms_tbl_request_error -t _api.sms_tbl_request_in -t
_api.sms_tbl_request_in_old -t _api.sms_tbl_request_out -t
_api.sms_tbl_request_out_arc -t _api.sms_tbl_request_out_arc_old -t
_api.sms_tbl_sms_template -f /tmp/table_metadata.sql API
pg_dump
--insert -t _api._api_channel_config -t _api._api_config -t
_api._api_config_activity -t _api._api_error -f /tmp/table_config.sql API
----------------------------------------------
time psql -f /tmp/role_api.sql postgres
time psql -f /tmp/tablespace_api.sql postgres
time pg_restore -d API /tmp/table_metadata.dump
time pg_restore -d API /tmp/table_config.dump
time psql --dbname=API -U postgres -f
/tmp/table_config.sql
time psql --dbname=API -U postgres -f /tmp/table_metadata.sql
time psql --dbname=API -U postgres -f
/tmp/sequence.sql
SELECT * FROM pg_catalog.pg_tables where
schemaname='_api' order by tablename;
SELECT * FROM pg_catalog.pg_tables where
schemaname='_api' and tablename like 'sms_%' order by tablename;
SELECT * FROM pg_catalog.pg_tables where
schemaname='_api' and tablename like 'cust_info_%' order by tablename;
SELECT * FROM pg_catalog.pg_tables where
schemaname='_api' and tablename like 'auth_%' order by tablename;
select * from _api._api_channel_config;
select * from _api._api_config;
select * from _api._api_config_activity;
select * from _api._api_channel_config;
----------------
pg_md5 --md5auth --username=lg_apiauthen xxx
pg_md5 --md5auth --username=lg_custinfo xxx
pg_md5 --md5auth --username=lg_apisms xxx
GRANT ALL ON SCHEMA _api TO lg_apisms;
ALTER DEFAULT PRIVILEGES IN SCHEMA _api
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES TO
lg_apisms;
ALTER DEFAULT PRIVILEGES IN SCHEMA _api
GRANT ALL ON TABLES TO lg_apisms;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO
lg_swib_website;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public
TO lg_swib_website;
ALTER TABLE api_ewallet.wallet_his OWNER TO
grp_ewallet;
ALTER TABLE api_ewallet.wallet_addition_type SET
TABLESPACE sea_tblspc_api;
ALTER SCHEMA eba OWNER TO u_nextgen_pilot;
--grant all schema
SELECT format(
'GRANT
ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO u_nextgen_pilot;',
nspname
)
FROM pg_namespace
WHERE nspname NOT in
('pg_catalog','information_schema')
\gexec
--check size:
select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')/1024/1024/1024
as "table_size (GB)"
from information_schema.tables
where
table_schema not in
('pg_catalog','information_schema')
and table_type = 'BASE TABLE'
and table_name like 'sms_tbl%'
order by 3 desc;
--Create new table (include new index) + sequence:
done
SET search_path TO _api;
ALTER TABLE test_out RENAME TO test_out_old1;
ALTER TABLE test_out_new RENAME TO test_out;
--Archive data (90 days):
select * from public.tbl_data_in where dayid >
'20211019';
COPY (SELECT * FROM public.tbl_data_in WHERE dayid
> '20210720') TO '/u02/backup/export/tbl_data_in.csv';
SET search_path TO public;
ALTER TABLE tbl_data_in RENAME TO
tbl_data_in_20211021;
=> create new table tbl_data_in
COPY public.tbl_data_in FROM
'/u02/backup/export/tbl_data_in.csv';
-- Table size:
select
table_catalog, table_schema, table_name,
pg_relation_size(table_schema||'.'||table_name) from information_schema.tables
order by 3;
--API
select schemaname as table_schema, relname as
table_name, pg_size_pretty(pg_relation_size(relid)) as data_size from
pg_catalog.pg_statio_user_tables order by pg_relation_size(relid) desc;
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* 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
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 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: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Đị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
=============================
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,khóa học pl/sql, 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, 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 dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, middleware, hoc solaris, hoc linux, hoc aix, unix, securecrt, xshell, mobaxterm, putty
