# cd /usr/ports/databases/postgresql16-server/
# make install clean
查看安装后配置
root@ykla:~ # pkg info -D postgresql16-server
postgresql16-server-16.7_1:
On install:
For procedural languages and postgresql functions, please note that
you might have to update them when updating the server.
If you have many tables and many clients running, consider raising
kern.maxfiles using sysctl(8), or reconfigure your kernel
appropriately.
The port is set up to use autovacuum for new databases, but you might
also want to vacuum and perhaps backup your database regularly. There
is a periodic script, /usr/local/etc/periodic/daily/502.pgsql, that
you may find useful. You can use it to backup and perform vacuum on all
databases nightly. Per default, it performs `vacuum analyze'. See the
script for instructions. For autovacuum settings, please review
~postgres/data/postgresql.conf.
If you plan to access your PostgreSQL server using ODBC, please
consider running the SQL script /usr/local/share/postgresql/odbc.sql
to get the functions required for ODBC compliance.
Please note that if you use the rc script,
/usr/local/etc/rc.d/postgresql, to initialize the database, unicode
(UTF-8) will be used to store character data by default. Set
postgresql_initdb_flags or use login.conf settings described below to
alter this behaviour. See the start rc script for more info.
To set limits, environment stuff like locale and collation and other
things, you can set up a class in /etc/login.conf before initializing
the database. Add something similar to this to /etc/login.conf:
---
postgres:\
:lang=en_US.UTF-8:\
:setenv=LC_COLLATE=C:\
:tc=default:
---
and run `cap_mkdb /etc/login.conf'.
Then add 'postgresql_login_class="postgres"' to /etc/rc.conf, or
set it as the postgres user's login class in /etc/passwd.
======================================================================
To use PostgreSQL, enable it in rc.conf using
sysrc postgresql_enable=yes
To initialize the database, run
service postgresql initdb
You can then start PostgreSQL by running:
service postgresql start
For postmaster settings, see ~postgres/data/postgresql.conf
NB. FreeBSD's PostgreSQL port logs to syslog by default
See ~postgres/data/postgresql.conf for more info
NB. If you're not using a checksumming filesystem like ZFS, you might
wish to enable data checksumming. It can be enabled during
the initdb phase, by adding the "--data-checksums" flag to
the postgresql_initdb_flags rcvar. Otherwise you can enable it later by
using pg_checksums. Check the initdb(1) manpage for more info
and make sure you understand the performance implications.
======================================================================
SECURITY ADVICE
If upgrading from a version 16.x < 16.3
A security vulnerability was found in the system views pg_stats_ext
and pg_stats_ext_exprs, potentially allowing authenticated database
users to see data they shouldn't. If this is of concern in your
installation, run the SQL script /usr/local/share/postgresql/fix-CVE-2024-4317.sql
for each of your databases. For details, see
https://www.postgresql.org/support/security/CVE-2024-4317/
root@ykla:~ #
加入启动项
# service postgresql enable
初始化数据库
# service postgresql initdb
示例输出：
root@ykla:~ # service 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 this locale configuration:
provider: libc
LC_COLLATE: C
LC_CTYPE: C.UTF-8
LC_MESSAGES: C.UTF-8
LC_MONETARY: C.UTF-8
LC_NUMERIC: C.UTF-8
LC_TIME: C.UTF-8
The default text search configuration will be set to "english".
Data page checksums are disabled.
creating directory /var/db/postgres/data16 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
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
initdb: hint: 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/data16 -l logfile start
登录使用
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/data16 -l logfile start
#创建新用户 ykla，并设置密码
$ createuser -sdrP ykla
Enter password for new role:
Enter it again:
$
#创建数据库
$ createdb new_db
#登录进数据库并将数据库权限赋予用户 ykla。
$ psql
psql (16.7)
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 控制面板：
