postgresql安装

ubuntu上安装

安装

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql

查看状态

 sudo systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Thu 2024-02-29 02:43:27 UTC; 3min 7s ago
   Main PID: 3556 (code=exited, status=0/SUCCESS)
        CPU: 2ms

Feb 29 02:43:27 gslserver systemd[1]: Starting PostgreSQL RDBMS...
Feb 29 02:43:27 gslserver systemd[1]: Finished PostgreSQL RDBMS.

查看端口

设置 postgres 用户密码

sudo passwd postgres

进入postgres用户

sudo su - postgres
psql
 
#修改密码
\password postgres   

上述修改是pgsql服务器的密码，远程连接时需要这个密码。

查看所有数据库

\l

postgres=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
(3 rows)

帮助

\h

创建数据库

CREATE DATABASE test WITH OWNER=postgres ENCODING='UTF-8';

删除数据库

DROP DATABASE test ;

安装客户端

https://www.postgresql.org/ftp/pgadmin/pgadmin4/v8.3/windows/

查看配置文件数据文件位置

# su - postgres # 这里是切换到操作系统的postgres用户
$ psql # 缺省用户名的情况下，会把当前系统用户名当作数据库登录用户名，数据库的postgres是超级用户
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.
postgres=# show config_file;    --配置文件目录
               config_file
-----------------------------------------
 /etc/postgresql/16/main/postgresql.conf
(1 row)


postgres=# show data_directory; -- 查询数据储存目录
       data_directory
-----------------------------
 /var/lib/postgresql/16/main
(1 row)

客户端连接错误

postgresql 报错 FATAL: no pg_hba.conf entry for host 未配置允许远程连接 解决方法

问题原因
这是在远程连接时pg_hba.conf文件没有配置正确。

pg_hba.conf文件在Postgre安装文件目录下的data文件夹中。
最最后加上

md5为加密算法，或为trust

centos上安装

准备主机环境

配置网卡

vi /etc/sysconfig/network-scripts/ifcfg-eth0

TYPE=Ethernet
PROXY_METHOD=none
BROWSER_ONLY=no
BOOTPROTO=static
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
NAME=eth0
DEVICE=eth0
ONBOOT=yes
IPADDR=192.168.2.20
#IPADDR=10.1.0.200
NETMASK=255.255.255.0
#NETMASK=255.255.0.0
GATEWAY=192.168.2.1
DNS1=114.114.114.114

重启网络

systemctl restart network

关闭selinux

vi /etc/selinux/config

关闭防护墙

systemctl disable firewalld
systemctl stop firewalld

yum安装pg

登陆官网

https://www.postgresql.org/download/linux/redhat/
选择需要的版本

安装yum源

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

安装12版本pg

# Install PostgreSQL:
sudo yum install -y postgresql12-server

Installed:
  postgresql12-server.x86_64 0:12.11-1PGDG.rhel7

Dependency Installed:
  libicu.x86_64 0:50.2-4.el7_7              postgresql12.x86_64 0:12.11-1PGDG.rhel7              postgresql12-libs.x86_64 0:12.11-1PGDG.rhel7

Complete!

可选初始化数据库和自启动

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
sudo systemctl enable postgresql-12
sudo systemctl start postgresql-12

[root@pg1 yum.repos.d]# sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
Initializing database ... OK

[root@pg1 yum.repos.d]# sudo systemctl enable postgresql-12
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
[root@pg1 yum.repos.d]# sudo systemctl start postgresql-12

查找安装程序位置

 rpm -aql|grep postgres

参数优化

登陆psql

[root@pg1 etc]# su postgres
bash-4.2$ psql --help