HƯỚNG DẪN GIÁM SÁT, VẬN HÀNH POSTGRESQL

1. Giám sát CSDL

Khi DB mức độ online 24/7 cần thực hiện các công việc kiểm tra đầu ngày và cuối ngày đầu ngày (8h) và cuối ngày (16h30) làm việc

a. Kiểm tra không gian trống

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2:  Kiểm tra dung lượng trống của các ổ đĩa bằng cách thực hiện câu lệnh df -g hoặc df -h

Bước 3:  : Xem cột %Used cho biết % không gian đã dùng của ổ đĩa. Nếu %Used ở mức cảnh báo Warning (85%) hoặc mức Critical (95%) cần thông báo cho nhân viên quản trị hệ thống để xử lý.

Bước 4: Ghi nhận kết quả kiểm tra vào PL1_Checklist vận hành PostgreSQL

b. Kiểm tra alert log file

Hành động

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2:  Đi đến thư mục /u03/pgAAA/log cd /u03/pgAAA/log

Bước 3:  Mở file và tìm “posgresql_“ hoặc “error” từ dưới lên của file alert log trong một ngày làm việc tính từ thời điểm kiểm tra về trước. Nếu có dữ liệu logs error, cần thông báo cho nhân viên quản trị CSDL (theo Danh sách người quản trị CSDL ở Mục 5) để xử lý.

Bước 4: Ghi nhận kết quả kiểm tra vào PL1_Checklist vận hành PostgreSQL

 c. Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2:  Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL pg_ctl -D $PGDATA status - Trạng thái bình thường: - Trạng thái không bình thường: Khi xuất hiện trạng thái không bình thường cần báo ngay cho nhân viên quản trị CSDL (theo Danh sách người quản trị CSDL ở Mục 5) để xử lý. - Các thao tác xử lý khi xuất hiện trạng thái không bình thường: Kiểm tra dữ liệu logs của dịch vụ PostgreSQL: tail –n 100 –f $PGLOG/postgresql_*.log tail –n 100 –f $PGLOG/logfile_start Phân tích dữ liệu logs của dịch vụ PostgreSQL, xác định nguyên nhân, tình trạng lỗi và xử lý (tham khảo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi) Thực hiện lại các Bước 1, Bước 2 để Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL

d. Kiểm tra trạng thái đồng bộ dữ liệu sang máy chủ dự phòng PostgreSQL

Hành động

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2:  Kiểm tra trạng thái đồng bộ dữ liệu sang máy chủ dự phòng PostgreSQL psql -x -c "select * from pg_stat_replication;" - Trạng thái bình thường: - Trạng thái không bình thường: Khi xuất hiện trạng thái không bình thường cần báo ngay cho nhân viên quản trị CSDL (theo Danh sách người quản trị CSDL ở Mục 5) để xử lý. - Các thao tác xử lý khi xuất hiện trạng thái không bình thường: Kiểm tra dữ liệu logs của dịch vụ PostgreSQL: tail –n 100 –f $PGLOG/postgresql_*.log tail –n 100 –f $PGLOG/logfile_start Kiểm tra trạng thái hoạt động của các máy chủ Slave theo mục 7.1.3 Phân tích dữ liệu logs và trạng thái hoạt động của các máy chủ Slave để xác định nguyên nhân, tình trạng lỗi và xử lý (tham khảo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi) Thực hiện lại các Bước 1, Bước 2 để Kiểm tra trạng thái đồng bộ dữ liệu của dịch vụ PostgreSQL

 e. Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi

-         Cách đọc log trong postgres

B1: Login vào server với user postgres

su – postgres

cd $PGLOG

ls -ltr

tail -f mba_master-year-month-day.log

Xác định sự cố, lỗi trong thời gian nào đó

Copy log về máy windown

2. Vận hành CSDL

a. Khỏi động dịch vụ PostgreSQL

Bước 1: Đăng nhập vào OS bằng tài khoản postgres su - postgres

Bước 2: Khởi động dịch vụ PostgreSQL pg_ctl -D $PGDATA -l $PGLOG/logfile_start start

Bước 3: Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL sau khi khởi động pg_ctl -D $PGDATA status - Trạng thái bình thường: - Trạng thái không bình thường: Khi xuất hiện trạng thái không bình thường cần báo ngay cho nhân viên quản trị (theo Danh sách người quản trị ở Mục 5) để xử lý. - Các thao tác xử lý khi xuất hiện trạng thái không bình thường:  Kiểm tra dữ liệu logs của dịch vụ PostgreSQL: tail –n 100 –f $PGLOG/postgresql_*.log tail –n 100 –f $PGLOG/logfile_start Phân tích dữ liệu logs của dịch vụ PostgreSQL và xác định nguyên nhân, trạng trái lỗi theo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi Sửa lỗi dựa trên quá trình phân dữ liệu logs và xác định nguyên nhân (Tham khảo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi) Thực hiện lại các Bước 2, Bước 3 để khởi động lại dịch vụ PostgreSQL và kiểm tra lại trạng thái.

b. Tắt dịch vụ PostgreSQL

Bước 1: Đăng nhập vào OS bằng tài khoản postgres su - postgres

Bước 2:  Tắt dịch vụ PostgreSQL pg_ctl -D $PGDATA stop -mf

Bước 3: Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL sau khi tắt pg_ctl -D $PGDATA status - Trạng thái bình thường: - Trạng thái không bình thường: Khi xuất hiện trạng thái không bình thường cần báo ngay cho nhân viên quản trị (theo Danh sách người quản trị ở Mục 5) - Các thao tác xử lý khi xuất hiện trạng thái không bình thường: Kiểm tra dữ liệu logs của dịch vụ PostgreSQL: tail –n 100 –f $PGLOG/postgresql_*.log tail –n 100 –f $PGLOG/logfile_start Phân tích dữ liệu logs của dịch vụ PostgreSQL và xác định nguyên nhân, tình trạng lỗi theo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi Sửa lỗi dựa trên quá trình phân dữ liệu logs và xác định nguyên nhân (Tham khảo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi) Thực hiện lại các Bước 2, Bước 3 để tắt dịch vụ PostgreSQL và kiểm tra lại trạng thái.

 

c. Khỏi động lại dịch vụ PostgreSQL

Bước 1: Đăng nhập vào OS bằng tài khoản postgres su – postgres

Bước 2:  Khởi động lại dịch vụ PostgreSQL pg_ctl -D $PGDATA restart

Bước 3: Kiểm tra trạng thái hoạt động của dịch vụ PostgreSQL sau khi khởi động lại pg_ctl -D $PGDATA status - Trạng thái bình thường: - Trạng thái không bình thường: Khi xuất hiện trạng thái không bình thường cần báo ngay cho nhân viên quản trị (theo Danh sách người quản trị ở Mục 5) - Các thao tác xử lý khi xuất hiện trạng thái không bình thường: Kiểm tra dữ liệu logs của dịch vụ PostgreSQL: tail –n 100 –f $PGLOG/postgresql_*.log tail –n 100 –f $PGLOG/logfile_start Phân tích dữ liệu logs của dịch vụ PostgreSQL, xác định nguyên nhân, tình trạng lỗi và xử lý (Tham khảo PL2_Hướng dẫn phân tích dữ liệu logs PostgreSQL và xử lý lỗi) Thực hiện lại các Bước 2, Bước 3 để khởi động lại dịch vụ PostgreSQL và kiểm tra lại trạng thái.

d. Sao lưu dữ liệu

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2: Thực hiện sao lưu database pg_dump -Fc pgsqldbname > $PG_BACKUP_DATA_DIR/full_pgsqldbname_YYYYMMDD_HH_mm.tar Sao lưu thành công: Sao lưu không thành công: Xuất hiện lỗi error bên dưới thông báo sao lưu. Khi đó cần báo ngay cho nhân viên quản trị (theo Danh sách người quản trị ở Mục 5) để xử lý.

e. Khôi phục dữ liệu

Bước 1: Đăng nhập OS bằng tài khoản postgres su - postgres

Bước 2:  Thực hiện khôi phục database pg_restore -C -d pgsqldbname > $PG_BACKUP_DATA_DIR/full_pgsqldbname_YYYYMMDD_HH_mm.tar Phục hồi thành công: Phục hồi không thành công: Xuất hiện lỗi error bên dưới thông báo phục hồi. Khi đó cần báo ngay cho nhân viên quản trị (theo Danh sách người quản trị ở Mục 5) để xử lý.

 

3. Các câu lệnh khác

- 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