Chương 1 - Managing Databases and the PostgreSQL Server
Postgres CLI
psql -h localhost -U postgres (-p PORT)
\q để thoát khi đang trong Postgres CLI
Create Database
SQL STATEMENT
CREATE DATABASE hrdb WITH ENCODING='UTF-8' OWNER=hr CONNECTION LIMIT=25;
CLI
createdb -h localhost -U postgres testdb1
List all Database in Postgres
SELECT datname from pg_database where datistemplate = false;
Create Users
CREATE USER hr with PASSWORD 'hr';
Create Schema
Schemas are among the most important objects with in a database. A schema is a named collecction of tables. A schema may also contain views, indexes, sequences, data types, operators, and functions. Schemas help organize database objects into logical groups, which helps make these object more manageable.
CREATE SCHEMA employee;
CREATE SCHEMA university AUTHORIZATION bob; (A schema named univerisy and is owned by bob)
Default Schema is public schema.
List all schema
\dn in psql
\du in psql (List all User)
Create Groups
CREATE GROUP dept;
ALTER GROUP dept ADD USER agovi1, nchabbra;
SELECT * FROM pg_group;
Destroying Database
DROP DATABASE hrdb;
CLI
dropdb hrdb
Tablespaces
A tablespace is a location on the disk where PostgreSQL stores data files containing database objects, for example indexes, tables, and so on.
CREATE TABLESPACE HRMS LOCATION '/var/lib/pgsql/data/hrms';
Moving Objects between tablespaces
ALTER DATABASE testdb1 SET default_tablespaces='hrms';
ALTER TABLE employee SET TABLESPACE hrms;
ALTER INDEX emp_idx SET TABLESPACE hrms;
Database Cluster
Database cluster is a collection of databases that are managed by a single server instance
Start and Stop SERVER
Các cách để khởi động và tắt SERVER (Trong đó có cách tắt khẩn cấp, Halt ngay lập tức, không kịp hoàn tất I/O ra Disk.)
Display the Server Status
Terminating connections
Có thể dùng để ngắt các phiên kết nối đang làm chậm Performance của Database.
Ngắt tất cả các phiên kết nối đến Database testdb1
SELECT pg_terminate_backend(pid) FROM pg_stat_activity where datnam='testdb1'
Also useful in free memory from idle postgress processes (pg_cancel_backend(pid) dùng để cancel running queries and not to terminate existing sessions)
Chương 2 - Controlling Security
Securing Database Objects
Bảo đảm chỉ có những authenticated users mới được truy cập đến data mà họ được cấp phép để truy cập.
Revoke - Thu hồi Grant - Cấp quyền
REVOKE ALL on testusers from testdb1;
REVOKE insert, update, delete, truncate on testusers from testdb1;
Các quyền có thể revoke hoặc grant là: SELECT, UPDATE, INSERT, DELETE, TRUNCATE, TRIGGER
Controlling access via firewalls
Cổng mặc định của PostgreSQL là 5432 (Cấu hình Firewall - Iptables open Port này)
Controlling access via configuration files
PostgreSQL có 2 file cấu hình chính
postgresql.conf - contains a single entry that controls on which network interfaces PostgreSQL listens for connections
pg_hba.conf - used to define which clients can connect to which database and using which login role
Show location of hba_files in Psql
SHOW hba_file;
SHOW data_directory;
Auditing Database Changes
Một bảng theo dõi toàn bộ sự thay đổi (Insert,Update,Delete) của các bảng khác, cái này rất tiện khi dùng để truy vết và blame cũng như restore lại một vài record.
Xem thêm tại - Audit Trigger Plus
Enabling SSL in PostgreSQL
Testing SSL Encryption
chưa rõ cụ thể là SSL để bảo vệ kết nối giữa client / server trong PostgreSQL hữu ích ra sao và nên sử dụng trong trường hợp nào, Tốc độ và hiệu suất có tốt hơn hay kém hơn? vì đa phần mình đều dùng localhost.
Encrypting confidential data
- Các ngôn ngữ Backend khác đã làm giúp việc này. Dữ liệu cần phải được mã hóa khi lưu vào Database.
Cracking PostgreSQL Passwords
- Đừng đặt password quá ngắn, quá đơn giản để tránh brute force và dictionary attack
Chương 3 - Backup and Recovery
Logical Backups - Dump File được tạo ra bởi pg_dump
Physical Backups - OS level backup of a database directory and its associated files
Logical Backup
pg_dump -U username -W -Fc database_name > [Backup Location Path].dump
-W (Prompt for Password) -F (Output File -Fc for Custom Format)
Logical backup of all PostgreSQL databases
pg_dumpall -U postgres > [Backup Location Path]
option –schema-only, –roles-only, –tablespaces-only
Logical backup of specific objects
pg_dump -U postgres -W -F t ubrand > 1.tar -n audit
Xem thêm tại http://www.postgresql.org/docs/9.1/static/app-pgdump.html
File system level backup
The easiest way to do this is to make an archive of the PostgreSQL data directory or the directory defined by the $PGDATA environment variable.
Nhưng có một lưu ý là The database must be shutdown completely in order to get a useful backup. A File System backup is meaningful only when the database is in a consistent state.
Một cách khác hay hơn là tận dụng Logical Volume Manager để backup Data của PostgreSQL (Tham khảo thêm trong Sách)
Taking a base backup
pg_basebackup tool takes base backups of a running PostgreSQL database Server. These backups are initiated without affecting other PostgreSQL database clients and can be used for both point-in-time recovery, as well as the start point for log shipping or to stream replication standby servers.
pg_basebackup -h localhost -D directory
user phải có quyền replication trước khi thực hiện pg_basebackup
Cần phải config postgresql.conf max_wal_senders > 1 wal_level must be set to archive
pg_dump có cách sử dụng đơn giản nhưng Performance không tốt bằng pg_basebackup, đặc biệt là với Database lớn (>50GB), pg_dump trả về dump file
Backup và Archive Log của PostgreSQL dùng để Roll forward, hỗ trợ thêm cho Point-In-Time Recovery
Hot Physical Backup and Continous Archiving
Cho dù chúng ta backup thường xuyên đến cỡ nào thì khi sự cố xảy ra, nhiều khả năng là bản Backup gần nhất đã lỗi thời, và bạn không thể restore lại nguyên hiện trạng trước khi xảy ra sự cố được.
Enable WAL (Write-ahead Log)
Thay đổi cấu hình trong file postgresql.conf
wal_level = hot_standby archive_mode = on archive_command = ‘test ! -f /home/abcd/pgsql/backup_in_progress || (test ! -f /home/abcd/pgsql/archive/%f && cp %p /home/abcd/pgsql/archive/%f) ‘
==Phần này đọc thêm trong Sách và Xây dựng thành Một bài viết khác, đầy đủ, chi tiết hơn==
Point-in-time Recovery
Phục hồi lại với bản Backup gần nhất Sử dụng Kỹ thuật Point-in-time Recovery để hồi phục Database đến checkpoint mong muốn.
Restoring databases and specific database objects Thông qua pg_restore Utility
pg_dump / pg_restore
pg_dump -U username -f backup.dump database_name -Fc
switch -F specify format of backup file:
c will use custom PostgreSQL format which is compressed and results in smallest backup file size d for directory where each file is one table t for TAR archive (bigger than custom format)
Restore backup
pg_restore -d database_name -U username -C backup.dump
Parameter -C should create database before importing data. If it doesn’t work you can always create database eg. with command (as user postgres or other account that has rights to create databases) createdb db_name -O owner
pg_dump/psql
In case that you didn’t specify the argument -F default plain text SQL format was used (or with -F p). Then you can’t use pg_restore. You can import data with psql.
backup:
pg_dump -U username -f backup.sql database_name
restore:
psql -d database_name -f backup.sql
Chương 4 - Routine Maintenance Tasks
(Có thời gian sẽ đọc thêm)
Chương 5 - Monitoring the System Using Unix Utilities
Mình đã viết thành bài riêng tại http://notes.viphat.work/monitoring-linux-system/
Tham khảo thêm cách cài sysstat - http://www.thegeekstuff.com/2011/03/sar-examples/
Các chương còn lại cũng hay nhưng ở mức độ Advance nên mình chưa hiểu hết, sẽ đọc lại sau (Cũng hy vọng là mình sẽ có dịp làm việc với một Hệ thống Database lớn, lúc đó thì mới được dịp áp dụng các kiến thức ở những Chương sau):
Chương 6 - Monitoring Database Activity and Investigating Performance Issues (Theo dõi hoạt động của Database và điều tra các vấn đề về Performance)
Chương 7 - High Availability and Replication
(Nhân bản Database để mang lại độ sẵn sàng cao cho các ứng dụng Database lớn và nhiều người sử dụng)
Chương 8 - Connection Pooling
Chương 9 - Table Partioning (Phân vùng các Table, tách riêng và chia nhỏ Table nếu Table quá lớn, chứa nhiều dữ liệu)
Chương 10 - Accessing PostgreSQL from Perl
Chương 11 - Accessing PostgreSQL from Python
(Nếu có thời gian và có dịp, mình sẽ học thêm về Python. Hiện mình chủ yếu làm việc với Rails, còn Ruby thực ra còn nhiều cái chưa rành lắm… chẳng hạn, block_given? là gì?)
Chương 12 - Data Migration from Other Databases and Upgrading PostgreSQL Cluster