示例环境信息:
1、Linux服务器,CentOS 7.9 可访问互联网
环境安装
安装PG客户端:
[root@centos home]# yum install postgresql
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package postgresql.x86_64 0:9.2.24-9.el7_9 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 9.2.24-9.el7_9 for package: postgresql-9.2.24-9.el7_9.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-9.2.24-9.el7_9.x86_64
--> Running transaction check
---> Package postgresql-libs.x86_64 0:9.2.24-9.el7_9 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================
Installing:
postgresql x86_64 9.2.24-9.el7_9 updates 3.0 M
Installing for dependencies:
postgresql-libs x86_64 9.2.24-9.el7_9 updates 235 k
Transaction Summary
==============================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 3.3 M
Installed size: 17 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql-libs-9.2.24-9.el7_9.x86_64.rpm | 235 kB 00:00:00
(2/2): postgresql-9.2.24-9.el7_9.x86_64.rpm | 3.0 MB 00:00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 18 MB/s | 3.3 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-9.2.24-9.el7_9.x86_64 1/2
/sbin/ldconfig: /lib64/libstdc++.so.6.0.24-gdb.py is not an ELF file - it has the wrong magic bytes at the start.
Installing : postgresql-9.2.24-9.el7_9.x86_64 2/2
Verifying : postgresql-9.2.24-9.el7_9.x86_64 1/2
Verifying : postgresql-libs-9.2.24-9.el7_9.x86_64 2/2
Installed:
postgresql.x86_64 0:9.2.24-9.el7_9
Dependency Installed:
postgresql-libs.x86_64 0:9.2.24-9.el7_9
Complete!
安装PG服务端:
[root@centos home]# yum install postgresql-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package postgresql-server.x86_64 0:9.2.24-9.el7_9 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
==============================================================================================================================================================
Package Arch Version Repository Size
==============================================================================================================================================================
Installing:
postgresql-server x86_64 9.2.24-9.el7_9 updates 3.8 M
Transaction Summary
==============================================================================================================================================================
Install 1 Package
Total download size: 3.8 M
Installed size: 16 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql-server-9.2.24-9.el7_9.x86_64.rpm | 3.8 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-server-9.2.24-9.el7_9.x86_64 1/1
Verifying : postgresql-server-9.2.24-9.el7_9.x86_64 1/1
Installed:
postgresql-server.x86_64 0:9.2.24-9.el7_9
Complete!
客户端与服务端的区别:
服务端(PostgreSQL Server):
主体与位置:服务端程序通常部署在专门的服务器或主机上,负责存储、管理和处理数据库的所有数据。
作用:它运行着主数据库引擎,负责接收客户端请求,执行SQL语句,管理事务,确保数据的一致性、完整性和安全性,并将结果返回给客户端。服务端包含了数据库管理系统的核心组件,如进程管理器、查询解析器、优化器、执行器以及物理文件的读写操作等。
资源需求:服务端对硬件资源要求较高,尤其是CPU、内存和磁盘空间,因为它需要处理大量的并发访问和复杂的数据操作。
客户端(PostgreSQL Client):
主体与位置:客户端软件可以在任何安装了相应客户端工具并与服务端网络可达的计算机上运行,可以是开发人员的工作站、应用服务器或其他需要连接到数据库的设备。
作用:客户端主要用来与PostgreSQL服务端进行通信,提供用户接口,使得用户或应用程序能够通过发送SQL命令来创建、查询、更新或删除数据库中的数据。常见的客户端工具有命令行客户端psql、图形界面工具(如pgAdmin)、编程语言的数据库驱动程序(如Python的psycopg2库)等。
资源需求:客户端相对于服务端来说,其资源需求较小,主要是为用户提供友好的交互环境或者让应用程序能便捷地访问数据库。
简而言之,服务端是数据库系统的后端核心,负责存储和处理数据;而客户端则提供了前端访问手段,允许用户和应用程序以各种形式与数据库进行交互。
查看安装信息
[root@centos home]# systemctl list-unit-files | grep postgresql
postgresql.service disabled
初始化服务
[root@centos home]# sudo -u postgres /usr/bin/initdb /var/lib/pgsql/data/
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".
fixing permissions on existing directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... 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/bin/postgres -D /var/lib/pgsql/data
or
/usr/bin/pg_ctl -D /var/lib/pgsql/data -l logfile start
启动服务
[root@centos home]# systemctl start postgresql
检查端口监听
[root@centos home]# netstat -anp | grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 26821/postgres
tcp6 0 0 ::1:5432 :::* LISTEN 26821/postgres
unix 2 [ ACC ] STREAM LISTENING 254125 26821/postgres /tmp/.s.PGSQL.5432
unix 2 [ ACC ] STREAM LISTENING 254123 26821/postgres /var/run/postgresql/.s.PGSQL.5432
设置开机自启动
[root@centos home]# systemctl enable postgresql.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@centos home]#
更改内存,监听端口等重要信息,可以通过/var/lib/pgsql/data/postgresql.conf文件