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 initdbThe files belonging to this databasesystem will be owned by user "postgres".This user must also own the server process.The database cluster will be initialized with localesCOLLATE: C CTYPE: C.UTF-8 MESSAGES: C.UTF-8 MONETARY: C.UTF-8NUMERIC: C.UTF-8TIME: C.UTF-8The defaulttext search configuration will be setto"english".Datapage checksums are disabled.creating directory /var/db/postgres/data96 ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default timezone ... PRCselecting dynamic shared memory implementation ... posixcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing datatodisk ... okWARNING: enabling "trust"authenticationforlocal connectionsYou can change this by editing pg_hba.conf orusing the option-A, or--auth-local and --auth-host, the next time you run initdb.Success. You can nowstart the databaseserverusing:/usr/local/bin/pg_ctl -D /var/db/postgres/data96 -l logfile startroot@ykla:~ #
登录使用
Postgresql 默认是没有 root 用户的,需要使用其创建的 postgres 用户登录。
示例输出:
root@ykla:~ # psqlpsql: 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 yklaEnter passwordfor new role:Enter it again:$#创建数据库$ createdb new_db#登录进数据库并将数据库权限赋予用户 ykla。$ psqlpsql (9.6.24)Type"help"for help.postgres=# ALTER USER ykla WITHENCRYPTEDPASSWORD'password';ALTERROLEpostgres=#postgres=# GRANT ALL PRIVILEGES ONDATABASE new_db TO ykla;GRANT#退出数据库postgres=# q$exitroot@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 控制面板: