如何安装和部署PostgreSQL数据库

postgresql 安装部署

一：修改postgresql 9.5端口号

/etc/postgresql/9.5/main/postgresql.conf

port = 5432

保存，重复服务，就可生效

#listen_addresses = 'localhost'

#监听所有的IP

listen_addresses = '*'

sudo service postgresql restart

psql -U postgres -h 127.0.0.1

IP访问配置

vi /etc/postgresql/9.5/main/pg_hba.conf

# IPv4 local connections:

host all all 192.168.137.130/32 md5

host all all 192.168.137.0/32 md5

sudo service postgresql restart

二：网络方法

http://wiki.ubuntu.org.cn/PostgreSQL

sudo apt-get install postgresql

sudo apt-get install pgadmin3

配置

https://blog.csdn.net/zpf336/article/details/50843674

sudo su postgres

psql postgres

配置密码

ALTER USER postgres with PASSWORD 'postgres'

修改配置

vi /etc/postgresql/9.5/main/postgresql.conf

#password_encryption = on 改为 password_encryption = on

重启：

sudo service postgresql restart

psql -U postgres -h 127.0.0.1

psql -U postgres -h cp-kards.csd3yjynselh.rds.cn-north-1.amazonaws.com.cn

创建超级用户

sudo su postgres

sudo -u postgres createuser --superuser lduser

创建数据库

sudo -u postgres createdb -O lduser uppers_staging

配置密码

sudo -u postgres psql

\password lduser

psql -U lduser -d uppers_staging -h 127.0.0.1

查看权限

\du

GRANT ALL PRIVILEGES ON DATABASE uppers_staging to lduser;

配置IP访问

tail /etc/postgresql/9.5/main/pg_hba.conf

# IPv4 local connections:

host all all 192.168.137.130/32 md5

host all all 192.168.137.0/32 md5

三：删除pqsql

# 删除软件及其配置文件 sudo apt-get --purge remove postgresql* -y

查询包

dpkg -l postgresql*

安装指定版本

sudo apt-get install postgresql-9.5

四：编译安装后的 Bin路径

/usr/lib/postgresql/9.5/bin

增加Psql 搜索路径

vi /etc/profile

#psql path

PATH=$PATH:/usr/lib/postgresql/9.5/bin

export PATH

五：使用docker swarm部署

#docker-compose.yaml

version: "3.8"services:  postgresql-master:    image: bitnami/postgresql:11.11.0    hostname: postgresql    user: root    ports:      - '5432:5432'    environment:      - POSTGRESQL_POSTGRES_PASSWORD=Meng2022  #超级用户postgres密码      - POSTGRESQL_USERNAME=user01      - POSTGRESQL_PASSWORD=user2023      - POSTGRESQL_DATABASE=dolphinscheduler      - POSTGRESQL_LOG_TIMEZONE=Asia/Shanghai      - POSTGRESQL_TIMEZONE=Asia/Shanghai    volumes:      - /data/postgresql/data:/bitnami/postgresql/data      - /data/postgresql/conf/:/bitnami/postgresql/conf/#      - /data/postgresql/conf/postgresql.conf:/opt/bitnami/postgresql/conf/postgresql.conf#      - /data/postgresql/conf/pg_hba.conf:/opt/bitnami/postgresql/conf/pg_hba.conf      - /etc/localtime:/etc/localtime:ro    networks:      - meta    deploy:      mode: replicated      replicas: 1      placement:        constraints: [node.labels.dolphin-node == dolphin01]networks:  meta:    external: true