基于Kubernetes部署PostgreSQL可以获得快速伸缩、故障转移、在线修复等优点。在《Kubernetes快速部署高可用PostgreSQL》中介绍了使用Stolon项目进行部署的方法和步骤。集群安装完毕后,可以通过pgsql命令行工具进行操作。我们更希望有一个WebUI的图形化工具,这里介绍pgAdmin4的安装和使用(以Ubuntu 18.04LTS为例)。
- 关于pgadm4工具:https://wiki.postgresql.org/wiki/Apt
- 其它PostgreSQL管理工具: https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools#pgAdmin_4
- Helm install: https://github.com/helm/charts/tree/master/stable/postgresql
1、安装pgadm4
获得 repository key,在 https://www.postgresql.org/media/keys/ACCC4CF8.asc,如下:
sudo apt-get install curl ca-certificates curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
创建安装源描述文件 /etc/apt/sources.list.d/pgdg.list。操作系统分发版名称为 codename-pgdg。在下面的例子中,需要将 stretch 替换为你所用的deb/ubuntu版本(可以使用命令 lsb_release -c来确定版本号),如下:
deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main
自动获取版本号并创建安装源的元文件,如下:
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
然后,更新软件包的列表,并安装:
sudo apt-get update
# 安装postgresql服务器,这里我使用K8s的集群,不需要安装。
#sudo apt-get install postgresql-11
# 安装pgadmin4,用于管理postgresql服务器。
sudo apt-get install pgadmin4
2、使用pgadmin4
输入命令pgadmin4,启动管理服务:
supermap@podc01:~/openthings/stolon-chart$ pgadmin4
Python path: "/usr/lib/python3/dist-packages"
Python Home: ""
Webapp path: "/usr/share/pgadmin4/web/pgAdmin4.py"
NOTE: Configuring authentication for DESKTOP mode.
pgAdmin 4 - Application Initialisation
======================================
将会自动打开浏览器,并启动管理页面。
在管理页面添加之前在《Kubernetes快速部署高可用PostgreSQL》设置的服务器10.1.1.201,端口30900,数据库postgres。
- 用户名stolon,登录口令通过下面方法获取。
PGPASSWORD=$(kubectl get secret --namespace stolon waxen-seal-stolon -o jsonpath="{.data.pg_su_password}" | base64 --decode; echo)
echo $PGPASSWORD
然后登录到该PostgreSQL服务,打开页面:
选中Databases/postgres,然后在菜单tool选择Query tool,打开查询页面,输入:
select * from test
3、在容器中运行pgadmin4
拉取容器镜像:
# https://hub.docker.com/r/dpage/pgadmin4
docker pull dpage/pgadmin4
运行容器实例,注意将管理端口映射出来。
Run a simple container over port 80:
docker pull dpage/pgadmin4 docker run -p 80:80 \ -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \ -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \ -d dpage/pgadmin4
Run a TLS secured container using a shared config/storage directory in /private/var/lib/pgadmin on the host:
docker pull dpage/pgadmin4 docker run -p 443:443 \ -v "/private/var/lib/pgadmin:/var/lib/pgadmin" \ -v "/path/to/certificate.cert:/certs/server.cert" \ -v "/path/to/certificate.key:/certs/server.key" \ -e "PGADMIN_DEFAULT_EMAIL=user@domain.com" \ -e "PGADMIN_DEFAULT_PASSWORD=SuperSecret" \ -e "PGADMIN_ENABLE_TLS=True" \ -d dpage/pgadmin4
Ref: https://info.crunchydata.com/blog/topic/pgadmin4
4、在kubernetes中运行pgadmin4
get chart
https://github.com/jjcollinge/pgadmin-chart
config
values.yaml
will make your pgAdmin deployment accessible by its IP address over plaintext HTTP.
To access your pgAdmin instance using a domain name over plaintext HTTP:
- set
service.type
toNodePort
- set
ingress.enabled
totrue
- reserve a static IP address in your Kubernetes cluster (using e.g.
gcloud compute addresses create my-pgadmin-static-ip --global
for GCP) - set
ingress.staticIPReservation
to the name of the static IP address reservation you created in step 3 - At your domain registrar, create an A record pointing to the static IP address you reserved in step 3
To access your pgAdmin instance using a domain name over HTTPS, do the above steps, and as well:
- Set
ingress.tls.enabled
totrue
- Set
ingress.tls.clusterIssuer
to the name of a cert-managerClusterIssuer
deployed in your Kubernetes cluster - Set
ingress.tls.externalDNSName
to the (fully-qualified) domain name you registered in step 5
install
#helm install --set pgadmin.username=myuser,pgadmin.password=mypassword
helm install --name pgadmin4 --namespace stolon
Default Credentials:
username: pgadmin4@pgadmin.org
password: admin
- ref
- Helm chart that deploys a pgAdmin instance to your Kubernetes, https://github.com/jjcollinge/pgadmin-chart
- Deploy a distributed AI stack to a multi-host or single-host Kubernetes, https://github.com/jay-johnson/deploy-to-kubernetes