PostgreSQL数据库安装手册
0. 准备CMake和LLVM编译环境依赖
升级LLVM和切换版本
在Ubuntu 16.04和18.04操作系统上，缺省的 apt 源用 apt install llvm 命令安装的版本较低。而 datasketches-postgresql 需要llvm-10的编译环境。考虑进一步升级 LLVM 到版本 10。参考 https://stackoverflow.com/questions/58242715/cannot-install-llvm-9-or-clang-9-on-ubuntu-16-04。以及参考LLVM APT Nightly Build网站。
Ubuntu 16.04
wget -O - https://apt.llvm.org/llvm-snapshot.gpg.key | sudo apt-key add -
sudo apt-add-repository "deb http://apt.llvm.org/xenial/ llvm-toolchain-xenial-10 main"
sudo apt-get update
sudo apt-get install -y clang-3.8 lldb-3.8 ## default clange version of ubuntu 16.04
sudo apt-get install -y llvm-3.8 llvm-3.8-dev llvm-3.8-runtime libllvm3.8
#sudo apt-get install -y clang-6.0 lldb-6.0 lld-6.0 ## needed by postgresql from apt
#sudo apt-get install -y llvm-6.0 llvm-6.0-dev llvm-6.0-runtime llvm-6.0-tools libllvm6.0
sudo apt-get install -y clang-10 lldb-10 lld-10 libllvm10 ## needed by datasketches-postgresql
sudo apt-get install -y llvm-10 llvm-10-dev llvm-10-runtime llvm-10-tools
sudo apt-get upgrade
Ubuntu 18.04
wget -O - https://apt.llvm.org/llvm-snapshot.gpg.key | sudo apt-key add -
sudo apt-add-repository "deb http://apt.llvm.org/bionic/ llvm-toolchain-bionic-10 main"
sudo apt-get update
sudo apt-get install -y clang-6.0 lldb-6.0 lld-6.0 ## needed by postgresql-12 from apt
sudo apt-get install -y llvm-6.0 llvm-6.0-dev llvm-6.0-runtime llvm-6.0-tools libllvm6.0
sudo apt-get install -y clang-10 lldb-10 lld-10 libllvm10 ## needed by datasketches-postgresql
sudo apt-get install -y llvm-10 llvm-10-dev llvm-10-runtime llvm-10-tools
sudo apt-get upgrade
用下面的脚本 clang-update-alternatives.sh 来设置活跃的 llvm 和 clang 版本。该脚本参考了这个网页。
#!/usr/bin/env sh
# llvm update function
function register_clang_version {
local version=$1
local priority=$2
update-alternatives \
--install /usr/bin/llvm-config llvm-config /usr/bin/llvm-config-${version} ${priority} \
--slave /usr/bin/llvm-lto llvm-lto /usr/bin/llvm-lto-${version} \
--slave /usr/bin/llvm-ar llvm-ar /usr/bin/llvm-ar-${version} \
--slave /usr/bin/llvm-as llvm-as /usr/bin/llvm-as-${version} \
--slave /usr/bin/llvm-bcanalyzer llvm-bcanalyzer /usr/bin/llvm-bcanalyzer-${version} \
--slave /usr/bin/llvm-cov llvm-cov /usr/bin/llvm-cov-${version} \
--slave /usr/bin/llvm-diff llvm-diff /usr/bin/llvm-diff-${version} \
--slave /usr/bin/llvm-dis llvm-dis /usr/bin/llvm-dis-${version} \
--slave /usr/bin/llvm-dwarfdump llvm-dwarfdump /usr/bin/llvm-dwarfdump-${version} \
--slave /usr/bin/llvm-extract llvm-extract /usr/bin/llvm-extract-${version} \
--slave /usr/bin/llvm-link llvm-link /usr/bin/llvm-link-${version} \
--slave /usr/bin/llvm-mc llvm-mc /usr/bin/llvm-mc-${version} \
--slave /usr/bin/llvm-nm llvm-nm /usr/bin/llvm-nm-${version} \
--slave /usr/bin/llvm-objdump llvm-objdump /usr/bin/llvm-objdump-${version} \
--slave /usr/bin/llvm-ranlib llvm-ranlib /usr/bin/llvm-ranlib-${version} \
--slave /usr/bin/llvm-readobj llvm-readobj /usr/bin/llvm-readobj-${version} \
--slave /usr/bin/llvm-rtdyld llvm-rtdyld /usr/bin/llvm-rtdyld-${version} \
--slave /usr/bin/llvm-size llvm-size /usr/bin/llvm-size-${version} \
--slave /usr/bin/llvm-stress llvm-stress /usr/bin/llvm-stress-${version} \
--slave /usr/bin/llvm-symbolizer llvm-symbolizer /usr/bin/llvm-symbolizer-${version} \
--slave /usr/bin/llvm-tblgen llvm-tblgen /usr/bin/llvm-tblgen-${version} \
--slave /usr/bin/lldb lldb /usr/bin/lldb-${version} \
--slave /usr/bin/lldb-server lldb-server /usr/bin/lldb-server-${version}
update-alternatives \
--install /usr/bin/clang clang /usr/bin/clang-${version} ${priority}
update-alternatives \
--install /usr/bin/clang++ clang++ /usr/bin/clang++-${version} ${priority}
}
# Remove all existing alternatives
sudo update-alternatives --remove-all llvm-config
sudo update-alternatives --remove-all clang
sudo update-alternatives --remove-all clang++
# exit on first error
set -e
#register_clang_version 3.8 100 ## uncomment on ubuntu 16.04 default llvm
#register_clang_version 6.0 200 ## uncomment on ubuntu 18.04 default llvm
register_clang_version 10 1000 ## our upgraded llvm
然后执行如下的命令。
$ sudo bash clang-update-alternatives.sh
update-alternatives: 错误: 无 llvm 的候选项
update-alternatives: 错误: 无 clang 的候选项
update-alternatives: 错误: 无 clang++ 的候选项
update-alternatives: 使用 /usr/bin/clang-3.8 来在自动模式中提供 /usr/bin/clang (clang)
update-alternatives: 使用 /usr/bin/clang++-3.8 来在自动模式中提供 /usr/bin/clang++ (clang++)
update-alternatives: 使用 /usr/bin/clang-10 来在自动模式中提供 /usr/bin/clang (clang)
update-alternatives: 使用 /usr/bin/clang++-10 来在自动模式中提供 /usr/bin/clang++ (clang++)
$ llvm-config --version
10.0.1
$ clang --version
Ubuntu clang version 10.0.1-++20200529023737+a634a80615b-1~exp1~20200529004335.167
Target: x86_64-pc-linux-gnu
Thread model: posix
InstalledDir: /usr/bin
$ clang++ --version
Ubuntu clang version 10.0.1-++20200529023737+a634a80615b-1~exp1~20200529004335.167
Target: x86_64-pc-linux-gnu
Thread model: posix
InstalledDir: /usr/bin
$ sudo update-alternatives --config clang ## switch to the version you like
$ sudo update-alternatives --config clang++
$ sudo update-alternatives --config llvm-config
升级CMake
Datasketches 核心库需要高版本的 CMake。在 Ubuntu 16.04 和 18.04 操作系统上，按如下步骤升级 CMake。去 https://github.com/Kitware/CMake/releases 下载 cmake-3.12.0.tar.gz。这里，我并没有卸载旧版本的 cmake，而是直接安装新版本，而且这样没有出现问题。
sudo apt install qt5-default ## dependency of cmake
cd ~/Downloads
wget https://github.com/Kitware/CMake/releases/download/v3.12.0/cmake-3.12.0.tar.gz
tar zxvf cmake-3.12.0.tar.gz
cd cmake-3.12.0
./bootstrap
./configure
make
#make test
sudo make install
cmake --version ## 应该返回 cmake version 3.12.0，否则安装失败
# cmake version 3.12.0
#
# CMake suite maintained and supported by Kitware (kitware.com/cmake).
1. Install and Setup PostgreSQL Prebuilt Package via Homebrew on Mac or APT on Linux
MacOS上按照下面步骤用Homebrew安装PostgreSQL Prebuild
https://bundupress.com/code-skills/install-and-setup-postgresql-via-homebrew-on-mac-or-linux/
（放弃）Ubuntu 上用APT安装，这个安装方式缺省依赖LLVM-6.0，后面数据流插件安装不了
参考：
- https://www.postgresql.org/download/linux/ubuntu/
- https://wiki.postgresql.org/wiki/Apt
- https://wiki.postgresql.org/wiki/Apt/FAQ#Development_snapshots
Create the file /etc/apt/sources.list.d/pgdg.list and add a line for the repository
# Create the file repository configuration:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg-testing main 13" > /etc/apt/sources.list.d/pgdg.list' ## postgresql-13 beta1
# Import the repository signing key:
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
The repository contains many different packages including third party addons. The most common and important packages are (substitute the version number as required). https://www.postgresql.org/download/linux/ubuntu/
| postgresql-client-12 | client libraries and client binaries |
|---|---|
| Postgresql-12 | core database server |
| postgresql-contrib | additional supplied modules (part of the postgresql-xx package in version 10 and later) |
| libpq-dev | libraries and headers for C language frontend development |
| Postgresql-server-dev-12 | libraries and headers for C language backend development |
| pgadmin4 | pgAdmin 4 graphical administration utility |
然后，安装postgresql。我们选择最新的12版本。会发现postgresql自动安装了llvm-6.0的依赖。
Note: Datasketches-postgresql 在postgresql 11 relase上面测试过。This code is expected to be compatible with PostgreSQL versions 9.4 and higher. It was tested with REL_11_STABLE branch.
from https://github.com/apache/incubator-datasketches-postgresql
PostgreSQL Adaptor for C++ DataSketches: This code is expected to be compatible with PostgreSQL versions 9.4 and higher. It was tested with REL_11_STABLE branch. PostreSQL must be installed to compile the extension. The path to PostgreSQL executables must be set up (try running 'pg_config' to test). For PostgreSQL installation instructions see PostgreSQL documentation
要确认postgresql-12需要libllvm6.0依赖只需要执行下面的命令。
$ apt-cache depends postgresql-12
postgresql-12
|depends: locales
locales-all:i386
locales-all
depends: locales-all
locales-all:i386
depends: postgresql-client-12
postgresql-client-12:i386
depends: postgresql-common
depends: ssl-cert
depends: tzdata
depends: debconf
depends: <debconf-2.0>
cdebconf
debconf
depends: libc6
depends: libgcc1
depends: libgssapi-krb5-2
depends: libicu55
depends: libldap-2.4-2
depends: libllvm6.0
depends: libpam0g
depends: libpq5
depends: libselinux1
depends: libssl1.0.0
depends: libstdc++6
depends: libsystemd0
depends: libuuid1
depends: libxml2
depends: libxslt1.1
depends: zlib1g
recommends: sysstat
$ apt-cache depends postgresql-13
postgresql-13
|Depends: locales
Depends: locales-all
locales-all:i386
Depends: postgresql-client-13
postgresql-client-13:i386
Depends: postgresql-common
Depends: ssl-cert
Depends: tzdata
|Depends: debconf
Depends: <debconf-2.0>
cdebconf
debconf
Depends: libc6
Depends: libgcc1
Depends: libgssapi-krb5-2
Depends: libicu60
Depends: libldap-2.4-2
Depends: libllvm6.0
Depends: libpam0g
Depends: libpq5
Depends: libselinux1
Depends: libssl1.1
Depends: libstdc++6
Depends: libsystemd0
Depends: libuuid1
Depends: libxml2
Depends: libxslt1.1
Depends: zlib1g
Recommends: sysstat
PostgreSQL 11
sudo apt-get purge libpq-dev libpq5 postgresql-client-common postgresql-common postgresql ## remove old versions
sudo apt-get install -y postgresql-11 postgresql-contrib-9.6
sudo apt-get install -y postgresql-server-dev-11
PostgreSQL 12
sudo apt-get purge libpq-dev libpq5 postgresql-client-common postgresql-common postgresql ## remove old versions
sudo apt-get install -y postgresql-12 postgresql-contrib
sudo apt-get install -y postgresql-server-dev-12
什么是 postgresql-contrib?
postgresql-contrib 或者说 contrib 包，包含一些不属于 PostgreSQL 核心包的实用工具和功能。在大多数情况下，最好将 contrib 包与 PostgreSQL 核心一起安装。
在Ubuntu上面如何使用PostgreSQL，详细信息请查看此网页。下面是验证PostgreSQL安装的简单步骤。
Switch over to the postgres account on your server by typing:
sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
psql
This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.
Exit out of the PostgreSQL prompt by typing:
\q
This will bring you back to the postgres Linux command prompt.
2. Ubuntu上另一种选择，从源代码编译安装 PostgreSQL
Ubuntu上必须从源代码安装PostgreSQL，这样才能让该数据库依赖高版本的LLVM（至少版本10）。
背景知识：PostgreSQL 11 之后的版本引入了LLVM JIT (Just-In-Time)机制来加速SQL查询。
- Installing and Configuring JIT in PostgreSQL 11
- https://www.postgresql.org/docs/12/jit-extensibility.html
Installation Procedure
参考：
Before we begin, let's see if we have PostgreSQL packages already installed on our Ubuntu 18.04 system. We show various ways below to detect whether remants of postgres exist on our system. As you can, our system is clean of previous releases.
下面这些命令返回结果应该都是空的。
which psql
ps -e | grep post
dpkg -l | grep postgres
dpkg -l | grep libpq
ldconfig -p | grep libpq
If it is installed, you can perform a remove or purge. Note that a purge will remove the configuration files along with the packages.
sudo apt-get purge libpq-dev libpq5 postgresql-client-common postgresql-common postgresql
sudo apt-get autoremove
如果 ldconfig -p | grep libpq 命令运行结果存在下面情况
libpqwalreceiver.so (libc6,x86-64) => /usr/local/pgsql/lib/libpqwalreceiver.so
libpq.so.5 (libc6,x86-64) => /usr/local/pgsql/lib/libpq.so.5
libpq.so (libc6,x86-64) => /usr/local/pgsql/lib/libpq.so
进入到本用户的 postgresql 目录：cd ~/P4/postgresql/,执行卸载
sudo make uninstall
卸载后重新加载 config:sudo ldconfig，此时返回结果应为空
安装依赖
On Ubuntu
sudo apt-get update
sudo apt-get upgrade
sudo apt-get install -y cmake gcc g++ tar
sudo apt-get install -y tcl flex bison systemtap-sdt-dev
sudo apt-get install -y libreadline6-dev
sudo apt-get install -y libedit2 libperl-dev
sudo apt-get install -y krb5-kdc krb5-admin-server
sudo apt-get install -y zlib1g-dev
sudo apt-get install -y libxml2 libxml2-utils libxml2-dev libxslt-dev
sudo apt-get install -y slapd ldap-utils
sudo apt-get install -y libpam0g-dev python-dev
sudo apt-get install -y docbook-xml docbook-xsl xsltproc
On MacOS:
Compiling source code on Macs requires first installing either Xcode or the “Command Line Tools” (which is a much smaller download). To check if either is installed, run the xcode-select -p command. If you see an error message, then neither is installed and you can then run xcode-select --install which will prompt you to either get Xcode (by clicking “Get Xcode”) or to install the command line tools (by clicking “Install”). 用Homebrew的包管理工具安装依赖。
brew install gnu-tar readline libedit libxslt libxml2 zlib perl krb5 openssl openldap flex bison
brew cask install tcl
Also check that you have sufficient disk space. You will need about 100 MB for the source tree during compilation and about 20 MB for the installation directory. An empty database cluster takes about 35 MB; databases take about five times the amount of space that a flat text file with the same data would take. If you are going to run the regression tests you will temporarily need up to an extra 150 MB. Use the df command to check free disk space.
用下面的命令编译安装postgresql。这里缺省我们用了PostgreSQL版本11。版本12应该也可以。
git clone https://git.postgresql.org/git/postgresql.git
cd postgresql
git checkout "REL_11_STABLE" ## "REL_12_STABLE" is also OK on Ubunt 18.04
sudo make clean
sudo make uninstall ## remove previous installation
./configure --with-python --with-libxml --with-libxslt --with-openssl --with-pam --enable-depend --with-llvm LLVM_CONFIG='/usr/bin/llvm-config'
make world
make check
sudo make install-world
--enable-debugis used to enable gdb debugging option, so that we can set up a breakpoint to trace the source code.- --with-python: Build the PL/Python server-side language.
- --with-llvm: Build with support for LLVM based JIT compilation. This requires the LLVM library to be installed. The minimum required version of LLVM is currently 3.9. 可以测试一下
/usr/bin/llvm-config --version返回版本10.
Here, we use make world, because we want to build everything that can be built, including the documentation (HTML and man pages), and the additional modules (contrib). The command make install-world will install everything.
The default configuration will build the server and utilities, as well as all client applications and interfaces that require only a C compiler. All files will be installed under /usr/local/pgsql by default.
$ cd /usr/local/pgsql
$ tree -d -L 2
.
├── bin
├── include
│ ├── informix
│ ├── internal
│ ├── libpq
│ └── server
├── lib
│ ├── bitcode
│ ├── pgxs
│ └── pkgconfig
└── share
├── doc
├── extension
├── man
├── timezone
├── timezonesets
└── tsearch_data
17 directories
$ find . -name '*.conf.sample'
./share/pg_hba.conf.sample
./share/pg_ident.conf.sample
./share/postgresql.conf.sample
./share/pg_service.conf.sample
./share/recovery.conf.sample
Post-Installation Setup
参考 https://www.postgresql.org/docs/11/install-post.html
Shared Libraries 配置动态链接库的搜索路径
在Ubuntu系统
# edit libc.conf as root to append /usr/loccal/pgsql/lib
$ more /etc/ld.so.conf.d/libc.conf
/usr/local/lib
/usr/local/pgsql/lib
$ sudo ldconfig
$ sudo ldconfig -p | grep libpq
libpqwalreceiver.so (libc6,x86-64) => /usr/local/pgsql/lib/libpqwalreceiver.so
libpq.so.5 (libc6,x86-64) => /usr/local/pgsql/lib/libpq.so.5
libpq.so (libc6,x86-64) => /usr/local/pgsql/lib/libpq.so
在MacOS系统
sudo update_dyld_shared_cache
Environment Variables
you should add /usr/local/pgsql/bin into your PATH. Strictly speaking, this is not necessary, but it will make the use of PostgreSQL much more convenient. To do this, add the following to your shell start-up file, such as ~/.bash_rc (or/etc/profile`, if you want it to affect all users)。
建议放/etc/profile里面。后面还要创建postgresql用户组。And to enable your system to find the man documentation, you need to add lines like the following to a shell start-up file unless you installed into a location that is searched by default:
export PATH=/usr/local/pgsql/bin:$PATH
export MANPATH=/usr/local/pgsql/share/man:$MANPATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
export PGDATA=/usr/local/pgsql/data
这样设置还不够，sudo pg_config提示找不到命令。这是因为sudo在切换成root用户的时候，env并不会去保留这些环境变量，需要特别的指明才可以。向/etc/sudoers文件中env_reset下增加一行。
Defaults env_keep+="PATH PYTHONPATH LD_LIBRARY_PATH MANPATH JAVA_HOME"
另外，secure_path变量里面也要加上/usr/local/pgsql/bin路径。
Defaults secure_path="/usr/local/pgsql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin"
需要注意的是/etc/sudoers是只读文件，vi不能更改，只能使用visudo命令(不用加文件名)来更改内容。
下面测试一下配置结果。psql可执行程序在/usr/local/pgsql/bin中实现的。
$ source /etc/profile
$ sudo pg_config ## 返回和无su权限pg_config一样的结果
$ pg_config
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/share/doc
HTMLDIR = /usr/local/pgsql/share/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR = /usr/local/pgsql/share/locale
MANDIR = /usr/local/pgsql/share/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-python' '--with-libxml' '--with-libxslt' '--with-openssl' '--with-pam' '--enable-depend' '--with-llvm' 'LLVM_CONFIG=/usr/bin/llvm-config'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -O2
CFLAGS_SL = -fPIC
LDFLAGS = -L/usr/lib/llvm-10/lib -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lxslt -lxml2 -lpam -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 11.8
Basic Configure of PostgreSQL
参考：
As with any server daemon that is accessible to the outside world, it is advisable to run PostgreSQL under a separate user account. This user account should only own the data that is managed by the server, and should not be shared with other daemons. (For example, using the user nobody is a bad idea.) It is not advisable to install executables owned by this user because compromised systems could then modify their own binaries.
We want to work with a separate user account, and we'll follow the convention of using the user account postgres.
$ sudo deluser --system postgres ## remove the previous `postgres` account
$ sudo adduser --system --home /usr/local/pgsql/home --shell /bin/bash --group postgres
Adding system user `postgres' (UID 123) ...
Adding new group `postgres' (GID 130) ...
Adding new user `postgres' (UID 123) with group `postgres' ...
Creating home directory `/opt/pgsql/data' ...
$ sudo adduser postgres ssl-cert
Adding user `postgres' to group `ssl-cert' ...
Adding user postgres to group ssl-cert
Done.
$ sudo adduser postgres sudo # or optionally perform a visudo
Adding user `postgres' to group `sudo' ...
Adding user postgres to group sudo
Done.
$ sudo passwd postgres # set our user passwd
$ su postgres
$ cd
$ pwd
/usr/local/pgsql/home
$ sudo chown -R postgres:postgres /usr/local/pgsql
$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --pwprompt
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_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled
Enter new superuser password:
Enter it again:
creating directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/New_York
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: 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/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
Note the warning above about enabling trust authentication. Carefully review the documentation and setting in /usr/local/pgsql/data/pg_hba.conf
Starting the server and interacting with it
参考：https://www.postgresql.org/docs/11/server-start.html
Start the PostgresSQL server
$ su postgres
$ cd
$ pwd
/usr/local/pgsql/home
$ echo 'export PATH=$PATH:/usr/local/pgsql/bin' >> .bashrc
$ echo 'export MANPATH=/usr/local/pgsql/share/man:$MANPATH' >> .bashrc
$ echo 'export LD_LIBRARY_PATH=/usr/local/pgsql/lib' >> .bashrc
$ echo 'export PGDATA=/usr/local/pgsql/data' >> .bashrc
$ source .bashrc
$ tail -10 .bashrc
export PATH=$PATH:/usr/local/pgsql/bin
export MANPATH=/usr/local/pgsql/share/man:$MANPATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PGDATA=/usr/local/pgsql/data
$ pg_ctl start -l logfile
waiting for server to start.... done
server started
$ ps -e | grep postgres
2318 pts/1 00:00:00 postgres
2320 ? 00:00:00 postgres
...
Different systems have different conventions for starting up daemons at boot time. Many systems have a file /etc/rc.local or /etc/rc.d/rc.local. Others use init.d or rc.d directories. On Linux systems either add
su - postgres -c '/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data'
to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the PostgreSQL source distribution.
Start a command line connection.
$ psql
psql (11.8)
Type "help" for help.
postgres=#
Some basic interaction on the command line:
# select version();
version
-----------------------------------------------------------------------------
------------------------
PostgreSQL 11.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubunt
u1~18.04) 7.5.0, 64-bit
(1 row)
# \list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
(3 rows)
# \d
Did not find any relations.
# select 2+2;
----------
4
(1 row)
#\q
Consider carefully the timeout setting. systemd has a default timeout of 90 seconds as of this writing and will kill a process that does not notify readiness within that time. But a PostgreSQL server that might have to perform crash recovery at startup could take much longer to become ready. The suggested value of 0 disables the timeout logic.
Create an example database
参考：https://mindchasers.com/dev/postgres
Below we create a minimal database, table, and table entry.
$ psql
# create role dbowner with createdb login;
CREATE ROLE
# create database testdb owner dbowner;
CREATE DATABASE
# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+-------------+----------+-------------+-------------+-----------------------
testdb | dbowner | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
...
#\q
默认情况下，postgresql本机的连接是信任连接，只要有用户名就可以连接，不用密码。所以本机只要dbowner的用户名对了，就可以连接本地的pgsql服务。postgresql在缺省配置下，为了安全，不会监听除了本地外的所有请求。如果要接受远程连接，就必须修改data目录下的pg_hba.conf配置文件。详细参考：https://www.jianshu.com/p/f246dc45e6dc
psql -h url_or_ipaddr -p 3432 -d testdb -U dbowner
参数说明：
- -h：指定主机地址。
- -p：指定端口号。
- -d：指定数据库（默认的数据库是 postgres），
- -U：指定连接的用户。
$ psql testdb -U dbowner
# CREATE TABLE users (
uid integer PRIMARY KEY,
name varchar(32)
);
CREATE TABLE
# INSERT INTO users VALUES (1, 'Tony');
INSERT 0 1
# select * from users;
uid | name
-----+------
1 | Tony
Access our database using the C language
libpq 是 PostgreSQL 数据库的 C 语言接口，用户可在 C 程序中通过 libpq 库访问 PostgreSQL 数据库并进行数据库操作。
For this next step, we'll rely on some guidance provided by the following documentation on postgresql.org:
- Building libpq Programs
- Example Programs
- libpq - C Library: Command Execution Functions
Let's first build and run the testlibpq.c example provided in the above reference.
$ cd /home/p4-basic/P4/postgresql-learning/tests/c
$ gcc -O0 -g -I/usr/local/pgsql/include testlibpq.c -L/usr/local/pgsql/lib -lpq -o testlibpq
$ su postgres -c "./testlibpq"
oid datname datdba encoding datcollate datctype datistemplate datallowconn datconnlimit datlastsysoid datfrozenxid datminmxid dattablespace datacl
12695 postgres 10 6 en_US.UTF-8 en_US.UTF-8 f t -1 12694 480 1 1663
16385 testdb 16384 6 en_US.UTF-8 en_US.UTF-8 f t -1 12694 480 1 1663
1 template1 10 6 en_US.UTF-8 en_US.UTF-8 t t -1 12694 480 1 1663 {=c/postgres,postgres=CTc/postgres}
12694 template0 10 6 en_US.UTF-8 en_US.UTF-8 t f -1 12694 480 1 1663 {=c/postgres,postgres=CTc/postgres}
Next let's create a simple C program named testlibpq-testdb.c that accesses our database and prints out the first row from our users table:
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>
int main() {
PGconn *conn = PQconnectdb("dbname=testdb");
if (PQstatus(conn) == CONNECTION_BAD) {
fprintf(stderr, "Connection to database failed: %s\n", PQerrorMessage(conn));
PQfinish(conn);
exit(1);
}
PGresult *res = PQexec(conn, "SELECT * FROM users");
printf("id: %s\n", PQgetvalue(res, 0, 0));
printf("name: %s\n", PQgetvalue(res, 0, 1));
PQclear(res);
PQfinish(conn);
return 0;
}
Build it and run it (as postgres):
$ gcc -O0 -g -I/usr/local/pgsql/include testlibpq-testdb.c -L/usr/local/pgsql/lib -lpq -o testlibpq-testdb
$ su postgres -c "./testlibpq-testdb"
id: 1
name: Tony
Access our database using Python
Python 连接 PostgreSQL 采用的库是 psycopg2。使用步骤如下：
For Python access, we'll turn to the popular, third-party, open source library Psycopg. As stated on its web site: "Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL."
Psycopg2 wraps around the libpq library, which we used in the previous C programming examples. Below we clone psycopg2 and take a brief look at the source:
Note that we're performing the following steps as a typical Ubuntu Linux user and not postgres or root.
$ cd $P4_HOME
$ git clone https://github.com/psycopg/psycopg2.git
$ cd $P4_HOME/psycopg2/
$ ls
AUTHORS INSTALL LICENSE MANIFEST.in psycopg README.rst setup.cfg tests
doc lib Makefile NEWS psycopg2.cproj scripts setup.py tox.ini
$ find psycopg/ -name *.c | xargs grep PQ
psycopg/cursor_type.c: status = PQtransactionStatus(self->conn->pgconn);
psycopg/cursor_type.c: status = PQTRANS_UNKNOWN;
psycopg/cursor_type.c: if (status == PQTRANS_UNKNOWN || status == PQTRANS_INERROR) {
psycopg/cursor_type.c: if (PQgetisnull(self->pgres, row, i)) {
...
Below we choose to install psycopg2 into /opt/lib/python3.5/site-packages/ since we're experimenting with a master branch and don't want to conflict with stable installations.
$ which pg_config
/usr/local/pgsql/bin/pg_config
$ cd $P4_HOME/psycopg2/
$ sudo python3 setup.py build ## default python3.5 on ubuntu16.04
$ find . -name '*.so'
./build/lib.linux-x86_64-3.5/psycopg2/_psycopg.cpython-35m-x86_64-linux-gnu.so
$ su
$ source /etc/profile
$ mkdir /usr/local/lib/python3.5/site-packages/
$ PYTHONPATH=/usr/local/lib/python3.5/site-packages/ python3 setup.py install ## default python3 version on ubuntu 16.04 is python3.5
# Installed /usr/local/lib/python3.5/dist-packages/psycopg2-2.8.6.dev0-py3.5-linux-x86_64.egg
# Processing dependencies for psycopg2==2.8.6.dev0
# Finished processing dependencies for psycopg2==2.8.6.dev0
$ exit
$ ls /usr/local/lib/python3.5/dist-packages/psycopg2-2.8.6.dev0-py3.5-linux-x86_64.egg
EGG-INFO psycopg2
下面python3里面测试一下。
$ python3
import psycopg2
conn = psycopg2.connect("dbname='testdb' user='dbowner'")
cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows=cur.fetchall()
rows[0] # returns (1, 'Tony')
在python3.6环境下面安装psycopg2。
$ cd $P4_HOME/psycopg2/
$ sudo python3.6 setup.py build ## our added python3.6 on ubuntu16.04
$ find . -name '*.so'
./build/lib.linux-x86_64-3.5/psycopg2/_psycopg.cpython-35m-x86_64-linux-gnu.so
./build/lib.linux-x86_64-3.6/psycopg2/_psycopg.cpython-36m-x86_64-linux-gnu.so
$ su
$ source /etc/profile
$ mkdir /usr/local/lib/python3.6/site-packages/
$ PYTHONPATH=/usr/local/lib/python3.6/site-packages/ python3.6 setup.py install
# Installed /usr/local/lib/python3.6/site-packages/psycopg2-2.8.6.dev0-py3.6-linux-x86_64.egg
# Processing dependencies for psycopg2==2.8.6.dev0
# Finished processing dependencies for psycopg2==2.8.6.dev0
$ exit
$ ls /usr/local/lib/python3.6/site-packages/psycopg2-2.8.6.dev0-py3.6-linux-x86_64.egg/
EGG-INFO psycopg2
install的时候遇到报错'AttributeError: module 'enum' has no attribute 'IntFlag''。这是因为pip3.6环境下面安装了enum34组件。可以通过pip3.6 freeze | grep enum命令看到。Since python 3.6 the enum34 library is no longer compatible with the standard library. The library is also unnecessary, so you can simply uninstall it. 参考网页。要解决问题，只要sudo pip3.6 uninstall enum34删除这个不兼容软件包。
下面python3.6里面测试一下。
$ python3.6
import psycopg2
conn = psycopg2.connect("dbname='testdb' user='dbowner'")
cur = conn.cursor()
cur.execute("SELECT * FROM users")
rows=cur.fetchall()
rows[0] # returns (1, 'Tony')
Postgresql 查看数据库,表,索引,表空间以及大小
进入数据库命令行
$ su postgres
$ psql
- 查看数据库: \加上字母l，相当于mysql的，mysql> show databases;
# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privi
leges
-----------+----------+----------+-------------+-------------+---------------
--------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
# select pg_database_size('test');
pg_database_size
------------------
8389487
(1 row)
# select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
datname | size
-----------+---------
postgres | 8242031
csqjxiao | 8242031
template1 | 8094211
template0 | 8094211
test | 8389487
(5 rows)
# select pg_size_pretty(pg_database_size('test')); //以KB，MB，GB的方式来查看数据库大
pg_size_pretty
----------------
8193 kB
(1 row)
# \q
- 显示用户
$ psql test
# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
csqjxiao | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
# \q
- 查看多表和单表
$ psql test
# \dt //相当于mysql的，mysql> show tables;
psql (12.2)
Type "help" for help.
test=# \dt
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | cpc_sketch_test | table | csqjxiao
public | test_table | table | csqjxiao
(2 rows)
test=# \d test_table
Table "public.test_table"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | |
# \q
3. 系统学习 PostgreSQL Tutorials
参考：https://www.postgresqltutorial.com/
4. （可选）安装 PgAdmin 图形化界面
MACOS 系统安装 PgAdmin
PostgreSQL GUI 应用程序：你也可以安装用于管理 PostgreSQL 数据库的 GUI 应用程序（pgAdmin）。可以直接到pgAdmin 4的官方网页下载dmg安装文件。
Ubuntu APT安装PgAdmin
PostgreSQL GUI 应用程序：你也可以安装用于管理 PostgreSQL 数据库的 GUI 应用程序（pgAdmin）：
sudo apt-get install -y pgadmin4 pgadmin4-apache2
During the installation, you will be prompted the email username configuration and the password configuration.
Type your own email address that will be used as a username.
Next, you will be prompted for your password. Provide your preferred password. The system will then proceed to wrap up the installation of PgAdmin4.
Great ! At this point you have successfully installed PgAdmin4.
验证 PgAdmin 的安装
Now that we have successfully installed PgAdmin4, it’s time now to connect to it. 先用命令行的客户端查一下，看postgres服务有没有起来。
$ su postgres
$ psql
# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".
PgAdmin的web服务进程跑在Apache服务器里面。用浏览器可以直接连接。
Open your browser and browse your server’s IP address as shown
https://IP-address:57100/browser
In our example,
https://127.0.0.1:57100/browser
