Cài đặt PostgreSQL

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
    
  1. 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.

Facebook Comments Box