> For the complete documentation index, see [llms.txt](https://book.bsdcn.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://book.bsdcn.org/ask/flat/chapter-35-database-management/di-35.2-jie-postgresql.md). # 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](https://www.postgresql.org/about/licence/), 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: ```sh # pkg install postgresql18-server ``` Or install using Ports: ```sh # cd /usr/ports/databases/postgresql18-server/ # make install clean ``` View the configuration information after PostgreSQL installation: ```sh # pkg info -D postgresql18-server ``` ## File Structure The file structure of PostgreSQL is as follows. ```sh / ├── 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: ```sh # 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: ```sh # service postgresql initdb ``` Example output (with internationalization settings; English output is also normal): ```sh # 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: ```sh # psql psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: role "root" does not exist ``` Correct usage: ```sh # 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: ```sh # 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 ```sh # 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: ```sh # 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. ```sh # 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: ```ini 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. ```sh # 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: ```sh # 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: ```sh # 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: ```sh # 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]. . 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. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://book.bsdcn.org/ask/flat/chapter-35-database-management/di-35.2-jie-postgresql.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.