WSL Ubuntu下启动Postgresql碰到的问题

1. WSL 相关版本信息

        在Win10下安装的wsl2, ubuntu 20.04.03 LTS, Postgresql 版本:14.2。

C:\Users\becker>wsl -l -v
  NAME            STATE           VERSION
* Ubuntu-20.04    Running         2

C:\Users\becker>bash
Welcome to Ubuntu 20.04.3 LTS (GNU/Linux 5.10.60.1-microsoft-standard-WSL2 x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

becker@DESKTOP-TMLSIJ4:/$ psql --version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)

becker@DESKTOP-TMLSIJ4:/$ psql --version
psql (PostgreSQL) 14.2 (Ubuntu 14.2-1.pgdg20.04+1)

2. 运行psql碰到的问题

        切换到postgres用户，通过psql启动postgresql客户端，发现启动不了，提示连接服务器失败。

becker@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ sudo su postgres
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$

        这时，第一反应是通过systemctl开启postgresql服务，结果开启失败（提示入下）：

postgres@DESKTOP-TMLSIJ4:/$ systemctl start postgresql
Traceback (most recent call last):
  File "/usr/bin/systemctl", line 4521, in <module>
    result = command_func(*modules)
  File "/usr/bin/systemctl", line 1755, in start_modules
    return self.start_units(units, init) and found_all
  File "/usr/bin/systemctl", line 1765, in start_units
    if not self.start_unit(unit):
  File "/usr/bin/systemctl", line 1783, in start_unit
    return self.start_unit_from(conf)
  File "/usr/bin/systemctl", line 1793, in start_unit_from
    return self.do_start_unit_from(conf)
  File "/usr/bin/systemctl", line 1852, in do_start_unit_from
    self.execve_from(conf, newcmd, env)
  File "/usr/bin/systemctl", line 2049, in execve_from
    out = self.open_journal_log(conf)
  File "/usr/bin/systemctl", line 1638, in open_journal_log
    return open(os.path.join(log_file), "a")
PermissionError: [Errno 13] Permission denied: '/var/log/journal/postgresql.service.log'
ERROR:systemctl:oneshot start failed (1) <->
ERROR:systemctl:writing STATUS {'AS': 'failed'}: [Errno 13] Permission denied: '/var/run/postgresql.service.status'
         to status file /var/run/postgresql.service.status

 提示授权拒绝，猜测可能是权限不够，因此改用 sudo systemctl start postgresql，期间提示输入postgres密码，这时意识到不知道postgres密码。因此通过 sudo passwd -d postgres删掉postgres的密码，再通过sudo -u postgres passwd为postgres重设密码。再次运行sudo systemctl start postgresql启动服务，发现系统没有反馈异常，难道这就解决问题了？事实上并没有，运行psql仍然无法进入postgresql界面。

postgres@DESKTOP-TMLSIJ4:/$ sudo systemctl start postgresql
[sudo] password for postgres:
Sorry, try again.
[sudo] password for postgres:
sudo: 1 incorrect password attempt
postgres@DESKTOP-TMLSIJ4:/$ exit
exit
becker@DESKTOP-TMLSIJ4:/$ sudo passwd -d postgres
passwd: password expiry information changed.
becker@DESKTOP-TMLSIJ4:/$ sudo -u postgres passwd
New password:
Retype new password:
passwd: password updated successfully
becker@DESKTOP-TMLSIJ4:/$ sudo systemctl start postgresql

becker@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ sudo su postgres
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$

 因为提示connection to server on socket "/var/run/postgresql/.s.PGSQL.5432，所以去看看5432端口的状态如何？安装net-tools后运行netstat -nlp|grep 5432

        安装net-tools

becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ netstat -nlp | grep 5432

Command 'netstat' not found, but can be installed with:

sudo apt install net-tools

becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ sudo apt install net-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required

....


Use 'sudo apt autoremove' to remove them.
The following NEW packages will be installed:
  net-tools
0 upgraded, 1 newly installed, 0 to remove and 61 not upgraded.
Need to get 196 kB of archives.
After this operation, 864 kB of additional disk space will be used.
Get:1 http://archive.ubuntu.com/ubuntu focal/main amd64 net-tools amd64 1.60+git20180626.aebd88e-1ubuntu1 [196 kB]
Fetched 196 kB in 2s (113 kB/s)
Selecting previously unselected package net-tools.
(Reading database ... 64399 files and directories currently installed.)
Preparing to unpack .../net-tools_1.60+git20180626.aebd88e-1ubuntu1_amd64.deb ...
Unpacking net-tools (1.60+git20180626.aebd88e-1ubuntu1) ...
Setting up net-tools (1.60+git20180626.aebd88e-1ubuntu1) ...
Processing triggers for man-db (2.9.1-1) ...

        运行netstat -nlp | grep 5432,发现没有任何信息反馈，说明postgresql服务没有顺利启动。不出意料的再次提示无法连接服务器。

becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ netstat -nlp | grep 5432
(No info could be read for "-p": geteuid()=1000 but you should be root.)
becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ sudo netstat -nlp | grep 5432
becker@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ sudo su postgres
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
postgres@DESKTOP-TMLSIJ4:/mnt/c/Users/becker$

3. 运行psql遇到的问题

postgres@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?


becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ netstat -nlp | grep 5432
(No info could be read for "-p": geteuid()=1000 but you should be root.)


becker@DESKTOP-TMLSIJ4:/etc/postgresql/14/main$ sudo netstat -nlp | grep 5432

3. 换个角度，尝试service postgresql start。还是提出出错，但是这次提供的错误说明非常具体，/var/lib/postgresql/14/main的权限无效，应为750或700。

becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ sudo service postgresql start
 * Starting PostgreSQL 14 database server                                                                                                                                            * Error: /usr/lib/postgresql/14/bin/pg_ctl /usr/lib/postgresql/14/bin/pg_ctl start -D /var/lib/postgresql/14/main -l /var/log/postgresql/postgresql-14-main.log -s -o  -c config_file="/etc/postgresql/14/main/postgresql.conf"  exited with status 1:
2022-03-13 00:56:54.525 CST [6060] FATAL:  data directory "/var/lib/postgresql/14/main" has invalid permissions
2022-03-13 00:56:54.525 CST [6060] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).
pg_ctl: could not start server
Examine the log output.

        查看main的权限是766(读写执行+读写+读写），估计除了root权限用户意外，其他用户不应该具被读写权限，因此要换成750或700，我选择改为750（读写执行+写执行+无）。再次运行service postgresql start,这下可以了，服务器顺利启动，psql也可以顺利执行了。（把main 目录的访问权限改为700和710也可以，但是711不行）

becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ ls -l
total 4
drwxrw-rw- 19 postgres postgres 4096 Mar 12 23:46 main
becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ sudo chmod -R 0750 main
becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ ls -l
total 4
drwxr-x--- 19 postgres postgres 4096 Mar 12 23:46 main
becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ sudo service postgresql start
 * Starting PostgreSQL 14 database server

becker@DESKTOP-TMLSIJ4:/var/lib/postgresql/14$ sudo su postgres
postgres@DESKTOP-TMLSIJ4:~/14$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# ls
postgres-# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(3 rows)

4. 总结：之前在阿里云没有碰到这个问题，安装 postgresql以后可以顺利执行psql，主要是因为用root 账号执行相关操作，没有碰到权限问题，在本地运行时由于不是root账号，就需要查看相关的权限。另外，systemctl 命令开启或关闭postgresql服务并没有影响到服务器的运行，启动服务器还是要用root权限的sudo service postgresql start.