Ubuntu 16.04下编译安装PostgreSQL 10.3
in 日常技巧 with 1 comment

Ubuntu 16.04下编译安装PostgreSQL 10.3

in 日常技巧 with 1 comment

事情要从明月登楼MySQL5.5升级到5.7 遇到的问题以及解决方法评论

我已经果断将MySQL转成 MariaDB 了,目前是5.5.59最新版!⌇●﹏●⌇

说起。

既然被吐槽了,干脆抽空直接上PostgreSQL吧(typecho早在2008年就支持pgsql

你可能对如下文章也感兴趣~

[Ubuntu 16.04下编译安装Nginx]
[Ubuntu 16.04下编译安装php7.2]

PostgreSQL-logo

前期准备

这是必须的,因为编译前的配置需要它们的支持。根据配置报错来吧,我不保证不会遇到错误(呵呵哒,我就是这样不负责任)

sudo apt-get install libreadline-dev zlib1g-dev libssl-dev libxml2-dev libperl-dev libpq-dev libpam0g-dev
sudo groupadd -r postgres
sudo useradd -r -g postgres -s /bin/false -d /usr/local/pgsql -M postgres

可以在这里获取源码包

截至到目前,最新稳定版本为PostgreSQL 10.3,那么就用它了。

下载,并进行解压操作

wget https://ftp.postgresql.org/pub/source/v10.3/postgresql-10.3.tar.gz
tar -zxvf postgresql-10.3.tar.gz
cd postgresql-10.3

你可以通过./configure --help查看配置选项并根据自己的实际需求进行配置

./configure \
--prefix=/usr/local/pgsql \
--with-pgport=5432 \
--with-perl \
--with-python \
--with-openssl \
--with-libxml \
--with-zlib \
--with-pam \
--enable-thread-safety

如果不报错的话就编译安装吧

make
make install

安装contrib目录下的工具(推荐)

cd contrib
make && sudo make install

安装完成

配置、创建数据目录

配置PGSQL_HOMEPGDATA

sudo vim /etc/profile

在最底下一行的上面添加如下内容:

#set for pgsql
export PGSQL_HOME=/usr/local/pgsql/bin
export PGDATA=/srv/data/postgresql
export PATH=$PATH:$PGSQL_HOME

保存并退出,刷新/etc/profile 使配置生效

source /etc/profile

sudo mkdir -p /srv/{data,logs}/postgresql/
sudo chown -R postgres:postgres /srv/{data,logs}/postgresql/

使用initdb进行初始化,输出信息如下:

The files belonging to this database system will be owned by user "xxxx".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
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.

fixing permissions on existing directory /srv/data/postgresql ... 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:

    pg_ctl -D /srv/data/postgresql -l logfile start
pg_ctl -D /srv/data/postgresql -l /srv/logs/postgresql/logfile start

systemd配置目录在/lib/systemd/system或者/lib/systemd/system
一般放置在后者路径,有必要的话软链接到前者路径下。

sudo vim /lib/systemd/system/postgresql.service

写入如下信息并保存:

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=postgres
Group=postgres

# Port number for server to listen on
Environment=PGPORT=5432

# Location of database directory
Environment=PGDATA=/srv/data/postgresql

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000

# ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -l /srv/logs/postgresql/logfile -w -t 300 
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

保存以后,设置开机自启以及开启服务:

systemctl enable postgresql.service
systemctl start postgresql.service

最后,记得重新编译PHP哟,详细可以见[Ubuntu 16.04下编译安装php7.2]

常用语句以及配置

$ psql -U postgres 以postgres登陆
postgres=#\l 列出所有的数据库
postgres=#create database test; 创建测试数据库
postgres=#\c test 切换到test 数据库
postgres=#create table test (id integer, name text);创建测试表
postgres=#insert into test values (1,'hello');插入测试数据
postgres=#select * from test;查询数据
postgres=#\password postgres 给postgres设置密码
postgres=#create user kay with login password 'qwe#1234';创建一个用户并设置密码
postgres=#GRANT ALL ON DATABASE test TO kaytest所有权限给kay
postgres=#create role kay 不带登陆属性
postgres=#alter role kay login ;给予登录属性
postgres=#\du 查看角色列表
postgres=#drop role name; 删除用户
$createuser NAME shell命令行直接创建用户
$dropuser NAME shell命令行直接删除用户
postgres=#grant all on all tables in schema SCHEMANAME to kay; 给用户某个schema的所有权限
postgres=#ALTER USER kay WITH PASSWORD 'qwe#12345';给其他用户重置密码
postgres=#\q 退出

允许远程访问,如果没有必要不推荐配置。
postgresql.conf修改或增加listen_addresses = '*'

pg_hba.conf增加

host all all 0.0.0.0/0 md5

推荐配置!
postgresql.conf查找Security and Authentication字段,

--EOF--

Responses
  1. 主题不错 哈哈哈

    Reply