Cài đặt PostgreSQL trên môi trường Linux

Cài đặt PostgreSQL trên môi trường linux thông thường có 2 cách. Build từ source hoặc cài đặt qua packages(rpm) có sẵn từ community. Binary từ 2 cách cài đặt này có một vài điểm nhỏ khác nhau trong file setting sau khi tạo data cluster. Ví dụ như logging_collector(xuất log qua file) mặc định on trên rpm và off khi build. Cài đặt qua source thì dễ tùy chỉnh các options bằng cách configure -> build lại.

Cài đặt qua source

1. Download source
   Tất cả các phiên bản của PostgreSQL có thể download từ trang chủ. Chọn version bạn muốn dùng cho hệ thống của bạn. Khuyến cáo bạn nên chọn rivision mới nhất vì các chỉnh sửa giữa các revision hầu hết chỉ là bug fix.

2. Cài đặt
   Sau khi giải nén bạn chạy lệnh ./configure với các options tùy thích. Bạn có thể xem ý nghĩa các options qua lệnh ./configure --help. Tiếp đó bạn dùng lệnh make và make install để cài đặt. Chú ý user chạy lệnh make install phải có quyền write thư mục bạn muốn cài đặt (option --prefix).

   
   [postgres@localhost src]$ ls postgresql-9.6.3.tar.gz
   postgresql-9.6.3.tar.gz
   [postgres@localhost src]$ tar xfz postgresql-9.6.3.tar.gz
   [postgres@localhost src]$ cd postgresql-9.6.3/
   [postgres@localhost postgresql-9.6.3]$ sudo mkdir -p /usr/local/pgsql/pg9603
   [sudo] password for postgres:
   [postgres@localhost postgresql-9.6.3]$ sudo chown postgres:postgres /usr/local/pgsql/pg9603
   [postgres@localhost postgresql-9.6.3]$ ./configure --prefix=/usr/local/pgsql/pg9603 --enable-debug \
    > /dev/null
   configure: WARNING:
   *** Without Bison you will not be able to build PostgreSQL from Git nor
   *** change any of the parser definition files. You can obtain Bison from
   *** a GNU mirror site. (If you are using the official distribution of
   *** PostgreSQL then you do not need to worry about this, because the Bison
   *** output is pre-generated.)
   configure: WARNING:
   *** Without Flex you will not be able to build PostgreSQL from Git nor
   *** change any of the scanner definition files. You can obtain Flex from
   *** a GNU mirror site. (If you are using the official distribution of
   *** PostgreSQL then you do not need to worry about this because the Flex
   *** output is pre-generated.)
   [postgres@localhost postgresql-9.6.3]$ make -j 4 > /dev/null
   [postgres@localhost postgresql-9.6.3]$ make install > /dev/null
   [postgres@localhost postgresql-9.6.3]$ ll /usr/local/pgsql/pg9603/
   total 16
   drwxrwxr-x. 2 postgres postgres 4096 Apr 12 23:05 bin
   drwxrwxr-x. 6 postgres postgres 4096 Apr 12 23:05 include
   drwxrwxr-x. 4 postgres postgres 4096 Apr 12 23:05 lib
   drwxrwxr-x. 6 postgres postgres 4096 Apr 12 23:05 share
   [postgres@localhost postgresql-9.6.3]$
   
   

   Tùy chỉnh configure đã sử dụng:

   Tùy chọn	ý nghĩa
   --prefix	đường dẫn thư mục cài đặt
   --enable-debug	cho phép debug

   Lưu ý:

• PostgreSQL không cho phép khởi động bằng user root, bạn nên cài đặt bằng user thường.

• Nếu môi trường thiếu packages readline-devel và zlib-devel, lúc chạy ./configure sẽ có lỗi. Bạn nên cài 2 packages và configure lại.

• Manual của PostgreSQL viết là sử dụng gmake thay vì make, nhưng môi trường Linux gmake được link tới make.

  
  $ ll /usr/bin/gmake
  lrwxrwxrwx. 1 root root 4 Nov 20 09:41 /usr/bin/gmake -> make
  
  

3. Tạo database cluster
   Cài đặt xong PostgreSQL bạn có thể tạo database cluster (nơi chứa dữ liệu cho PostgreSQL) bằng lệnh initdb.

   
    [postgres@localhost postgresql-9.6.3]$ /usr/local/pgsql/pg9603/bin/initdb -D /usr/local/pgsql/pg9603/data \
    --no-locale -E utf8
    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 "C".
    The default text search configuration will be set to "english".
    Data page checksums are disabled.
    creating directory /usr/local/pgsql/pg9603/data ... ok
    creating subdirectories ... ok
    selecting default max_connections ... 100
    selecting default shared_buffers ... 128MB
    selecting dynamic shared memory implementation ... posix
    creating configuration files ... ok
    running bootstrap script ... ok
    performing post-bootstrap initialization ... ok
    syncing data to disk ... ok
    WARNING: enabling "trust" authentication for local connections
    You can change this by editing pg_hba.conf or using the option -A, or
    --auth-local and --auth-host, the next time you run initdb.
    Success. You can now start the database server using:
    /usr/local/pgsql/pg9603/bin/pg_ctl -D /usr/local/pgsql/pg9603/data -l logfile start
    [postgres@localhost postgresql-9.6.3]$ ll /usr/local/pgsql/pg9603/data
    total 48
    drwx------. 5 postgres postgres 38 Apr 12 23:43 base
    drwx------. 2 postgres postgres 4096 Apr 12 23:43 global
    drwx------. 2 postgres postgres 17 Apr 12 23:43 pg_clog
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_commit_ts
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_dynshmem
    -rw-------. 1 postgres postgres 4468 Apr 12 23:43 pg_hba.conf
    -rw-------. 1 postgres postgres 1636 Apr 12 23:43 pg_ident.conf
    drwx------. 4 postgres postgres 37 Apr 12 23:43 pg_logical
    drwx------. 4 postgres postgres 34 Apr 12 23:43 pg_multixact
    drwx------. 2 postgres postgres 17 Apr 12 23:43 pg_notify
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_replslot
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_serial
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_snapshots
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_stat
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_stat_tmp
    drwx------. 2 postgres postgres 17 Apr 12 23:43 pg_subtrans
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_tblspc
    drwx------. 2 postgres postgres 6 Apr 12 23:43 pg_twophase
    -rw-------. 1 postgres postgres 4 Apr 12 23:43 PG_VERSION
    drwx------. 3 postgres postgres 58 Apr 12 23:43 pg_xlog
    -rw-------. 1 postgres postgres 88 Apr 12 23:43 postgresql.auto.conf
    -rw-------. 1 postgres postgres 22204 Apr 12 23:43 postgresql.conf
    [postgres@localhost postgresql-9.6.3]$
   
   

   Tùy chỉnh initdb đã sử dụng:

   Tùy chọn	ý nghĩa
   -D	đường dẫn thư mục database cluster
   --no-locale	sử dụng locale C cho database cluster
   -E utf8	sử dụng server encoding thông dụng utf8

Cài đặt qua packages(rpm)

1. Cài đặt từ yum reporitory
   Bạn có thể download reporitory RPMs tương ứng ở đây, sau khi cài đặt dùng lệnh yum để cài đặt packages cần thiết. Ở ví dụ bên dưới, mình đã install 4 packages thông dụng của PostgreSQL. Sau khi cài đặt bạn có thể tạo database cluster bằng lệnh initdb.

   
    [root@localhost src]# wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
    --2017-05-21 22:26:49-- https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
    Resolving download.postgresql.org (download.postgresql.org)... 174.143.35.246, 204.145.124.244, 217.196.149.55, ...
    Connecting to download.postgresql.org (download.postgresql.org)|174.143.35.246|:443... connected.
    HTTP request sent, awaiting response... 200 OK
    Length: 4816 (4.7K) [application/x-redhat-package-manager]
    Saving to: 'pgdg-redhat96-9.6-3.noarch.rpm'
    100%[===================================================================================================================================================================================================>] 4,816 --.-K/s in 0.005s
    2017-05-21 22:26:50 (902 KB/s) - 'pgdg-redhat96-9.6-3.noarch.rpm'saved [4816/4816]
    [root@localhost src]# rpm -ivh pgdg-redhat96-9.6-3.noarch.rpm
    Preparing... ################################# [100%]
    Updating / installing...
    1:pgdg-redhat96-9.6-3 ################################# [100%]
    [root@localhost src]# yum search postgresql96
    Loaded plugins: fastestmirror, langpacks
    pgdg96 | 4.1 kB 00:00:00
    (1/2): pgdg96/7/x86_64/group_gz | 249 B 00:00:01
    (2/2): pgdg96/7/x86_64/primary_db | 137 kB 00:00:01
    Loading mirror speeds from cached hostfile
    * base: ftp.riken.jp
    * extras: ftp.riken.jp
    * updates: ftp.riken.jp
    ========================================================================================================= N/S matched: postgresql96 =========================================================================================================
    postgresql96-debuginfo.x86_64 : Debug information for package postgresql96
    postgresql96-tcl-debuginfo.x86_64 : Debug information for package postgresql96-tcl
    postgresql96.x86_64 : PostgreSQL client programs and libraries
    postgresql96-contrib.x86_64 : Contributed source and binaries distributed with PostgreSQL
    postgresql96-devel.x86_64 : PostgreSQL development header files and libraries
    postgresql96-docs.x86_64 : Extra documentation for PostgreSQL
    postgresql96-libs.x86_64 : The shared libraries required for any PostgreSQL clients
    postgresql96-odbc.x86_64 : PostgreSQL ODBC driver
    postgresql96-plperl.x86_64 : The Perl procedural language for PostgreSQL
    postgresql96-plpython.x86_64 : The Python procedural language for PostgreSQL
    postgresql96-pltcl.x86_64 : The Tcl procedural language for PostgreSQL
    postgresql96-server.x86_64 : The programs needed to create and run a PostgreSQL server
    postgresql96-tcl.x86_64 : A Tcl client library for PostgreSQL
    postgresql96-test.x86_64 : The test suite distributed with PostgreSQL
    Name and summary matches only, use "search all" for everything.
    [root@localhost src]# yum install postgresql96.x86_64 postgresql96-contrib.x86_64 postgresql96-libs.x86_64 postgresql96-server.x86_64
    Loaded plugins: fastestmirror, langpacks
    Loading mirror speeds from cached hostfile
    * base: ftp.riken.jp
    * extras: ftp.riken.jp
    * updates: ftp.riken.jp
    Resolving Dependencies
    --> Running transaction check
    ---> Package postgresql96.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
    ---> Package postgresql96-contrib.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
    ---> Package postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
    ---> Package postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
    --> Finished Dependency Resolution
    Dependencies Resolved
    =============================================================================================================================================================================================================================================
    Package Arch Version Repository Size
    =============================================================================================================================================================================================================================================
    Installing:
    postgresql96 x86_64 9.6.3-1PGDG.rhel7 pgdg96 1.3 M
    postgresql96-contrib x86_64 9.6.3-1PGDG.rhel7 pgdg96 563 k
    postgresql96-libs x86_64 9.6.3-1PGDG.rhel7 pgdg96 310 k
    postgresql96-server x86_64 9.6.3-1PGDG.rhel7 pgdg96 4.3 M
    Transaction Summary
    =============================================================================================================================================================================================================================================
    Install 4 Packages
    Total download size: 6.5 M
    Installed size: 28 M
    Is this ok [y/d/N]: y
    Downloading packages:
    (1/4): postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64.rpm | 563 kB 00:00:03
    (2/4): postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm | 310 kB 00:00:00
    (3/4): postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm | 1.3 MB 00:00:05
    (4/4): postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm | 4.3 MB 00:00:05
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Total 686 kB/s | 6.5 MB 00:00:09
    Running transaction check
    Running transaction test
    Transaction test succeeded
    Running transaction
    Warning: RPMDB altered outside of yum.
    Installing : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/4
    Installing : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/4
    Installing : postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64 3/4
    Installing : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 4/4
    Verifying : postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64 1/4
    Verifying : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 2/4
    Verifying : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 3/4
    Verifying : postgresql96-9.6.3-1PGDG.rhel7.x86_64 4/4
    Installed:
    postgresql96.x86_64 0:9.6.3-1PGDG.rhel7 postgresql96-contrib.x86_64 0:9.6.3-1PGDG.rhel7 postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7 postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7
    Complete!
    [root@localhost src]#
    [root@localhost src]# ls /usr/pgsql-9.6/
    bin doc lib share
    root@localhost src]# ls /usr/pgsql-9.6/bin/
    clusterdb createuser dropuser pg_archivecleanup pg_config pg_dump pg_receivexlog pg_restore pg_test_fsync pg_xlogdump postgresql96-setup reindexdb
    createdb dropdb initdb pg_basebackup pg_controldata pg_dumpall pg_recvlogical pg_rewind pg_test_timing postgres postmaster vacuumdb
    createlang droplang oid2name pgbench pg_ctl pg_isready pg_resetxlog pg_standby pg_upgrade postgresql96-check-db-dir psql vacuumlo
    [root@localhost src]#
   
   

2. Cài đặt qua rpm
   Nếu môi trường cài đặt của bạn không thể connect ra internet bên ngoài. Bạn có thể download packages trực tiếp từ máy có kết nối tới internet từ đây. Sau đó cài đặt packages bằng lệnh rpm. Chú ý nếu cài đặt packages qua rpm, do quan hệ phụ thuộc giữa các package bạn phải cài đặt trước một số packages cần thiết.

   
    [root@localhost src]# rpm -qa | grep postgresql
    [root@localhost src]# (wget https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64.rpm https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm https://yum.postgresql.org/9.6/redhat/rhel-7-x86_64/postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm ) > /dev/null 2>&1
    [root@localhost src]# echo $?
    0
    [root@localhost src]# ls
    pgdg-redhat96-9.6-3.noarch.rpm postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm
    [root@localhost src]# ls
    pgdg-redhat96-9.6-3.noarch.rpm postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm
    [root@localhost src]# rpm -ivh postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-contrib-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm
    Preparing... ################################# [100%]
    Updating / installing...
    1:postgresql96-libs-9.6.3-1PGDG.rhe################################# [ 25%]
    2:postgresql96-9.6.3-1PGDG.rhel7 ################################# [ 50%]
    3:postgresql96-contrib-9.6.3-1PGDG.################################# [ 75%]
    4:postgresql96-server-9.6.3-1PGDG.r################################# [100%]
    [root@localhost src]# ls /usr/pgsql-9.6/
    bin doc lib share
    [root@localhost src]# ls /usr/pgsql-9.6/bin
    clusterdb createuser dropuser pg_archivecleanup pg_config pg_dump pg_receivexlog pg_restore pg_test_fsync pg_xlogdump postgresql96-setup reindexdb
    createdb dropdb initdb pg_basebackup pg_controldata pg_dumpall pg_recvlogical pg_rewind pg_test_timing postgres postmaster vacuumdb
    createlang droplang oid2name pgbench pg_ctl pg_isready pg_resetxlog pg_standby pg_upgrade postgresql96-check-db-dir psql vacuumlo
    [root@localhost src]#
   
   

Cài đặt PostgreSQL trên môi trường Windows và MacOS

Bạn có thể download installer cho Windows ở đây, và cho MacOS ở đây và cài đặt theo winzard của installer. Trên cả 2 môi trường này đều có thể build từ source, nếu có nhu cầu bạn có thể tham khảo manual và build.