# cd /usr/ports/databases/postgresql96-server/
# make install clean
加入启动项
# sysrc postgresql_enable=YES
初始化数据库
/usr/local/etc/rc.d/postgresql initdb
示例输出：
root@ykla:~ # /usr/local/etc/rc.d/postgresql initdb
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 locales
COLLATE: C
CTYPE: C.UTF-8
MESSAGES: C.UTF-8
MONETARY: C.UTF-8
NUMERIC: C.UTF-8
TIME: C.UTF-8
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/db/postgres/data96 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... PRC
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/bin/pg_ctl -D /var/db/postgres/data96 -l logfile start
root@ykla:~ #
登录使用
Postgresql 默认是没有 root 用户的，需要使用其创建的用户 postgres 登录。
示例输出:
root@ykla:~ # psql
psql: FATAL: role "root" does not exist
正确用法：
#切换用户
root@ykla:~ # su - postgres
#启动服务
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data96 -l logfile start
#创建新用户 ykla，并设置密码
$ createuser -sdrP ykla
Enter password for new role:
Enter it again:
$
#创建数据库
$ createdb new_db
#登录进数据库并将数据库权限赋予用户 ykla。
$ psql
psql (9.6.24)
Type "help" for help.
postgres=# ALTER USER ykla WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=#
postgres=# GRANT ALL PRIVILEGES ON DATABASE new_db TO ykla;
GRANT
#退出数据库
postgres=# q
$exit
root@ykla:~ #
Using base prefix '/usr/local'
New python executable in /home/vagrant/pgadmin4/bin/python3.8
Also creating executable in /home/vagrant/pgadmin4/bin/python
Installing setuptools, pip, wheel…done.
done.
安装 sqlite3
#pkg install py38-sqlite3
激活创建的虚拟环境.
#source pgadmin4/bin/activate.csh
你会看到 shell 已经变为（pgadmin4）（以下操作均在该 shell 下进行）
实例如下：
(pgadmin4) root@ykla:~ #
安装 pgAdmin4：
现在 pip 源一律要求使用 https，由于缺少 SSL 证书还需要安装。
(pgadmin4) root@ykla:~# pkg install ca_root_nss
然后对 pip 进行换源，此处使用清华源：
pip config set global.index-url https://pypi.tuna.tsinghua.edu.cn/simple
其中有依赖 openjpeg，先进行安装
(pgadmin4) root@ykla:~# pkg install openjpeg
如果报错：
WARNING: Retrying (Retry(total=3, connect=None, read=None, redirect=None, status=None)) after connection broken by 'SSLError(SSLCertVerificationError(1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: certificate is not yet valid (_ssl.c:1136)'))': /simple/pgadmin4/
是由于时间不正确引发的，先同步时间:
ntpdate ntp.api.bz
(pgadmin4) root@ykla:~# ntpdate ntp.api.bz
17 Dec 16:35:36 ntpdate[1453]: step time server 114.118.7.161 offset +401965.911037 sec
NOTE: Configuring authentication for SERVER mode.
Enter the email address and password to use for the initial pgAdmin user account:
Email address: your_email # 输入你的邮件地址
Password: your_new_password # 输入你的登录密码
Retype password: # 再次输入密码
Starting pgAdmin 4. Please navigate to http://0.0.0.0:5050 in your browser.
现在我们已经安装并运行了 pgAdmin4，并可以通过 http://ip:5050 访问 Web 控制面板：
