Linux 下安装 PostgreSQL

官网下载

1. 直接下载.tar.gz 安装包，地址 https://www.enterprisedb.com/download-postgresql-binaries

2. 使用 wget 命令下载（推荐）

   wget -c https://get.enterprisedb.com/postgresql/postgresql-10.5-1-linux-x64-binaries.tar.gz

解压

  直接使用命令：
  
  tar -zxvf postgresql-10.5-1-linux-x64-binaries.tar.gz
  
  移动到指定目录
  
  mv pgsql/ /pg/pgsql/

创建 pgsql 用户并设置密码

[root@muggle local]# useradd postgres
[root@muggle local]# passwd postgres
Changing password for user postgres.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.

创建 pgsql 数据目录并给 postgres 用户授权

[root@muggle pg]# mkdir /pg/pgsql_data/
[root@muggle pg]# chown postgres /pg/pgsql_data/

初始化

切换用户为postgres

[root@muggle pg]# su - postgres
[postgres@muggle ~]$ 

初始化数据库

[postgres@muggle pg]$ /pg/pgsql/bin/initdb -D /pg/pgsql_data/
The 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 "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /pg/pgsql_data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
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.

Success. You can now start the database server using:

    /pg/pgsql/bin/pg_ctl -D /pg/pgsql_data/ -l logfile start

等待执行完毕，看到提示：/pg/pgsql/bin/pg_ctl -D /pg/pgsql_data/ -l logfile start，说明初始化成功。

启动数据库

[postgres@muggle ~]$ /pg/pgsql/bin/pg_ctl -D /pg/pgsql_data/ -l logfile start
waiting for server to start.... done
server started

查看进程

[postgres@muggle ~]$ ps -ef | grep postgres
root      2738 32180  0 21:38 pts/0    00:00:00 su - postgres
postgres  2739  2738  0 21:38 pts/0    00:00:00 -bash
postgres  2760     1  0 21:38 pts/0    00:00:00 /pg/pgsql/bin/postgres -D /pg/pgsql_data
postgres  2762  2760  0 21:38 ?        00:00:00 postgres: checkpointer process   
postgres  2763  2760  0 21:38 ?        00:00:00 postgres: writer process   
postgres  2764  2760  0 21:38 ?        00:00:00 postgres: wal writer process   
postgres  2765  2760  0 21:38 ?        00:00:00 postgres: autovacuum launcher process   
postgres  2766  2760  0 21:38 ?        00:00:00 postgres: stats collector process   
postgres  2767  2760  0 21:38 ?        00:00:00 postgres: bgworker: logical replication launcher  
postgres  2770  2739  2 21:39 pts/0    00:00:00 ps -ef
postgres  2771  2739  0 21:39 pts/0    00:00:00 grep --color=auto postgres

pgsql 默认的端口号为 5432

连接数据库

[postgres@muggle pgsql]$ ./bin/psql -h 127.0.0.1 -d postgres -U postgres -p 5432
psql.bin (10.5)
Type "help" for help.

postgres=# 

设置远程访问

修改配置文件postgresql.conf

[postgres@muggle pgsql_data]$ pwd
/pg/pgsql_data
[postgres@muggle pgsql_data]$ vi postgresql.conf

将# listen_addresses='localhost' 修改为 listen_addresses = '*'

修改配置文件pg_hba.conf

[postgres@muggle pgsql_data]$ vi pg_hba.conf
...
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    all             all             0.0.0.0/0              trust
...

添加一条 IP 授权记录，0.0.0.0/0 代表所有 ip 都可以访问。

设置完需要重启数据库才能生效

远程连接测试

不听乱世的耳语，只过自己想要的生活