Article directory
-
- illustrate
- 1. Preparations
-
- 1.1 Source package download
- 1.2 Unzip the installation directory
- 1.3 Install dependent packages
- 1.4 Add user
- 1.5 Create a data directory
- 2. Compile and install
-
- 2.1 Source code compilation
- 2.2 Configure environment variables
- 2.3 Initialize the database
- 2.4 Start the database
- 2.5 Connect to the database
- 3. Parameter adjustment
-
- 3.1 Configure pg_hba
- 3.2 Monitoring Related
- 2.4 Log files
- 2.5 Memory parameters
Description
This article introduces the detailed steps of PostgreSQL stand-alone source code compilation and deployment.
1. Preparations
1.1 Source package download
Enter the PostgreSQL official website download page and select the Source column:
Then enter the source version directory, select the version to be installed and download it.
1.2 Unzip the installation directory
After the source package is downloaded, upload it to the server and decompress it:
tar -xf postgresql-14.8.tar.gz
1.3 Install dependencies
yum install gcc gcc-c++ readline-devel readline readline-dev zlib-devel
1.4 Adding users
group add postgres useradd -g postgres postgres
1.5 Create data directory
Create a directory for PostgreSQL to store data:
mkdir -p /data/pgsql/{<!-- -->data,logs}
chown -R postgres:postgres /data/pgsql/
2. Compile and install
2.1 Source code compilation
cd to the source code directory:
cd /opt/postgresql-14.8
Execute configure:
./configure --prefix=/usr/local/pgsql
| Parameter name | Meaning |
|---|---|
| prefix | The software directory is also the installation directory |
| with-perl | Add this parameter when compiling to be able to use the PL/Perl procedural language of perl syntax to write custom function, you need to install the relevant perl development package in advance: libperl-dev |
| with-python | Add this parameter when compiling to use python syntax To write custom functions in PL/Perl procedural language, you need to install the relevant python development kit in advance: python-dev |
| with-blocksize & amp; with-wal-blocksize | By default, the data page size of the PG database is 8KB. If the database is used for data warehouse business, the data page can be adjusted during compilation to improve disk IO |
Compile and install:
make & amp; & amp; make install
After the compilation is complete, the PostgreSQL program file will be generated in the directory specified by the prefix parameter.
2.2 Configure environment variables
According to your actual environment, modify the installation directory and data directory:
vi /etc/profile
export PGHOME=/usr/local/pgsql export PGDATA=/data/pgsql/data export PATH=$PGHOME/bin:$PATH export LD_LIBRARY_PATH=/usr/local/pgsql/lib
source /etc/profile
2.3 Initialize the database
Switch to the postgres user:
su postgres
Perform database initialization with the -D option followed by the data directory:
initdb -D /data/pgsql/data/
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 locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
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 disabled.
fixing permissions on existing directory /data/pgsql/data ... 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
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /data/pgsql/data/ -l logfile start
2.4 Start the database
Enter the logs directory, create a startup log file, and output the startup log to this file:
touch /data/pgsql/logs/start.log
Start PostgreSQL:
pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logs/start.log start
To close the database you can use the following command:
# Close the database pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logs/start.log stop # restart the database pg_ctl -D /data/pgsql/data/ -l /data/pgsql/logs/start.log restart
2.5 Connecting to the database
Start successfully and use psql to enter the database:
>>>>$ psql
psql (14.8)
Type "help" for help.
postgres=# select version();
version
-------------------------------------------------- -------------------------------------------------- -----
PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
3. Parameter adjustment
3.1 Configuring pg_hba
In the PostgreSQL data directory, the pg_hba.conf file is automatically generated. This file is a blacklist access control file, which can control which IP addresses are allowed to access the database. By default, remote access to data is not allowed, so it needs to be configured after installation.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only # Type indicates access mode, local indicates local socket access, DATABASE, USER indicate database and user respectively # The parameter all indicates all databases or users, ADDRESS indicates an address or network segment, and METHOD indicates the verification method # The default trust means full trust, password means to send plaintext password, it is not recommended to use, it is recommended to use md5 mode local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust
You can add the following line to the pg_hba file, which means that any user is allowed to connect to the database remotely, and a password is required when connecting:
host all all 0/0 md5
For details, please refer to the document: pg_hba file description
3.2 Monitoring related
In postgresql.cnf in the data directory, you can find the following:
listen_addresses = 'localhoset' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
Among them, the parameter listen_addresses indicates the listening IP address, and the default is to listen at localhost/127.0.0.1, which will cause the remote host to be unable to access the database. If remote access is required, you need Set it to the actual network address, and set it to * to monitor all addresses, and the modification of this parameter will take effect after restarting.
PS: After configuring steps 3.1 and 3.2, PostgreSQL can support remote connections.
The following table lists other common monitor-related parameters, which can be set as needed:
| parameter | meaning |
|---|---|
| port | The server listens to the TCP port, the default is 5432 |
| max_connections | The maximum number of connections allowed by the server, the default is 100 |
| superuser_reserved_connections | The number of connections reserved for super accounts on the server side, the default is 3 |
| unix_socket_directory | Server listens to the client Unix embedded Socket directory, default /tmp |
2.4 Log files
The following are parameters related to PostgreSQL logs, which generally need to be configured:
| parameter | meaning |
|---|---|
| logging_collector | Whether to open the log |
| log_rotation_age | how many days to generate a new log file |
| log_rotation_size | Generate a new log file over the size |
| log_directory | The log directory can be an absolute path or a relative path relative to PGDATA |
| log_destination | The log record type, the default is stderr, only record error output |
| log_filename | The log file name, the default is postgresql-%Y-%m-%d_%H%M%S.log |
| log_truncate_on_rotation | When the log name already exists, whether to overwrite the original file |
The following are several common configuration templates that generate a new log file every day:
logging_collector = on log_directory = '/data/pgsql/logs' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 1d log_rotation_size = 0
Switch a log whenever a log is full (eg 100MB):
logging_collector = on log_directory = '/data/pgsql/logs' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_truncate_on_rotation = off log_rotation_age = 0 log_rotation_size = 100MB
Only keep the logs of the last 7 days for circular coverage:
logging_collector = on log_directory = '/data/pgsql/logs' log_filename = 'error_log.log' log_truncate_on_rotation = on log_rotation_age = 7d log_rotation_size = 0
2.5 Memory parameters
Students who are familiar with MySQL know that it has a parameter innodb_buffer_pool to limit the size of the innodb engine buffer pool. The larger the buffer pool, the more pages can be cached, which can reduce a lot of disk IO consumption and improve database performance. shared_buffer is similar to MySQL’s buffer pool in PostgreSQL.
| parameter | meaning |
|---|---|
| shared_buffer | Shared memory buffer size, default 128MB |
| temp_buffers | Temporary buffer size used by each session, default 8MB |
| work_mem | Memory cache size required for memory temporary table sorting operation or hash join, default 4MB |
| maintenance_work_mem | Maximum memory usage for maintenance operations (vacuum, create index), default 64M, minimum 1M |
| max_stack_depth | The maximum execution stack on the server Security depth, the default is 2M, if you find that complex functions cannot be executed, you can adjust this parameter appropriately |