PostgreSQL的安装和卸载,远程连接

Dear 丶 2023-07-24 09:10 17阅读 0赞

# 一,PostgreSQL的卸载 #

1.  查看版本号和系统类别：
    
        cat /etc/redhat-release
2.  如果是redhat/centos:(yum install)
    
    1.  yum 删除软件包：
        
            yum remove postgresql*
    2.  删除相关目录文件：
        
            rm -rf  /var/lib/pgsql
            rm -rf  /usr/pgsql*
    3.  删除pg相关用户组/用户
        
            userdel -r postgres
            groupdel postgres
3.  **rpm包**安装的使用以下方法来卸载
    
    1.  列出已安装的包， 并使用yum remove逐一删除。
        
            $ rpm -qa | grep postgresql
            postgresql-docs-8.0.3-1.1AX
            postgresql-libs-8.0.3-1.1AX
            freeradius-postgresql-1.0.1-3.RHEL4.1AX
            postgresql-Python-8.0.3-1.1AX
            postfix-2.1.5-4.2.2AX
            postgresql-8.0.3-1.1AX
            postgresql-contrib-8.0.3-1.1AX
            postgresql-pl-8.0.3-1.1AX
            postfix-pflogsumm-2.1.5-4.2.2AX
            compat-postgresql-libs-7.4.7-2.RHEL4.1.1AX
            postgresql-tcl-8.0.3-1.1AX
            postgresql-devel-8.0.3-1.1AX
            postgresql-jdbc-8.0.3-1.1AX
            postgresql-odbc-08.00.0100-1.1AX
            postgresql-server-8.0.3-1.1AX
            
            说明：
            查询命令格式： 
            rpm -q ( or --query) options 
            参数： 
            pkg1 ... pkgN ：查询已安装的软件包 
            详细选项
            -a                  查询所有安装的软件包 
            而grep post则是查找所有列表中包含"post"字符串的项
    2.  找到了旧有的版本，则卸载掉他们
        
            rpm -ev postgresql-docs-8.0.3-1.1AX
            rpm -ev --nodeps postgresql-libs-8.0.3-1.1AX
            rpm -ev freeradius-postgresql-1.0.1-3.RHEL4.1AX
            rpm -ev postgresql-python-8.0.3-1.1AX
            rpm -ev postfix-2.1.5-4.2.2AX
            rpm -ev --nodeps postgresql-8.0.3-1.1AX
            rpm -ev postgresql-contrib-8.0.3-1.1AX
            rpm -ev postgresql-pl-8.0.3-1.1AX
            rpm -ev postfix-pflogsumm-2.1.5-4.2.2AX
            rpm -ev --nodeps compat-postgresql-libs-7.4.7-2.RHEL4.1.1AX
            rpm -ev postgresql-tcl-8.0.3-1.1AX
            rpm -ev postgresql-devel-8.0.3-1.1AX
            rpm -ev postgresql-jdbc-8.0.3-1.1AX
            rpm -ev postgresql-odbc-08.00.0100-1.1AX
            rpm -ev postgresql-server-8.0.3-1.1AX
            说明：
            删除命令格式：
            rpm -e ( or --erase) options pkg1 ... pkgN 
            参数 
            pkg1 ... pkgN ：要删除的软件包 
            详细选项 
            --nodeps    不检查依赖性 
            通用选项 
            -v  显示附加信息 
            -vv 显示调试信息
    3.  删除服务管理脚本
        
            rm -f /etc/init.d/postgresql-10
4.  如果是ubuntu (apt-get install)
    
        dpkg -l |grep postgresql
    
    删除安装包
    
        sudo apt-get --purge remove postgresql\*
    
    删除相关配置文件及用户信息
    
        sudo rm -r /etc/postgresql/
        sudo rm -r /etc/postgresql-common/
        sudo rm -r /var/lib/postgresql/
        sudo userdel -r postgres
        sudo groupdel postgres

# 二,linux上PostgreSQL的安装 #

## 2.1 使用yum库安装postgresql11 ##

> https://www.postgresql.org/download/linux/redhat/

1.  下载pg的rpm库
    
        yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
2.  下载客户端包
    
        yum -y install postgresql11
3.  安装服务器软件包(可选)
    
        yum -y install postgresql11-server
4.  初始化数据库并启用开机自启(可选)
    
        /usr/pgsql-11/bin/postgresql-11-setup initdb
        systemctl enable postgresql-11
        systemctl start postgresql-11

## 2.2 使用rpm安装postgresql11.4 ##

### 1.下载rpm包 ###

下载地址: [https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86\_64/][https_download.postgresql.org_pub_repos_yum_11_redhat_rhel-7-x86_64]

或者直接执行wget下载

wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.2-x86_64/postgresql11-11.4-1PGDG.rhel7.x86_64.rpm
    wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.2-x86_64/postgresql11-libs-11.4-1PGDG.rhel7.x86_64.rpm
    wget https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7.2-x86_64/postgresql11-server-11.4-1PGDG.rhel7.x86_64.rpm

### 2.安装 ###

先安装相应依赖

yum install -y libicu systemd-sysv

安装rpm包，需要按照这个顺序安装，卸载就反序卸载

rpm -ivh postgresql11-libs-11.4-1PGDG.rhel7.x86_64.rpm
    rpm -ivh postgresql11-11.4-1PGDG.rhel7.x86_64.rpm
    rpm -ivh postgresql11-server-11.4-1PGDG.rhel7.x86_64.rpm

### 3.数据库初始化 ###

/usr/pgsql-11/bin/postgresql-11-setup initdb
    #Initializing database ... OK

### 4.启动 ###

systemctl enable postgresql-11  #设置开机启动psql
    systemctl start postgresql-11   #启动postgresql

### 5.查看状态 ###

[root@7beccf3c3c2c pg11.4]# systemctl status postgresql-11
    ● postgresql-11.service - PostgreSQL 11 database server
       Loaded: loaded (/usr/lib/systemd/system/postgresql-11.service; enabled; vendor preset: disabled)
       Active: active (running) since Fri 2019-07-26 08:00:06 UTC; 11min ago
         Docs: https://www.postgresql.org/docs/11/static/
      Process: 390 ExecStartPre=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
     Main PID: 395 (postmaster)
       CGroup: /system.slice/docker-7beccf3c3c2c90152d9c6eeb67fc2f4bdb854426e27e1e765a91011415f9650e.scope/system.slice/postgresql-11.service
               ├─395 /usr/pgsql-11/bin/postmaster -D /var/lib/pgsql/11/data/
               ├─396 postgres: logger   
               ├─398 postgres: checkpointer   
               ├─399 postgres: background writer   
               ├─400 postgres: walwriter   
               ├─401 postgres: autovacuum launcher   
               ├─402 postgres: stats collector   
               └─403 postgres: logical replication launcher   
    
    Jul 26 08:00:06 7beccf3c3c2c systemd[1]: Starting PostgreSQL 11 database server...
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.251 UTC [395] LOG:  listening on IPv6 address "::1", port 5432
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.251 UTC [395] LOG:  listening on IPv4 address "127.0.0.1", port 5432
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.253 UTC [395] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.254 UTC [395] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.267 UTC [395] LOG:  redirecting log output to logging collector process
    Jul 26 08:00:06 7beccf3c3c2c postmaster[395]: 2019-07-26 08:00:06.267 UTC [395] HINT:  Future log output will appear in directory "log".
    Jul 26 08:00:06 7beccf3c3c2c systemd[1]: Started PostgreSQL 11 database server.

### 6.卸载 ###

rpm -e postgresql11-server-11.4-1PGDG.rhel7.x86_64
    rpm -e postgresql11-11.4-1PGDG.rhel7.x86_64
    rpm -e postgresql11-libs-11.4-1PGDG.rhel7.x86_64
    
    rm -rf /usr/pgsql-11/
    rm -rf /var/lib/pgsql/

# 三,Windows上PostgreSQL的安装 #

这里使用 EnterpriseDB 来下载安装，EnterpriseDB 是全球唯一一家提供基于 PostgreSQL 企业级产品与服务的厂商。

下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads。

双击下载安装包，开始安装

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70][]

可以修改安装路径

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 1][]

选择安装组件，不懂的选就是全部勾上：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 2][]

设置数据库路径

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 3][]

设置超级用户的密码

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 4][]

设置端口号，可以直接用默认就行

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 5][]

直接点 Next

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 6][]

点 Next

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 7][]

去掉勾选，直接点 Finish

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 8][]

打开 pgAdmin 4

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XLRbQ9XV-1586511811357)(img/1586316580434.png)\]][img-XLRbQ9XV-1586511811357_img_1586316580434.png]

pgAdmin 主页如下

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YyL3VFAb-1586511811358)(img/1586316618461.png)\]][img-YyL3VFAb-1586511811358_img_1586316618461.png]

点击左侧的 Servers > Postgre SQL 11

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P5rQOTRJ-1586511811358)(img/1586316663436.png)\]][img-P5rQOTRJ-1586511811358_img_1586316663436.png]

输入密码，点击 OK 即可

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hBsGgfrv-1586511811358)(img/1586316708070.png)\]][img-hBsGgfrv-1586511811358_img_1586316708070.png]

控制面板如下

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hQkINWkR-1586511811359)(img/1586316917140.png)\]][img-hQkINWkR-1586511811359_img_1586316917140.png]

打开 SQL Shell(psql)

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 9]

# 四,使用pgAdmin4进行远程连接 #

> https://www.cnblogs.com/XiaoGuanYu/p/9892321.html

1.  修改用户密码
    
        su - postgres 
        切换用户，执行后提示符会变为 '-bash-4.2$'
        
        psql -U postgres
        登录数据库，执行后提示符变为 'postgres=#'
        
        ALTER USER postgres WITH PASSWORD 'postgres';   
        设置postgres用户密码为postgres
        
        \q 
        退出数据库
2.  开启远程访问
    
        vi /var/lib/pgsql/11/data/postgresql.conf
        
        修改
        #listen_addresses = 'localhost'
        为 
        listen_addresses='*'
        当然，此处'*'也可以改为任何你想开放的服务器IP
3.  Trusted remote connection
    
        vim /var/lib/pgsql/11/data/pg_hba.conf
        
            修改如下内容，信任指定服务器连接
        
            # IPv4 local connections:
        
            host    all            all      127.0.0.1/32      ident
        	host    all            all      192.168.139.101/22      md5
            host all all 192.168.139.101/22 （需要连接的服务器IP） md5
4.  重启服务
    
        systemctl restart postgresql-11
5.  测试连接

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 10]

# 五,pgAdmin4设置为中文 #

1.  首先，我们运行pgAdmin4，进入如下的软件界面，此时默认的界面语言为英文。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 11]  
2. 用户点击菜单栏上的【file】按钮，在呼出的文件选项中选择【preferences】

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 12]  
3. 紧接着，弹出了如下所示的pgAdmin4的首选项设置窗口。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 13]  
4. 在左侧的首选下列表下找到【Miscellaneous】的杂项，然后在杂项下拉菜单中选择【user language】的界面语言。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 14]  
5. 此时语言默认为“English”，我们点击下拉菜单，然后在语言列表中找到并选择【Chinese（simplified）】的简体中文语言,刷新即可。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 15]

[https_download.postgresql.org_pub_repos_yum_11_redhat_rhel-7-x86_64]: https://links.jianshu.com/go?to=https%3A%2F%2Fdownload.postgresql.org%2Fpub%2Frepos%2Fyum%2F11%2Fredhat%2Frhel-7-x86_64%2F
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70]: /images/20230528/7804b8065db44aa1b22055c92ca07d08.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 1]: /images/20230528/c9a83f60f406436d880a071c4690e399.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 2]: /images/20230528/0f57adcd71eb47858b65880833adb4f3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 3]: /images/20230528/c54ac5966fb348c69e8978dafd6e93f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 4]: /images/20230528/a1763c8fc3004cc89a8fd9164cada4ef.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 5]: /images/20230528/a66a77e648714f5497129bbb34257f73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 6]: /images/20230528/97c037cd42ef4982877ed40e4d9d96e7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 7]: /images/20230528/6dca19d2e1374ea9a48ee351ca650933.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 8]: /images/20230528/54888f1b36db47d2b2bf376c181a4547.png
[img-XLRbQ9XV-1586511811357_img_1586316580434.png]: /images/20230528/bbe294f310544dd6bfdeb7bfb6b8303b.png
[img-YyL3VFAb-1586511811358_img_1586316618461.png]: /images/20230528/a0b932a9e95744b3a20ed26876ace061.png
[img-P5rQOTRJ-1586511811358_img_1586316663436.png]: /images/20230528/91b5fd1366ba4bc988fcbbbb47da18a8.png
[img-hBsGgfrv-1586511811358_img_1586316708070.png]: /images/20230528/afc2fb2e194349aa948df9be2c4c1117.png
[img-hQkINWkR-1586511811359_img_1586316917140.png]: /images/20230528/915fea9a799b46a78cd9b49471e389a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 9]: /images/20230528/d722d91e81264919a474939c5f26f5bf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 10]: /images/20230528/c7d5b93d0c354fa2b7be49b24b6c0fc2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 11]: /images/20230528/b78637c1f433422db52d5ab8e91abedd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 12]: /images/20230528/831af8bfbcc64984947ac9f2f404100c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 13]: /images/20230528/0647436764484ee99ee6c0a274235997.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 14]: /images/20230528/60bb9ffc381546e8882b4f49a9b36ad1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI2ODM4MzE1_size_16_color_FFFFFF_t_70 15]: /images/20230528/c57edbc8e2754881a3e44b48961cf0ec.png