WSL Ubuntu下启动Postgresql碰到的问题_rbk2022_ubuntu启动postgresql

irpas 02-07 3163

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不行）

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