›
›

Hệ quản trị cơ sở dữ liệu - Postgre SQL

pdf 45 trang vanle 2650
Download
Bạn đang xem 20 trang mẫu của tài liệu "Hệ quản trị cơ sở dữ liệu - Postgre SQL", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

  • pdfhe_quan_tri_co_so_du_lieu_postgre_sql.pdf

Nội dung text: Hệ quản trị cơ sở dữ liệu - Postgre SQL

  1. Khoa Công Nghệ Thông Tin Trường Đại Học Cần Thơ Hệ quản trị cơ sở dữ liệu Đỗ Thanh Nghị dtnghi@cit.ctu.edu.vn Cần Thơ 24-04-2005
  2. Nội dung  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu 2
  3.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu 3
  4.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Mô hình client-server PostgreSQL  Khôi phục cơ sở dữ liệu 4
  5.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Kiến trúc hệ thống ở Backend  Khôi phục cơ sở dữ liệu 5
  6.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Giao tiếp client-server qua Libpq  Khôi phục cơ sở dữ liệu 6
  7.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu 7
  8.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Môi trường cho PostgreSQL  Khôi phục cơ sở dữ liệu  Hệ điều hành  Windows, Linux,  Chọn Linux (Redhat 9.0, Fedora Core 3, Mandriva 10.2)  Miễn phí  Hỗ trợ đủ các công cụ & ngôn ngữ lập trình  Đáp ứng được tốc độ / bảo mật 8
  9.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Cấu trúc cây thư mục của Linux  Khôi phục cơ sở dữ liệu  /: thư mục gốc  /etc: các tập tin cấu hình  /dev: các tập tin thiết bị  /bin, /sbin: lệnh thực thi thường dùng của Linux  /home: thư mục chứa các thư mục người dùng  /usr: tài nguyên dành cho người dùng, thư viện tĩnh hoặc chia sẽ  /usr/local, /opt: phần mềm  /var: dữ liệu thay đổi, tập tin log, web server, email, máy in, etc.  Các thư mục khác  /root, /mnt, /tmp, /proc, /lib, /boot, 9
  10.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Download PostgreSQL dạng *.rpm  Khôi phục cơ sở dữ liệu  Web site:  Tập tin *.rpm  Giả sử với hệ điều hành Linux, Fedora Core 3  Cài đặt các packages: rpm -ivh *.rpm  Tự động tạo ra DB Admin tên postgres 10
  11.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Download tập tin mã nguồn  Khôi phục cơ sở dữ liệu PostgreSQL dạng *.tar.gz  Web site:  Tập tin *.tar.gz: chẳng hạn, postgresql-8.0.3.tar.gz  Giải nén bằng lệnh: gzip -d postgresql-8.0.3.tar.gz  Ta sẽ có tập tin postgresql-8.0.3.tar  Mở tập tin postgresql-8.0.3.tar  Sử dụng lệnh: tar -xvf postgresql-8.0.3.tar  Ta sẽ có thư mục postgresql-8.0.3  Chuyển vào thư mục postgresql-8.0.3  Sử dụng lệnh: cd postgresql-8.0.3 11
  12.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Dịch tập tin mã nguồn PostgreSQL  Khôi phục cơ sở dữ liệu  Biên dịch và cài đặt postgreSQL  Phải chuyển sang người dùng root (su root hay login như root)  Tạo tập tin cấu hình: ./configure  Biên dịch: gmake  Giả sử quá trình biên dịch kết thúc và thành công  Cài đặt: gmake install  PostgreSQL sẽ được cài đặt ở thư mục: /usr/local/pgsql  Tạo DB Admin tên postgres: useradd  Đổi password cho postgres: passwd postgresql 12
  13.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Dịch tập tin mã nguồn PostgreSQL  Khôi phục cơ sở dữ liệu  Biên dịch và cài đặt postgreSQL  Phải chuyển sang người dùng root (su root hay login như root)  Tạo tập tin cấu hình: ./configure  Biên dịch: gmake  Giả sử quá trình biên dịch kết thúc và thành công  Cài đặt: gmake install  PostgreSQL sẽ được cài đặt ở thư mục: /usr/local/pgsql  Tạo DB Admin tên postgres: useradd  Đổi password cho postgres: passwd postgresql  Dùng lệnh: cat /etc/passwd  postgres:x:79:79:system user for postgresql:/var/lib/pgsql:/bin/bash  /var/lib/pgsql: có thể thay đổi trong lúc tạo DB Admin postgres 13
  14.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Khởi tạo cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Khởi tạo cơ sở dữ liệu cho postgreSQL  Chuyển sang người dùng postgres (su postgres hay login như postgres)  Chuyển đến thư mục người dùng postgres (/var/lib/pgsql)  Tạo thư mục chứa cơ sở dữ liệu: mkdir database  Để khởi tạo /var/lib/pgsql/database sẽ chứa cơ sở dữ liệu  Lệnh: initdb /var/lib/pgsql/database 14
  15.  Kiến trúc cơ bản  Cài đặt Màn hình sẽ xuất hiện  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu The files belonging to this database system will be owned by user "postgres". This user must also own the server process. The database cluster will be initialized with locale en_GB. This locale setting will prevent the use of indexes for pattern matching operations. If that is a concern, rerun initdb with the collation order set to "C". For more information see the Administrator's Guide. Fixing permissions on existing directory /var/lib/pgsql/database/ ok creating directory /var/lib/pgsql/database/base ok creating directory /var/lib/pgsql/database/global ok creating directory /var/lib/pgsql/database/pg_xlog ok creating directory /var/lib/pgsql/database/pg_clog ok creating template1 database in /var/lib/pgsql/database/base/1 ok creating configuration files ok initializing pg_shadow ok enabling unlimited row size for system tables ok initializing pg_depend ok creating system views ok loading pg_description ok creating conversions ok setting privileges on built-in objects ok vacuuming database template1 ok copying template1 to template0 ok Success. You can now start the database server using: /usr/bin/postmaster -D /var/lib/pgsql/database/ or /usr/bin/pg_ctl -D /var/lib/pgsql/database/ -l logfile start 15
  16.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Tập tin cấu hình  Khôi phục cơ sở dữ liệu  Các tập tin cấu hình của postgreSQL server  Ở /var/lib/pgsql/database sẽ chứa tập tin cấu hình  “postgresql.conf”  “pg_hba.conf”  “pg_ident.conf” 16
  17.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu /var/lib/pgsql/database/postgresql.conf  Khôi phục cơ sở dữ liệu  CONNECTIONS AND AUTHENTICATION  max_connections, listen_addresses, port, ssl, password_encryption, etc.  RESOURCE USAGE  shared_buffers, max_fsm_pages, max_files_per_process, etc.  WRITE AHEAD LOG  fsync, wal_sync_method, checkpoint_segments, etc.  Các phần khác  QUERY TUNING, ERROR REPORTING AND LOGGING, RUNTIME STATISTICS, CLIENT CONNECTION DEFAULTS, LOCK MANAGEMENT, VERSION/PLATFORM COMPATIBILITY. 17
  18.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu /var/lib/pgsql/database/pg_hba.conf  Khôi phục cơ sở dữ liệu  PostgreSQL Client Authentication  local DATABASE USER METHOD [OPTION]  host DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]  hostssl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]  hostnossl DATABASE USER IP-ADDRESS IP-MASK METHOD [OPTION]  host DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]  hostssl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION]  hostnossl DATABASE USER IP-ADDRESS/CIDR-MASK METHOD [OPTION] 18
  19.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu /var/lib/pgsql/database/pg_ident.conf  Khôi phục cơ sở dữ liệu  PostgreSQL Ident Authentication Maps  MAPNAME IDENT-USERNAME PG-USERNAME 19
  20.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Starting PostgreSQL server  Khôi phục cơ sở dữ liệu  /usr/bin/postmaster –D /var/lib/pgsql/database & LOG: database system was shut down at 2005-06-14 08:28:40 BST LOG: checkpoint record is at 0/8018BC LOG: redo record is at 0/8018BC; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 480; next oid: 16976 LOG: database system is ready  /usr/bin/pg_ctl –D /var/lib/pgsql/database –l logfile start & 20
  21.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Shutting down PostgreSQL server  Khôi phục cơ sở dữ liệu  /bin/kill -INT `head -1 /var/lib/pgsql/database/postmaster.pid` LOG: fast shutdown request LOG: shutting down LOG: database system is shut down  /usr/bin/pg_ctl –D /var/lib/pgsql/database stop 21
  22.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu 22
  23.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Tạo cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Tạo cơ sở dữ liệu  Chuyển sang người dùng postgres (su postgres hay login như postgres)  Đọc trợ giúp của lệnh createdb  /usr/bin/createdb help  Tạo cơ sở dữ liệu (mydb): /usr/bin/createdb mydb 23
  24.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Cài đặt ngôn ngữ thủ tục cho cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Cài đặt ngôn ngữ thủ tục cho cơ sở dữ liệu  Chuyển sang người dùng postgres  Đọc trợ giúp của lệnh createlang  /usr/bin/createlang help  Cài đặt ngôn ngữ PL/pgSQL tới cơ sở dữ liệu mydb  /usr/bin/createlang plpgsql mydb 24
  25.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Tạo người dùng trong hệ quản trị cơ sở  Khôi phục cơ sở dữ liệu dữ liệu  Tạo người dùng  Chuyển sang người dùng postgres  Đọc trợ giúp của lệnh createuser  /usr/bin/createuser help  Tạo người dùng (nghi): /usr/bin/createuser –P nghi Enter password for user « nghi »: Enter it again: Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) n 25
  26.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Xóa bỏ cơ sở dữ liệu, ngôn ngữ thủ tục  Khôi phục cơ sở dữ liệu hay người dùng  Xóa bỏ  Chuyển sang người dùng postgres  Đọc trợ giúp của lệnh: dropdb, droplang, dropuser  /usr/bin/drop___ help  Xóa cơ sở dữ liệu mydb1: /usr/bin/dropdb mydb1  Xóa ngôn ngữ plpgsql: từ cơ sở dữ liệu mydb1  /usr/bin/droplang plpgsql mydb1  Xóa người dùng nghi: /usr/bin/dropuser nghi 26
  27.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Giao tiếp với cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Sử dụng psql  Đọc hướng dẫn: psql help  Giao tiếp với cơ sở dữ liệu mydb  Trên máy cục bộ: psql -U username -W mydb  Từ máy tính khác: psql –h servername -U username -W mydb  Liệt kê các lệnh bên trong psql: mydb=# \?  Tra cứu lệnh SQL: mydb=# \h SQL_command  Thực thi lệnh SQL: mydb=# SQL_command;  Thực thi tập tin lệnh SQL: psql –f file.sql mydb 27
  28.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Phân quyền  Khôi phục cơ sở dữ liệu  Sử dụng psql  Nối kết với cơ sở dữ liệu  Sử dụng: grant, revoke  Tra cứu lệnh grant, revoke: mydb=# \h grant|revoke  Cho người dùng « nghi » có toàn quyền trên cơ sở dữ liệu « mydb »: mydb=# grant all on database mydb to nghi;  Cho những người dùng trong nhóm « staff » có quyền thực hiện phép truy vấn trên bảng « tourists »: mydb=# grant select on tourists to group staff;  Xóa tất cả quyền truy cập của tất cả người dùng tới bảng « cities »: mydb=# revoke all on cities from public; 28
  29.  Kiến trúc cơ bản  Cài đặt Các bảng hệ thống  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu Bảng Nội dung pg_aggregate Aggregates pg_attribute Columns pg_class Tables pg_database Databases pg_description Comment pg_group Group pg_index Indexes pg_log Transaction status pg_operator Operators pg_proc Functions pg_rewrite Rules and views pg_shadow Users pg_trigger Triggers pg_type Types 23 bảng hệ thống khác 29
  30.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Giao diện đồ họa thân thiện cho việc  Khôi phục cơ sở dữ liệu quản trị cơ sở dữ liệu  Quản trị cơ sở dữ liệu với công cụ đồ họa thân thiện  Miễn phí   pgAdmin III  phpPgAdmin  pgaccess 30
  31.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu pgAdmin III  Khôi phục cơ sở dữ liệu 31
  32.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu phpPgAdmin  Khôi phục cơ sở dữ liệu 32
  33.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu pgaccess  Khôi phục cơ sở dữ liệu 33
  34.  Kiến trúc cơ bản?  Cài đặt  Quản lý cơ sở dữ liệu  Khôi phục cơ sở dữ liệu 34
  35.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Backup cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Tại sao cần thiết phải backup cơ sở dữ liệu?  Cơ sở dữ liệu bị hỏng hóc  Do đĩa cứng bị hư, bị virus, cúp điện  Hoặc do lỗi trong quá trình xử lý  Dữ liệu mất tính nhất quán  Để khắc phục sự cố này, chúng ta cần lưu dự phòng  Khi gặp sự cố xãy ra, ta sẽ khôi phục lại trạng thái ban đầu từ bản lưu dự phòng của dữ liệu 35
  36.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Backup cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Có nhiều chiến lược backup cơ sở dữ liệu  SQL dump  File system  Online backup  Replication (nhân bản) 36
  37.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu SQL dump  Khôi phục cơ sở dữ liệu  Backup với pg_dump (pg_dumpall)  Đọc trợ giúp: /usr/bin/pg_dump help  Lưu cơ sở dữ liệu mydb ra tập tin mydb.txt  /usr/bin/pg_dump mydb > mydb.txt  Lưu bảng tourists của cơ sở dữ liệu mydb ra tập tin tourists.txt  /usr/bin/pg_dump table=tourists mydb > tourists.txt  Lưu cơ sở dữ liệu mydb ra tập tin dự phòng mydb.tar  /usr/bin/pg_dump –Ft mydb > mydb.tar  Lưu cơ sở dữ liệu mydb ra tập tin nén mydb.gz  /usr/bin/pg_dump mydb | gzip > mydb.gz  Chú ý với large objects 37
  38.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu SQL dump  Khôi phục cơ sở dữ liệu  Khôi phục dữ liệu  Phục hồi cơ sở dữ liệu mydb từ tập tin mydb.txt  /usr/bin/psql –f mydb.txt mydb  Phục hồi cơ sở dữ liệu mydb từ tập tin mydb.tar  /usr/bin/pg_restore –d mydb mydb.tar  Phục hồi cơ sở dữ liệu mydb từ tập tin mydb.gz  /bin/cat mydb.gz | gunzip | /usr/bin/psql mydb  Có thể lưu dự phòng  Cơ sở dữ liệu mydb từ machine1 tới machine2  /usr/bin/pg_dump –h machine1 mydb | /usr/bin/psql –h machine2 mydb 38
  39.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu File system  Khôi phục cơ sở dữ liệu  Backup với tar  Lưu toàn bộ thư mục chứa cơ sở dữ liệu ra tập tin dự phòng  Đọc trợ giúp: /bin/tar help  Nhớ shutting down PostgreSQL server  Lưu thư mục cơ sở dữ liệu /var/lib/pgsql/database ra tập tin database.tar  /bin/tar -cvf database.tar /var/lib/pgsql/database  Khôi phục lại toàn bộ thư mục chứa cơ sở dữ liệu /var/lib/pgsql/database từ tập tin database.tar  /bin/tar -xvf database.tar 39
  40.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Online backup  Khôi phục cơ sở dữ liệu  Point-in-Time Recovery  Write-Ahead-Log mô tả tất cả các thay đổi  Lưu dự phòng từng thời điểm những thay đổi  Dựa trên đó, tìm ra được nguyên nhân gây hỏng hóc  Khôi phục lại trạng thái ngay trước thời điểm xãy ra sự cố 40
  41.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Online backup  Khôi phục cơ sở dữ liệu  Write-Ahead-Log  Chú ý đến cấu hình postgresql.conf archive_command = on archive_command = 'cp %p wals/%f'  Đừng quên tạo thư mục wals để lưu log  Khởi động lại server  Tất cả thông tin trong thư mục pg_xlog được sao chép tự động vào thự mục wals 41
  42.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Online backup  Khôi phục cơ sở dữ liệu  Write-Ahead-Log  Tạo 1 backup cơ sở SELECT pg_start_backup('label');  Dùng lệnh tar để lưu trữ 1 dự phòng của thư mục data tar –cvf bak.tar data  Kết thúc việc tạo backup cơ sở SELECT pg_stop_backup(); 42
  43.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Online backup  Khôi phục cơ sở dữ liệu  Point-in-Time Recovery  Khi có sự cố, dừng server  Chép thư mục pg_xlog ra pg_xlog.old  Khôi phục bak.tar về data tar -xvf bak.tar  Chép thư mục pg_xlog.old trở lại pg_xlog  Tạo tập tin recovery.conf restore_command = 'cp wals/%f %p' recovery_target_time = '2008-11-25 18:08:06'  Khởi động server khôi phục lại trạng thái ngay thời điểm recovery_target_time 43
  44.  Kiến trúc cơ bản  Cài đặt  Quản lý cơ sở dữ liệu Nhân bản cơ sở dữ liệu  Khôi phục cơ sở dữ liệu  Có nhiều công cụ giúp nhân bản cơ sở dữ liệu  pgpool (gửi đồng thời transaction tới 2 server, đồng bộ)  Slony-I (master - multiple slaves, đồng bộ và bất đồng bộ)  DBmirror (tạo bảng ảnh, đồng bộ và bất đồng bộ)  pgcluster (nhân bản, đồng bộ)  eRServer (master - multiple slaves, bất đồng bộ)  pgReplicator (nhân bản, đồng bộ và bất đồng bộ) 44