35.2 PostgreSQL

PostgreSQL is a free object-relational database management system (Object-Relational Database Management System, ORDBMS). Its academic origins trace back to the Ingres project at the University of California, Berkeley, where Professor Michael Stonebraker initiated the successor project POSTGRES (meaning "post-Ingres", i.e., the post-Ingres era) in 1986, and Version 1 was released to external users in June 1989.

PostgreSQL uses the PostgreSQL License, which is similar to the BSD License and is likewise a permissive open-source license.

As a database system that strictly adheres to ACID (Atomicity, Consistency, Isolation, Durability) properties, PostgreSQL supports transaction processing, complex query optimization, and data integrity constraints.

Installing PostgreSQL

PostgreSQL provides multiple stable versions to choose from. This section uses PostgreSQL 18 as an example; the installation and configuration methods for other versions are similar.

Install using pkg:

# pkg install postgresql18-server

Or install using Ports:

# cd /usr/ports/databases/postgresql18-server/
# make install clean

View the configuration information after PostgreSQL installation:

# pkg info -D postgresql18-server

File Structure

The file structure of PostgreSQL is as follows.

/
├── etc/
│   ├── login.conf  # Login configuration file
│   ├── rc.conf     # System startup configuration
│   └── passwd      # User password file
├── usr/
│   ├── local/
│   │   ├── etc/
│   │   │   ├── rc.d/
│   │   │   │   └── postgresql  # PostgreSQL startup script
│   │   │   └── periodic/
│   │   │       └── daily/
│   │   │           └── 502.pgsql  # PostgreSQL scheduled backup script
│   │   ├── share/
│   │   │   └── postgresql/
│   │   │       └── odbc.sql  # ODBC compatibility SQL script
│   │   └── bin/
│   │       └── pg_ctl  # PostgreSQL control utility
│   └── ports/
│       └── databases/
│           └── postgresql18-server/  # PostgreSQL 18 Port
└── var/
    └── db/
        └── postgres/
            ├── data18/  # PostgreSQL 18 data directory
            │   └── postgresql.conf  # PostgreSQL configuration file
            ├── main/    # main instance data directory
            └── dev/     # dev instance data directory

Service Management

Set the PostgreSQL service to start on boot:

# service postgresql enable

Initializing the Database

A database cluster is a collection of directories where PostgreSQL stores data, containing all database files and configuration. The initialization method is as follows.

Initialize the PostgreSQL database cluster:

# service postgresql initdb

Example output (with internationalization settings; English output is also normal):

# service postgresql initdb
initdb postgresql
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with this locale configuration:
  locale provider:   libc
  LC_COLLATE:  C
  LC_CTYPE:    zh_CN.UTF-8
  LC_MESSAGES: zh_CN.UTF-8
  LC_MONETARY: zh_CN.UTF-8
  LC_NUMERIC:  zh_CN.UTF-8
  LC_TIME:     zh_CN.UTF-8
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are enabled.

creating directory /var/db/postgres/data18 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the -A or --auth-local and --auth-host options the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/bin/pg_ctl -D /var/db/postgres/data18 -l logfile start

Logging In and Usage

PostgreSQL does not have a root user by default; you need to log in using the postgres user created during installation.

Example output:

# psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

Correct usage:

# su - postgres	    # Switch to the postgres user to manage the PostgreSQL database

-- Start the service; note that the version number after the data directory may differ from the example in this section
$ /usr/local/bin/pg_ctl -D /var/db/postgres/data18 -l logfile start
waiting for server to start .... done
server started

# Create a new user ykla and set a password
$ createuser -sdrP ykla
Enter password for new role:
Enter it again:
$
-- Create a PostgreSQL database
$ createdb new_db
-- Log in to the PostgreSQL database and grant database privileges to user ykla.
$ psql
psql (18rc1)
Type "help" for help.

postgres=# ALTER USER ykla WITH ENCRYPTED PASSWORD 'password';
ALTER ROLE
postgres=#
postgres=# GRANT ALL PRIVILEGES ON DATABASE new_db TO ykla;
GRANT
-- Exit the PostgreSQL database
postgres=# \q
$ exit
root@ykla:~ #

PostgreSQL Service Management

Initialization Recommendations

Initializing the database is recommended using the service postgresql initdb command, which is the most convenient method.

The following explains how to use the postgresql_initdb_flags rc script parameter.

The default value of the postgresql_initdb_flags parameter is "--encoding=utf-8 --lc-collate=C", which means: specifying UTF-8 encoding and C collation.

It is recommended to set it using sysrc as follows:

# sysrc postgresql_initdb_flags="--encoding=utf-8 --lc-collate=C -A scram-sha-256 -W"

Set the PostgreSQL database initialization parameters: UTF-8 encoding, C collation, SCRAM-SHA-256 authentication (a secure authentication protocol based on the Salted Challenge Response Authentication Mechanism), and require a password to be entered.

Explanation:

• -A scram-sha-256 specifies the default authentication method; otherwise, the default authentication method in pg_hba.conf is trust, which allows passwordless login;
• -W requires setting the postgres user password during initialization, eliminating the need to set it after logging in.

This simplifies some initialization operations and also allows customizing initialization options such as encoding and authentication method through the postgresql_initdb_flags parameter.

Managing Multiple Database Instances (Clusters)

PostgreSQL can run multiple instances on a single machine, suitable for scenarios such as testing different configurations and environment isolation.

The PostgreSQL rc script encapsulates these management functions, making them equally convenient.

Below, two named instances, main and dev, will be created.

Configuring Instances

# Add a main instance
# sysrc postgresql_profiles+=main

# Add a dev instance
# sysrc postgresql_profiles+=dev

# You can also set multiple instances at once
# sysrc postgresql_profiles="main dev"

# Corresponding main instance data directory ②
# sysrc postgresql_main_data=/var/db/postgres/main

# Corresponding dev instance data directory ③
# sysrc postgresql_dev_data=/var/db/postgres/dev

# A data directory must be set for each instance
# (see the two entries above)

# Set initialization parameters for the main instance ①
# The dev instance uses default initialization, for comparison
# sysrc postgresql_main_initdb_flags="-E utf-8 -A scram-sha-256 --lc-collate C -W"

# Set enable or disable for each instance
# sysrc postgresql_main_enable=YES   # Set the PostgreSQL service to start on boot
# sysrc postgresql_dev_enable=YES    # Set the PostgreSQL development service to start on boot

Notes:

• ① Placing the instance name in the rc parameter name creates the corresponding instance parameter name.
• ② Parameters such as postgresql_data and postgresql_enable can still be used. These parameters serve as default values for the corresponding instance parameters and are used when the specific instance parameter cannot be found. However, once instance parameters are explicitly configured, the default parameters are no longer used.
• ③ When managing multiple instances, it is recommended to use only parameters with instance names. The advantage of this configuration is clarity and more flexible control, as different instances can have different settings.

Initializing Instance Directories

After configuration is complete, you can initialize the database instances.

Initialize the PostgreSQL database cluster:

# service postgresql initdb

This command initializes both database instances.

You can also specify the instance name at the end of the command to initialize them separately; this method is suitable for adding new instances.

# service postgresql initdb main   # Initialize the PostgreSQL main service database
# service postgresql initdb dev    # Initialize the PostgreSQL development service database

Note

Two different instances must run on different ports, so the port number of the corresponding instance needs to be modified.

The main instance uses the default port 5432 and does not need modification; the dev instance uses port 5433, and its corresponding configuration file needs to be modified.

Edit the /var/db/postgres/dev/postgresql.conf file. Find the port line and modify it as follows:

port = 5433

Set the PostgreSQL listening port to 5433.

Starting, Stopping, and Restarting

After the instance configuration is complete, you can start or stop them.

# service postgresql start     # Start the PostgreSQL service
# service postgresql status    # Check the PostgreSQL service status
# service postgresql restart   # Restart the PostgreSQL service
# service postgresql stop      # Stop the PostgreSQL service

The above operations apply to all instances. Example:

# service postgresql start # Start the service
===> postgresql profile: main
start postgresql
===> postgresql profile: dev
start postgresql
# service postgresql status # Check status
===> postgresql profile: main
status postgresql
pg_ctl: server is running (PID: 2624)
/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2641)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql restart # Restart the service
===> postgresql profile: main
restart postgresql
===> postgresql profile: dev
restart postgresql
# service postgresql status # Check status
===> postgresql profile: main
status postgresql
pg_ctl: server is running (PID: 2685)
/usr/local/bin/postgres "-D" "/var/db/postgres/main"
===> postgresql profile: dev
status postgresql
pg_ctl: server is running (PID: 2701)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql stop # Stop the service
===> postgresql profile: main
stop postgresql
===> postgresql profile: dev
stop postgresql
# service postgresql status # Check status
===> postgresql profile: main
status postgresql
pg_ctl: no server running
===> postgresql profile: dev
status postgresql
pg_ctl: no server running

You can also specify an instance name to operate on a single instance:

# service postgresql start dev     # Start the PostgreSQL development service
# service postgresql restart dev   # Restart the PostgreSQL development service
# service postgresql status dev    # Check the PostgreSQL development service status
# service postgresql stop dev      # Stop the PostgreSQL development service

Example output:

# service postgresql start dev     # Start the PostgreSQL development service
start postgresql
# service postgresql status dev    # Check the PostgreSQL development service status
status postgresql
pg_ctl: server is running (PID: 2807)
/usr/local/bin/postgres "-D" "/var/db/postgres/dev"
# service postgresql restart dev   # Restart the PostgreSQL development service
restart postgresql
# service postgresql stop dev      # Stop the PostgreSQL development service
stop postgresql

References

• The PostgreSQL Global Development Group. A Brief History of PostgreSQL[EB/OL]. [2026-04-17]. https://www.postgresql.org/docs/current/history.html. Documents the complete history of the POSTGRES project from its Ingres successor to PostgreSQL, including key milestones such as the release of Version 1 in June 1989.