Postgres Tips and Tricks

Image of Author
March 21, 2022 (last updated September 11, 2023)

Architecture

Every command mentioned can be explored further via the --help flag.

A server controls access to a database cluster. The server program is postgres. A convenience function exists to help users start the server, stop the server, etc., called pg_ctl. For example, pg_ctl start will start the server.

A single server instance manages a collection of databases referred to as a database cluster. To create a cluster, call the initdb command. The only required flag for initdb is -D, aka, --pgdata, the value of which is the directory within which to store all the data in the cluster.

To access the database cluster, you need to connect to it through the server. The server is configured to listen in particular places, either through TCP/IP, or via unix sockets. The most fundamental way to configure the server is via a configuration file in the data directory, postgresql.conf. (Calling initdb will create a default config.) There are other ways of configuring the server, such as passing commands on server start via the shell. Once you connect to the server, you can interact with any database in the cluster, as well as create new databases.

Connecting via unix socket

In the postgresql.conf file is a setting for unix_socket_directories, which by default is /tmp. It controls the location of the unix socket.

(A socket allows for inter-process communication on a single unix machine. By analogy, data moving over a socket is like cars moving over a bridge. You can name a bridge, you can name a socket, i.e., its filename. The reason it might seem confusing is because it's rarely the destination, just like a bridge, but it is still a place, just like a bridge.)

Once you specify the location of the socket, when you start postgres, a socket file will be created in the target directory with a name .s.PGSQL.5432 (or whatever port you specify). You can then connect to that socket via psql to test the connection with psql -h /absolute/path/to/dir/containing/socket -l to list the databases.

Resources