postgres 调试篇 --"unix_socket_directories"

1、问题描述

在修改postgresql.conf文件中"unix_socket_directories" 值时，重启数据库生效，但是必须制定 -h /xx/xx来使用 the Unix domain socket 连接数据库。

测试如下：

[wln@localhost postgres9.3]$ cat data/postgresql.conf | grep unix_socket_directories

unix_socket_directories = '/tmp/wln'    # comma-separated list of directories

[wln@localhost postgres9.3]$ psql -d postgres -p 5432

psql: could not connect to server: No such file or directory.

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[wln@localhost postgres9.3]$ psql -d postgres -p 5432 -h /tmp/wln

psql (9.3beta2)

Type "help" for help.

postgres=# \q

[wln@localhost postgres9.3]$ 

2、通过查看源码分析问题

（1）手动分析

(1)src/interfaces/libpq/fe-connect.c

static void

connectFailureMessage(PGconn *conn, int errorno)

{

char sebuf[256];

#ifdef HAVE_UNIX_SOCKETS

if (IS_AF_UNIX(conn->raddr.addr.ss_family))

{

char service[NI_MAXHOST];

pg_getnameinfo_all(&conn->raddr.addr, conn->raddr.salen,

  NULL, 0,

  service, sizeof(service),

  NI_NUMERICSERV);

appendPQExpBuffer(&conn->errorMessage,

 libpq_gettext("could not connect to server: %s\n"

"\tIs the server running locally and accepting\n"

"\tconnections on Unix domain socket \"%s\"?\n"),

 SOCK_STRERROR(errorno, sebuf, sizeof(sebuf)),

 service);

}

(2)Innitdb.c

#ifdef HAVE_UNIX_SOCKETS

snprintf(repltok, sizeof(repltok), "#unix_socket_directories = '%s'",

DEFAULT_PGSOCKET_DIR);

(3)pg_config_manual.h

#define DEFAULT_PGSOCKET_DIR  "/tmp"

#if defined(WIN32)

#undef HAVE_UNIX_SOCKETS

#endif

#define HAVE_UNIX_SOCKETS 1

fe-connect.c

if (pghost && pghost[0] != '\0')

{

if (conn->pghost)

free(conn->pghost);

conn->pghost = strdup(pghost);

}

1. fe-connect.c

修改函数PQconnectStart

---【1】

if (!connectDBStart(conn))

{

conn->status = CONNECTION_BAD;

}

return conn;

在【1】处 添加：

if(!(conn->pghost && conn->pghost[0] != '\0'))

{

if (conn->pghost)

free(conn->pghost);

conn->pghost = strdup(DefaultHost);

}

2、具体错在 

 * src/include/libpq/pqcomm.h

#define UNIXSOCK_PATH(path, port, sockdir) \

snprintf(path, sizeof(path), "%s/.s.PGSQL.%d", \

((sockdir) && *(sockdir) != '\0') ? (sockdir) : \

DEFAULT_PGSOCKET_DIR, \

(port))

---sockdir 的值没传递过来

而 UNIXSOCK_PATH 是由fe-connect.c 文件调用（line 1371 ）：

node = NULL;

hint.ai_family = AF_UNIX;

UNIXSOCK_PATH(portstr, portnum, conn->pgunixsocket);

现在修改了unix_socket_directory ,而默认的DEFAULT_PGSOCKET_DIR 则为/tmp 故出错。

-----------

snprintf(pid_file, MAXPGPATH, "%s/postmaster.pid", pg_data);

could not create lock file "/tmp/fads/.s.PGSQL.5410.lock": 没有那个文件或目录

AddToDataDirLockFile  用来写 postmaster.pid 文件

[wln@localhost ~]$ cat postgres9.3/data/postmaster.pid 

30613

/home/wln/postgres9.3/data

1404760442

5410

/tmp/wln

localhost

  5410001  11894796

 * 1 postmaster PID (or negative of a standalone backend's PID)

 * 2 data directory path

 * 3 postmaster start timestamp (time_t representation)

 * 4 port number

 * 5 first Unix socket directory path (empty if none)

 * 6 first listen_address (IP address or "*"; empty if no TCP port)

 * 7 shared memory key (not present on Windows)

 *

 * Lines 6 and up are added via AddToDataDirLockFile() after initial file

 * creation.

 *

 * The socket lock file, if used, has the same contents as lines 1-5.

 */

#define LOCK_FILE_LINE_PID 1

#define LOCK_FILE_LINE_DATA_DIR 2

#define LOCK_FILE_LINE_START_TIME 3

#define LOCK_FILE_LINE_PORT 4

#define LOCK_FILE_LINE_SOCKET_DIR 5

#define LOCK_FILE_LINE_LISTEN_ADDR 6

#define LOCK_FILE_LINE_SHMEM_KEY 7

--------

(1) postmaster.h 

pg_ctl.c (line 469)

if ((optlines = readfile(pid_file)) != NULL &&

optlines[0] != NULL &&

optlines[1] != NULL &&

optlines[2] != NULL)

Pqcomm.c 文件

函数Lock_AF_UNIX  将 list 类型变量 sock_paths 填充 ，sock_paths 内中值为 unix_socket_directory 中的值

|

》StreamServerPort函数 传递 unix_socket_directory 中的值 

StreamServerPort(int family, char *hostName, unsigned short portNumber,

char *unixSocketDir,

pgsocket ListenSocket[], int MaxListen)

 src/backend/utils/init/miscinit.c  用来创建 sock 文件

CreateSocketLockFile()

void

CreateSocketLockFile(const char *socketfile, bool amPostmaster,

const char *socketDir)

CreateLockFile(const char *filename, bool amPostmaster,

  const char *socketDir,

  bool isDDLock, const char *refName)   --真正用来创建文件的

而 StreamServerPort函数引用 （至此一处）  （--没问题，因为能够正常创建sock文件）

Postmaster.c  中定义

startup.c             

main()          ->   parse_psql_options()  -> process_psqlrc()  -> process_psqlrc_file()

--该过程用时过长且效果不好

（2）gdb 调试

很快分析出函数一步步调用顺序以及什么地方出错。

3 、邮件列表解决问题

能解决问题的办法都是好办法。

不到一个小时便收到9封恢复，非常好的说明了问题及解决了问题。

我的邮件内容如下:

Hi all,

     I change the value of "unix_socket_directories" in postgresql.conf ,  then restart the database, but it cannot connect the database used like this 

"psql -d postgres -p 5432" ,  it must given the parameter " -h /xx/xx" to use the Unix domain socket。

    how to fix   this issue ?

the test steps as below :

[wln@localhost postgres9.3]$ cat data/postgresql.conf | grep unix_socket_directories

unix_socket_directories = '/tmp/wln'    # comma-separated list of directories

[wln@localhost postgres9.3]$ psql -d postgres -p 5432

psql: could not connect to server: No such file or directory.

        Is the server running locally and accepting

        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

[wln@localhost postgres9.3]$ psql -d postgres -p 5432 -h /tmp/wln

psql (9.3beta2)

Type "help" for help.

postgres=# \q

[wln@localhost postgres9.3]$ 

    Thanks,

     waln

---问题解决之道

（1）john r pierce
the client has no access to postgresql.conf, it has no idea you changed 
it. the default value is baked into libpq.so at compile time.
   --点评：是的，可以修改pg_config_manual.h 中 #define DEFAULT_PGSOCKET_DIR  "/tmp" 从而修改默认位置，不过要重新编译安装数据库。
（2）Cheers,Steve
the client has no access to postgresql.conf, it has no idea you changed it. the default value is baked into libpq.so at compile time.


You might find the environment variable PGHOST useful.
   --点评：在~/.bashrc中设置：export PGHOST=/tmp/wln 即可，直接输入psql -d postgres -p 5432 则实际命令为psql -d postgres -p 5432 -h /tmp/wln  (可以通过删除socket锁文件测试)
（3）Nick Guenther
  I'll start by saying that your test case is very clear, and thank you for it. I am unsure what your goal is, however. I assume you are trying to set up parallel postgres processes, for debugging. I've done this recently, for that reason.
First thing to point out is that you need only one of -h and -p.  They are redundant options, because you only connect to postgres either over TCP (-p) or with a unix domain socket (-h).

Second, what you're seeing is necessary. If you change the default, then psql doesn't know where to look. However, you can recover the old behaviour with shell tricks:
$ alias psql='psql -h /xx/xx'
$ psql -d postgres


(Personally, I wrote a short wrapper script called "client.sh" which depth-first searches for a postgres db directory and the runs 'psql -h' with the first one it finds; equally well you could have this script install an alias)


Are you perhaps confused about what a unix domain socket is? Why are you changing it? This is a decent description of it: 
http://www.openbsd.org/cgi-bin/man.cgi/OpenBSD-current/man4/unix.4

--点评：果然一个详细，赞一个。

（4）JohnR Pierce

R Pierce
java/jdbc doesn't support unix domain sockets anyways, so that's moot 
for the OP's issue.

Warning

On Unix, forking a process with open libpq connections can lead to unpredictable results because the parent and child processes share the same sockets and operating system resources. For this reason, such usage is not recommended, though doing an exec from the child process to load a new executable is safe.

（2）http://www.openbsd.org/cgi-bin/man.cgi/OpenBSD-current/man4/unix.4
（3）31.16. The Connection Service File