云原生之使用docker部署Postgresql数据库

  • 一、Postgresql介绍
    • 1.PostgreSQL简介
    • 2.PostgreSQL的特点
  • 二、检查本地docker环境
    • 1.检查系统版本
    • 2.检查docker版本
    • 3.检查docker状态
  • 三、下载Postgresql镜像
  • 四、部署Postgresql数据库
    • 1.创建Postgresql容器
    • 2.查看Postgresql容器状态
    • 3.查看Postgresql容器日志
  • 五、远程连接Postgresql数据库
    • 1.psql客户端连接
    • 2.查看Postgresql内的所有数据库

一、Postgresql介绍

1.PostgreSQL简介

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的BSD许可证下发行。

2.PostgreSQL的特点

  • 维护者是PostgreSQL Global Development Group，首次发布于1989年6月。
  • 操作系统支持WINDOWS、Linux、UNIX、MAC OS X、BSD。
  • 从基本功能上来看，支持ACID、关联完整性、数据库事务、Unicode多国语言。
  • 表和视图方面，PostgreSQL支持临时表，而物化视图，可以使用PL/pgSQL、PL/Perl、PL/Python或其他过程语言的存储过程和触发器模拟。
  • 索引方面，全面支持R-/R+tree索引、哈希索引、反向索引、部分索引、Expression 索引、GiST、GIN（用来加速全文检索），从8.3版本开始支持位图索引。
  • 其他对象上，支持数据域，支持存储过程、触发器、函数、外部调用、游标7）数据表分区方面，支持4种分区，即范围、哈希、混合、列表。
  • 从事务的支持度上看，对事务的支持与MySQL相比，经历了更为彻底的测试。
  • ）My ISAM表处理方式方面，MySQL对于无事务的MyISAM表，采用表锁定，1个长时间运行的查询很可能会阻碍对表的更新，而PostgreSQL不存在这样的问题。
  • 从存储过程上看，PostgreSQL支持存储过程。因为存储过程的存在也避免了在网络上大量原始的SQL语句的传输，这样的优势是显而易见的。
  • 用户定义函数的扩展方面，PostgreSQL可以更方便地使用UDF（用户定义函数）进行扩展。

二、检查本地docker环境

1.检查系统版本

[root@docker ~]# cat /etc/os-release NAME="CentOS Linux"VERSION="7 (Core)"ID="centos"ID_LIKE="rhel fedora"VERSION_ID="7"PRETTY_NAME="CentOS Linux 7 (Core)"ANSI_COLOR="0;31"CPE_NAME="cpe:/o:centos:centos:7"HOME_URL="https://www.centos.org/"BUG_REPORT_URL="https://bugs.centos.org/"CENTOS_MANTISBT_PROJECT="CentOS-7"CENTOS_MANTISBT_PROJECT_VERSION="7"REDHAT_SUPPORT_PRODUCT="centos"REDHAT_SUPPORT_PRODUCT_VERSION="7"

2.检查docker版本

[root@docker ~]# docker versionClient: Docker Engine - Community Version: 20.10.22 API version: 1.41 Go version:go1.18.9 Git commit:3a2c30b Built: Thu Dec 15 22:30:24 2022 OS/Arch: linux/amd64 Context: default Experimental:trueServer: Docker Engine - Community Engine:Version:20.10.22API version:1.41 (minimum version 1.12)Go version: go1.18.9Git commit: 42c8b31Built:Thu Dec 15 22:28:33 2022OS/Arch:linux/amd64Experimental: false containerd:Version:1.6.14GitCommit:9ba4b250366a5ddde94bb7c9d1def331423aa323 runc:Version:1.1.4GitCommit:v1.1.4-0-g5fd4c4d docker-init:Version:0.19.0GitCommit:de40ad0

3.检查docker状态

[root@docker ~]# systemctl status docker● docker.service - Docker Application Container Engine Loaded: loaded (/usr/lib/systemd/system/docker.service; enabled; vendor preset: disabled) Active: active (running) since Thu 2023-01-12 20:05:15 CST; 4 days ago Docs: https://docs.docker.com Main PID: 5888 (dockerd)Tasks: 10 Memory: 112.9M CGroup: /system.slice/docker.service └─5888 /usr/bin/dockerd -H fd:// --containerd=/run/containerd/containerd.sock

三、下载Postgresql镜像

[root@docker ~]# docker pull postgresUsing default tag: latestlatest: Pulling from library/postgres8740c948ffd4: Pull complete c8dbd2beab50: Pull complete 05d9dc9d0fbd: Pull complete ddd89d5ec714: Pull complete f98bb9f03867: Pull complete 0554611e703f: Pull complete 64e0a8694477: Pull complete 8b868a753f47: Pull complete 12ed9aefbab3: Pull complete 825b08d51ffd: Pull complete 8f272b487267: Pull complete ba2eed7bd2cc: Pull complete ff59f63f47d6: Pull complete Digest: sha256:6b07fc4fbcf551ea4546093e90cecefc9dc60d7ea8c56a4ace704940b6d6b7a3Status: Downloaded newer image for postgres:latestdocker.io/library/postgres:latest

四、部署Postgresql数据库

1.创建Postgresql容器

docker run -d --name postgres --restart always -e POSTGRES_PASSWORD='admin' -e POSTGRES_USER='admin' -e ALLOW_IP_RANGE=0.0.0.0/0 -v /data/postgres/data:/var/lib/postgresql -p 55433:5432 -d postgres
-name : 自定义容器名称-e POSTGRES_PASSWORD： Postgresql数据库密码-e POSTGRES_USER： Postgresql数据库账号-e ALLOW_IP_RANGE=0.0.0.0/0，这个表示允许所有ip访问，如果不加，则非本机 ip 访问不了。-v : 本地文件系统目录：容器内目录-p： 映射端口,宿主机端口：容器端口

2.查看Postgresql容器状态

[root@docker ~]# docker psCONTAINER ID IMAGECOMMANDCREATED STATUS PORTS NAMESde5b3c8570a9 postgres "docker-entrypoint.s…" 6 minutes ago Up 6 minutes 0.0.0.0:55433->5432/tcp, :::55433->5432/tcp postgres

3.查看Postgresql容器日志

查看Postgresql容器日志，检查Postgresql数据库是否正常启动。

[root@docker ~]# docker logs postgresThe 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.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 /var/lib/postgresql/data ... okcreating subdirectories ... okselecting dynamic shared memory implementation ... posixselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting default time zone ... Etc/UTCcreating configuration files ... okrunning bootstrap script ... okperforming post-bootstrap initialization ... oksyncing data to disk ... okSuccess. You can now start the database server using:pg_ctl -D /var/lib/postgresql/data -l logfile startinitdb: warning: enabling "trust" authentication for local connectionsinitdb: 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.waiting for server to start....2023-01-17 06:10:55.857 UTC [47] LOG:starting PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit2023-01-17 06:10:55.883 UTC [47] LOG:listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"2023-01-17 06:10:55.962 UTC [50] LOG:database system was shut down at 2023-01-17 06:10:54 UTC2023-01-17 06:10:55.988 UTC [47] LOG:database system is ready to accept connections doneserver startedCREATE DATABASE/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*2023-01-17 06:10:56.314 UTC [47] LOG:received fast shutdown requestwaiting for server to shut down....2023-01-17 06:10:56.349 UTC [47] LOG:aborting any active transactions2023-01-17 06:10:56.351 UTC [47] LOG:background worker "logical replication launcher" (PID 53) exited with exit code 12023-01-17 06:10:56.351 UTC [48] LOG:shutting down2023-01-17 06:10:56.375 UTC [48] LOG:checkpoint starting: shutdown immediate2023-01-17 06:10:56.783 UTC [48] LOG:checkpoint complete: wrote 918 buffers (5.6%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.016 s, sync=0.306 s, total=0.433 s; sync files=250, longest=0.253 s, average=0.002 s; distance=4217 kB, estimate=4217 kB2023-01-17 06:10:56.787 UTC [47] LOG:database system is shut down doneserver stoppedPostgreSQL init process complete; ready for start up.2023-01-17 06:10:56.896 UTC [1] LOG:starting PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit2023-01-17 06:10:56.897 UTC [1] LOG:listening on IPv4 address "0.0.0.0", port 54322023-01-17 06:10:56.897 UTC [1] LOG:listening on IPv6 address "::", port 54322023-01-17 06:10:56.948 UTC [1] LOG:listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"2023-01-17 06:10:57.000 UTC [63] LOG:database system was shut down at 2023-01-17 06:10:56 UTC2023-01-17 06:10:57.026 UTC [1] LOG:database system is ready to accept connections2023-01-17 06:15:57.100 UTC [61] LOG:checkpoint starting: time2023-01-17 06:16:01.354 UTC [61] LOG:checkpoint complete: wrote 44 buffers (0.3%); 0 WAL file(s) added, 0 removed, 0 recycled; write=4.110 s, sync=0.055 s, total=4.254 s; sync files=12, longest=0.029 s, average=0.005 s; distance=252 kB, estimate=252 kB

五、远程连接Postgresql数据库

1.psql客户端连接

[root@docker ~]# psql -h192.168.3.157 -p 55433 -Uadmin -WPassword: psql (13.9, server 15.1 (Debian 15.1-1.pgdg110+1))WARNING: psql major version 13, server major version 15. Some psql features might not work.Type "help" for help.admin=#

2.查看Postgresql内的所有数据库

admin=# \l List of databases Name| Owner | Encoding |Collate | Ctype| Access privileges -----------+-------+----------+------------+------------+------------------- admin | admin | UTF8 | en_US.utf8 | en_US.utf8 |postgres| admin | UTF8 | en_US.utf8 | en_US.utf8 |template0 | admin | UTF8 | en_US.utf8 | en_US.utf8 | =c/admin + | |||| admin=CTc/admin template1 | admin | UTF8 | en_US.utf8 | en_US.utf8 | =c/admin + | |||| admin=CTc/admin(4 rows)admin=#