It appears that databases/postgresql96-server's rc.d script starts pgsql directly (via su(1)), rather than using daemon(8). The result can be a wasted pty due to the lack of redirecting stdout and stderr prior to process start, if the daemon is started from an interactive shell directly. That pty also continues to hold the username/uid of whomever started postgres itself. Issue likely affects other versions of databases/postgresql*-server. Solution: daemon(8) should be used instead of su(1), particularly with the -f flag, when starting the daemon. There are other example ports which use daemon(8) if the maintainer is unfamiliar with it; I believe databases/mysql*-server is a good example: https://svnweb.freebsd.org/ports/head/databases/mysql80-server/files/mysql-server.in?revision=466508&view=markup You may also find additional flags (such as -u to setuid() in advance) useful. Evidence: Confirmed on stable/11 (r339174). Note that fd 1 (stdout) and fd 2 (stderr) are still being held open and are not /dev/null; pts/0 is being "held" by postgresql. $ w 7:43PM up 51 days, 19:17, 1 user, load averages: 2.08, 2.10, 2.08 USER TTY FROM LOGIN@ IDLE WHAT jdc pts/0 koitsu 7:43PM - w $ sudo service postgresql stop $ sudo service postgresql start LOG: ending log output to stderr HINT: Future log output will go to log destination "syslog". $ ls -l /dev/pts total 0 crw--w---- 1 jdc tty 0x61 Dec 5 19:44 0 $ logout {closes session} {open new session} $ w 7:44PM up 51 days, 19:18, 1 user, load averages: 2.02, 2.08, 2.07 USER TTY FROM LOGIN@ IDLE WHAT jdc pts/1 XXXXXX 7:44PM - w $ ls -l /dev/pts total 0 crw--w---- 1 jdc tty 0x61 Dec 5 19:44 0 crw--w---- 1 jdc tty 0x63 Dec 5 19:44 1 $ sudo pgrep -a -S -t 0 66606 $ ps -auxw | grep 66606 postgres 66606 0.0 0.3 167640 28648 0- S 19:43 0:00.03 /usr/local/bin/postgres -D /postgresql jdc 66709 0.0 0.0 6640 2476 1 R+ 19:45 0:00.00 grep 66606 $ sudo fstat -p 66606 USER CMD PID FD MOUNT INUM MODE SZ|DV R/W postgres postgres 66606 text / 12771700 -rwxr-xr-x 6285760 r postgres postgres 66606 wd /postgresql 4 drwx------ 29 r postgres postgres 66606 root / 2 drwxr-xr-x 512 r postgres postgres 66606 0 /dev 28 crw-rw-rw- null r postgres postgres 66606 1 - - bad - postgres postgres 66606 2 - - bad - postgres postgres 66606 3* internet stream tcp fffff800214fe000 postgres postgres 66606 4* local stream fffff800402bc4b0 postgres postgres 66606 5* pipe fffff800089125f0 <-> fffff80008912758 0 rw postgres postgres 66606 6* pipe fffff80008912758 <-> fffff800089125f0 0 rw postgres postgres 66606 7* local dgram fffff80011d4a2d0 <-> fffff80011cd1780 postgres postgres 66606 8* internet dgram udp fffff800119b6910 Other irrelevant details: $ grep postgresql /etc/rc.conf postgresql_enable="yes" postgresql_data="/postgresql"
Removing expired port, is this still a problem with contemporary versions of PostgreSQL?
Quite rude to close this ticket without doing the proper verification, especially given that all details incl. commands are provided. This should have been looked into over 3 years ago. Anyway... Here is my analysis as of this writing, using FreeBSD 13.1-PRERELEASE as a test system and the latest pkg/ports. It turns out this is/was a PostgreSQL problem and not a FreeBSD problem: databases/postgresql10-server (10.20): problem still exists databases/postgresql11-server (11.15): problem still exists databases/postgresql12-server (12.10): problem no longer present databases/postgresql13-server (13.6): problem no longer present databases/postgresql14-server (14.2): problem no longer present Below numbers refer to PostgreSQL versions and not FreeBSD versions. I tried to go through the PostgreSQL changelog to find out what changed. Since 11.15 is the latest 11.x version, we start with 12.0 and work our way up to 12.10: https://www.postgresql.org/docs/release/ . Things we look for are the terms "stdout", "stderr", "pts", "pty", "tty", and "freebsd" (in case it's FreeBSD-specific). Sadly, we find nothing relevant. We are thus forced to do a git clone of the postgres source and sift through git logs, annotations, and the code ourselves. 12.0 was released on 2019-10-13. "git log" and looking for "daemon" turns up a few possibly relevant commits: c95ede41b8 In count_usable_fds(), duplicate stderr not stdin. bb24439cef Detach postmaster process from pg_ctl's session at server startup. bb24439cef sounds likely, but a git show of the commit doesn't really turn up anything code-wise that looks obviously relevant (it looks signal and SIGINT-related). That commit is also from Mon Jan 14 14:50:58 2019 +0200. The discussion that lead to it, which hints at this possibly being relevant after all: https://www.postgresql.org/message-id/CAEET0ZH5Bf7dhZB3mYy8zZQttJrdZg_0Wwaj0o1PuuBny1JkEw%40mail.gmail.com c95ede41b8 on the other hand seems to be a report about a user going from 12.x to 13.0 and running into issues on OS X, who then later tracked it down as a kind of regression (introduced in 13.x but not present in 12.4), but admitted it had to do with how they were starting pgctl. However, this is from 2020-10-05: https://postgr.es/m/48bafc63-c30f-3962-2ded-f2e985d93e86@emmenlauer.de Their mailing list web interface for pgsql-general does not turn up any results for "tty", but pgsql-hackers turns up quite a lot, but most are during a date long, long prior to 12.0 (and since we know 11.15 does not have the problem fixed, we can safely assume the issue was fixed in 12.x branch at some point). One may want to dig through their REL_12_STABLE branch exclusively. Summary: we do not know what the relevant code change in Postgres is that fixed this between 12.0 and 12.10, and remains fixed in 13.x and 14.x. I would have to try each version to narrow down the timeframe, and I am not willing to spend that amount of time for this. It would not surprise me if one of those two above commits (esp. the latter one) addressed it. I'm glad it's fixed though. I hope this ticket and investigation comes in handy for some other SA running (older) Postgres on FreeBSD and wonders why there's an allocated pty/tty. We can keep this one closed.